Purtroppo non utilizzo Zend Framework ma ho fatto esperienze positive usando una classe dedicata per le stored procedure che gestiscono i parametri, eseguono le query e gestiscono i risultati.
Il mio modello estende la classe SPData e aggiunge i metodi in base agli SP specificati.
PHP PDO con MS SQL Drivers sono usati per chiamare le stored procedure disponibili.
Esempio di utilizzo di un SP per ottenere un array come risultato:
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;
}
Esempio che utilizza un SP per ottenere come risultato un parametro di output specchiato:
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'];
}
Codice sorgente della classe SPData:
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;
}
}