views:

288

answers:

1

Is it possible to use the SQL BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION when embedding SQL Queries into an application with mutiple calls to the SQL for Table Updates. For example I have the following code:

Q.SQL.ADD(<UPDATE A RECORD>);
Q.ExecSQL;

Q.Close;
Q.SQL.Clear;
Q.SQL.ADD(<Select Some Data>);
Q.Open;

Set Some Variables

Q.Close;
Q.SQL.Clear;
Q.SQL.ADD(<UPDATE A RECORD>);
Q.ExecSQL;

What I would like to do is if the second update fails I want to roll back the first transaction.

If I set a unique notation for the BEGIN, COMMIT, ROLLBACK so as to specify what is being committed or rolled back, is it feasible.

i.e. before the first Update specify BEGIN TRANSACTION_A then after the last update specify COMMIT TRANSACTION_A

I hope that makes sense. If I was doing this in a SQL Stored Procedure then I would be able to specify this at the start and end of the procedure, but I have had to break the code down into manageable chunks due to process blocks and deadlocks on a heavy loaded SQL Server.

+7  A: 

Wouldn't it be easier to use StartTransaction, Commit and Rollback on the Connection?

e.g.

Q.Connection.StartTransaction
try
    Q.SQL.ADD(); 
    Q.ExecSQL;

    Q.Close; 
    Q.SQL.Clear; 
    Q.SQL.ADD(); 
    Q.Open;

    ...


    Q.Close; 
    Q.SQL.Clear; 
    Q.SQL.ADD(); 
    Q.ExecSQL;
    Q.Connection.Commit;
except
    Q.Connection.Rollback;
end;
JamesB
Ah that is what I am looking for, so as I am using Delphi 5, and TADOConnection I guess I will need to use:Q.Connection.BeginTrans;Q.Connection.CommitTrans;Q.Connection.RollbackTrans;Thanks
Matt
I think there's a logical problem in this solution. When the sql statement in the 3rd line fails it raises an exception and you're left with a pending transaction. To avoid this you will have to move the try statement upwards and make it the 2nd line
Joe Meyer
You're right, Updated.
JamesB