Calling MSSQL stored procedure from Zend Controller ? Any other approaches

sqlsql serverstored-procedureszend-framework

MSSQL and DB, Zend as PHP Framework,

I am using this way to call SP with I/P Parameters and to get O/p Parameters.

It seems I am writing SQL code in PHP.

Any other good approaches?

 $str1 = "DECLARE @Msgvar   varchar(100); DECLARE @last_id int;

exec DispatchProduct_m_Ins $DispatchChallanId,'$FRUNo',$QTY,$Rate,$Amount,
".$this->cmpId.",".$this->aspId.",".$this->usrId.",@Msg = @Msgvar OUTPUT,@LAST_ID = @last_id OUTPUT;

SELECT  @Msgvar AS N'@Msg',@last_id AS '@LAST_ID'; ";//Calling SP


$stmt = $db->prepare($str1);

$stmt->execute();
$rsDispProd = $stmt->fetchAll();


$DispatchProductId   = $rsDispProd[0]["@LAST_ID"];//get last ins ID as O/p Parameter

Best Answer

Unfortunately I am not using the Zend Framework but I did good experiences using a dedicated class for the stored procedure stuff which handles the parameters, do the queries and is dealing with the results.

My Model extends the class SPData and adds the methods according to the given SPs. PHP PDO with MS SQL Drivers are used to call the available stored procedures.

Example using a SP to get an array as a result:

public function getTransactionList($intLimit=0){
    $intUserId = $this->getCurrentUserId();
    if (false == $intUserId) return false;
    $strStatement = '{? = call web.usp_GetTransactionList(?,?)}';
    $this->setParam(null, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT, 4);
    $this->setParam($intUserId);
    $this->setParam($intLimit);
    $arr = $this->doCall($strStatement);
    return $arr;
}

Example using a SP to get a speciefed output parameter as a result:

public function getBalance(){
    $intUserId = $this->getCurrentUserId();
    if (false == $intUserId) return false;
    $strStatement = '{? = call web.usp_GetBalance(?,?)}';
    $this->setParam(null, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT, 4);
    $this->setParam($intUserId);
    $this->setParam(null, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT, 30);
    $this->doCall($strStatement);
    $arrParam = $this->getParam(3);
    return (float)$arrParam['value'];
}

Source code of the SPData Class:

class SPData {

protected $con=false;
protected $arrParams=array();
protected $arrParamsBackup=array();
protected $intColCount = 0;
protected $rs=array();

protected function getConnection() {
    if (false == isset($GLOBALS['dbcon']))
        try {
            $GLOBALS['dbcon'] = new PDO('sqlsrv:Server='.DB_SERVER.';database='.DB_DB, DB_USER, DB_PASS, array('ConnectionPooling' => 0));
        } catch (PDOException $e) {
            die('Connection failed: '.$e->getMessage());
        }
    return $GLOBALS['dbcon'];
}

protected function resetParams(){
    $this->arrParams=array();
}

protected function backupParams(){
    $this->arrParamsBackup = $this->arrParams;
}

public function setParam($strValue, $intParams=PDO::PARAM_INT, $intLength=null){
    $intIdx = count($this->arrParams);
    $intIdx++;

    if ($intLength == null)
        $this->arrParams[$intIdx] = array('value'=>$strValue, 'params'=>$intParams);
    else
        $this->arrParams[$intIdx] = array('value'=>$strValue, 'params'=>$intParams, 'length'=>$intLength);
}

public function getParam($intIdx){ // from backup array!
    return $this->arrParamsBackup[$intIdx];
}

public function doCall($strStatement){

    if (false == $this->con) $this->con = $this->getConnection();

    $stmt = $this->con->prepare($strStatement);
    foreach ($this->arrParams as $key => $value){
        if (isset($value['length'])) $stmt->bindParam($key, $this->arrParams[$key]['value'], $this->arrParams[$key]['params'],$this->arrParams[$key]['length']);
        else $stmt->bindParam($key, $this->arrParams[$key]['value'], $this->arrParams[$key]['params']);
    }

    $stmt->execute();
    $arrReturn = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $this->intColCount = count($arrReturn);

    $stmt->closeCursor();

    $this->backupParams(); // backup for getting output params
    $this->resetParams();
    unset($stmt);

    return $arrReturn;
}

public function getColCount(){
    return $this->intColCount;
}


}