Devart Forums

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

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).

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.

To solve the problem you should use the UpdateTransaction property for the IBCQueryB query. Here is a code example:The UpdateTransaction property is used to get or set the transaction for modifying a dataset. For more information, please read the IBDAC documentation.

UpdateTransaction is the solution, thank you!

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