Sqlite transactions in a lazarus program. Looking for an explanation.

[Graham Saint]

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]

I can at least offer one answer to one of your questions:

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 ?

In principle they are the same (see also https://www.freepascal.org/docs-html/current/fcl/db/tdbtransaction.database.html).

But from the documentation:

StartTransaction 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]

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

[Graham Saint]

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]

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?

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