note 109326 modified in function.mssql-execute by danbrown
| From: | danbrown@php.net | Date: | Mon, 16 Jul 2012 15:46:08 +0000 |
| Subject: | note 109326 modified in function.mssql-execute by danbrown | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-189222@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: <?php 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); ?>
--was--
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);
?>
http://php.net/manual/en/function.mssql-execute.php