PHP获取存储过程的返回值 [英] PHP get return value of stored procedure
本文介绍了PHP获取存储过程的返回值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我很高兴通过 PDO 使用 SQL Server 处理旧的 PHP 应用程序.
I have pleasure to work with legacy PHP application using SQL Server via PDO.
如何在 PHP 中检索使用 RETURN
语句作为输出渠道?
How in PHP can I retrieve return value of stored procedure which is using RETURN
statement as output channel?
示例程序
CREATE PROCEDURE [dbo].[mleko_test]
@param INT
AS
BEGIN
RETURN @param * 3;
END
GO
如果可能,我宁愿不修改程序.
If possible, I would prefer to not modify procedure.
我知道有类似的问题,但不包括这种情况
I am aware that there are similar questions, but they don't cover this case
推荐答案
像这样执行存储过程:"exec ?=mleko_test(?)"
.
Execute stored procedure like this: "exec ?=mleko_test(?)"
.
工作示例:
<?php
#------------------------------
# Connection info
#------------------------------
$server = 'server\instance,port';
$database = 'database';
$uid = 'user';
$pwd = 'password';
#------------------------------
# With PDO
#------------------------------
try {
$conn = new PDO("sqlsrv:server=$server;Database=$database", $uid, $pwd);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch( PDOException $e ) {
die( "Error connecting to SQL Server" );
}
try {
$sql = "exec ?=mleko_test(?)";
$param = 3;
$spresult = 0;
$stmt = $conn->prepare($sql);
$stmt->bindParam(1, $spresult, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, PDO::SQLSRV_PARAM_OUT_DEFAULT_SIZE);
$stmt->bindParam(2, $param);
$stmt->execute();
} catch( PDOException $e ) {
die( "Error connecting to SQL Server" );
}
$stmt = null;
$conn = null;
echo 'Stored procedure return value (with PDO): '.$spresult."</br>";
#------------------------------
# Without PDO
#------------------------------
$cinfo = array(
"Database" => $database,
"UID" => $uid,
"PWD" => $pwd
);
$conn = sqlsrv_connect($server, $cinfo);
if( $conn === false )
{
echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
exit;
}
$sql = "exec ?=mleko_test(?)";
$param = 3;
$spresult = 0;
$params = array(
array(&$spresult, SQLSRV_PARAM_OUT),
array($param, SQLSRV_PARAM_IN),
);
$stmt = sqlsrv_query($conn, $sql, $params);
if( $stmt === false ) {
echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
exit;
}
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
}
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
echo 'Stored procedure return value (without PDO): '.$spresult."</br>";
?>
注意事项:
使用 PHP 7.1.12 和适用于 SQL Server 的 PHP 驱动程序进行测试(pdo_sqlsrv 版本 4.3.0+9904).
Tested with PHP 7.1.12 and PHP Driver for SQL Server (pdo_sqlsrv version 4.3.0+9904).
这篇关于PHP获取存储过程的返回值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文