#37209 [Opn->Asn]: mssql_execute with non fatal errors
ID: 37209
Updated by: sniper@php.net
Reported By: netvbonline at yahoo dot co dot uk
-Status: Open
+Status: Assigned
Bug Type: MSSQL related
Operating System: Win XP Pro
PHP Version: 5.1.2
Assigned To: fmk
New Comment:
Frank, there's the feedback now. :)
Previous Comments:
------------------------------------------------------------------------
[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] 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-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
Thread (9 messages)