ID: 37209
Comment by: issvar at hotmail dot com
Reported By: netvbonline at yahoo dot co dot uk
Status: Assigned
Bug Type: MSSQL related
Operating System: Win XP Pro
PHP Version: 5.1.2
Assigned To: fmk
New Comment:
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");
}
Previous Comments:
------------------------------------------------------------------------
[2006-07-27 00:03:22] [EMAIL PROTECTED]
Frank, there's the feedback now. :)
------------------------------------------------------------------------
[2006-05-02 08:24:19] 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
[EMAIL PROTECTED]
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-04-29 03:13:35] [EMAIL PROTECTED]
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-04-26 14:34:02] 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-26 14:32:23] 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
------------------------------------------------------------------------
--
Edit this bug report at http://bugs.php.net/?id=37209&edit=1
