Delphi Application using COMMIT and ROLLBACK for Multiple SQL Updates

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.

Answers


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;

Need Your Help

SSRS Report Server with Forms Auth Not Working on a Port other than 80

sql-server sql-server-2005 reporting-services reportingservices-2005

We setup a custom implementation of an SSRS report server to use forms authentication. Can we use a port other than 80? We are getting 401 unauthorized when we do use another port other than port...

xcodebuild -exportArchive fails for tvOS apps

xcode7 xcodebuild ipa tvos

If I tried to export an .ipa from an .xcarchive using the command line using xcodebuild as such: