SQLTransaction always active after sqlquery open/close

[wittbo]

I have a small application using a sqlite database, 2 screens showing some contents with datagrids, inserting new records with sqlconnection.executedirect, no explicit transaction handling. Everything is working well.

Up to now this was a single user, single platform application. Now the application is intended to run on two or more computers and the database should be hold on a network drive. Therefore I must meet providence against concurrent write locks. The idea is to encapsulate the executedirect Statement into a StartTransaction try ... except on E: exception do ...rollback; frame.

Maybe a good idea, but: upon executing the starttransaction statement, I get an error message, that there is already an open transaction. When searching for the reason, I found, that after every open of a sqlquery the active flag of the corresponding transaction is set to true and it remains true even after closing the query. This behaviour prevents the starttransaction working correctly. As a workaround I tested an additional endtransaction statement before starttransaction, this seems to be OK.

Any hints of how to end a Transaction after closing a query correctly? It does not seem to be reasonable, that a transaction remains active and all queries are closed. Or do I understand something wrong?

-wittbo-

[Zoran]

I have not used sqlite, but I believe that sqlite is just not intended for concurrent usage.

[dsiders]

I have a small application using a sqlite database, 2 screens showing some contents with datagrids, inserting new records with sqlconnection.executedirect, no explicit transaction handling. Everything is working well.

Up to now this was a single user, single platform application. Now the application is intended to run on two or more computers and the database should be hold on a network drive. Therefore I must meet providence against concurrent write locks. The idea is to encapsulate the executedirect Statement into a StartTransaction try ... except on E: exception do ...rollback; frame.

Maybe a good idea, but: upon executing the starttransaction statement, I get an error message, that there is already an open transaction. When searching for the reason, I found, that after every open of a sqlquery the active flag of the corresponding transaction is set to true and it remains true even after closing the query. This behaviour prevents the starttransaction working correctly. As a workaround I tested an additional endtransaction statement before starttransaction, this seems to be OK.

Any hints of how to end a Transaction after closing a query correctly? It does not seem to be reasonable, that a transaction remains active and all queries are closed. Or do I understand something wrong?

-wittbo-

The TSQLTransaction component has an Options property to control how trasactions are started. Make sure it contains stoExplicitStart and not stoUseImplicit.

Sqlite will not deliver what you're looking for in a multi-user environment. See https://www.sqlite.org/faq.html#q5. Use Firebird SQL.

[wittbo]

When setting stoExplicitStart = True and stoUseImplicit = False there is an error message on opening the first sqlquery with a simple select statement:
"Error: attempt to implicitly start a transaction on Connection ..."