php.net |  support |  documentation |  report a bug |  advanced search |  search howto |  statistics |  random bug |  login
Bug #37209 mssql_execute with non fatal errors
Submitted: 2006-04-26 14:32 UTC Modified: 2009-02-23 21:22 UTC
Votes:5
Avg. Score:4.8 ± 0.4
Reproduced:3 of 3 (100.0%)
Same Version:1 (33.3%)
Same OS:1 (33.3%)
From: netvbonline at yahoo dot co dot uk Assigned: fmk (profile)
Status: Closed Package: MSSQL related
PHP Version: 5.1.2 OS: Win XP Pro
Private report: No CVE-ID: None
 [2006-04-26 14:32 UTC] netvbonline at yahoo dot co dot uk
Description:
------------
Using the extension php_mssql.dll and mssql_execute method to execute a stored procedure within SQL Server 2000.

Basically, if the stored procedure generates a non-fatal message, for example trying to insert a duplicate value into a table before doing a big seek then output parameters are still available to Query analyser.

However when using the mssql_execute method, the execution fails.. even though stored procedure is returning a successful execution value of 0.

Its possible to have a stored procedure with 10 statements, and 5 legitamately may fail with a non fatal message, which is handled internally by the stored procedure.

The mssql_execute method should fail on execution (any in anycase should still have populated output parameters).

Cheers.

Reproduce code:
---------------
$hStmt = mssql_init("usp_fetch_or_insert_forename", $hCon);
mssql_bind($hStmt, "@Forename", $name, SQLVARCHAR);

// Output
mssql_bind($hStmt, "@ForenameID", $ForenameID, SQLINT4, TRUE);

$result=mssql_execute($hStmt,true);

if (!$result) {
 echo "ERROR HAS OCCURRED";
} else {
 echo "NO ERROR";
}

if ($ForenameID==0) {
 
 // error must have occurred
 
 echo "There was an error trying to get forename pk and forenameid is ".$ForenameID;
 

} else {

 echo "everything is fine and forenameid is ".$ForenameID;
 
}
 

Expected result:
----------------
$ForenameID=Value Returned From Stored Procedure





Actual result:
--------------
$ForenameID=0

Patches

Add a Patch

Pull Requests

Add a Pull Request

History

AllCommentsChangesGit/SVN commitsRelated reports
 [2006-04-26 14:34 UTC] netvbonline at yahoo dot co dot uk
SORRY, BIG TYPO

"The mssql_execute method SHOULD NOT fail on execution (but in anycase should still have populated output parameters)."
 [2006-04-29 03:13 UTC] fmk@php.net
Please provide a short example of the procedure used to generate this.

The current implementation will handle multiple results from a storred procedure and it will skip all results without column.
 [2006-05-02 08:24 UTC] netvbonline at yahoo dot co dot uk
*
[Description]
  Procedure handles the process of either fetching or inserting a forename, this procedure must never update existing name otherwise it would affect all other person  FK's


*/

CREATE PROCEDURE usp_fetch_or_insert_forename
@Forename [varchar](50),
@ForenameID [int] OUTPUT

AS 
DECLARE @err int -- needed as the global @@ERROR is reset after each statement, even logical comparison

	-- reduce network traffic
	SET NOCOUNT ON
	

	-- first do common validation and checks
	BEGIN
				
		
INSERT INTO [dbtest].[dbo].[Forename]   ( [Forename] ) 
          VALUES ( @Forename )
		
		SET @err=@@ERROR
		
		print 'non fatal error ...'

		IF (@err = 2627)
		  BEGIN
			RAISERROR('myMessage:test', 2, 1) WITH SETERROR
		  END
			
			
		IF (@err=0)
		  BEGIN

		      -- explicitly retrieve the identity of row just inserted from above statement
		      SET @ForenameID=SCOPE_IDENTITY()			
	                   RETURN(0)
		   
		   END
			
		 -- Test for key violation before returning error code
		 IF (@err=2627 ) 
		   BEGIN
		       
		      print 'Looking up the forename value'
		      
		         -- I know the name exists, so fetch the PK	
 		        SELECT @ForenameID=ForenameID FROM Forename WHERE Forename=@Forename

print 'still here .. and forenameid is ' + str(@ForenameID)

 		         RETURN(0) 
		   END
		
	             -- There was an error in the insert statement, not related to unique key violation constraint so return error code
	  	RETURN(@err)
		
	END
GO



Hope this is ok, as you can see it contains various debug messages.
 [2006-07-27 00:03 UTC] sniper@php.net
Frank, there's the feedback now. :)
 [2006-09-29 15:59 UTC] issvar at hotmail dot com
This is caused by php stopping processing when dbsqlok() returns FAIL.

Microsoft db-library for c manual states that even if dbsqlok() returns FAIL there could be results, so results and return values and output parameters should be processed.

So in ext/mssql/php_mssql.c there should be changed the following part in PHP_FUNCTION(mssql_execute):

	if (dbrpcexec(mssql_ptr->link)==FAIL || dbsqlok(mssql_ptr->link)==FAIL) {
		php_error_docref(NULL TSRMLS_CC, E_WARNING, "stored procedure execution failed");
		dbcancel(mssql_ptr->link);
		RETURN_FALSE;
	}

this should be changed to:

	if (dbrpcexec(mssql_ptr->link)==FAIL) {
		php_error_docref(NULL TSRMLS_CC, E_WARNING, "stored procedure execution failed");
		dbcancel(mssql_ptr->link);
		RETURN_FALSE;
	}
	if (dbsqlok(mssql_ptr->link)==FAIL) {
		php_error_docref(NULL TSRMLS_CC, E_WARNING, "stored procedure execution failed");
	}
 [2007-08-15 08:19 UTC] netvbonline at yahoo dot co dot uk
issvar left the comment

"This is caused by php stopping processing when dbsqlok() returns FAIL."

Will this change be carried out at source? Seems to me that handling output parameters in a non fatal sp error should be resolved. Has this been fixed?

:-) Cheers
 [2009-02-23 21:22 UTC] kalle@php.net
This bug has been fixed in CVS.

Snapshots of the sources are packaged every three hours; this change
will be in the next snapshot. You can grab the snapshot at
http://snaps.php.net/.
 
Thank you for the report, and for helping us make PHP better.

Fixed in 5.2.9RC4
 
PHP Copyright © 2001-2024 The PHP Group
All rights reserved.
Last updated: Tue Mar 19 10:01:30 2024 UTC