SaveAll method does not use a database transaction if supported on the database type

[Todd Flora]

Running Oracle and using the Session.SaveAll method for an Object with child objects. If a failure in a child insert occurs it does not rollback the parent insert.

Is there a way to tell the session to start a transaction on the database if supported before performing a SaveAll?

If not can we add this to the list :)

[Stefan Glienke]

I wonder what TSession.BeginTransaction does... ;)

[Todd Flora]

LOL, maybe that is the ticket.

[Todd Flora]

OK So I tried the following:

procedure TForm2.btnSaveAllClick(Sender: TObject);
var
  Subscribers : IList<Prism.Model.PubSubscriber.TPubSubscriber>;
  Subscriber : TPubSubscriber;
  Transaction : IDBTransaction;
begin

  Subscribers := GetSerializedPayload;
  Transaction := GetSession.BeginTransaction;
  try
    for Subscriber in Subscribers do
      GetSession.SaveAll(Subscriber);
    Transaction.Commit;
  except
    Transaction.Rollback;
    raise;
  end;
  Transaction := nil;
  Memo1.Lines.Text := 'SUCCESS';
end;

When the Transaction := nil line runs I get an Access violation when the transaction is being destroyed. Looking into the Firedac Adapter further I am noticing the following:

In the method below Start Transaction is called, but when the Connection.Transaction is used in the constructor to the TFireDACTransactionAdapter.Create it is actually Nil. So it looks like FireDac is either not starting a transaction or somehow the Transaction property is nil

function TFireDACConnectionAdapter.BeginTransaction: IDBTransaction;
begin
  if Connection = nil then
    Exit(nil);

  Connection.Connected := True;

  if not Connection.InTransaction then
    Connection.StartTransaction;

  Result := TFireDACTransactionAdapter.Create(Connection.Transaction as TFDTransaction); <--Connection.Transaction returns nil
end;

This of course causes an AV in the clean up code when the Base Transaction adapter tries to destroy it calls the InTransaction method of the TFireDacTransactionAdapter which has a nil Transaction.

function TFireDACTransactionAdapter.InTransaction: Boolean;
begin
  Result := Transaction.Active;  <--This line causes an AV because Transaction is nil
end;

I have changed the method as follows and this gets rid of the AV, but I have to wonder if this is the correct fix.

function TFireDACTransactionAdapter.InTransaction: Boolean;
begin
  Result := Assigned(Transaction) and Transaction.Active;
end;

I am getting odd results as the records do seem to end up in the database even though the TFireDacTransactionAdapter cannot call Commit because of the nil Transaction. I am confused as to what is actually going on. BTW here is my FDConnection creation code as well.

function TForm2.GetSession : TSession;
begin
  if not Assigned(Session) then
  begin
    {Setup a FireDac Connection}
    FDConnection := TFDConnection.Create(nil);
    FDConnection.ConnectionName := 'OracleConnection1';
    FDConnection.ConnectionString := edConnectString.Text;
    FDConnection.Params.Add('CharacterSet=utf8');
    FDConnection.TxOptions.AutoCommit := False;
    FDConnection.TxOptions.Isolation := TFDTxIsolation.xiReadCommitted;
    FDConnection.Connected := True;
    {Wrap the connection with a Marshmallow Connection}
    Connection := TConnectionFactory.GetInstance(dtFiredac, FDConnection);
    Connection.SetQueryLanguage(TQueryLanguage.qlOracle);
    Session := TSession.Create(Connection);
  end;
  Result := Session;
end;

[Todd Flora]

OK So after some more research on this one I did determine that the transaction is actually running, but because the TFireDACTransactionAdapter.Commit method only commits when the Transaction is assigned it was never being committed.

procedure TFireDACTransactionAdapter.Commit;
begin
  if Assigned(Transaction) then  <-- Never assigned so Commit never happens.
    Transaction.Commit;
end;

Here is my crack at starting a transaction that can actually be passed to the TFireDACTransactionAdapter so that a subsequent commit or rolback will work successfully.

function TFireDACConnectionAdapter.BeginTransaction: IDBTransaction;
var
  Transaction : TFDTransaction;
begin
  if Connection = nil then
    Exit(nil);

  Connection.Connected := True;

  if (not Connection.InTransaction) or (Connection.TxOptions.EnableNested) then
  begin
    {Have the connection own the transaction so it will free it when the connection is destroyed,
     Alternatively the TFireDACTransactionAdapter could free it when it is destroyed.}
    Transaction := TFDTransaction.Create(Connection);
    Transaction.Connection := Connection;
    Transaction.StartTransaction;
  end
  else
    EFireDACAdapterException.Create('Transaction already started, and EnableNested transaction is false');

  Result := TFireDACTransactionAdapter.Create(Transaction as TFDTransaction);
end;

Seems to be working now.

[Linas Naginionis]

fixes #78

→ <<cset cc3e81156233>>