PHP/sqlsrv_query资源ID是3，但不返回数据

问题描述：

我有一个简单的存储过程 -PHP/sqlsrv_query资源ID是3，但不返回数据

CREATE PROCEDURE [dbo].[usp_ws_s_GetCargoClearanceApproval_t] 
    @BOLID INT, 
    @ACCOUNTID INT, 
    @TOTALAMOUNT FLOAT OUT, 
    @STORAGEDAYS INT OUT, 
    @ACCOUNTCREDIT FLOAT OUT, 
    @CREDITLIMIT FLOAT OUT 
AS 

BEGIN 
--SET NOCOUNT ON 
    SET @TOTALAMOUNT= '25.00'; SET @STORAGEDAYS = 5; SET @ACCOUNTCREDIT =2500.00; SET @CREDITLIMIT=75000.00 ; 
    SELECT @TOTALAMOUNT,@STORAGEDAYS,@ACCOUNTCREDIT,@CREDITLIMIT; 
END

这是由一个PHP文件调用并且代码 -

<?php 
$bol_id = 426863; 
$account_id = 26293; 
$totalamount = 0.00; 
$storagedays = 0; 
$accountcredit = 0.00; 
$creditlimit = 0.00; 

$param1 = array($bol_id, SQLSRV_PARAM_IN); 
$param2 = array($account_id, SQLSRV_PARAM_IN); 

$param3 = array($totalamount, SQLSRV_PARAM_OUT); 
$param4 = array($storagedays, SQLSRV_PARAM_OUT); 
$param5 = array($accountcredit, SQLSRV_PARAM_OUT); 
$param6 = array($creditlimit, SQLSRV_PARAM_INOUT); 


$params = array($param1, $param2, $param3, $param4, $param5, $param6); 

$tsql = "{call usp_ws_s_GetCargoClearanceApproval_t(?,?,?,?,?,?)}"; 

$Account_Transaction_List = array(); 

$connectionInfo = array( "Database"=>'**', "UID"=>'**', "PWD"=>'***'); 
$conn = sqlsrv_connect("**", $connectionInfo); 

if($conn === false) { 
    echo "Couldn't connect to SQL Server on $this->servername.<br />"; 
    die(print_r(sqlsrv_errors(), true)); 
} else { 
    echo "Connected!"; 
} 
echo '<br/>'; 

$result = sqlsrv_query($conn, $tsql, $params); 
echo "<pre/> $result - ";print_r($result); 
echo '<br/>'; 

if ($result !== NULL) { 
    $rows = sqlsrv_has_rows($result); 
    if ($rows === true) 
     echo "\nthere are rows\n"; 
    else 
     echo "\nno rows\n"; 
} 

while($row = sqlsrv_fetch_array($result)) 
{ 
    echo 'IN <br/>'; 
} 

?>

连接！

资源ID＃3 - 资源ID＃3

没有行

不管我怎么努力我没有得到数据。我曾尝试通过ref传递参数，将光标选项设置为过去两天的连接和其他排列，但没有运气。

有没有人遇到过这个问题。任何指针将不胜感激。

感谢

您是否尝试过自行测试存储过程？ – TinkerTenorSoftwareGuy

'资源ID＃3'只是表示查询成功，并不意味着它会返回行。所以一定要亲自尝试一下程序，然后从那里开始。 – aynber

感谢您关注它们。 sp在sql server management studio上运行时返回值。正如你所看到的，sp本身非常简单。 –

答

我发现唯一的问题是，$creditlimit设置为SQLSRV_PARAM_INOUT。我建议在运行查询后检查sqlsrv_errors()。可能会有警告返回，从而揭示了这种情况。尝试这样的：

<?php 
$bol_id  = 426863; 
$account_id = 26293; 
$totalamount = 0.00; 
$storagedays = 0; 
$accountcredit = 0.00; 
$creditlimit = 0.00; 

$params = [ 
    [$bol_id,  SQLSRV_PARAM_IN], 
    [$account_id, SQLSRV_PARAM_IN], 
    [$totalamount, SQLSRV_PARAM_OUT], 
    [$storagedays, SQLSRV_PARAM_OUT], 
    [$accountcredit, SQLSRV_PARAM_OUT], 
    [$creditlimit, SQLSRV_PARAM_OUT], 
]; 

$conn = sqlsrv_connect("**", ["Database" => "**", "UID"=> "**", "PWD" => "***"]); 

if ($conn === false) { 
    echo "Couldn't connect to SQL Server on $this->servername.<br />"; 
    die(print_r(sqlsrv_errors(), true)); 
} else { 
    echo "Connected!<br/>"; 
} 

$tsql = "{CALL usp_ws_s_GetCargoClearanceApproval_t(?, ?, ?, ?, ?, ?)}"; 

$result = sqlsrv_query($conn, $tsql, $params); 

if ($err = sqlsrv_errors()) { 
    echo "There were errors or warnings!<br/>"; 
    print_r($err); 
    echo "<br/>"; 
} 

if ($result && sqlsrv_has_rows($result)) { 
    echo "There are rows<br/>"; 
} else { 
    echo "There are no rows<br/>"; 
} 

while($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) { 
    print_r($row); 
    echo "<br/>"; 
} 

?>

谢谢你miken32，它的工作原理。有趣的是，我只是尝试使用SQLSRV_PARAM_OUT。无论如何，结局不错。 –

答

显然，sqlsrv扩展有问题的绘图数据，这是在SP中计算。以前我读的SP -

ALTER PROCEDURE [dbo].[usp_ws_s_GetCargoClearanceApproval_t] 
@BOLID INT, 
@ACCOUNTID INT, 
@TOTALAMOUNT FLOAT OUT, 
@STORAGEDAYS INT OUT, 
@ACCOUNTCREDIT FLOAT OUT, 
@CREDITLIMIT FLOAT OUT 
AS 

BEGIN 
--SET NOCOUNT ON 
    set @TOTALAMOUNT= '25.00'; SET @STORAGEDAYS = 5; SET @ACCOUNTCREDIT =2500.00; SET @CREDITLIMIT=75000.00 ; 
    select @TOTALAMOUNT,@STORAGEDAYS,@ACCOUNTCREDIT,@CREDITLIMIT; 
END

获取数据我不得不修改SP在这种情况下读取

select @TOTALAMOUNT as TotalAmount,@STORAGEDAYS as StorageDays,@ACCOUNTCREDIT as AccountCredit,@CREDITLIMIT as CreditLimit;

我没有要么设置输出参数，可以皆大欢喜:)

PHP/sqlsrv_query资源ID是3，但不返回数据

相关推荐