Location: PHPKode > scripts > SqlCommand Class > sqlcommand-class/SqlCommand.php
<?php
/**
This class is designed to simplify and enhance the use of stored procedures in a
MS SQLServer database through PHP.  It allows you to create command object, and
add parameters to it, somewhat similar to the ADO.Net SqlCommand object.  The
most important features are that it supports type-safety in parameter values (i.e.
if a parameter is meant to be an int then it's value will be forced to an integer
type, while if the parameter is meant to be a date value then it will be validated
as a date value - unsafe, or incorrect parameter values will trigger an error
from the object, as opposed to issuing an unsafe query), and that the class allows
you to easily retrieve return and output parameter values, along with the actual
results of the stored procedure.

The class does not require the use of a PEAR database connection until you 
actually want to execute a query, and in fact allows you to retrieve the exact
SQL query that will be executed as a string value.  However, if you intend to 
actually execute a query from within the class (which is quite likely), you will
need to supply an (open) PEAR database connection to your data source.

NOTES:
		require_once('DB.php');
        Uses the named parameter style of procedure calling, i.e.
            EXEC @lngReturnValue = stpProcedure @Param1 = 'Value1', @Param2 = 'Value2'
        Performs "type-safe" filtering of all values being passed to the database
        Supports return values, output parameters.
        
        Does NOT support input/output parameters.
        Does NOT fully support the use of the GETDATE() function for date parameters.
        Does NOT support passing of datetime values with an actual time portion for date parameters, i.e.
            date values must be of the form "mm/dd/yyyy" and NOT "mm/dd/yyyy hh:mm:ss".

REVISIONS:
	DGC 12-07-2005	Added a check in "_getOutputParamValues" to prevent out of bounds
					of array issue when no query results were returned.
	DGC 9-26-2003	Commented out a section of code in getAll() related to some custom
					processing when DB_FETCHMODE_ORDERED is specified.  Apparently the
					results are returned correctly without this code.

* @version 1.0
* @access public
*/
class SqlCommand
{
    var $_bGetReturnValue;
    var $_sCommandText;
    var $_arrParams;
    var $_arrErrors;
    
    var $_arrOutputValues; // An array of the output parameter values (including return code) from the most recently executed query
    var $_iOutputParamIndicator = 135; //A special flag value used for differentiating parameter output values from actual query results
    
    /* --- Public Methods ---  */

    /**
    * Constructor method for the SqlStoredProcedure_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)
    {
        $this->_sCommandText = ($sCommandText === null ? null : $sCommandText);
        $this->_arrParams = array();
        $this->_arrErrors = array();
        $this->_bGetReturnValue = ($bGetReturnValue === null ? true : $bGetReturnValue);
        return;
    }
    
    /**
    * 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)
    {
        $this->_sCommandText = $sCommandText;
        return;
    }
    
    /**
    * 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)
    {
        $arrCurParam = array(   'name' => $sParamName,
                                'value' => $oParamValue,
                                'type' => $sParamType,
                                'len' => $iParamLength,
                                'output' => $bParamOutput
                                );
        $this->_arrParams[$sParamName] = $arrCurParam;
        return;
    }
    
    /**
    * 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)
    {
        $this->_arrParams[$sParamName]['type'] = $sParamType;
        $this->_arrParams[$sParamName]['len'] = $iParamLength;
        return;
    }
    
    /**
    * 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)
    {
        $this->_arrParams[$sParamName]['value'] = $oParamValue;
        return;
    }
    
    /**
    * 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)
    {
        $this->_arrParams[$sParamName]['output'] = $bParamOutput;
        return;
    }

    /**
    * 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)
    {
    	$arrResult = $this->getAll($oDB);
    	if (DB::isError($arrResult)) {
    		return(false);
    	}
    	else { return(true); }
    }
    
    /**
    * 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)
    {
        $sSql = $this->getSqlText();
        if ($sSql === '') { 
        	array_push($this->_arrErrors, 'getAll: No SQL text returned by query construction.');
        	return(new DB_Error('getAll: No SQL text returned by query construction.')); 
        }
        
        // Execute the Sql command
        $oDB->setFetchmode($mode);
        $oResult = $oDB->query($sSql);
        if (DB::isError($oResult)) {
            // An error occurred; no way to check return values or output parameters
            array_push($this->_arrErrors, 'getAll: Error executing specified SQL text.');
            array_push($this->_arrErrors, $oResult->getMessage());
            return($oResult);
        }
        
        // Get an array of the results from the query
		$arrResultOrig = array();
		while (DB_OK === $oResult->fetchInto($row, $mode)) {
        	$arrResultOrig[] = $row;
		}
		
		
		/*
		// The DB_FETCHMODE_ORDERED style doesn't currently work correctly
		// with MSSQL, apparently, so if that was the result requested
		// then reformat before returning
		if ($mode === DB_FETCHMODE_ORDERED)
		{
			$arrResultNew = array();
			for ($i = 0; $i < count($arrResultOrig); $i++)
			{
				$k = 0;
				for ($j = 0; $j < count($arrResultOrig[$i]); $j+=1)
				{
					$arrResultNew[$i][$k] = $arrResultOrig[$i][$j];
					$k++;	
				}
			}
			
			$arrResultOrig = $arrResultNew;
		}
		*/
        
