存储过程不返回数据

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.

我正在将脚本从（已停产的）Windows服务器转移到我们的Linux服务器。我需要传输的脚本之一是与MSSQL服务器的连接。

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.

建立了与服务器的连接，我可以从任何表中获取“常规”数据，但是当我执行存储过程时，我没有收到任何所需的数据。该过程在执行时返回false。

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.

使用$ stmt-> errorInfo（）测试准备好的错误语句不会向我显示任何相关信息，它只返回错误代码00000，这应该表明一切（应该）正常工作。

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

php

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();
    }
}

output

产量

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)

I also tried to call the SP in different ways, but with no avail as well. The exact same code runs perfectly on the windows server, with the only difference is that the windows server uses the sqlsrv-driver

我也尝试以不同的方式打电话给SP，但也无济于事。完全相同的代码在Windows服务器上运行完美，唯一的区别是Windows服务器使用sqlsrv驱动程序

/** ============================== **/
/*  @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

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

那么dblib实际上能够执行存储过程并检索它返回的数据吗？

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

注意：客户端字符集已在FreeDTS配置文件中设置为UTF-8

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

这是freeDTS日志的一部分，似乎我从MSSQL服务器接收数据就好了吗？

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             |........ ....|

3 个解决方案

#1

If you are hemmed in by the version of PHP and FreeTDS, a sort of kludge might serve depending on your performance requirements.

如果您使用的是PHP和FreeTDS版本，那么根据您的性能要求，可能会提供一种kludge。

In broad strokes;

广泛的;

Create a view based on your stored procedure
根据存储过程创建视图
query the view
查询视图
drop the view
放下视图

A few different approaches are discussed on this MSDN thread: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/75a686f0-2192-4c6c-bdb8-04c074b916fc/create-view-from-stored-procedure?forum=transactsql

在这个MSDN线程上讨论了几种不同的方法：https：//social.msdn.microsoft.com/Forums/sqlserver/en-US/75a686f0-2192-4c6c-bdb8-04c074b916fc/create-view-from-stored-procedure ？论坛= TRANSACTSQL

Notably:

值得注意的是：

declare @sql_String nvarchar(4000)
set @sql_String = N'
create view dbo.Whatever as
select ''Hello World'' as Hello_World'

exec sp_executeSql @sql_String

#2

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.

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

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

解决每个语句的打开连接对象 - 确保在每次提取后关闭游标。

$stmt->closeCursor();

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

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

#3

Try to run SQL Profiler at SQL Server and see what is running and if it is generating any warnings / errors.

尝试在SQL Server上运行SQL事件探查器，查看正在运行的内容以及是否生成任何警告/错误。
Could you try to execute your SP as below?

您可以尝试执行以下SP吗？

SELECT CAST(soort AS NVARCHAR(1)) as N'@soort', CAST(dagen AS MONEY) as N'@dagen'

SELECT CAST（soort AS NVARCHAR（1））为N'@ soort'，CAST（dagen AS MONEY）为N'@ dagen'

FROM OPENQUERY([server_name],

来自OPENQUERY（[server_name]，

'DECLARE @return_value int, @soort nvarchar(1), @dagen money

'DECLARE @return_value int，@ soort nvarchar（1），@ dagen money

EXEC @return_value = db_name.[dbo].[web_voorraadstatus] @produkt = N''ABEC24_9002'', @aantal = 1, @soort = @soort OUTPUT, @dagen = @dagen OUTPUT

EXEC @return_value = db_name。[dbo]。[web_voorraadstatus] @produkt = N''ABEC24_9002''，@ aantal = 1，@ soort = @soort OUTPUT，@ dagen = @dagen OUTPUT

SELECT @soort as N''soort'', @dagen as N''dagen''

SELECT @soort as N''soort''，@ dagen as N''dagen''

')

“）

server_name is what is shown in

server_name是显示的内容

select name
from sys.servers
where server_id = 0

#1