Devart Forums

Hello,
I'm using UniDac 2.70 with firebird 2.1 and have problems with prepared statements in transactions:

There is a master table (tblmaster) with an ID field and a detail table (tbldetail) with an ID+MasterID field and a foreign key MasterID->ID to the master table. In my testapplication are a UniConnection and two UniSQL (insMaster, insDetail) components for inserting data on form.

The following code fails by the insertion of the detail-data:

insDetail.SQL.Text := 'insert into TBLDETAIL(ID, MASTERID) values (:ID, :MASTERID)';
insDetail.Prepared := True;

UniConnection.StartTransaction;

insMaster.SQL.Text := 'insert into TBLMASTER(ID) values (:ID)';
insMaster.Prepared := True;

insMaster.Params[0].AsInteger := 1;
insMaster.Execute;

insDetail.Params[0].AsInteger := 1;
insDetail.Params[1].AsInteger := 1;
insDetail.Execute;

UniConnection.Commit;

It seems, that the prepared insert-command of the detail-table runs not in the transaction. When I prepare this command after the StartTransaction, then it works.

Other situation:

insMaster.SQL.Text := 'insert into TBLMASTER(ID) values (:ID)';
insMaster.Prepared := True;

UniConnection.StartTransaction;

insMaster.Params[0].AsInteger := 1;
insMaster.Execute;

UniConnection.Rollback;

The command is not rolled back because it runs not in the transaction.

Is this problem fixed in version 3 of unidac?

InterBase/Firebird supports parallel transactions and requires an active transaction for each operation (like Prepare). When you call Prepare, UniDAC starts an internal transaction and performs preparation on this transaction.

When you call StartTransaction, UniDAC starts another transaction. But the prepared query uses the first transaction.

This problem exists in all UniDAC versions. It is related with the specificity of InterBase. You should prepare a query after you start transaction.

Is there a property to check for an internal transaction? Can I commit this transaction and than use this prepared query with the normal transaction?

I'm migrating a project from dbexpress to UniDAC and there are many datamodules/objects that prepares often used commands at startup and stores the params into objectfields for faster access. In dbexpress this works without problems.

There is no such property. The internal transaction is started when you prepare or execute a query. This transaction may remain active until you close the connection.

When you prepare a query, it is linked to a transaction on the level of InterBase server. When you close the transaction all queries that were prepared on this transaction become unprepared. You cannot switch a prepered statement to another transaction.