我尝试从 PHP 调用 SQL Server 存储过程。
这是我的存储过程:
CREATE procedure [dbo].[tester]
@id NVARCHAR(MAX)
AS
BEGIN
DECLARE @tab TABLE (myxml XML)
INSERT INTO @tab(myxml)
SELECT map
FROM forms
WHERE mapid = @id
SELECT * FROM @tab
END
和我的 PHP 脚本:
<?php
$serverName = "servername";
$connectionInfo = array("UID" => "sa","PWD" => "mypass","Database" => "database");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if ($conn) {
$tsql = "exec tester 'FORMgRGVL7bfpEnpBpg7vz2sHoKAs5zxU5LW'";
$result = sqlsrv_query($conn, $tsql);
if ($result === false) {
die( print_r( sqlsrv_errors(), true) );
$response=array('response'=>'notok','data'=>'loyo');
$serverresponse=JSON_encode($response);
} else {
$row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_NUMERIC);
$response=array('response'=>'ok','data'=>$row[0]);
$serverresponse=JSON_encode($response);
}
sqlsrv_free_stmt($stmt);
} else {
$response=array('response'=>'notok','flag'=>$flag,'data'=>'cc');
$serverresponse = $serverresponse=JSON_encode($response);
}
echo ($serverresponse);
?>
当我从 SSMS 执行存储过程时,它返回预期的值,但是当我从 PHP 执行它时,它返回 null。
说明:
你需要把SET NOCOUNT ON
作为存储过程中的第一行,以防止将受 T-SQL 语句影响的行数作为结果集的一部分返回。这就是你的原因NULL
结果。
请注意,请始终使用准备好的语句和参数化查询来防止 SQL 注入。借助 PHP Driver for SQL Server,功能sqlsrv_query()
执行语句准备和语句执行,并且可用于执行参数化查询。
Example:
您的脚本中有错误,已在示例中修复:
-
sqlsrv_free_stmt($stmt)
更改为sqlsrv_free_stmt($result)
- 多变的
$flag
没有定义
-
$serverresponse = $serverresponse=JSON_encode($response)
更改为$serverresponse = json_encode($response)
T-SQL:
CREATE procedure [dbo].[tester]
@id nvarchar(max)
as
begin
SET NOCOUNT ON
declare @tab table (myxml xml)
insert into @tab(myxml)
select map from forms where mapid=@id
select * from @tab
end
PHP:
<?php
$flag = "";
$serverName = "servername";
$connectionInfo = array("UID" => "sa", "PWD" => "mypass", "Database" => "database");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if ($conn) {
$tsql = "exec tester ?";
$params = array('FORMgRGVL7bfpEnpBpg7vz2sHoKAs5zxU5LW');
$result = sqlsrv_query($conn, $tsql, $params);
if ($result === false) {
die( print_r( sqlsrv_errors(), true) );
$response = array('response'=>'notok', 'data'=>'loyo');
$serverresponse = json_encode($response);
} else {
$row = sqlsrv_fetch_array( $result, SQLSRV_FETCH_NUMERIC);
$response = array('response'=>'ok', 'data'=>$row[0]);
$serverresponse = json_encode($response);
}
sqlsrv_free_stmt($result);
} else {
$response = array('response'=>'notok', 'flag'=>$flag, 'data'=>'cc');
$serverresponse = json_encode($response);
}
echo ($serverresponse);
?>
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)