theCalico.com
 

The SqlCommand Class - Readme

November 2003

Introduction
Method Description
Example Usage

Introduction

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.

Method Description

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

Example Usage

Example 1

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.

GetCustomerList @StoreId int, @CustomerType varchar(50)
For a case like this, configuration and use of the SqlCommand class would be something along the lines of this:
$oQuery = new SqlCommand('GetCustomerList');
$oQuery->addParam('StoreId', $lngStoreId, 'int');
$oQuery->addParam('CustomerType', $strCustomerType, 'varchar', 50);
 
// Assume you have an open PEAR database connection ($pearDB)
$arrResults = $oQuery->getAll($pearDB);
The final array ($arrResults) is the recordset for your query.
 
Example 2

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 getAll() method takes a $mode 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:

$arrResults = $oQuery->getAll($pearDB, DB_FETCHMODE_ASSOC); //This is the default fetch mode
 
$arrResults = $oQuery->getAll($pearDB, DB_FETCHMODE_ORDERED);
Example 3

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:

UpdateCustomerInfo @CustomerId int, @CustomerName varchar(50)
To call such a stored procedure without regard to return values or output, you can simply use the execute() method of the SqlCommand class instead of getAll() as shown:
$oQuery = new SqlCommand('UpdateCustomerInfo');
$oQuery->addParam('CustomerId', $lngCustomerId, 'int');
$oQuery->addParam('CustomerName', $strCustomerName, 'varchar', 50);
 
// Assume you have an open PEAR database connection ($pearDB)
$oQuery->execute($pearDB);
Example 4

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:

$returnValue = $oQuery->getReturnValue();
Example 5

You want to call a stored procedure that uses output parameters, as in the following definition:

GetCustomerList @StoreId int, @CustomerType varchar(50), @CustomerCount int OUTPUT
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:
$oQuery = new SqlCommand('GetCustomerList');
$oQuery->addParam('StoreId', $lngStoreId, 'int');
$oQuery->addParam('CustomerType', $strCustomerType, 'varchar', 50);
$oQuery->addParam('CustomerCount', null, 'int');
 
//One extra method call is required to configure a parameter for output
$oQuery->setParamDirection('CustomerCount', true);
 
// Assume you have an open PEAR database connection ($pearDB)
$arrResults = $oQuery->getAll($pearDB);
 
// Now retrieve the value of your output parameter
$count = $oQuery->getOutputValue('CustomerCount');
Example 6

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:

// **
// Syntax 1 (Longer version)
// **
$oQuery->addParam('StoreId');
$oQuery->setParamType('StoreId', 'int');
 
$oQuery->addParam('CustomerType');
$oQuery->setParamType('CustomerType', 'varchar', 50);
 
// ...at any point later in your code, set the parameter values
$oQuery->setParamValue('StoreId', $lngStoreId);
$oQuery->setParamValue('CustomerType', $strCustomerType);
 
// **
// Syntax 2 (Shorter version)
// **
$oQuery->addParam('StoreId', null, 'int');
 
$oQuery->addParam('CustomerType', null, 'varchar', 50);
 
// ...at any point later in your code, set the parameter values
$oQuery->setParamValue('StoreId', $lngStoreId);
$oQuery->setParamValue('CustomerType', $strCustomerType);
Example 7

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:

$oQuery = new SqlCommand('UpdateCustomerInfo');
$oQuery->addParam('CustomerId', null, 'int');
$oQuery->addParam('CustomerName', null, 'varchar', 50);
 
for ($i = i; $i < 10; $i++) {
    $oQuery->setParamValue('CustomerId', $i);
    $oQuery->setParamValue('CustomerName', $_GET["Name_$i"]);
 
    // Assume you have an open PEAR database connection ($pearDB)
    $oQuery->execute($pearDB);
}
Pumpkin the cat with a rubber band
08/03/2014 10:13:31

Valid XHTML 1.0 Transitional