PHP gets the output parameter function of mssql stored procedure implementation

PHP gets the output parameter function of mssql stored procedure implementation_PHP tutorial

WBOY

Release： 2016-07-21 15:14:29

Original

875 people have browsed it

During the development process, you may encounter the inability to obtain the output parameters of the MSSQL stored procedure. Many friends do not know what to do. This article will introduce in detail the implementation of the function of obtaining the output parameters of the mssql stored procedure in PHP. Friends who need to know more can refer to the following

Copy code The code is as follows:

 
$conn=mssql_connect("127.0.0.1","user ","passwd"); 
mssql_select_db("mydb"); 
$stmt=mssql_init("pr_name",$conn);// 
$a=50001; 
mssql_bind($stmt ,"RETVAL",$val,SQLVARCHAR); //Used to directly return values ​​such as return -103. 
mssql_bind($stmt,"@outvar",$b,SQLVARCHAR,true);//Used to return the output parameters defined in the stored procedure
mssql_bind($stmt,"@invar",$a, SQLINT4); 
$result = mssql_execute($stmt,true);//The result set cannot be returned, only the output parameters can be obtained
//$result = mssql_execute($stmt,false); //Return the result set 
//$records=mssql_fetch_array($result); 
//print_r($records); 
//mssql_next_result($result); The next result set, when equal to FALSE, the next one is the output Parameters 
echo $b; 
echo $val; 
?> 

The following are seen from other places.
Little trouble:
We use a stored procedure procA of MS Sql Server as usual, which gives an output parameter nReturn,
and returns a result set.
We encountered a little trouble when asking PHP to call this procA.
You can’t have your cake and eat it too:
We originally hoped that such a code could get both the output parameters and the returned result set:

Copy code The code is as follows:

 
// Initialize the parameters to be passed into the stored procedure: 
$nYear = 2004; 
$nPageSize = 20; 
$nPageNo = 1 ; 
// Initializes a stored procedure: 
$stmt = mssql_init("proc_stat_page", $db_mssql->Link_ID); 
// Bind input parameters: 
mssql_bind($stmt, " @nReturn", $nReturn, SQLINT4, TRUE); 
mssql_bind($stmt, "@nYear", $nYear, SQLINT4); 
mssql_bind($stmt, "@nPageSize", $nPageSize, SQLINT4); 
mssql_bind($stmt, "@nPageNo", $nPageNo, SQLINT4); 
// Execute the stored procedure and get the QueryID: 
$db_mssql->Query_ID = mssql_execute($stmt,false); 

Although the result set is obtained, the $nReturn parameter cannot get the output parameter.
If you change the last sentence to:
$db_mssql->Query_ID = mssql_execute($stmt,true);
The output parameters are obtained, but the result set is gone.
It seems like you can’t have your cake and eat it too.
Can’t PHP even do this? This issue is not mentioned in the PHP manual.
Explanation from the PHP maintainer:
Originally, our calling method was definitely supported before PHP version 4.3.
"However, since version 4.3 of PHP," they say, "PHP has changed this feature in order to be compatible with stored procedures returning multiple result sets."

"If you don't need result sets, you should Set the second optional parameter of mssql_execute to TRUE so that you can get the output parameters after the mssql_execute method. "

" If you need the returned result sets, you should call it once for each result set. mssql_next_result. After the last result set is returned, you will get the return value FALSE when you call mssql_next_result. At this time, you can access the output parameters. "
Solution:
Let’s add a sentence at the end. Words:
// After the last result has been returned the return value will have the value returned by the stored procedure.
mssql_next_result($db_mssql->Query_ID);
Immediately, the magic takes effect Already:
PHP populates $nRetVal with the correct output parameters.