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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.