Devart Forums

Hi!

Normally, I'm using direct sql commands like:

DBQuery.SQL.Text:='...';
DBQuery.ExecSQL;

Now I'm experimenting with transactions, and I seem to have found a solution that works, but I'm not sure if there are any drawbacks that I don't see now, or if there is a "more elegant" version of this?

The thing I don't understand is having to call "Transaction.commit" before changing autocommit to false, since all my transactions are direct, and committed straight after calling... (as mentioned above)

Both DBConnection and DBQuery (using DBConnection) are set to Autocommit:=true!

Here is the code:

DBQuery.Transaction.Commit;
DBQuery.AutoCommit:=false;
DBQuery.Transaction.StartTransaction;

DBQuery.SQL.Text:='DELETE FROM ... ';
DBQuery.ParamByName('...').AsInteger:=...;
DBQuery.ExecSQL;

DBQuery.SQL.Text:='INSERT INTO ...';
DBQuery.ParamByName('...').AsInteger:=...;
DBQuery.ExecSQL;

DBQuery.Transaction.Commit;
DBQuery.AutoCommit:=true;

Am I using the default transaction with this code? Won't I have to create a separate transaction?

Thank you!

>The thing I don't understand is having to call "Transaction.commit" before changing autocommit to false, since >all my transactions are direct, and committed straight after calling... (as mentioned above)
If your TIBCQuery.AutoCommit and TIBCConnection.AutoCommit property are set and you don't use external UpdateObjects (TIBCQuery\TIBCSQL trough TIBCUpdateSQL), then this operation is not necessary.

>Am I using the default transaction with this code? Won't I have to create a separate transaction?
Yes, most probably you are using the default connection transaction, and yes it is better to use a separate transaction object to process your changes. To do this you should place on the form (or create in run-time) another TIBCTransaction object, and link it to the TIBCConnection. After that link the TIBCQuery.Transaction property with this transaction, and perform your code (do not remove AutoCommit:=False; line).
Note that in your example the best practice is to use TIBCSQL instead of TIBCQuery, doing so you will obtain some performance gain, and there is no need to reset AutoCommit to False (the default value is already False).