在PHP中，SQL Server存储过程输出Params。

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:

我需要在PHP中使用SQL Server来运行存储过程。PHP在Unix/Linux服务器上运行。我们不能让输出变量在PHP中返回。下面是PHP代码:

$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!

有人能告诉我为什么$outVar1和$outVar2没有填充吗?非常感谢您的帮助!

8 个解决方案

#1

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

根据PHP bug的这一页，您必须(强调我的):

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

为SP返回的每个结果集调用mssql_next_result()，这样可以处理多个结果。

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

当mssql_next_result()返回false时，您将访问输出参数和返回值。

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

#3

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.

还要注意显式&通过引用传递输出vars，尽管我不知道是否仍然需要它。

#4

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.

这只是一个codesmell—任何时候您看到它时，它意味着有可能返回正在被“处理”的副本，以及为什么要返回副本可能需要查看。

#5

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:

不知道你在运行哪个版本的PHP，但我认为在一些较老的版本中，你需要通过引用传递变量来获得值再次出现:

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

因此，在调用函数时，需要将& charcter放在变量之前:

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

根据这个链接，一些版本与输出参数有问题。

#6

Hm. A few comments

嗯。一些评论

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

1)“mssql_execute($procedure，$conn);”是错误的，因为第二个参数不是连接。

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

2)如果您收到“存储过程执行失败”，那么我必须在freetds中创建一个DB主机。conf和参考。

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

此时，我不会得到错误，但也不会得到输出参数。这是RHEL5上的PHP 5.1。

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!)

如果启用freeTDS日志记录，则会看到返回包中的数据。现在，我也不知道为什么它不能工作(除了缺少对PHP的SQL server支持!)

#7

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

假设您正在使用FreeTDS驱动程序与SQL Server进行通信，那么关于驱动程序的工作方式存在一个已知的问题。它在FAQ中突出显示

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

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:

FAQ中建议的API文档在这里，但是我无法找到使用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.

我还是不能把它连接起来，现在我要放弃所有的输出参数。

#8

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

我通过Adodb库以非常简单的方式做到了这一点……

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

$addProduct = $obj->ExecuteQuery(“Begin;DECLARE @ProductCode as varchar (100);EXEC CREATEPRODUCT'$pname'， '$price'， @ProductCode OUTPUT， '$商船';select @ProductCode;End;”);

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

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

更多的解释，你可以访问这个网站。http://developer99.blogspot.com/2011/07/calling-ms-sql-sp-from-php.html

#1