CODEWITHSUNDEEP
phpsql-serverlinux
DarkBee
DarkBee

Reputation: 15624

Stored procedure is not returning data

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.

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.

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

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

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

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

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. [email protected]|
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             |........ ....|

Upvotes: 13

Views: 2984

Answers (3)

Anton
Anton

Reputation: 2882

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

  • Could you try to execute your SP as below?

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

    FROM OPENQUERY([server_name],

    '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

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

    ')

server_name is what is shown in

select name
from sys.servers
where server_id = 0

Upvotes: 0

Fraser
Fraser

Reputation: 17059

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

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

Upvotes: 1

J E Carter II
J E Carter II

Reputation: 1506

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

In broad strokes;

  1. Create a view based on your stored procedure
  2. query the view
  3. 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

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

Upvotes: 1

Related Questions