Use IBCQuery without transaction on FB 2.x

calou · Post by **calou** » Thu 01 Jan 2009 17:18

Hello,

Suppose with have an IBCQuery with autocommit to false and no transaction linked (it is )

Now i do
IBCQuery. SQL.Text:='Select* from....'
IBCQuery.Open
While not IBCQuery.eof do
...

When i call open i suppose a transaction is started? But which king of transaction? ReadOnly? ReadCommited?

Now what is about the end of the transaction if commit or rollback is not called?
Will it closed on the next open?At the end of the application?

Is it correct to do all the select statements like this or should i connect an IBCTransaction with ReadOnly... and manually close the transaction by commit or rollback when IBCQruery is at eof ?

Thank you for help and happy new year

Plash · Post by **Plash** » Mon 05 Jan 2009 08:59

When you call the Open method, the transaction that is selected in the DefaultTransaction property of TIBCConnection is started. You can change IsolationLevel of this transaction by clicking '+' to the left of the property name in Object Inspector, and editing properties of DefaultTransaction. You can also select another TIBCTransaction component for the DefaultTransaction property.

This transaction is rolled back when you close the connection. You can manually commit or rollback it by using the following code:

IBCConnection.DefaultTransaction.Commit;

Note that the TIBCQuery component is closed when you commit or rollback the transaction.

calou · Post by **calou** » Mon 05 Jan 2009 13:21

Hello,

Thank you for your answer

Now suppose i have an IBCTransaction liked with an IBCQuery

If i do

IBCQuery.Sql.Text:='select * from T1'
IBCQuery.Open;
...
IBCQuery.Sql.Text:='select * from T2'
IBCQuery.Open;
...
IBCQuery.Sql.Text:='select * from T3'
IBCQuery.Open;
...

If i do IBCTransaction.Commit, will it close the transactions on T1 and T2 and T3 or only T3?

Regards

Plash · Post by **Plash** » Tue 06 Jan 2009 08:28

When you call TIBCQuery.Open for the first time, a transaction is started. Two other calls to the Open method do not start a transaction. They use a transaction that was started by the first call of Open. So there is only one transaction. This transaction is closed when you call the Commit method.

Roaster2 · Post by **Roaster2** » Wed 07 Jan 2009 09:22

Plash,

you've mentioned that the transaction is automatically closed if I call a Commit previously.
I wonder however, why I should call Commit if I only select some rows from a table and do not alter any of them with e.g. ExecSQL.

Plash · Post by **Plash** » Thu 08 Jan 2009 09:39

You can call either Commit or Rollback to close transaction. These commands will have the same effect.

Roaster2 · Post by **Roaster2** » Thu 08 Jan 2009 12:02

Plash,

sorry to ask again.

I would like to know why a simple SELECT statement and a subsequent Query.Open opens a (default) transaction which is not automatically closed after using e.g. Query.Close.

Of course a Commit or Rollback will close the transaction after an Query.ExecSQL, but I don't need those commands when doing a simple SELECT and Query.Open.

Plash · Post by **Plash** » Fri 09 Jan 2009 10:39

Transaction is not closed because of performance issues. If you open and close many queries the transaction is started only once.

Roaster2 · Post by **Roaster2** » Fri 09 Jan 2009 14:54

Thanks Plash for clarification!