views:

869

answers:

1

Trying to call an SQL SERVER stored procedure from php. Think I've got it working but I can't get the data it returns back into php. I'm copying my php code and also the sample SQL SERVER code below.

Believe my problem is how do get the data back from reportData? mssql_execute() just returns boolean true. Looks to me like the procedure stores the data in reportData as xml. However when i try to use mssql_bind() to get the data into reportData by reference I get warnings about converting from xml to varchar.

My php Code

mssql_query("exec ZUSER.pSessionCreate @LoginName = 'admin', @Password = 'xxxx'", $Conn);
$proc = mssql_init("ZUSER.pAppraisal", $Conn);
$reportData="";
$portfolios=3014;
$date="08/19/2009";
mssql_bind($proc, "@reportData", &$reportData, SQLVARCHAR , TRUE, FALSE, 4000);
mssql_bind($proc, "@portfolios", $portfolios, SQLVARCHAR);
mssql_bind($proc, "@date", $date, SQLINT4 );
$result = mssql_execute($proc);

That will throw these errors.

Warning: mssql_execute() [function.mssql-execute]: message: Implicit conversion from data type xml to varchar is not allowed. Use the CONVERT function to run this query. 
Warning: mssql_execute() [function.mssql-execute]: stored procedure execution failed

If I change the type of mssql_bind from SQLVARCHAR TO SQLTEXT then I get this error.

Warning: mssql_execute() [function.mssql-execute]: message: Invalid parameter 1 ('@reportData'): Data type 0x23 is a deprecated large object, or LOB, but is marked as output parameter. Deprecated types are not supported as output parameters. Use current large object types instead.

Also..if I don't use mssql_bind like this so that it doesn't try and return data to reportData it runs with no errors and returns true but I have no idea how I can get the data I need.

mssql_bind($proc, "@reportData", $reportData, SQLVARCHAR);

In this case if I try and run this query after my call to mssql_execute I get this error.

$result=mssql_query("select field1, field2, field3 from ZUSER.pAppraisal(@reportData)", $Conn);

Warning: mssql_query() [function.mssql-query]: message: Must declare the scalar variable "@reportData"

Example SQL Server Code:

declare @SessionGuid uniqueidentifier
declare @portfolios nvarchar(max)
declare @date datetime
set @portfolios='3014'
set @date='08/19/2009'
exec ZUSER.pSessionCreate @LoginName = 'admin', @Password = 'xxxx'
declare @reportData xml
exec ZUSER.pAppraisal
    @reportData=@reportData out
    ,@portfolios = @portfolios
    ,@date = @date

select field1, field2, field3 from ZUSER.fAppraisal(@reportData)
A: 

You need the SQL Server Native client for PHP to leverage the new data types like XML.

Remus Rusanu
I did install that yesterday. Without that I couldn't even get mssql_connect() to work. What datatype would I specify in mssql_bind() for XML? Also, it is possible that I didn't install correctly? Looking at phpinfo I do see a section for sqlsrvqlsrv.LogSeverity 0 0sqlsrv.LogSubsystems 0 0sqlsrv.WarningsReturnAsErrors On OnShould means it's enabled, right? That's the dll I added to php.inithanks
Jason
`SQLSRV_SQLTYPE_XML`. See http://msdn.microsoft.com/en-us/library/cc296183(SQL.90).aspx
Remus Rusanu
Get unsupported type when I use that. mssql_bind($proc, "@reportData", Warning: mssql_bind() [function.mssql-bind]: unsupported type
Jason
Sry, me bad. According to http://msdn.microsoft.com/en-us/library/cc296193(SQL.90).aspx the XML is mapped to a stream, so you should use `SQLSRV_PHPTYPE_STREAM(SQLSRV_ENC_CHAR))` as in the example at http://msdn.microsoft.com/en-us/library/cc296163(SQL.90).aspx. All examples though are for fetching results, not for binding output params.
Remus Rusanu
Thanks. For that need to use the sqlsrv_ functions you linked to. If I use those types with mssql_bind() it gives unsupported type so it looks like it'll need to use those sqlsrv_ functions. Any idea how to call a stored procedure using those functons? I don't see _init(), _bind() and _execute() listed as functions for sqlsrv. http://msdn.microsoft.com/en-us/library/cc296161(SQL.90).aspxThanks.
Jason
See How to: Perform parameterized queries: http://msdn.microsoft.com/en-us/library/cc296201(SQL.90).aspx. `sqlsrv_prepare` is at http://msdn.microsoft.com/en-us/library/cc296181(SQL.90).aspx, `sqlsrv_execute` is http://msdn.microsoft.com/en-us/library/cc296162(SQL.90).aspx
Remus Rusanu