Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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    
share|improve this question
1  
What's wrong with what you are already doing? –  Yannis Rizos Sep 15 '12 at 5:34
    
it seems i m writing SQL code in PHP.... i don't want that ! –  Bhavin Rana Sep 15 '12 at 5:37
    
@gnat What you have Edited ? –  Bhavin Rana Sep 15 '12 at 6:01
1  
@BhavinRana: click on the "edit x time ago" link above the editor's avatar, you'll see exactly what was changed. –  Mat Sep 15 '12 at 6:40

1 Answer 1

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;
}


}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.