Do i need to END TRANSACTION?

Databases
1

Do i always need to send an “END TRANSACTION” when i started a transaction, or are there cases (like Database.Commit f.e.) were the transaction is automatically ENDed?

An example:

[code]If ConnectToOTRS Then

OTRS_DB.SQLExecute("BEGIN TRANSACTION")

'Here i do my UPDATE stuff...    

If OTRS_DB.Error then
  OTRS_DB.Rollback
Else
  OTRS_DB.Commit
End If

End If[/code]

Do i have to place a OTRS_DB.SQLExecute("END TRANSACTION") somewhere or is the OTRS_DB.Commit automatically ENDing the TRANSACTION?

Thank you

2

Disclaimer: this may depend on the DBMS you are using. Not all respect SQL standard and most extend it in some ways.
For example in PostgreSQL:
SQL-Standard “COMMIT [ WORK | TRANSACTION ];” is equivalent to “END [ WORK | TRANSACTION ];”
SQL-Standard “START TRANSACTION;” is equivalent to “BEGIN [ WORK | TRANSACTION ];”
In general, Transactions do end by either Commit or Rollback. The Plugin has wrapper methods for these.
Please note that depending on the DBMS, Xojo DB Plugin and Settings (Autocommit) the behaviour when a Transaction is started (implicitly when issuing the first command or explicitly with the commands listed above) and if it needs to be committed may differ. See also: <https://xojo.com/issue/14477>

3

You do not. Database.Commit ends the transaction.

4

[quote=45714:@Tobias Bussmann]
Please note that depending on the DBMS, Xojo DB Plugin and Settings (Autocommit) the behaviour when a Transaction is started (implicitly when issuing the first command or explicitly with the commands listed above) and if it needs to be committed may differ. See also: <https://xojo.com/issue/14477>[/quote]

Hence why “auto commit” has been removed from some plugins as it tried to smartly begin & end transactions and its simply much clearer to have YOU do it when you need it - that’s one of the big differences between REALSQLDatabase & SQLiteDatabase despite them both using the same underlying SQLite engine

5

Thank you all for your help.
English is not my native. :slight_smile:

If I have understood correctly, the answer is “Yes and No”. I should end the transaction with END TRANSACTION once my transactions are done, but a simple Database.Commit will also end the transaction gracefully?

6

Database.Commit sends END TRANSACTION to the database. It’s the same.

7