Location: PHPKode > scripts > SqlCommand Class > sqlcommand-class/sqlcommand_readme.html
<?php
$strPageTitle_g = 'Software - The SqlCommand Class';
$strPageBody_g = <<<END
<h2>The SqlCommand Class - Readme</h2>
<h4>November 2003</h4>

<p>
<a href="#Introduction">Introduction</a><br />
<a href="#Methods">Method Description</a><br />
<a href="#Examples">Example Usage</a><br />
</p>

<a name="Introduction"><h3>Introduction</h3></a>
The SqlCommand class is designed to provide a simple, effective, and yet powerful
way to use Microsoft SQL Server stored procedures through PHP, with the aid of
the PEAR database abstraction layer.  It provides a readable method of declaring
and configuring stored procedures in your PHP code, performs strong parameter
"typing" of all values passed to the database, and allows you to easily retrieve
return and output parameter values from your stored procedures.

<a name="Methods"><h3>Method Description</h3></a>
This is the full listing of all publicly accessible methods to the class.<br />
<code>
/**<br />
* Constructor method for the SqlCommand class<br />
* @param string \$sCommandText The string value of the command (ex: "stpGetClientList")<br />
* @param boolean \$bGetReturnValue Whether or not to check return values for this command; default is TRUE<br />
* @return void<br />
* @access public<br />
*/<br />
function SqlCommand(\$sCommandText = null, \$bGetReturnValue = null)<br />
&nbsp;<br />
/**<br />
* Sets the command text of the query to execute<br />
* @param string \$sCommandText The string value of the command (ex: "stpGetClientList")<br />
* @return void<br />
* @access public<br />
*/<br />
function setCommandText(\$sCommandText)<br />
&nbsp;<br />
/**<br />
* Adds a parameter value to the query.  Optionally sets any or all details<br />
*       about the parameter including value, field type, and direction.<br />
* @param string \$sParamName The name of the parameter (ex: "lngClientId")<br />
* @param object \$oParamValue The value to use for the parameter<br />
* @param string \$sParamType The field type of the parameter, currently using<br />
*       normal SQLServer syntax (ex: "int", "bit", "varchar", "datetime")<br />
* @param integer \$iParamLength For string/text parameters, this is the maximum<br />
*       number of characters that should be allowed<br />
* @param boolean \$bParamOutput True/false for whether or not the parameter<br />
*       is an output parameter<br />
* @return void<br />
* @access public<br />
*/<br />
function addParam(\$sParamName, \$oParamValue = null, \$sParamType = null, \$iParamLength = null, \$bParamOutput = false)<br />
&nbsp;<br />
/**<br />
* Sets the field type for a given parameter that has already been added to the<br />
*       query command<br />
* @param string \$sParamName The name of the parameter being modified<br />
* @param string \$sParamType The field type of the parameter, currently using<br />
*       normal SQLServer syntax (ex: "int", "bit", "varchar", "datetime")<br />
* @param integer \$iParamLength For string/text parameters, this is the maximum<br />
*       number of characters that should be allowed<br />
* @return void<br />
* @access public<br />
*/<br />
function setParamType(\$sParamName, \$sParamType, \$iParamLength = null)<br />
&nbsp;<br />
/**<br />
* Sets the value for a given parameter that has already been added to the<br />
*       query command<br />
* @param string \$sParamName The name of the parameter being modified<br />
* @param object \$oParamValue The value to use for the parameter<br />
* @return void<br />
* @access public<br />
*/<br />
function setParamValue(\$sParamName, \$oParamValue)<br />
&nbsp;<br />
/**<br />
* Sets the direction for a given parameter that has already been added to the<br />
*       query command<br />
* @param string \$sParamName The name of the parameter being modified<br />
* @param boolean \$bParamOutput True/false for whether or not the parameter<br />
*       is an output parameter<br />
* @return void<br />
* @access public<br />
*/<br />
function setParamDirection(\$sParamName, \$bParamOutput)<br />
&nbsp;<br />
/**<br />
* Executes the current command query and simply returns true/false on completion.<br />
* Intended for use with action queries (INSERT, UPDATE, DELETE) where no result<br />
* set is returned.<br />
* @param object \$oDB  An instance of an open PEAR database connection, or<br />
*           object that implements a similar interface<br />
* @return boolean<br />
* @access public<br />
*/<br />
function execute(\$oDB)<br />
&nbsp;<br />
/**<br />
* Executes the current command query<br />
* @param object \$oDB  An instance of an open PEAR database connection, or<br />
*           object that implements a similar interface<br />
* @param object \$mode  The PEAR mode to use when building the result array,<br />
*			i.e. either associative (DB_FETCHMODE_ASSOC, default) or ordinal<br />
*			(DB_FETCHMODE_ORDERED)<br />
* @return object An array of results from the current command query<br />
* @access public<br />
*/<br />
function getAll(\$oDB, \$mode = DB_FETCHMODE_ASSOC)<br />
&nbsp;<br />
/**<br />
* Returns the array of errors (if any) that have been generated during the<br />
*       use of this instance of the class<br />
* @return string array<br />
* @access public<br />
*/<br />
function getErrors()<br />
&nbsp;<br />
/**<br />
* Gets the return value (if any) of the previously executed command query.<br />
*       If return values are not being checked, or no return was found, the<br />
*       value should be null.<br />
* @return integer<br />
* @access public<br />
*/<br />
function getReturnValue()<br />
&nbsp;<br />
/**<br />
* Gets the output value (if any) of the specified parameter from the previously<br />
*		executed command query.<br />
* @param string \$sParamName  The name of the output parameter to check<br />
* @return object<br />
* @access public<br />
*/<br />
function getOutputValue(\$sParamName)<br />
&nbsp;<br />
/**<br />
* Builds and returns the full Sql text that must be executed for this<br />
*       query command.  Any errors will trigger an empty string to<br />
*       be returned as the function value.<br />
* @return string<br />
* @access public<br />
*/<br />
function getSqlText()<br />
</code>

