SQL Server Stored Procedure Output Params in PHP

Question

I need help running a stored procedure from SQL Server in PHP. PHP is running on a Unix/Linux server. We cannot get OUTPUT variables to return 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 so :

    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 anyone tell me why $outVar1 and $outVar2 are not being populated? Thanks a lot for any help!

mssql_get_last_message() returns 'Changed database context to "dbName"' — ericwindham
– ericwindham, Commented Jan 26, 2009 at 19:35
Yeah, we've tried specifying the length in the mssql_bind call. Still no luck. — ericwindham
– ericwindham, Commented Jan 26, 2009 at 19:57
No, give it a tailored string: $outVar2 = ' ...36 spaces... '; — Tomalak
– Tomalak, Commented Jan 26, 2009 at 20:02

Tomalak · Accepted Answer · 2009-01-26 20:19:04Z

3

According to this page on PHP bugs, you have to (emphasis mine):

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

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

answered Jan 26, 2009 at 20:19

Tomalak

339k68 gold badges547 silver badges635 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joe Over a year ago

You can pass an optional 2nd param to mssql_execute which (if true) gives you immediate access to the results without having to call mssql_next_result... It still doesn't help here, unfortunately.

Tomalak Over a year ago

Sounded plausible at first, though. :-\

icc97 Over a year ago

This will work if you don't know if there going to be results returned from the stored procedure, i.e. $result = mssql_execute($procedure), works whether there are results returned or not, you can then call mysql_next_result($result) to get at the output params

sth · Accepted Answer · 2009-09-16 00:24:46Z

2

The second param of execute needs to be true, rather than 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);

edited Sep 16, 2009 at 0:24

sth

231k56 gold badges288 silver badges370 bronze badges

answered Sep 14, 2009 at 15:44

4737Carlinsir

Comments

Joe · Accepted Answer · 2009-01-26 19:47:26Z

1

Try specifying the specific lengths of the output fields

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

And see if that makes a difference.

Also note the explicit & to pass the output vars by reference, though I don't know if it's still required or not.

answered Jan 26, 2009 at 19:47

Joe

42.9k20 gold badges108 silver badges129 bronze badges

4 Comments

Tomalak Over a year ago

This would have been my next thought as well, +1. Providing a string of 36 spaces might also serve as a useful test.

ericwindham Over a year ago

Yeah, we've tried it all with/without lenghths. With the by-reference params(which throws and error now saying that way is deprecated) and without.

Tomalak Over a year ago

Regarding the explicit reference passing - don't think this is needed. The documentation states that the function takes references only.

Joe Over a year ago

I have this vague recollection that you used to have to explicitly pass by ref, though I see now that the docs say it's all by ref all the time. Thanks.

Cade Roux · Accepted Answer · 2009-01-26 19:27:33Z

0

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

That's just a codesmell - any time you see that, it means that there is a potential for returning duplicates that is being "handled" with DISTINCT, and why duplicates would be returned probably needs to be looked at.

answered Jan 26, 2009 at 19:27

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

6 Comments

ericwindham Over a year ago

Distinct could be left out, but we're looking for distinct combinations of row1 and row2. The stored procedure runs fine if we run it from the SQL Server Management tools. It's getting the output parameters in php is what we're having trouble with.

Cade Roux Over a year ago

OK. Keep in mind that on SELECT assignments like this, any time your resultset would contain more than a single row, you will get an error. Any time the result set is empty, the variables will be unaltered (NULL if they were never assigned).

ericwindham Over a year ago

Good call, Cade. But the actual query only returns one row, so that's not it. This is killing me. Everything we do with stored procedures works, except retrieving the output params.

Cade Roux Over a year ago

Tomalak is right about having to have consumed all the resultsets before you have access to the OUTPUT parameters. I had trouble with this when I wanted to use the OUTPUT parameters to return count estimates that the client could use prior to consuming the rows - not going to work.

ericwindham Over a year ago

I've seen this several places, but how would one do this? You have to execute a stored proc using mssql_execute, which does not return a resource.

|

feihtthief · Accepted Answer · 2009-01-26 19:49:54Z

0

Not sure which version of PHP you are running, but i think in some of the older ones you needed to pass variables by reference to get the value to come out again:

So you'd need to put the & charcter before the variable when calling the function:

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

Also according to this link some versions had a issue with output paramters

answered Jan 26, 2009 at 19:49

feihtthief

7,2438 gold badges34 silver badges30 bronze badges

1 Comment

ericwindham Over a year ago

Yeah, we are running php 5.*, forgot the exact version, but those problems shouldn't persist in this version. The pass-by-reference method throws an error that says 'passing by reference in mssql_bind has been deprecated'

Joe · Accepted Answer · 2009-01-26 20:30:32Z

0

Hm. A few comments

1) "mssql_execute($procedure,$conn);" is wrong in that the 2nd parameter is not the connection.

2) If you are getting "stored procedure execution failed" then I had to create a DB host in freetds.conf and reference that.

At that point, I don't get errors but I don't get the output params either. This is PHP 5.1 on RHEL5.

If I enable freeTDS logging, I see the data come back in the return packet. At this point, I don't know why it isn't working either (other than the SQL server support for PHP is a bit lacking!)

answered Jan 26, 2009 at 20:30

Joe

42.9k20 gold badges108 silver badges129 bronze badges

1 Comment

ericwindham Over a year ago

Thanks, Joe. We had already removed the $conn param from mssql_query. Yeah, I just ran this script from my Windows box via WampServer and it worked fine. So, I believe you're right in that SQL Server support is a bit lacking in PHP on Unix/Linux.

Mr.Wizard · Accepted Answer · 2011-05-31 15:46:31Z

0

I am having the same problems.

Presuming that you are using the FreeTDS driver to communicate with SQL Server there is a known issue with the way the driver works. It's highlighted in the FAQ

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

The API docs for what is suggested in the FAQ is here but I can't find a way to access this with PHP:

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

I still can't get this hooked up and I am at the point where I am going to give up on output parameters all together.

edited May 31, 2011 at 15:46

Mr.Wizard

24.4k5 gold badges46 silver badges131 bronze badges

answered Jul 19, 2010 at 13:44

benembery

6768 silver badges21 bronze badges

Comments

developer99 · Accepted Answer · 2011-07-26 19:39:41Z

0

i have done this through Adodb Library in very simple manner...

$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 explanation you can visit this site.. http://developer99.blogspot.com/2011/07/calling-ms-sql-sp-from-php.html

answered Jul 26, 2011 at 19:39

developer99

5,4091 gold badge16 silver badges2 bronze badges

Collectives™ on Stack Overflow

SQL Server Stored Procedure Output Params in PHP

8 Answers 8

3 Comments

Comments

4 Comments

6 Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

3 Comments

Comments

4 Comments

6 Comments

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related