Re: Stored Procedure returns nothing on ROLLBACK

On Jan 11, 2007, at 5:53 PM, Chris wrote:

Philip Thompson wrote:

On Jan 10, 2007, at 6:36 PM, Chris wrote:

Philip Thompson wrote:

Hi. I have been experiencing MSSQL woes lately. I have a stored procedure that I call in PHP using mssql_* functions. In my procedure, I have a transaction that rolls back on failure and commits on success (of course). If it commits, I get the proper return value (int) and the appropriate output parameters. However, if it rolls back, I get NOTHING... even if I try to return an error code (int) after the ROLLBACK TRANSACTION statement. The output parameter also does not get set upon rollback. Is there a way to grab the error even in a rollback? or at least return something besides an empty string/int? I would like to know what error message to provide the user/admin. SQL Server 2000, PHP 5.1.6, ntwdblib.dll version 8.00.194

Does it work outside of php? That is - you run it manually in mssql (whatever tools that has) you get the proper codes?

Yes, it works perfectly from the server and returns the expected outcome. It's as though PHP sees the 'rollback' and automatically assumes that the whole thing failed... so why return a value or output parameters. Weird, I know. Any other suggestions?

Can you show us the part of the code that deals with the rollback? If you are issuing a rollback yourself: $result = mssql_query("rollback transaction"); then you need to handle that yourself because that's treated as a separate query. If you're not doing that (the transaction is rolling itself back), then I'm out of ideas :)

I'm calling a stored procedure, not running the query from my code. So yes, I have a statement in my procedure that has ROLLBACK TRANSACTION. It's just odd because I know it works as a script on the server, but PHP doesn't recognize it properly, IMHO. I would almost be so presumptuous to say that it's a bug. But before I go that direction, I wanted to see if anyone had a similar experience. If not, I'll go ahead and think about reporting a bug. Thanks, ~Phil