How to capture T-SQL output generated by print statement with PHP ODBC client?

Question

I have a stored procedure that is returning its result using a transact print statement to return it's result.

Until now I used the php_mssql extension and its mssql_get_last_message() function to get the returned text.

As PHP has dropped the support for the mssql extension, I decided to fall back to the php_odbc extension.

The problem I face is that I cannot capture the info using odbc_errormsg(). Is there a way to retrieve the print message using PHP and ODBC ?

I found some interesting questions dealing with the subject of print and odbc, but none related to PHP.

PHP code with mssql (works):

$query = "exec dbo.PdaBLCreer 12345";
$res = mssql_query($query);
$message = mssql_get_last_message();

PHP code with odbc (does not work, $message is always empty):

$query = "exec dbo.PdaBLCreer 12345";
$res = odbc_exec($connection, $query);
$message = odbc_errormsg($connection);

T-SQL stored procedure

CREATE PROCEDURE dbo.PdaBLCreer
    @iArgument int
AS
BEGIN
    ...
    PRINT @vNr
    RETURN @vNr
END

@shivan Here they are ... – Lorenz Meyer Nov 08 '13 at 11:21 — Lorenz Meyer, Nov 08 '13 at 11:21

score 1 · Answer 1 · answered Nov 08 '13 at 11:28

1

Empty $message by odbc_errmsg() is good. That means no error.

Please note that "get last message" is different than "get error message". In fact, the 2 MSSQL PHP drivers work differently. You can't expect they return similar results.

Moreover as a side note, mssql_* functions are removed in PHP 5.3 . Use sqlsrv_* functions ( require additional software installation from Microsoft ) instead. Read further.

According to documentation,

Returns a string containing the last ODBC error message, or an empty string if there has been no errors.

answered Nov 08 '13 at 11:28

Raptor

53,206
45
230
366

You are right, but it doesn't solve the problem. I cannot use sql_srv, because it is not compatible with sql server 2000. – Lorenz Meyer Nov 08 '13 at 11:35
How else could I return the value ( integer ) from the stored procedure, given that neither print nor return work ? – Lorenz Meyer Nov 08 '13 at 11:37
Incorrect. `sqlsrv` is compatible with SQL Server 2000. See: http://sqlsrvphp.codeplex.com/discussions/35511 – Raptor Nov 10 '13 at 07:40
ODBC driver does NOT support stored procedure. From [documentation](http://php.net/manual/en/function.odbc-execute.php), *If you need to call a stored procedure using INOUT or OUT parameters, the recommended workaround is to use a native extension for your database (for example, mssql for MS SQL Server)* – Raptor Nov 10 '13 at 07:43
Yes, sqlsrv was compatible with sql server 2000, but sqlsrv version 3.0, that is the only one compatible with php 5.4 dropped support for sql server 2000. – Lorenz Meyer Nov 10 '13 at 08:37
I also found an unofficial version, with added compatibility for SQL 2000 (http://stackoverflow.com/a/19787528/1951708), but I'm not sure that it is a good idea to rely on it for future proof development – Lorenz Meyer Nov 10 '13 at 13:19
As a developer, you should setup your own stable production environment. If you need SQL2000, you shouldn't use PHP 5.4 & sqlsrv 3.0. Of course, another choice is to upgrade your SQL server, or switch to MySQL server for better compatibility with PHP. – Raptor Nov 11 '13 at 02:36
Thanks for all your comments. – Lorenz Meyer Nov 11 '13 at 08:27

Lorenz Meyer · Accepted Answer · 2013-11-08T14:32:41.523

I found this workaround. It works for the return value of the stored procedure, but not for print. Since my stored procedure prints and returns, this allows to get the result from the procedure without modifying it:

Replace old 5.2 PHP code:

$query = "exec dbo.PdaBLCreer 12345";
$res = mssql_query($query);
$message = mssql_get_last_message();

with this PHP 5.4 code using php_odbc:

$query = "DECLARE @return_status int
    EXEC @return_status = dbo.PdaBLCreer 12345
    SELECT 'ReturnStatus' = @return_status";
$res = odbc_exec($connection, $query);
$line = odbc_fetch_array($res);
$message = $line['ReturnStatus'];

This solution is limited. It will not work when your query returns a tabular result and a message.

this solution is a smart workaround actually. Agreed with what you note in the answer, if the Stored Procedure returns a table of results, this won't work. Another stupid workaround I can think of is to output to a file & read it back in PHP. — Raptor, Nov 11 '13 at 02:37

How to capture T-SQL output generated by print statement with PHP ODBC client?

2 Answers2