UniDac 5.0.1 + Firebird Transaction problem

[ecosoft.it]

Hello,
I have installed newer version of UniDac 5.0.1 into XE4 and I'm locked because in the new version I have a problem with the transaction.
When I begin a transaction in Firebird an exception raises:
First chance exception at $7547C41F. Exception class Exception with message 'Can't perform operation on active transaction'.

If I use this code with XE3 + Firebird 2.52 x64 + UniDac 4.6.12 it works correctly.
If I use this code with XE3/4 + SQLServer 2008 + UniDac 5.0.1 it works correctly.

Code: Select all

procedure TForm66.TestTransaction(AU: TUniConnection; const ATableName: string);
var
  q: TUniQuery;
begin
  q := TUniQuery.Create(nil);
  try
    AU.Open;
    q.Connection := AU;
    q.SQL.Text := 'select * from ' + ATableName;
    q.Open;

    AU.StartTransaction; // <--- Exception XE4 + UniDac 5 + Firebird 2.52
    try
      //
      // Code in transaction
      //
      AU.Commit;
    except
      on e: Exception do
      begin
        AU.Rollback;
      end;
    end;
  finally
    q.Free;
  end;
end;

Best regards
Alessandro Savoiardo

[AndreyZ]

Hello,

The point is that in UniDAC 5.0.1 we added the DefaultTransaction property in TUniConnection. From now, the TUniConnection component and all datasets that work through it, use DefaultTransaction for all operation under data. Because InterBase and Firebird require an active transaction for any operation under data, DefaultTransaction is started when you call "q.Open;". Because DefaultTransaction is already active the "AU.StartTransaction;" call generates the "Can't perform operation on active transaction" error.
We added the DefaultTransaction transaction property because a lot of our users asked us to do it. To avoid this problem, you should use different (from DefaultTransaction) transaction for your datasets. Here is an example:

Code: Select all

procedure TForm66.TestTransaction(AU: TUniConnection; const ATableName: string);
var
  q: TUniQuery;
  tr: TUniTransaction;
begin
  q := TUniQuery.Create(nil);
  tr := TUniTransaction.Create(nil);
  try
    AU.Open;
    q.Connection := AU;
    tr.DefaultConnection := AU;
    q.Transaction := tr;
    q.SQL.Text := 'select * from ' + ATableName;
    q.Open;

    AU.StartTransaction;
    try
      //
      // Code in transaction
      //
      AU.Commit;
    except
      on e: Exception do
      begin
        AU.Rollback;
      end;
    end;
  finally
    q.Free;
    tr.Free;
  end;
end;

[zemmyindrapatih]

I think theres a little error with Mr AndreyZ answer. For others as reference, it should be:

Code: Select all

    procedure TForm66.TestTransaction(AU: TUniConnection; const ATableName: string);
    var
      q: TUniQuery;
      tr: TUniTransaction;
    begin
      q := TUniQuery.Create(nil);
      tr := TUniTransaction.Create(nil);
      try
        AU.Open;
        q.Connection := AU;
        tr.DefaultConnection := AU;
        q.Transaction := tr;
        q.SQL.Text := 'select * from ' + ATableName;
        q.Open;

        tr.StartTransaction;   <-- tr here not AU
        try
          //
          // Code in transaction
          //
          tr.Commit;   <-- tr here not AU
        except
          on e: Exception do
          begin
            tr.Rollback;   <-- tr here not AU
          end;
        end;
      finally
        q.Free;
        tr.Free;
      end;
    end;

[ViktorV]

Hello,
AndreyZ's answer is correct, and your code contains an error.
Firebird requires an active transaction for any operation with data, including dataset opening. Since in your sample, the dataset q is set as "q.Transaction := tr", then when executing the q.Open method, an automatic start for the transaction tr will occur and the next attempt to execute the r.StartTransaction method will cause an error: "Can't perform operation on active transaction".
In the sample by AndreyZ, when executing the q.Open method, the tr transaction will be started automatically as well. But further in the code, the AU.StartTransaction method is called, that starts a default connection transaction - AU.DefaultTransaction. All the further operations are run in the context of this transaction, not affecting the tr transaction.