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]sniperphp.net[/email]

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
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-04-29 03:13:35] [email]fmkphp.net[/email]

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

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 [url]http://bugs.php.net/?id=37209&edit=1[/url]