<a name="Examples"><h3>Example Usage</h3></a>
<strong>Example 1</strong>
<p>
You have a stored procedure that you would like to use to return a
recordset (the result of some kind of SELECT statement).  Such a procedure might
have a definition similar to the following.
<blockquote><code>
GetCustomerList @StoreId int, @CustomerType varchar(50)
</code></blockquote>
For a case like this, configuration and use of the SqlCommand class would be
something along the lines of this:
<blockquote><code>
\$oQuery = new SqlCommand('GetCustomerList');<br />
\$oQuery->addParam('StoreId', \$lngStoreId, 'int');<br />
\$oQuery->addParam('CustomerType', \$strCustomerType, 'varchar', 50);<br />
&nbsp;<br />
// Assume you have an open PEAR database connection (\$pearDB)<br />
\$arrResults = \$oQuery->getAll(\$pearDB);<br />
</code></blockquote>
The final array (<code>\$arrResults</code>) is the recordset for your query.<br />
&nbsp;<br />
</p>

<strong>Example 2</strong>
<p>
You want to call a stored procedure that does a simple SELECT, but you need
to control what fetchmode the results are returned with (i.e. ordinal/ordered versus
associative).  The <code>getAll()</code> method takes a <code>\$mode</code> parameter
identical to that used in the method of the same name in the PEAR database library,
so you can specify either of these choices:
<blockquote><code>
\$arrResults = \$oQuery->getAll(\$pearDB, DB_FETCHMODE_ASSOC); //This is the default fetch mode<br />
&nbsp;<br />
\$arrResults = \$oQuery->getAll(\$pearDB, DB_FETCHMODE_ORDERED);<br />
</code></blockquote>
</p>

<strong>Example 3</strong>
<p>
You want to use a stored procedure to perform some type of data
modification (INSERT, UPDATE, or DELETE), and you don't care what results
are output or returned.   Such a procedure would likely resemble the following:
<blockquote><code>
UpdateCustomerInfo @CustomerId int, @CustomerName varchar(50)
</code></blockquote>
To call such a stored procedure without regard to return values or output, you
can simply use the <code>execute()</code> method of the SqlCommand class instead
of <code>getAll()</code> as shown:
<blockquote><code>
\$oQuery = new SqlCommand('UpdateCustomerInfo');<br />
\$oQuery->addParam('CustomerId', \$lngCustomerId, 'int');<br />
\$oQuery->addParam('CustomerName', \$strCustomerName, 'varchar', 50);<br />
&nbsp;<br />
// Assume you have an open PEAR database connection (\$pearDB)<br />
\$oQuery->execute(\$pearDB);<br />
</code></blockquote>
</p>

