SQLite and Transactions
lazarus / 16-Databases

----------------------------------------------------------------------
dve83 · 2012-11-24 07:47

Hi,

firstly, need to say, only recently started working with Lazarus - and
I love it :-)

my question is about transaction handling in sqlite.

i have a db grid showing the result of a select statement, however I
wish to update data in the dataset and then refresh this grid.

I ran into some issues earlier but solved it by using commitretaining
instead of just commit - however, i'd like to get a crash course on
lazarus and sqlite transactions to be sure of what I am causing /
doing.

afaik i only require a commit upon altering / updates / inserts. Could
someone either alter the code section below or show me  another
example.

Currently I have 1 connection component to my database and it has one
linked transaction component. I use a query component to select data
and display it in the dbgrid (for this i assume I need no transaction
handling). Then I run another query to Update the related dataset,
before refreshing my display grids query. My original problem was that
the Display Grid (dbgrid) would 'die' / ie connection closed and
reopened when committing the update query. I did some reading and the
recommendation was to do CommitRetaining (which keeps the connection
alive). Is this ok, or am i missing some fundamentals here.

Another question, why is it that I dont have to "Start Transaction".
The DBengine tells me transaction already active?

thanks for your reply - hope I make sense.
Quote  FConnection := TSQLConnection(aQuery.Database);
  try
    aQuery.SQL.Add('Update...');
    aQuery.ExecSQL;
    FConnection.Transaction.Commit;
  except
    FConnection.Transaction.Rollback;
    Exit;
  end;

----------------------------------------------------------------------
BigChimp · 2012-11-24 10:01

You do have to start the transaction, but in your code snippet, the
transaction may already have been started elsewhere? Anyway,
explicitly starting the transaction again won't hurt AFAIR.

I'd start a transaction and commit (/retaining) it when reading as
well. Not strictly necessary but helps if you run into concurrency
issues later (e.g. updated records in multi-user dbs not showing up
because your implicit read transaction was open all the time).

AFAIK commitretaining has no effect on the connection?!? But I haven't
used sqlite in anger... just written the tutorial
If you've read this:
http://wiki.lazarus.freepascal.org/SQLdb_Tutorial2
... could you suggest which parts are vague/need work?

Thanks

----------------------------------------------------------------------
dve83 · 2012-11-29 14:55

Thanks for the reply. Will get back to you If I have more issues -
need to still play around with it a bit.