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)