Problem with SQL Server RAISERROR and FoxPro PassThruSQL
Problem with SQL Server RAISERROR and FoxPro PassThruSQL
(OP)
Hi ...
We are running into a problem with the behavior of VFP handling of
RAISERROR. We are using PastThruSQL to call all of our data modification
routines, for example, one of the SQL Stored Procedure uses the following
codes..
...
...
BEGIN TRANS
-- Delete associated table records
DELETE FROM subtableA WHERE field1 = 13234
-- Delete main table record
DELETE FROM tableA WHERE field1 = @field1
IF (@@error != 0)
BEGIN
ROLLBACK TRANS
RETURN (1)
END
COMMIT TRANS
Let say the DELETE FROM ... statement caused a Constraint Error and resulted
in a RAISERROR. Using SQL QA, the execution will proceed to IF
(@@error!=0)... and issues a ROLLBACK TRANS. However, when calling this
procedure from VisualFoxPro, the RAISERROR is detected but the exeution of
the stored procedure stopped. This resulted in the deletion of subtableA
not being rolled back.
Any insight and advise is greatly appreciated.
Thanks.
We are running into a problem with the behavior of VFP handling of
RAISERROR. We are using PastThruSQL to call all of our data modification
routines, for example, one of the SQL Stored Procedure uses the following
codes..
...
...
BEGIN TRANS
-- Delete associated table records
DELETE FROM subtableA WHERE field1 = 13234
-- Delete main table record
DELETE FROM tableA WHERE field1 = @field1
IF (@@error != 0)
BEGIN
ROLLBACK TRANS
RETURN (1)
END
COMMIT TRANS
Let say the DELETE FROM ... statement caused a Constraint Error and resulted
in a RAISERROR. Using SQL QA, the execution will proceed to IF
(@@error!=0)... and issues a ROLLBACK TRANS. However, when calling this
procedure from VisualFoxPro, the RAISERROR is detected but the exeution of
the stored procedure stopped. This resulted in the deletion of subtableA
not being rolled back.
Any insight and advise is greatly appreciated.
Thanks.
RE: Problem with SQL Server RAISERROR and FoxPro PassThruSQL
Robert Bradley
FoxDev - Visual FoxPro Development
RE: Problem with SQL Server RAISERROR and FoxPro PassThruSQL
The problem we had was the behaviour inconsistency between running a stored procedure in SQL Query Analyzer and calling the stored procedure from VFP when it comes to RAISERROR.
In QA, the execution of the stored procedure will continue even after a RAISERROR has occur which allows for transaction ROLLBACK, if any previous change were made prior to the Constraint voilation. We are detecting the RAISERROR properly in VFP, the problem is the stored procedure code after the RAISERRROR is not executed when called from VFP.
Lin-Chow Sim
RE: Problem with SQL Server RAISERROR and FoxPro PassThruSQL
To manually control SQL transactions, change the default 1 (automatic) to 2 (manual).
Try also changing the Asynchronous property from the default .F. to .T. Program execution continues after the SQLEXEC() call. At some point later in the program, you'll have to issue SQLMORERESULTS() to determine if processing is still running, terminated normally, or terminated in an error condition.
I hope this helps.
RE: Problem with SQL Server RAISERROR and FoxPro PassThruSQL
How are you calling the SP? Are you using the ODBC {CALL My_SP} method?
Robert Bradley
FoxDev - Visual FoxPro Development
RE: Problem with SQL Server RAISERROR and FoxPro PassThruSQL
All transactions are controlled within the stored procedure and is not handle by the VFP application, at this point of the development and point of design, it is not an option to move the transaction handling logic from the Server to the client program.
LC
RE: Problem with SQL Server RAISERROR and FoxPro PassThruSQL
Thanks for the reply again. Yes, we have tried using both the EXEC MySP and {CALL MySP} as the lcSQLString and neither is working properly with RAISERROR.
LC
RE: Problem with SQL Server RAISERROR and FoxPro PassThruSQL
CREATE PROCEDURE TEST AS
RAISERROR (....)
SELECT author_id FROM authors
When this is being called from VFP, no data is being returned.
RE: Problem with SQL Server RAISERROR and FoxPro PassThruSQL
If you have an "abnormal" requirement, consider using a return value rather than RaiseError; you could encode some special flag or whatever in the return value.
Robert Bradley
FoxDev - Visual FoxPro Development