Transaction problem using DBExpress adapter

[Eurides Baptistella]

Transaction is already active: If I make a transaction, and then do commit/rollback and after open new transaction, again the error below:

Exception class EDatabaseError with message 'The transaction is already active'. Project3.exe Process (6888)

This problem may be related to the fwk DBExpress, I not found the problem...

program Project3;

{$APPTYPE CONSOLE}

{$R *.res}


uses
  FastMM4,
  FastMMMemLeakMonitor,
  Data.SqlExpr,
  System.SysUtils,
  Spring.Persistence.Core.ConnectionFactory,
  Spring.Persistence.Core.Interfaces,
  Spring.Persistence.Adapters.DBX,
  Spring.Persistence.Core.Session,
  Spring.Persistence.Mapping.Attributes;


type
  [Entity]
  [Table('tb_person')]
  [Sequence('seq_person', 1, 1)]
  TPerson = class
  private
    [Column('person_id', [cpRequired, cpPrimaryKey, cpNotNull], 0, 0, 0, 'Primary Key')]
    FId: Integer;
  private
    FName: string;
    FUpdatedAt: TDateTime;
    FCreatedAt: TDateTime;
  public
    property Id: Integer read FId write FId;

    [Column('name', [cpNotNull, cpRequired], 70, 0, 0, 'PERSON NAME')]
    property Name: string read FName write FName;

    [Column('created_at', [cpNotNull, cpRequired, cpDontUpdate, cpHidden], 0, 'CREATED IN')]
    property CreatedAt: TDateTime read FCreatedAt write FCreatedAt;

    [Column('updated_at', [cpNotNull, cpRequired, cpHidden], 0, 'UPDATED IN')]
    property UpdatedAt: TDateTime read FUpdatedAt write FUpdatedAt;
  end;

var
  oDBConnection: IDBConnection;
  oSession: TSession;
  oDBTransaction: IDBTransaction;
  oDBXSQLConnection: TSQLConnection;

  oPerson: TPerson;
begin
  try
    oDBXSQLConnection := TSQLConnection.Create(nil);
    try
//      if (THlpConexao.Conectar(oDBXSQLConnection) <> EmptyStr) then
//        raise Exception.Create('Erro ao se conectar');

      oDBConnection := TConnectionFactory.GetInstance(dtDBX, oDBXSQLConnection);
      oSession      := TSession.Create(oDBConnection);
      try

        oPerson := TPerson.Create;
        try
          oPerson.Name := 'Eurides Baptistella';

          oDBTransaction := oSession.BeginTransaction;
          oSession.insert(oPerson);
          oDBTransaction.Commit;

          oDBTransaction := oSession.BeginTransaction; // <~ Here the error

        finally
          oPerson.Free;
        end;
      finally
        oSession.Free;
      end;
    finally
      oDBXSQLConnection.Free;
    end;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;

  Readln;
  FullDebugModeScanMemoryPoolBeforeEveryOperation := True;
  ReportMemoryLeaksOnShutdown := True;

end.

• Delphi XE4
• PostgreSQL 9.2
• Driver DBXDevartPostgreSQL
• DBExpress
• Spring4D branche release/1.2

[Eurides Baptistella]

The problem of the transaction is a bug of DBExpress was simulated here:

program Project4;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  Data.SqlExpr,
  Data.DBXCommon;
var
  oConnection: TSQLConnection;
  oTransaction: TDBXTransaction;
begin
  try
    oConnection := TSQLConnection.Create(nil);
    try
//      if (THlpConexao.Conectar(oConnection) <> EmptyStr) then
//        raise Exception.Create('Erro ao conectar');

      writeln('After BeginTransaction: Transaction: ' + oConnection.InTransaction.ToString);

      oTransaction := oConnection.BeginTransaction;
      writeln('Before BeginTransaction: Transaction: ' + oConnection.InTransaction.ToString);

      oTransaction.Connection.CommitFreeAndNil(oTransaction);
      writeln('Before Connection.CommitFreeAndNil: Transaction: ' + oConnection.InTransaction.ToString);
    finally
      oConnection.Free;
    end;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
end.

[Eurides Baptistella]

I propose the following changes to fix the transaction error that remains opened, it requires a refactoring the class TDBXTransactionAdapter in unit Spring.Persistence.Adapters.DBX:

  TDBXTransactionAdapter = class(TDriverTransactionAdapter<TDBXTransaction>)
  private
    FConnection: TPSQLConnection;
  protected
    function InTransaction: Boolean; override;
  public
    constructor Create(const AConnection: TSQLConnection; const ATransaction: TDBXTransaction; const AExceptionHandler: IORMExceptionHandler); reintroduce;
    destructor Destroy; override;

    procedure Commit; override;
    procedure Rollback; override;
  end;


{ TDBXTransactionAdapter }



procedure TDBXTransactionAdapter.Commit;
begin
  if InTransaction then
    try
      FConnection.CommitFreeAndNil(fTransaction);
    except
      raise HandleException;
    end;
end;



constructor TDBXTransactionAdapter.Create(const AConnection: TSQLConnection; const ATransaction: TDBXTransaction; const AExceptionHandler: IORMExceptionHandler);
begin
  FConnection := AConnection;

  if (not(InTransaction)) then
    inherited Create(ATransaction, AExceptionHandler);
end;



destructor TDBXTransactionAdapter.Destroy;
begin
  if InTransaction then
    FConnection.RollbackIncompleteFreeAndNil(fTransaction);

  inherited;
end;



function TDBXTransactionAdapter.InTransaction: Boolean;
begin
  Result := FConnection.InTransaction;
end;



procedure TDBXTransactionAdapter.Rollback;
begin
  if InTransaction then
    try
      FConnection.RollbackIncompleteFreeAndNil(fTransaction);
    except
      raise HandleException;
    end;
end;

This requires a change in TDBXConnection Adapter.BeginTransaction

function TDBXConnectionAdapter.BeginTransaction: IDBTransaction;
begin
  if Assigned(connection) then
    try
      connection.Connected := True;

      Result := TDBXTransactionAdapter.Create(connection, connection.BeginTransaction, exceptionHandler);
    except
      raise HandleException;
    end
  else
    Result := nil;
end;

Of course, these changes were applied in my adapter

[Stefan Glienke]

Not going to be addressed until someone with enough knowledge about DBX volunteers.

[Eurides Baptistella]

I can send a pull request with the suggestions to fix this problem. If you accept all right, they correct the problem

[Stefan Glienke]

Removing milestone: Future version (automated comment)