        // Look for return values and output parameters
		$this->_getOutputParamValues($oResult, $mode, $arrResultOrig, $blnNoOutput);
		if ($blnNoOutput) {
			// Okay, this means there wasn't actually any output from the actual query,
			// all result set(s) were for output parameters
			$arrResultOrig = array();
		}
		
		$oResult->free();
        return($arrResultOrig);
    }
    
    /**
    * 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()
    {
        return($this->_arrErrors);
    }
    
    /**
    * 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()
    {
    	if (array_key_exists('lngStoredProcedureReturnValue', $this->_arrOutputValues)) {
        	return($this->_arrOutputValues['lngStoredProcedureReturnValue']);
    	}
    	else { return(null); }
    }
    
    /**
    * 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)
    {
        // NOTE: Could use array_key_exists to decide whether to return
        //      null or an actual value here
        return($this->_arrOutputValues[$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()
    {
        // Initialize to success
        $bErrors = false;
        $sQuery = '';
        $sQueryPrep = '';
        $sQueryPost = '';
        $iParamCount = 0;
        
        // Make sure the user has specified a query to execute
        if (trim($this->_sCommandText) === '') {
        	array_push($this->_arrErrors, 'getSqlText: No SQL command text specified.');
        	return('');
        }
        
        // Wipe out and prepare for any output parameters
        $this->_arrOutputValues = array();
        
        // Cycle through all the parameters and make sure they're type-safe
        // Build most of the actual query command as we go
        foreach ($this->_arrParams as $arrCurParam) {
            $sParamName = $arrCurParam['name'];
            $oParamValue = $arrCurParam['value'];
            $sParamType = strtolower($arrCurParam['type'] === null ? 'varchar' : $arrCurParam['type']);
            $iParamLength = ($arrCurParam['len'] === null ? 255 : $arrCurParam['len']);
            $bParamOutput = $arrCurParam['output'];
            
            // Allow NULL-valued parameters to be passed through
            if ($oParamValue === null || $oParamValue === 'NULL') {
                $oParamFilterValue = 'NULL';
                $sParamFilterType = 'null';
            }
            else { 
            	$oParamFilterValue = $oParamValue;
            	$sParamFilterType = $sParamType;
            }

            // Make sure the value is safe to be passed
            if ($this->_filterValueByType($oParamFilterValue, $sParamFilterType, $iParamLength)) {
                if ($iParamCount > 0) {
                    // Second (or later) parameter
                    $sQuery .= ', ';
                }
                if ($bParamOutput) {
                    // For output parameters, we have to do a little more work than usual
                    $sQueryPrep .= "DECLARE @$sParamName" . "_Output AS $sParamType";
                    if ($sParamType == 'char' || $sParamType == 'varchar') {
                        $sQueryPrep .= "($iParamLength)";
                    }
                    $sQueryPrep .= "; ";
                    
                    $sQuery .= "@$sParamName = @$sParamName" . '_Output OUTPUT';
                    $sQueryPost .= $this->_getOutputParamSelect($sParamName . '_Output');
                }
                else {
                    // Input parameter, simple name = value pairing
                    $sQuery .= "@$sParamName = $oParamFilterValue";
                }
            }
            else {
                array_push($this->_arrErrors, "getSqlText: Invalid ($sParamType) value specified for $sParamName.");
                $bErrors = true;
            }
            
            // Count how many parameters we're using
            $iParamCount++;
        }
        
        // Are we allowed/supposed to look for return values?
        if ($this->_bGetReturnValue) {
            $sQueryPrep = 'DECLARE @lngStoredProcedureReturnValue int; ' . $sQueryPrep;
            $sQueryPost = $this->_getOutputParamSelect('lngStoredProcedureReturnValue') . $sQueryPost;
            $sQuery = 'EXEC @lngStoredProcedureReturnValue = ' .
                      $this->_sCommandText . ' ' . $sQuery . '; ';
        }
        else { $sQuery = 'EXEC ' . $this->_sCommandText . ' ' . $sQuery . '; ';}
        
        // If anything went wrong, return an empty string as an error indicator
        // Otherwise return the full set of Sql text that should be executed
        if ($bErrors) {
            return('');
        }
        else { return($sQueryPrep . $sQuery . $sQueryPost); }
    }

    /* --- Private Methods ---  */
    
    /**
    * Builds and returns a SQL string for selecting the value of an output parameter; this
    *		particular construction is used to make it possible to differentiate results
    *		of output parameter selection from results of the actual query.  There appears
    *		to be no other way in PEAR to differentiate among multiple result sets.
    * @param string $sParamName The name of the output parameter to be used in the query
    * @return string
    * @access public
    */
    function _getOutputParamSelect($sParamName)
    {
        return('SELECT ' . $this->_iOutputParamIndicator . " AS intOutputParamMode, '$sParamName' AS strOutputParamName, @" . $sParamName . " AS $sParamName; ");
    }
    
    /**
    * Retrieves all output parameter values (including return values) from the query
    *		query results.  Also performs a check to see if the first result set returned
    * 		by the query was really the results of the actual command, or simply those
    *		returned by the output parameters for this query.
    * @param object $oResult  The PEAR DB_Result handle for that was returned on execution
    *		of the current command query
    * @param object $mode  The PEAR mode with which the query was executed
    * @param object $arrFirstResult  An array of the first set of results returned by the query
    * @param boolean $blnNoOutput  The parameter is used to show whether or not the first
    *		set of results from the query, where in fact for the command itself, or were a
    *		result of the output parameters being returned by the query.  True indicates
    *		the first result set are actual results, while False indicates that there were
    *		no true results returned by the query.
    * @return boolean
    * @access public
    */
    function _getOutputParamValues($oResult, $mode, $arrFirstResult, &$blnNoOutput)
    {
    	// Set the flag that states that the first result set found
    	// was in fact output from the query itself, and not just the
    	// return/output parameters
    	$blnNoOutput = false;
    	
        // Build an array of all output parameters to check (including the return code, if necessary)
        $arrOutputParams = array();
        if ($this->_bGetReturnValue) {
	        $arrOutputParams['lngStoredProcedureReturnValue'] =
    	            array(  'name' => 'lngStoredProcedureReturnValue',
        	                'checkname' => 'lngStoredProcedureReturnValue'
            	            );
        }
        foreach ($this->_arrParams as $arrCurParam) {
           if ($arrCurParam['output']) {
               $sParamName = $arrCurParam['name'];
               $arrOutputParams[$sParamName] =
                    array ( 'name' => $sParamName,
                            'checkname' => $sParamName . '_Output'
                            );
            }
        }
        
        // If there's nothing to do, then just leave (successfully!)
        if (count($arrOutputParams) === 0) { return(true); }

        // If this is an associative array, then check for flags by name
        // Otherwise, use ordinal positions
        if ($mode === DB_FETCHMODE_ASSOC) {
            $oParamModeCol = 'intOutputParamMode';
            $oParamNameCol = 'strOutputParamName';
        }
        else {
            $oParamModeCol = 0;
            $oParamNameCol = 1;
        }

        // Now look for each of these output values
        $iResultSet = 0;
        $bCheckResults = true;
        $arrResult = $arrFirstResult;
        do {
            // Test some special flags that we've worked into our output selection
            // queries to ensure that we're not looking at some results from the
            // actual stored procedure query (see _getOutputParamSelect)
            if (count($arrResult) > 0) {
	            if (is_array($arrResult[0])) {
		            if (array_key_exists($oParamModeCol, $arrResult[0]) &&
		                array_key_exists($oParamNameCol, $arrResult[0])) {
		                if ($arrResult[0][$oParamModeCol] === $this->_iOutputParamIndicator)
		                {
		                    foreach ($arrOutputParams as $arrCurParam) {
		                        if ($arrResult[0][$oParamNameCol] == $arrCurParam['checkname']) {
		                            if ($mode == DB_FETCHMODE_ASSOC) {
		                                $oParamValueCol = $arrCurParam['checkname'];
		                            }
		                            else { $oParamValueCol = 2; }
		
		                            $oParamValue = $arrResult[0][$oParamValueCol];
		                            $this->_arrOutputValues[$arrCurParam['name']] = $oParamValue;
		                            
		                        	// If this is the first result set, then there couldn't
		                        	// have been any actual output from the query itself
		                            if ($iResultSet === 0) { $blnNoOutput = true; }
		                        }
		                    }
		                }
		            }
	            }
            }

            // See if there are more result sets to check
            if ($oResult->nextResult()) {
            	$arrResult = array();
				if ($oResult->fetchInto($row, $mode) === DB_OK) {
					array_push($arrResult, $row);
					$iResultSet++;
				}
				else { return(false); }
            }
            else { $bCheckResults = false; }
        }
        while ($bCheckResults);
        
        return(true);
    }
    
    /**
    * Ensures that the specified value is safe for use in the specified data
    * type within the database.
    * @param string &$oValue The value to be filtered (and returned with database safe contents)
    * @param string $sFieldType The abbreviated type description for this field
    * @param integer $iFieldLength For string/text fields, the maximum length of data values
    * @return boolean
    * @access private
    */
    function _filterValueByType(&$oValue, $sFieldType, $iFieldLength)
    {
        // Default to failure
        $bResult = false;

        switch ($sFieldType) {
        case 'bit' :
            $oValue = abs(intval($oValue));
            if ($oValue > 1) { $oValue = 1; }
            $bResult = true;
            break;
        case 'int' :
        case 'integer' :
            $oValue = intval($oValue);
            $bResult = true;
            break;
        case 'smallint' :
            $oValue = intval($oValue);
            // The min and max cutoffs used here are based on SQLServer 2000
            if (($oValue < -32768) || ($oValue > 32767)) {
                $bResult = false;
            }
            else { $bResult = true; }
            break;
        case 'tinyint' :
            $oValue = intval($oValue);
            // The min and max cutoffs used here are based on SQLServer 2000
            if (($oValue < 0) || ($oValue > 255)) {
                $bResult = false;
            }
            else { $bResult = true; }
            break;
        case 'real' :
        case 'float' :
        case 'money' :
        case 'currency' :
    	    $oValue = doubleval($oValue);
            $bResult = true;
            break;
        case 'blob' :
        case 'text' :
        case 'char' :
        case 'string' :
        case 'varchar' :
        	if (trim($oValue) == '') {
        		// Allow for NULL string values
        		$oValue = 'NULL';
        	}
        	else {
	            // Make sure we're within the limits of the field size
        		if ($sFieldType != 'text' && $sFieldType != 'blob') {
    		        if ($iFieldLength > 0) {
        		        if (strlen($oValue) > $iFieldLength) {
            		        $oValue = substr($oValue, 0, $iFieldLength);
                		}
		            }
        		}
	
    		    // *** Watch out for unfiltered single quotes
        	    $oValue = str_replace("''", '&*?&*?', $oValue);
            	$oValue = str_replace("'", "''", $oValue);
	            $oValue = str_replace('&*?&*?', "''", $oValue);
    	        // ***
            
        	    $oValue = "'$oValue'";
        	}
        	
            $bResult = true;
            break;
        case 'date' :
        case 'datetime' :
        case 'smalldatetime' :
        	if (trim($oValue) == '') {
        		// Allow for NULL date values
        		$oValue = 'NULL';
        		$bResult = true;
        	}
        	else if (strtoupper($oValue) == 'GETDATE()') {
	            // Allow for the use of a server-side timestamp via GETDATE()
                $bResult = true;
            }
            else {
                // Make sure the incoming field looks like a date mm/dd/yyyy
                $oValue = eregi_replace('-', '/', $oValue);
                if (preg_match('/^[0-9][0-9]*\/[0-9][0-9]*\/[0-9][0-9]*$/', $oValue)) {
                    $oValue = "'$oValue'";
                    $bResult = true;
                }
            }
            break;
        case 'null' :
            if (strtoupper($oValue) == 'NULL') {
                $oValue = 'NULL';
                $bResult = true;
            }
            break;
        default :
            // Unknown field type
            $bResult = false;
            break;
        }

        return($bResult);
    }
}
?>
Return current item: SqlCommand Class