Commit close dataset [Delphi XE2 32Bit - UniDac 4.6.11 - Firebird 2.5.2 ]

[ecosoft.it]

I use Firebird 2.5.2 with UniDac 4.6.11 on Delphi XE2 32Bit after UniConnection.Commit close dataset but write data into db.

Code: Select all

begin
  UniConnection1.SpecificOptions.Values['ClientLibrary'] := 'fbclient.dll';
  UniConnection1.Open;
  UniConnection1.StartTransaction;
  try
    UniQuery1.Close;
    UniQuery1.CachedUpdates := true;
    UniQuery1.SQL.Text := 'select ID, CODICE, DESCRIZIONE from AGENTI where ID = :ID';
    UniQuery1.ParamByName('ID').AsInteger := 0;
    UniQuery1.Open;
    UniQuery1.Insert;
    UniQuery1.FieldByName('ID').AsInteger := 1000;
    UniQuery1.FieldByName('DESCRIZIONE').AsString := 'TEXT';
    UniQuery1.Post;
    UniQuery1.ApplyUpdates();
    UniConnection1.Commit;
    if UniQuery1.Active = false then
      ShowMessage('???????????');
  finally
    if UniConnection1.InTransaction then
      UniConnection1.Rollback;
  end;
end;

[AndreyZ]

Hello,

InterBase and Firebird require an active transaction for any operation under data.
When you open a dataset without transaction, UniDAC starts transaction internally, and it allows you to read and write data. When you explicitly start transaction and rollback or commit it, dataset will be closed (because there will be no active transaction).
To avoid the problem, you can use two approaches:
- call the CommitRetaining method instead of Commit. CommitRetaining stores to the database server all changes of data associated with the transaction permanently and then retains the transaction context.
- use different transactions for connections and datasets. Also, use different transactions for reading and modifying data. Here is a code example:

Code: Select all

begin
  UniConnection1.Open;
  UniQuery1.Close;
  UniQuery1.Transaction := UniTransaction1;
  UniQuery1.UpdateTransaction := UniTransaction2;
  UniQuery1.CachedUpdates := true;
  UniQuery1.SQL.Text := 'select ID, CODICE, DESCRIZIONE from AGENTI where ID = :ID';
  UniQuery1.ParamByName('ID').AsInteger := 0;
  UniQuery1.Open;
  UniQuery1.UpdateTransaction.StartTransaction;
  try
    UniQuery1.Insert;
    UniQuery1.FieldByName('ID').AsInteger := 1000;
    UniQuery1.FieldByName('DESCRIZIONE').AsString := 'TEXT';
    UniQuery1.Post;
    UniQuery1.ApplyUpdates();
    UniQuery1.UpdateTransaction.Commit;
    if UniQuery1.Active = false then
      ShowMessage('???????????');
  finally
    if UniQuery1.UpdateTransaction.Active then
      UniQuery1.UpdateTransaction.Rollback;
  end;
end;