CALLING STORED PROCEDURES TO SQL SERVER THROUGH PDO

Discuss everything related to ApPHP MVC Framework. All versions. Any questions related to the Framework developing or using should be posted to this forum.

Moderators: alexandrleonenko, alexmst

hliasmin
Newbie
Newbie
Posts: 8
Joined: May 29th, '10, 14:12
Location: Greece

CALLING STORED PROCEDURES TO SQL SERVER THROUGH PDO

Postby hliasmin » Sep 5th, '17, 21:00

Hallo, again.
While trying to access an SQL Server 2008 R2, i need to call some stored procedures through PDO
I am having serious problems.
Firstly, it is not obvious what procedure to call inside CDatabase ( I thought that the most closer to my needs is the customExec but i am having serious problems). I checked the code inside and saw that the use of PDO::PARAM_INPUT_OUTPUT is missing. ( I think it has to be set because as far as stored procedures and parameters INOUT are conserned, this type of parameter is very sigificant ). I also noticed that the result af calling stored procedure should be array containting the data returned from stored procedures.

So, i tried to fix this problem

I implemented the above classes
/**
* Reads parameters definition passed to stored procedures and extract param value and data type
* @param $key & $param definition
* @return array
*/
private function _prepareStoredProcParams($key, $param_def)
{
if ( empty($param_def) ){
$value = '';
$type = PDO::PARAM_STR;
}else{
$value = $param_def['value'];
$type = intval($param_def['type']);
}
return array($key, $value, $type );
}


/**
* Performs a stored proc execution
* @param string $sql
* @param array $params
* @return array
*/
public function execProc($sql, $params = array()){
if(APPHP_MODE == 'demo'){
self::$_errorMessage = A::t('core', 'This operation is blocked in Demo Mode!');
return false;
}
$startTime = $this->_formattedMicrotime();
try{
if(is_array($params) && !empty($params)){
$sth = $this->prepare($sql);
foreach($params as $key => $_param){
list($key, $value, $data_type ) = $this->_prepareStoredProcParams($key, $_param);
$sth->bindValue($key, $value, $data_type);
}
$sth->execute();
$result = $sth->fetchAll(PDO::FETCH_ASSOC);
}else{
$result = $this->exec($sql);
}
}catch(PDOException $e){
$this->_errorLog('execProc [database.php, ln.:'.$e->getLine().']', $e->getMessage().' => '.$sql);
$result = false;
}

// Interpolate query and save it
$this->_query = $this->_interpolateQuery($sql, $params);

// Save data for debug
if(APPHP_MODE == 'debug'){
$finishTime = $this->_formattedMicrotime();
$sqlTotalTime = round((float)$finishTime - (float)$startTime, 5);
CDebug::addSqlTime($sqlTotalTime);
CDebug::addMessage('queries', ++self::$count.'. query | '.$sqlTotalTime.' '.A::t('core', 'sec').'. | <i>'.A::t('core', 'total').': '.(($result) ? $result : '0 (<b>error</b>)').'</i>', $this->_query);
}
return $result;
}

I am still having problems.
Can you fix these problem?????

Thank you very much
Minogiannis Ilias

administrator
Site Admin
Site Admin
Posts: 6011
Joined: Jan 7th, '09, 23:18
Contact:

Re: CALLING STORED PROCEDURES TO SQL SERVER THROUGH PDO

Postby administrator » Sep 6th, '17, 06:25

I am still having problems.


What exactly the problem?
can you upload your code to any shared hosting to show us this issue online?


Return to “ApPHP MVC Framework {developers/users}”