存储过程未返回数据 [英] Stored procedure is not returning data

问题描述

我正在将脚本(从停产的Windows服务器)传输到我们的Linux服务器.我需要传输的脚本之一是与 MSSQL -服务器的连接.

I am in the progress of transferring a script from a (discontinued) windows server to our Linux one. One of the scripts I need to transfer is a connection with a MSSQL-server.

与服务器的连接已建立，我能够从任何表中获取常规"数据，但是当我执行存储过程时，我没有收到任何期望的数据.该过程在执行时仅返回 false .

The connection with the server is established and I am able to fetch "regular" data from any of the tables, but when I execute a stored procedure, I don't receive any of the desired data. The procedure just returns false when executed.

使用 $stmt->errorInfo() 测试准备好的语句的错误没有显示任何相关信息，它只返回错误代码 00000，这应该表明一切(都应该)正常工作.

Testing the prepared statement for errors with $stmt->errorInfo() does not show me any relevant information, it just returns error code 00000, which should indicate everything (should) work fine.

Array
(
    [0] => 00000
    [1] => 0
    [2] => (null) [0] (severity 0) [(null)]
    [3] => 0
    [4] => 0
)

---

php

$con = new \PDO('dblib:charset=UTF-8;host=freedts;dbname=database', 'user', 'password');
/** ------------------------------------------------------**/
$sql = 'SELECT * FROM prgroepen';
$stmt = $con->prepare($sql);
if ($stmt) {
    try {
        $stmt->execute();
        $data = $stmt->fetch(\PDO::FETCH_ASSOC);
        if ($data) echo '<pre>'.print_r($data, true).'</pre>';
        else var_dump($data);

    }catch(\Exception $e) {
        echo $e->getMessage();
    }
}

/** ------------------------------------------------------**/
$SP = <<<SQL
    DECLARE @return_value int,
            @soort nvarchar(1),
            @dagen money

    EXEC    @return_value = [dbo].[web_voorraadstatus] @produkt = N'ABEC24_9002', @aantal = 1, @soort = @soort OUTPUT, @dagen = @dagen OUTPUT
    SELECT  @soort as N'@soort', @dagen as N'@dagen'
SQL;

$stmt = $con->prepare($SP);
if ($stmt) {
    try {
        $stmt->execute();
        $data = $stmt->fetch(\PDO::FETCH_ASSOC);
        if ($data) echo '<pre>'.print_r($data, true).'</pre>';
        else var_dump($data);

    }catch(\Exception $e) {
        echo $e->getMessage();
    }
}

---

输出

Array
(
    [kode] => A
    [omschrijving] => ACCESSOIRE DISPLAYS
    [aeenheid] => ST
    [agb] => 604006
    [veenheid] => ST
    [vgb] => 700011
    [coefaank] => 
    [coefverk] => 
    [internet] => 1
    [foto] => #\\serverpc\fws$\GROEPEN\A.jpg#
    [vader] => 
    [produkt_niveau] => 0
    [bs_kode] => 
    [bs_vader] => 
    [web_volgorde] => 6
    [pdfcataloog] => 
)

bool(false) 

---

我也尝试用不同的方式调用SP，但是也无济于事.完全相同的代码可以在Windows服务器上完美运行，唯一的区别是Windows服务器使用 sqlsrv -driver

/** ============================== **/
/*  @produkt as nvarchar(15),
/*  @aantal as money,
/*  @soort as nvarchar(1) output,
/*  @dagen as money output
/** ============================== **/
$stmt = $con->prepare('execute web_voorraadstatus ?, ?, ?, ?');

$stmt->bindParam(1, $produkt, PDO::PARAM_STR);
$stmt->bindParam(2, $aantal, PDO::PARAM_STR);
$stmt->bindParam(3, $soort, PDO::PARAM_STR, 1);
$stmt->bindParam(4, $dagen, PDO::PARAM_STR, 10);

var_dump($stmt->execute()); # true
var_dump($soort, $dagen);   # NULL, NULL

---

那么dblib真的能够执行存储过程并检索它返回的数据吗?

---

So is dblib actually able to execute stored procedures and retrieving the data returned by it?

注意:客户端字符集已在 FreeDTS 配置文件

note: the client charset is already set to UTF-8 in the FreeDTS config file

这是 freeDTS 日志中的一部分，看来我正在从 MSSQL -server接收数据吗?

Here is a partial from the freeDTS log, it's seems I'm receiving data from the MSSQL-server just fine?

dblib.c:4639:dbsqlok(0x7fcfd8acc530)
dblib.c:4669:dbsqlok() not done, calling tds_process_tokens()
token.c:540:tds_process_tokens(0x7fcfd78d7bd0, 0x7ffe281bec38, 0x7ffe281bec3c, 0x6914)
util.c:156:Changed query state from PENDING to READING
net.c:555:Received header
0000 04 01 00 5c 00 37 01 00-                        |...\.7..|

net.c:609:Received packet
0000 04 01 00 5c 00 37 01 00-79 00 00 00 00 fe 01 00 |...\.7.. y.......|
0010 e0 00 00 00 00 00 81 02-00 00 00 21 00 e7 02 00 |........ ...!....|
0020 09 04 d0 00 34 06 40 00-73 00 6f 00 6f 00 72 00 |....4.@. s.o.o.r.|
0030 74 00 00 00 21 00 6e 08-06 40 00 64 00 61 00 67 |t...!.n. .@.d.a.g|
0040 00 65 00 6e 00 d1 02 00-56 00 08 00 00 00 00 90 |.e.n.... V.......|
0050 d0 03 00 fd 10 00 c1 00-01 00 00 00             |........ ....|

推荐答案

我可能是错的，但是我认为这是DBLIB和FreeTDS的标准行为，因为它们每个连接规则只有一个语句.

I could be wrong but I think this is the standard behavior of DBLIB and FreeTDS, in that they have a one statement per connection rule.

要解决此问题，请为每个语句打开连接对象-确保在每次提取后关闭游标.

To workaround open connection object for each statement - making sure you close the cursor after each fetch.

$stmt->closeCursor();

Windows 上的 sqlsrv 没有这种行为，因此跨平台的结果不同.

sqlsrv on Windows does not have this behaviour hence the different results across platforms.

这篇关于存储过程未返回数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！