PHP MS SQL Stored Procedure output issue SQLSRV driver

Hi,
I have the following Stored procedure in SQL Server 2008;

ALTER procedure [dbo].[usp_NewImageReference]
(@NewImageReference Char(10) OUTPUT)
AS
	SET NOCOUNT ON;
	SET ANSI_WARNINGS OFF;
BEGIN
	
	SET @NewImageReference = (SELECT 'I' + RIGHT ('000000000' + CAST(ImageNumber AS VARCHAR (10)),9) FROM [ImageReference])
	
	UPDATE ImageReference Set ImageNumber = (ImageNumber +1)

	SELECT @NewImageReference;

END

Open in new window


As you can see it outputs a new, formatted image reference for me. It works just fine in SSMS and in vbscript.

 I have to access this from a PHP file upload script:

	$conn = sqlsrv_connect($serverName, $connectionInfo);
	
	if( $conn === false )
		{
			echo "Error in retrieving row.\n";
			die( print_r( sqlsrv_errors(), true));
		}
	$NewImageReference = "";
	$SQLStmt = "{CALL usp_NewImageReference(?)}";

	$params = array
		(
			array($NewImageReference,SQLSRV_PARAM_OUT)
		);

	$RS_NewImageReference01 = sqlsrv_query($conn, $SQLStmt, $params);
	
	if($RS_NewImageReference01 === false )
		{
			echo "Error in retrieving row.\n";
			die( print_r( sqlsrv_errors(), true));
		}

	while ($obj=sqlsrv_fetch_object($RS_NewImageReference01))
		{
			echo $obj->NewImageReference;
		}

Open in new window


I can't get any output out of the Stored procedure at all. It doesn't error, just return nothing!

HELP!

Kind regards,
LVL 2
splantonAsked:
Who is Participating?
 
Anthony PerkinsCommented:
Incidentally and unrelated, but the following line in your Stored Procedure is not necessary and defeats the whole point of using an OUTPUT parameter:
SELECT @NewImageReference;

So either use an OUTPUT parameter or a resultset, not both.
0
 
sammySeltzerCommented:
Did you try echoing the sql statement $SQLStmt  to see what it outputs?
or even dump var_dump($SQLStmt )

The code looks good to me.

But try something like this:

        ini_set('display_errors', 1);
        error_reporting(~0);
	$conn = sqlsrv_connect($serverName, $connectionInfo);
	
	$SQLStmt = "{CALL usp_NewImageReference(?)}";

	$params = array
		(
			array($NewImageReference,SQLSRV_PARAM_OUT)
		);

	$RS_NewImageReference01 = sqlsrv_query($conn, $SQLStmt, $params);
	
	if( $RS_NewImageReference01 === false ) {
		 die( print_r( sqlsrv_errors(), true));
	}
	
	while ($obj=sqlsrv_fetch_object($RS_NewImageReference01))
		{
			echo $obj->NewImageReference;
		}	
		
		sqlsrv_close($conn);

Open in new window

0
 
splantonAuthor Commented:
Anthony,

Well spotted. I have remedied that and whilst it has not answered my question directly, I have been able to use the SELECT result rather than the OUTPUT to get the data I needed.

I will leave the question open for another day or so in case someone has any ideas on OUTPUT.

Regards,

Spencer
0
 
splantonAuthor Commented:
Anthony has spotted a flaw in my Stored Procedure and it allows me to use a standard SELECT rather than an OUPUT parameter (and definitely not both!). It allows me to move forward but doesn't provide a solution, as such.
0
 
Anthony PerkinsCommented:
It allows me to move forward but doesn't provide a solution, as such.
Then pray tell why would you award points to a non-solution.

Please request the thread be deleted as it serves no purpose in the PAQ.  Let me know if  you need help with that.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.