Transaction in TIBCQuery.Post

kirby886 · Post by **kirby886** » Wed 09 Mar 2011 08:13

Hi,

we are using IBDAC 3.10 with firebird 2.1 and we got the following problem:

MyIBCQueryB.SQL.Text := 'Select * from Table1 where ID = -1';
MyIBCQueryB.Open;

MyDatabase.StartTransaction;

MyIBCQueryA.SQL.Text := 'Insert into Table1(ID) values(1)';
MyIBCQueryA.ExecSQL;

MyIBCQueryB.Insert;
MyIBCQueryB.FieldByName('ID').AsInteger := 2;
[...]

MyIBCQueryB.Post;
[...]

Both Queries insert data in Table1. Behind the modified Table in the database is a trigger, which executes a stored procedure. This StoredProc modifies a second table.
Because of the transaction, the MyIBCQueryB.Post; raises an exception caused by a lock conflict on the second table. The changes are gone.
This problem seems to exist just in the case mentioned above.

In the normal case we are able to set the Query's transactions to prevent lock conflicts, but here we already got an open Query before we start the transaction - and setting the Query's transaction would close the Query.

Do you see a solution?

Why do you disconnect the DataSet in TCustomIBCDataSet.SetTransaction? Is it possible to call TCustomDADataSet.SetTransaction without disconnect?

Regards

AndreyZ · Post by **AndreyZ** » Thu 10 Mar 2011 09:46

Hello,

I cannot reproduce the problem. Please try composing a small sample to demonstrate the problem and send it to andreyz*devart*com, including a script to create server objects (tables, triggers, stored procedures).

kirby886 · Post by **kirby886** » Thu 10 Mar 2011 15:55

Hi AndreyZ,

thank you for your time. I've created a small sample, but while I were testing I've found the real problem:

We want to use the same transaction for both the ExecSQL and the Post - just like this:

IBCQueryB.Connection := IBCConnection1;
IBCQueryB.SQL.Text := 'Select * from Table1 where ID = -1';
IBCQueryB.Open;

IBCTransaction1.DefaultConnection := IBCConnection1;
IBCTransaction1.StartTransaction;

IBCQueryA.Connection := IBCConnection1;
IBCQueryA.SQL.Text := 'Insert into Table1(ID) values(1)';
IBCQueryA.Transaction := IBCTransaction1; // (1)
IBCQueryA.ExecSQL;

IBCQueryB.Transaction := IBCTransaction1; // (2)
IBCQueryB.Insert;
IBCQueryB.FieldByName('ID').AsInteger := 2;
IBCQueryB.Post;

IBCTransaction1.Commit;

IBCQueryA works pretty fine with the transaction set at (1). The problem is at (2), because IBCQueryB is already open and setting the transaction will close it.

At least it's not a bug of IBDAC, but how may I use a transaction with an open query? Is it necessary to close the query on setting a transaction?

I guess you don't need a sample to understand or reproduce the issue - if you do, just tell me.

AndreyZ · Post by **AndreyZ** » Fri 11 Mar 2011 10:07

To solve the problem you should use the UpdateTransaction property for the IBCQueryB query. Here is a code example:

Code: Select all

IBCQueryB.Connection := IBCConnection1;
IBCQueryB.SQL.Text := 'Select * from Table1 where ID = -1';
IBCQueryB.Open;

IBCTransaction1.DefaultConnection := IBCConnection1;
IBCTransaction1.StartTransaction;

IBCQueryA.Connection := IBCConnection1;
IBCQueryA.SQL.Text := 'Insert into Table1(ID) values(1)';
IBCQueryA.Transaction := IBCTransaction1;
IBCQueryA.ExecSQL;

IBCQueryB.UpdateTransaction := IBCTransaction1;
IBCQueryB.Insert;
IBCQueryB.FieldByName('ID').AsInteger := 2;
IBCQueryB.Post;

IBCTransaction1.Commit;

The UpdateTransaction property is used to get or set the transaction for modifying a dataset. For more information, please read the IBDAC documentation.

kirby886 · Post by **kirby886** » Tue 15 Mar 2011 07:26

UpdateTransaction is the solution, thank you!

AndreyZ · Post by **AndreyZ** » Tue 15 Mar 2011 07:38

It is good to see that this problem was solved. If any other questions come up, please contact us.