SQL Server Stored Output Options in PHP

Question

I need help starting a stored procedure from SQL Server to PHP. PHP runs on a Unix / Linux server. We cannot return OUTPUT variables in PHP. The following is the PHP code:

$conn = mssql_connect('server', 'user', 'pass'); mssql_select_db('db', $conn); $procedure = mssql_init('usp_StoredProc', $conn); $tmpVar1 = 'value'; $tmpVar2 = 'value2'; $outVar1 = ''; $outVar2 = ''; mssql_bind($procedure, "@var1", $tmpVar1, SQLVARCHAR, false, false); mssql_bind($procedure, "@var2", $tmpVar2, SQLVARCHAR, false, false); mssql_bind($procedure, "@outVar1", $outVar1, SQLVARCHAR, true); mssql_bind($procedure, "@outVar2", $outVar2, SQLVARCHAR, true); mssql_execute($procedure,$conn); print($outVar1); print($outVar2); 

The stored procedure looks like this:

  SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER proc [dbo].[usp_StoredProc] ( @var1 as varchar(36), @var2 as varchar(10), @outVar1 varchar(36) OUTPUT, @outVar2 varchar(36) OUTPUT ) as select distinct @outVar1 = row1, @outVar2 = row2 from table1 where column1 = @var1 and column2 = @var2 

Can someone tell me why $ outVar1 and $ outVar2 are not populating? Thanks so much for any help!

Answer 1

According to this page on PHP errors , you should (focus):

call mssql_next_result() for each result set returned by SP. This you can handle multiple results.

When mssql_next_result() returns false you will have access to the output parameters and return value.

Answer 2

The second execution parameter should be true, not conn. This should work:

 $conn = mssql_connect('server', 'user', 'pass'); mssql_select_db('db', $conn); $procedure = mssql_init('usp_StoredProc', $conn); $tmpVar1 = 'value'; $tmpVar2 = 'value2'; $outVar1 = ''; $outVar2 = ''; mssql_bind($procedure, "@var1", $tmpVar1, SQLVARCHAR, false, false); mssql_bind($procedure, "@var2", $tmpVar2, SQLVARCHAR, false, false); mssql_bind($procedure, "@outVar1", $outVar1, SQLVARCHAR, true); mssql_bind($procedure, "@outVar2", $outVar2, SQLVARCHAR, true); mssql_execute($procedure,true); print($outVar1); print($outVar2); 

Answer 3

Try specifying specific output field lengths

 mssql_bind($procedure, "@outVar1", &$outVar1, SQLVARCHAR, true, false, 36); mssql_bind($procedure, "@outVar2", &$outVar2, SQLVARCHAR, true, false, 36); 

And see if that has changed.

Also pay attention to the explicit and pass the output by reference, although I do not know if this is required yet or not.

Answer 4

I doubt this causes your problem, but why are you using DISTINCT ?

This is just a code name — anytime you see it, it means that there is a chance of returning duplicates that are “processed” with DISTINCT, and why duplicates will be returned, you probably need to look.

Answer 5

I don’t know what version of PHP you are using, but I think that in some of the old ones you had to pass variables by reference in order to get a value that will come out again:

So, you will need to place the character and charcter in front of the variable when calling the function:

 mssql_bind($procedure, "@outVar1", &$outVar1, SQLVARCHAR, true); mssql_bind($procedure, "@outVar2", &$outVar2, SQLVARCHAR, true); 

Also, in accordance with this link in some versions there was a problem with the output parameters

Answer 6

Hm. Some comments

1) "mssql_execute ($ procedure, $ conn);" it is not true that the second parameter is not a connection.

2) If you are trying to execute a “stored procedure execution”, I had to create a database node in freetds.conf and reference it.

At this moment, I am not getting errors, but I am not getting output parameters either. This is PHP 5.1 on RHEL5.

If I turn on freeTDS logging, I see that the data is being returned in the returned packet. At the moment, I don’t know why it doesn’t work (besides the support of SQL-server for PHP a little is missing!)

Answer 7

I have the same problems.

Assuming you are using the FreeTDS driver to communicate with SQL Server, there is a known issue with how the driver works. It is covered in the FAQ.

http://www.freetds.org/faq.html#ms.output.parameters

The API docs for what is being offered in the FAQ are here, but I cannot find a way to access this using PHP:

http://www.freetds.org/reference/a00276.html

I still cannot connect, and I am at the point where I am going to completely abandon the output parameters.

Answer 8

I made it through the Adodb Library very simple ...

$ addProduct = $ obj-> ExecuteQuery ("Begin; DECLARE @ProductCode as varchar (100); EXEC CREATEPRODUCT '$ pname', '$ price', @ProductCode OUTPUT, '$ merchantId', select @ProductCode; End;") ;

$ productCode = $ addProduct [0] [0];

for more information you can visit this site .. http://developer99.blogspot.com/2011/07/calling-ms-sql-sp-from-php.html