note 109326 added to function.mssql-execute
| From: | skari | Date: | Fri, 06 Jul 2012 14:11:38 +0000 |
| Subject: | note 109326 added to function.mssql-execute | ||
| Groups: | php.notes | ||
| Request: | Send a blank email to php-notes+get-189052@lists.php.net to get a copy of this message | ||
want to share with you that I had a hard time with mssql_execute on a platform that was running php
using FastCGI. Well, I guess the problem was that mssql_init always returned null. So after
investigating this, and plus the fact that I don't have total control over the platform, I gave
up and used mssql_query instead.
Using the function below, it is possible to generate most stored procedure calls, with the added
benefits that one doesn't have to worry to much about datatype mappings, and that the funtion
return exception codes and error message. So here goes:
function _mssql_exec_sp($storedproc,$params) {
$varlist = "";
$setlist = "";
$parmlist = "";
$outs = "";
foreach ($params as $key => $value) {
$quote = strpos($value['type'],'char')!==false;
$varlist .= "@$key ".$value['type'].",\n";
if (isset($value['value'])) {
$setlist .= "set
@".$key."=".($quote?"'":'').$value['value'].($quote?"'\n":"\n");
}
$paramlist .= " @".$key.(isset($value['out'])?'
output,':',');
if (isset($value['out'])) {
$outs .= "@$key as $key,";
}
}
if (strlen($paramlist)) {
$paramlist = substr($paramlist,0,strlen($paramlist)-1);
}
$stmt = "begin try\n";
$stmt .= "declare\n";
$stmt .= "@ret int";
if (strlen($varlist)) {
$stmt .= ",\n";
$stmt .= $varlist;
$stmt = substr($stmt,0,strlen($stmt)-2);
}
else {
$stmt .= "\n";
}
$outs = "@ret as ret,".$outs;
$outs = substr($outs,0,strlen($outs)-1);
$stmt .= "\n".$setlist;
$stmt .= "exec @ret = ".$storedproc.$paramlist."\n";
$stmt .= "select ".$outs."\n";
$stmt .= "end try\n";
$stmt .= "begin catch\n";
$stmt .= "select error_number() as ret,error_message() as errorMsg\n";
$stmt .= "end catch\n";
return mssql_query($stmt);
}
example call:
<?php
$params = array(
'socialid' => array(
'type' => 'char(10)',
'value' => $sid
),
'cust_name' => array(
'type' => 'varchar(20)',
'value' => 'our name'
),
'id' => array(
'type' => 'int',
'out' => true
),
);
$result = _mssql_exec_sp('sp_register_cust', $params);
$row = mssql_fetch_array($result);
?>
----
Server IP: 69.147.83.197
Probable Submitter: 157.157.77.149
----
Manual Page -- http://www.php.net/manual/en/function.mssql-execute.php
Edit -- https://master.php.net/note/edit/109326
Del: integrated -- https://master.php.net/note/delete/109326/integrated
Del: useless -- https://master.php.net/note/delete/109326/useless
Del: bad code -- https://master.php.net/note/delete/109326/bad+code
Del: spam -- https://master.php.net/note/delete/109326/spam
Del: non-english -- https://master.php.net/note/delete/109326/non-english
Del: in docs -- https://master.php.net/note/delete/109326/in+docs
Del: other reasons-- https://master.php.net/note/delete/109326
Reject -- https://master.php.net/note/reject/109326
Search -- https://master.php.net/manage/user-notes.php