SQLTransaction opening and closing

[mangakissa]

I made some test when SQLTransaction will be opened and closed.
Using this command:

Code: [Select]

showmessage(booltostr(dmconnections.SQLTransaction.active ,true));The OnAfterOpen property shows SQLTransaction.active is true.
The OnAfterClose property shows SQLTransaction.active is true.

So, my conclusion is SQLTransaction must be closes manually. Is that correct?

How does SQLTransaction works if I open another form in the active form and opening/editing/saving/closing a table? Do I really need a TSQLTransaction for each table?

[clauslack]

For "close" depend of you program

SQLTransaction.Commit (confirm transaction)
or
SQLTransaction.Rollback

Regards

[emaza]

  Thjs question deserves a beter answer. I have had trouble with this too. I am no expert but perhaps this helps (or confuses everybody more):

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.

Above copied from:  https://www.freepascal.org/docs-html/current/fcl/sqldb/tsqltransaction.options.html

In the Lazarus IDE you can write:

trNameX.Options:=[stoUseImplicit, stoExplicitStart];     or set the option in the objecy inspector.

Better late than never... perhaps.

[egsuh]

In SQL DB, the changes you make to the database is not reflected to the physical database until you commit the transaction. The basic structure is:

   1) SQLTransaction1.StartTransaction;    // or SQLTransaction1.Active := True;
   2) do database operations --- insert, delete or update
   3)  SQLTransaction1.Commit;  // or SQLTransaction1.Rollback;

The changes done in step 2 are not reflected to the final physical database until you commit or rollback at the step3. If you rollback it, the changes are abandoned. So a block of operations that should be done together are grouped within a transaction.

You'd better manually commit or rollback transaction yourself. The timing is important for the database integrity.

Transaction is related with SQL database.  Even though you do many things with different dataset descendants, all the changes done to the database is within one transaction. For example, you may write

Code: Pascal  [Select][+][-]

1.     SQLQuery1.SQL.Text := 'insert into table1 values (1,2,3,4,5)';
2.     SQLQuery2.SQL.Text := 'update table2 set f1=12345 where field2=99';
3.  
4.     if not SQLTransaction1.Active then SQLTransaction1.StartTransaction;
5.     SQLQuery1.ExecSQL;
6.     SQLQuery2.ExecSQL;
7.     SQLTransaction1.Commit;
8.  

Here, both changes of SQLQuery1 and SQLQuery2 may be committed or rollbacked at the same time, not separately. If you want to do that one by one, you have to do

Code: Pascal  [Select][+][-]

1.     SQLQuery1.SQL.Text := 'insert into table1 values (1,2,3,4,5)';
2.     SQLQuery2.SQL.Text := 'update table2 set f1=12345 where field2=99';
3.  
4.     if not SQLTransaction1.Active then SQLTransaction1.StartTransaction;
5.     SQLQuery1.ExecSQL;
6.     SQLTransaction1.Commit;  // or rollback
7.  
8.     SQLTransaction1.StartTransaction;
9.     SQLQuery2.ExecSQL;
10.     SQLTransaction1.Commit;  // or rollback
11.  

You can commit or rollback each of SQLQuery1 or SQLQuery2 changes.
Or you may assign different transactions to SQLQuery1 and SQLQuery2, e.g.

Code: Pascal  [Select][+][-]

1.     SQLQuery1.Transaction := SQLTransaction1;  
2.     SQLQuery2.Transaction := SQLTransaction2;
3.  

But in most cases, only one transaction is sufficient.

So, in many forms, if the transactions are contained in each form, then there are as many transactions as the number of forms. If you put database component and transaction component in one datamodule and datasets in each form (which is recommended), then you have only one transaction. You must be careful when to set active and commit/rollback the transactions at each form.