Transaction and ExecSQL
lazarus / 16-Databases

----------------------------------------------------------------------
mangakissa · 2014-01-19 16:45

Back again with a question on transaction.
I want to insert a record in a table @runtime. This is my function:
Code: [Select]function TDMBatches.CreateNewBatch(const aNewBatch :
string; const aDate : TDate) : boolean;
var MyQuery        : TSQLQuery;
begin
  result             := true;
  MyQuery        := TSQLQuery.create(nil);
  try
    try
      MyQuery.DataBase    := DBConnection;
      MyQuery.SQL.Add('INSERT INTO batches
(datum,batchnummer,messageid,usernr_create,
usernr_update,date_create,date_update)');
      MyQuery.SQL.Add('VALUES');
    
MyQuery.SQL.Add('(:datum,:batchnummer,:messageid,:usernr_create,:usern
r_update,:date_create,:date_update)');
      MyQuery.parambyname('datum').AsDate            := aDate;
      MyQuery.parambyname('batchnummer').AsString    := aNewBatch;
      MyQuery.parambyname('messageid').AsString      := '';
      MyQuery.parambyname('usernr_create').AsInteger := u.ID;
      MyQuery.parambyname('usernr_update').AsInteger := u.ID;
      MyQuery.parambyname('date_create').AsDate      := now;
      MyQuery.parambyname('date_update').AsDate      := now;
      MyQuery.ExecSQL;
      if  MyQuery.RowsAffected = -1 then
        result := false;
    except
      result := false;
    end;
  finally
    MyQuery.free;
  end;
end;
But I want to use transaction. If something goes wrong, the whole
batch must be rolled back.
Code: [Select]function TDMBatches.CreateNewBatch(const aNewBatch :
string; const aDate : TDate) : boolean;
var MyQuery        : TSQLQuery;
    SQLTransaction : TSQLTransaction;
begin
  MyQuery        := TSQLQuery.create(nil);
  SQLTransaction := TSQLTransaction.Create(nil);
  result := true;
  try
    try
      MyQuery.DataBase    := DBConnection;
      MyQuery.Transaction := SQLTransaction;
      SQLTransaction.DataBase := DBConnection;
      SQLTransaction.StartTransaction;
      MyQuery.SQL.Add('INSERT INTO batches
(datum,batchnummer,messageid,usernr_create,
usernr_update,date_create,date_update)');
      MyQuery.SQL.Add('VALUES');
      MyQuery.SQL.Add('(:datum,:batchnummer,:messageid,:usernr_create,
:usernr_update,:date_create,:date_update)');
      MyQuery.parambyname('datum').AsDate            := aDate;
      MyQuery.parambyname('batchnummer').AsString    := aNewBatch;
      MyQuery.parambyname('messageid').AsString      := '';
      MyQuery.parambyname('usernr_create').AsInteger := u.ID;
      MyQuery.parambyname('usernr_update').AsInteger := u.ID;
      MyQuery.parambyname('date_create').AsDate      := now;
      MyQuery.parambyname('date_update').AsDate      := now;
      MyQuery.ExecSQL;
      result := MyQuery.RowsAffected >= -1;
      if result then
      begin
        SQLTransaction.Commit;
      end else
      begin
        SQLTransaction.Rollback;
        result := false;
      end;
    except
      SQLTransaction.Rollback;
      result := false;
    end;
  finally
    SQLTransaction.free;
    MyQuery.free;
  end;
end;
This works also, but I Only see the changes after starting my
application. It look's like the transaction is not being freed.
If I Insert several records with ExecSQL, the transaction must be
active. If one insert collapses, the whole transaction must be rolled
back. If all records are inserted, the commit must be done.
As far as I can see, this is not happening.

Will SQLTransaction  works with ExecSQL?

----------------------------------------------------------------------
taazz · 2014-01-19 18:26

1) if you see the data after you restart your application means that
the transaction was comited succesfuly but the transaction on the read
only dataset has not been refreshed/requeried for the new data.
2) if there is no memory leak when you call your method means that the
objects are freed correctly other wise you have forgot to free
something.

3) If you use the function you showed us multiple times then multiple
transactions will be started and committed if the last one fails for
some reason then only the data of that last transaction will be rolled
back. You can execute as many sql statements after a StartTransaction
call as you like, from as many SQLQuery controls as you like, as long
as those SQLQueries are linked on the same transaction object.

----------------------------------------------------------------------
mangakissa · 2014-01-19 19:28

You're right to option 3. The situation should look like this:

1. create objects TSQLTransaction and TSQLQuery
2. Create insert
3. start transaction
4. Create loop, stops until error or EOF
5. if EOF commit all at once, otherwise Rollback.
6. free objects TSQLTransaction and TSQLQuery

The strangest thing is the transaction in my example is a created
transaction, started, commited and freed. All these things are handled
in one procedure. I noticed this 'disfunction', because in another
procedure I open the table without TSQLTransaction, read the required
fields in a TListItem.Items and close the table. So no tables are
opened.

I shall lookup tomorrow if it goes well in another form.

----------------------------------------------------------------------
taazz · 2014-01-19 19:37

SQLDB does not support transactionless communications with the server
not even for read only datasets. If you haven't specified one then it
probably uses the connection's default transaction instead which must
be refreshed before it can show the new data depending always on the
isolation level of the transaction when it was created. As a general
rule of thumb I always specify my transactions just to be on the safe
side and in most cases I have a transaction per query for the GUI
linked SQLQueries.