Title: Smart Transaction Management with Firebird/Interbase
Question: With interbase/firebird we use transactions. you start transactions before you get/post data from the database. With this article i'd like to show how you can easily centralize the code and prevent errors like "Transaction already started, or errors while committing".
It's really simple - but quite usefull, since it saves some coding time.
Answer:
Add the following functions to your main datamodule (dmMain)
public
procedure OpenQuery(Q: TIBQuery; AForceRB: boolean = false);
procedure ExecQuery(Q: TIBQuery; AForceRB: boolean = false; FAutoCommit: boolean = false);
and in the implementation:
procedure TdMain.ExecQuery(Q: TIBQuery; AForceRB, FAutoCommit: boolean);
begin
if Q.Transaction.InTransaction then // only rollback if active
if AForceRB then
Q.Transaction.Rollback;
if not Q.Transaction.InTransaction then
Q.Transaction.StartTransaction;
try
Q.ExecSQL;
except
on E:Exception do
begin
if Q.Transaction.InTransaction then
Q.Transaction.RollBack;
Assert(false, 'An error has occurred while executing the query '+Q.Name+'.'#13#13'Errormessage: '+E.Message);
end;
if FAutoCommit then
Q.Transaction.Commit;
end;
procedure TdMain.OpenQuery(Q: TIBQuery; AForceRB: boolean);
begin
if Q.Transaction.InTransaction then
if AForceRB then
Q.Transaction.Rollback;
if not Q.Transaction.InTransaction then
Q.Transaction.StartTransaction;
try
Q.Open;
except
on E:Exception do
begin
Assert(false, 'An error has occurred while opening the query '+Q.Name+'.'#13#13'Errormessage: '+E.Message);
end;
end;
In order to use these function think like this:
Some form, getting data to fill listbox:
procedure UpdateListbox;
begin
qGet.SQL.Text:= 'SELECT NAME FROM CUSTOMERS ORDER BY CITY';
dMain.OpenQuery(qGet, true); // force rollback before opening - most recent data is wanted.
while not qGet.Eof do
begin
lbCustomers.Items.Add(qGet.Fields[0].AsString);
qGet.Next;
end;
qGet.Transaction.Commit;
end;
Hope you'll find a use for it!