Sqlite transactions in a lazarus program. Looking for an explanation.
lazarus / 16-Databases

----------------------------------------------------------------------
Graham Saint · 2023-05-18 04:35

I am trying to wrap my head around the way the use transactions for
sqlite from within a lazarus program.
I should explain that my background is DB2 and in that environment
transactions are implicit. A transaction starts when a statement that
updates the database is issued and ends when a COMMIT or ROLLBACK
command is issued. In sqlite you need to issue an explicit START
TRANSACTION command.
That's okay, but in Lazarus a transaction is an object which gets
placed in a data module (or created dynamically) which seems odd to
me. To make things more confusing the StartTransaction command be be
issued on either the database connection or on the transaction object.
Is there a difference between my_database.Starttransaction and
my_transaction.starttransaction ?
I have a read only SQL query. Trying to run it produces the message
"Transaction not active" which I don't follow. If I'm not updating the
database why is a transaction required?
If I add a transaction to the Data Module and try and start it before
opening the sql I get "Transaction already active"
I've scanned all the tutorials I can find but I can't find any that
explain how this works. Can anyone point me to a good explanation?
Thanks in advance.
Graham

----------------------------------------------------------------------
TRon · 2023-05-18 05:19

I can at least offer one answer to one of your questions:
QuoteTo make things more confusing the StartTransaction command be be
issued on either the database connection or on the transaction object.
Is there a difference between my_database.Starttransaction and
my_transaction.starttransaction ?
In principle they are the same (see also
https://www.freepascal.org/docs-
html/current/fcl/db/tdbtransaction.database.html).

But from the documentation:
QuoteStartTransaction must be implemented by descendent classes to
start a new transaction. This method is provided for Delphi
compatibility: new applications should use a TDBTransaction component
instead and invoke the TDBTransaction.StartTransaction method.

I do not use the visual designer to create my database applications
but rather setup the transactions manually. It makes it more clear
(for me) what needs to be done to prepare things and in what order.

----------------------------------------------------------------------
Graham Saint · 2023-05-18 06:11

Many thanks, Tron. That clears up at least one point of confusion for
me.
Graham

----------------------------------------------------------------------
Graham Saint · 2023-05-18 06:35

Ah, we make progress! Discovered that the sql statement also has a
transaction property. When I set it there it works.
So the program is now running. But I'd appreciate a better
understanding of how this all works to reduce head scratching.

----------------------------------------------------------------------
Zvoni · 2023-05-19 06:46

Quote from: Graham Saint on May 18, 2023, 06:35:21 amI have a read
only SQL query. Trying to run it produces the message "Transaction not
active" which I don't follow. If I'm not updating the database why is
a transaction required?
It has to do with concurrent access.
Imagine you firing off your SELECT-Statement.
And a split second later another user UPDATES that record.

You'd be looking at a SELECT-result which isn't valid anymore.
Read here: https://stackoverflow.com/questions/5982517/use-
transactions-for-select-statements