|
|
php.net | support | documentation | report a bug | advanced search | search howto | statistics | random bug | login |
[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
PatchesPull RequestsHistoryAllCommentsChangesGit/SVN commits
|
|||||||||||||||||||||||||||||||||||||
Copyright © 2001-2024 The PHP GroupAll rights reserved. |
Last updated: Thu Nov 21 11:01:29 2024 UTC |
* [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.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"); }