<strong>Example 4</strong>
<p>
You want to use a stored procedure that yields an actual return value, and
would like to be able to retrieve that particular value.  In this case, you
would configure your SqlCommand object exactly as in Example 2.  You can then
retrieve the return value with the following code:
<blockquote><code>
\$returnValue = \$oQuery->getReturnValue();
</code></blockquote>
</p>
<strong>Example 5</strong>
<p>
You want to call a stored procedure that uses output parameters, as in the
following definition:
<blockquote><code>
GetCustomerList @StoreId int, @CustomerType varchar(50), @CustomerCount int OUTPUT
</code></blockquote>
In this case you can modify your configuration of the SqlCommand class, and easily
obtain the value of this new output parameter as in the following:
<blockquote><code>
\$oQuery = new SqlCommand('GetCustomerList');<br />
\$oQuery->addParam('StoreId', \$lngStoreId, 'int');<br />
\$oQuery->addParam('CustomerType', \$strCustomerType, 'varchar', 50);<br />
\$oQuery->addParam('CustomerCount', null, 'int');<br />
&nbsp;<br />
//One extra method call is required to configure a parameter for output<br />
\$oQuery->setParamDirection('CustomerCount', true);<br />
&nbsp;<br />
// Assume you have an open PEAR database connection (\$pearDB)<br />
\$arrResults = \$oQuery->getAll(\$pearDB);<br />
&nbsp;<br />
// Now retrieve the value of your output parameter<br />
\$count = \$oQuery->getOutputValue('CustomerCount');<br />
</code></blockquote>
</p>

<strong>Example 6</strong>
<p>
You want to use the SqlCommand class, but won't know the exact values of your
parameters at the time when you configure the object itself (i.e. you know
parameter names but not necessarily values until later in your code).  Each of
the following sets of code are equivalent with the shortest style of syntax
shown above, so use whichever one most closely suits your needs:
<blockquote><code>
// **<br />
// Syntax 1 (Longer version)<br />
// **<br />
\$oQuery->addParam('StoreId');<br />
\$oQuery->setParamType('StoreId', 'int');<br />
&nbsp;<br />
\$oQuery->addParam('CustomerType');<br />
\$oQuery->setParamType('CustomerType', 'varchar', 50);<br />
&nbsp;<br />
// ...at any point later in your code, set the parameter values<br />
\$oQuery->setParamValue('StoreId', \$lngStoreId);<br />
\$oQuery->setParamValue('CustomerType', \$strCustomerType);<br />
&nbsp;<br />
// **<br />
// Syntax 2 (Shorter version)<br />
// **<br />
\$oQuery->addParam('StoreId', null, 'int');<br />
&nbsp;<br />
\$oQuery->addParam('CustomerType', null, 'varchar', 50);<br />
&nbsp;<br />
// ...at any point later in your code, set the parameter values<br />
\$oQuery->setParamValue('StoreId', \$lngStoreId);<br />
\$oQuery->setParamValue('CustomerType', \$strCustomerType);<br />
</code></blockquote>
</p>
<strong>Example 7</strong>
<p>
You have a stored procedure that you need to call repeatedly from within a single
page, changing some or all of the paramter values for each call.  This is a common
situation when you need to loop through a list of options and save whether or not
each was selected, for instance.  In cases like this, you configure your SqlCommand
object in advance, and then set (or reset) the parameter values each time you
need to call the query:
<blockquote><code>
\$oQuery = new SqlCommand('UpdateCustomerInfo');<br />
\$oQuery->addParam('CustomerId', null, 'int');<br />
\$oQuery->addParam('CustomerName', null, 'varchar', 50);<br />
&nbsp;<br />
for (\$i = i; \$i < 10; \$i++) {<br />
&nbsp;&nbsp;&nbsp;    \$oQuery->setParamValue('CustomerId', \$i);<br />
&nbsp;&nbsp;&nbsp;    \$oQuery->setParamValue('CustomerName', \$_GET["Name_\$i"]);<br />
&nbsp;<br />
&nbsp;&nbsp;&nbsp;    // Assume you have an open PEAR database connection (\$pearDB)<br />
&nbsp;&nbsp;&nbsp;    \$oQuery->execute(\$pearDB);<br />
}<br />
</code></blockquote>
</p>
END;

require_once('../calico_template.php');
?>
Return current item: SqlCommand Class