[SOLVED] What means TSQLTransaction.Option "stoUseImplicit"?

[Hartmut]

I want to ask the experts about the meaning and consequences of this Option, because I do not really understand this from the documentation:

Options can be used to control the behaviour of SQLDB for this transaction:
 - stoUseImplicit: Use the implicit transaction support of the DB engine. This means that no explicit transaction start and stop commands will be sent to the server when the Commit or Rollback methods are called (effectively making them a no-op at the DB level).
 - stoExplicitStart: When set, whenever an SQL statement is executed, the transaction must have been started explicitly. Default behaviour is that the TSQLStatement or TSQLQuery start the transaction as needed.

Here are my questions: Does setting Option "stoUseImplicit" mean:
 - transactions and rollbacks are completely deactivated and impossible?
 - all commands to start a transaction or to do a commit or rollback are completely ignored?

I asked this before in another Topic but maybe no one saw it because of the other subject. Thanks in advance.

[Zvoni]

For SQLite: https://www.sqlite.org/lang_transaction.html
Read Chapter 2.3
Basically it boils down to: Every SQL-Statement is committed immediatly as long as it doesn't produce an error

[Hartmut]

Thank you very much Zvoni for that valuable link and for boiling it down, because that stuff is too hard for me as a beginner to databases to anderstand it really.

Please is this correct with Option "stoUseImplicit":
 - rollbacks are not possible, because commits are done immediately automatically?
 - commits are not neccessary, because they are done immediately automatically?
 - all commands to start a transaction or to do a commit or rollback are completely ignored?
Thanks for your help.

[Zvoni]

Thank you very much Zvoni for that valuable link and for boiling it down, because that stuff is too hard for me as a beginner to databases to anderstand it really.

Please is this correct with Option "stoUseImplicit":
(1)- rollbacks are not possible, because commits are done immediately automatically?
(2) - commits are not neccessary, because they are done immediately automatically?
(3) - all commands to start a transaction or to do a commit or rollback are completely ignored?
Thanks for your help.

I'm no expert, but i'll try:
First off (From Docs): stoUseImplicit, --> Use implicit transaction control if the engine allows it.
If the db-engine allows/supports it

As i said: No expert here
1) My guess is: Yes
-->  to test that would be actually pretty simple: use stoUseImplicit, fire off an INSERT or UPDATE against a Test-DB (without starting a Transaction), stop the app, and use a DB-Client to check if the record has been written to the db. Or after firing off the INSERT/UPDATE, fire off a ROLLBACK on it, and check if it succeeds/fails (record should/shouldn't be in the DB)
2) See Answer 1
3) See Answer 1

That said: I'm probably the wrong guy to ask this, since i always use explicit Transactions

[Hartmut]

Thank you Zvoni for your post. I made some tests, as suggested. I used my small demo from https://forum.lazarus.freepascal.org/index.php/topic,55964.0.html but with the extension of using "stoUseImplicit" like shown there in reply #1. For all tests I used SQLite and checked the database for updates with a DB client, while my demo was running.

Test 1: with commit-command and with command 'SQLTransactionX.Active:=True' in sql_exec() to start a transaction => the DB was updated, no errors occured.

Test 2: I replaced commit by rollback => same result.

Test 3: I skipped commit and rollback, but kept command 'SQLTransactionX.Active:=True' => same result.

Test 4: I skipped commit and rollback and skipped command 'SQLTransactionX.Active:=True' => same result.

Test 5: I used commit, but skipped command 'SQLTransactionX.Active:=True' => same result.

Summary: now we are sure that:
 - SQLite allows "implicit transaction control"
 - commit and rollback and command 'SQLTransactionX.Active:=True' do nothing (they are completely ignored)
 - it works with and without them
 - you get no errors, if you use them

That's exactly what I needed for my current project :-))

Thanks again a lot for your help Zvoni.

[Zvoni]

Gern geschehen :-)