Firebird Adapters (Firedac and UIB) dont returns the autogenerated IDs

[Daniele Teti]

Firebird adapters dont modify the SQL sent to the database to get autogenerated value back. The object cannot be updated after an insert (because the id is 0). I tried it using FireDAC and UIB adapters.

procedure TForm5.Button1Click(Sender: TObject);
var
  lCustomer: TCustomer;
  TestDB: TSQLiteDatabase;
  Connection: IDBConnection;
  Session: TSession;
begin
//  uncomment these 3 lines to use sqlite (id is correctly returned)
//   TestDB := TSQLiteDatabase.Create('PIPPO.DB');
//   TestDB.ExecSQL('CREATE TABLE IF NOT EXISTS CLIENTI(ID INTEGER PRIMARY KEY, CODICE TEXT, RAG_SOC TEXT, LOCALITA TEXT)');
//   Connection := TConnectionFactory.GetInstance(dtSQLite, TestDB);


//  uncomment this line to use firebird (id is always 0)
  Connection := TConnectionFactory.GetInstanceFromFile(dtUIB, 'conn_Firebird.json');
  Session := TSession.Create(Connection);
  try
    lCustomer := TCustomer.Create;
    try
      lCustomer.Code := '1234';
      lCustomer.CustomerName := 'Walt Disney';
      lCustomer.City := 'Rome';
      Session.Save(lCustomer);
      ShowMessage(lCustomer.Id.ToString); //show "0" using firebird
      lCustomer.City := 'Milan';
      Session.Save(lCustomer); //wrong behaviour in firebird
    finally
      lCustomer.Free;
    end
  finally
    Session.Free;
  end;
end;

The business object is defined as follows:

type
  [Entity]
  [Table('CLIENTI')]
  TCustomer = class
  private
    [Sequence('gen_clienti_id',1,1)]
    [AutoGenerated]
    [Column('ID', [cpRequired, cpPrimaryKey])]
    FId: Integer;
    FRagioneSociale: string;
    FLocalita: Nullable<String>;
    FCodice: String;
  public
    property Id: Integer read FId;
    [Column('CODICE')]
    property Code: String read FCodice write FCodice;
    [Column('RAG_SOC')]
    property CustomerName: string read FRagioneSociale write FRagioneSociale;
    [Column('LOCALITA')]
    property City: Nullable<String> read FLocalita write FLocalita;
  end;

[Stefan Glienke]

Can you please attach the FB database you are using?

[Stefan Glienke]

Two things:

1. when using FD you need to set the QueryLanguage of the IDBConnection to qlFirebird
2. the Sequence attribute needs to be on the class instead of the field

[Daniele Teti]

Thank you, now it works with FireDAC adapter (populate the ID with the sequence generated values), but the same code doesn't work using the UIB adapter. SHould I include something else?

Here's the updated BO, the client code

procedure TForm5.Button1Click(Sender: TObject);
var
  lCustomer: TCustomer;
  TestDB: TSQLiteDatabase;
  Connection: IDBConnection;
  Session: TSession;
begin
  // uncomment these 3 lines to use sqlite
  // TestDB := TSQLiteDatabase.Create('PIPPO.DB');
  // TestDB.ExecSQL('CREATE TABLE IF NOT EXISTS CLIENTI(ID INTEGER PRIMARY KEY, CODICE TEXT, RAG_SOC TEXT, LOCALITA TEXT)');
  // Connection := TConnectionFactory.GetInstance(dtSQLite, TestDB);

  // uncomment this line to use firebird with UIB (doesn't work)
  // Connection := TConnectionFactory.GetInstanceFromFile(dtUIB, 'conn_Firebird.json');
  // uncomment this line to use firebird with FireDAC (works)
  Connection := TConnectionFactory.GetInstance(dtFireDAC, FDConnection1);



  Connection.QueryLanguage := qlFirebird; //added this
  Session := TSession.Create(Connection);
  try
    lCustomer := TCustomer.Create;
    try
      lCustomer.Code := '1234';
      lCustomer.CustomerName := 'Walt Disney';
      lCustomer.City := 'Rome';
      Session.Save(lCustomer);
      ShowMessage('After the first save: ' + lCustomer.Id.ToString);
      lCustomer.City := 'Milan';
      Session.Save(lCustomer);
      ShowMessage('After the second save: ' + lCustomer.Id.ToString);
    finally
      lCustomer.Free;
    end
  finally
    Session.Free;
  end;
end;

This is the BO

type

  [Entity]
  [Table('CLIENTI')]
  [Sequence('gen_clienti_id',1,1)] //moved here from the FId field
  TCustomer = class
  private
    [AutoGenerated]
    [Column('ID', [cpRequired, cpPrimaryKey])]
    FId: Integer;
    FRagioneSociale: string;
    FLocalita: Nullable<String>;
    FCodice: String;
  public
    property Id: Integer read FId;
    [Column('CODICE')]
    property Code: String read FCodice write FCodice;
    [Column('RAG_SOC')]
    property CustomerName: string read FRagioneSociale write FRagioneSociale;
    [Column('LOCALITA')]
    property City: Nullable<String> read FLocalita write FLocalita;
  end;

[Daniele Teti]

The firebird 2.5 database used for the test

[Stefan Glienke]

Works for me (using UIB from https://github.com/hgourvest/uib.git)

[Daniele Teti]

Using the updated UIB version, the sequence works for me too. However, as you noted, the data aren't committed.

But this is another ISSUE

[Stefan Glienke]

FWIW: What I noticed is that the adapter does not commit automatically when not using Session.BeginTransaction/Commit explicitly because it uses some implicit transaction inside of UIB.

Looks like TUIBStatementAdapter.Execute is not properly implemented

[Daniele Teti]

Yes, this is the test I did

procedure TForm5.Button1Click(Sender: TObject);
var
  lCustomer: TCustomer;
  TestDB: TSQLiteDatabase;
  Connection: IDBConnection;
  Session: TSession;
  lTrans: IDBTransaction;
  lID: Integer;
  lStoredCustomer: TCustomer;
begin
  // uncomment these 3 lines to use sqlite
  // TestDB := TSQLiteDatabase.Create('PIPPO.DB');
  // TestDB.ExecSQL('CREATE TABLE IF NOT EXISTS CLIENTI(ID INTEGER PRIMARY KEY, CODICE TEXT, RAG_SOC TEXT, LOCALITA TEXT)');
  // Connection := TConnectionFactory.GetInstance(dtSQLite, TestDB);

  // uncomment this line to use firebird with UIB (doesn't work)
  Connection := TConnectionFactory.GetInstanceFromFile(dtUIB,
    'conn_Firebird.json');
  // uncomment this line to use firebird with FireDAC (works)
  // Connection := TConnectionFactory.GetInstance(dtFireDAC, FDConnection1);

  Connection.QueryLanguage := qlFirebird;
  Session := TSession.Create(Connection);
  try
    //enabling transaction handling it works
    // lTrans := Session.BeginTransaction;
    try
      lCustomer := TCustomer.Create;
      try
        lCustomer.Code := '1234';
        lCustomer.CustomerName := 'Walt Disney';
        lCustomer.City := 'Rome';
        Session.Save(lCustomer);
        lID := lCustomer.Id;
        ShowMessage('After the first save: ' + lID.ToString);
        lCustomer.City := 'Milan';
        Session.Save(lCustomer);
      finally
        lCustomer.Free;
      end;
      // lTrans.Commit;
    except
      // lTrans.Rollback;
      raise;
    end;
  finally
    Session.Free;
  end;
  Connection.Disconnect;



  Connection.Connect;
  Session := TSession.Create(Connection);
  try
    lStoredCustomer := Session.FindOne<TCustomer>(lID);
    try
      if Assigned(lStoredCustomer) then
        ShowMessage('Just reloaded ' + lStoredCustomer.CustomerName) //using explicit transaction handling
      else
        ShowMessage('Customer is not in the database'); //using implicit transaction handling
    finally
      lStoredCustomer.Free;
    end;
  finally
    Session.Free;
  end;
end;

[Stefan Glienke]

I just committed a change to the UIB adapter that should solve the issues with the implicit transaction handling - if you can test it and give me feedback I would appreciate that.

[Daniele Teti]

Sure! It works now, thank you Stefan.

However, just a side note (if needed I'll open another issue) there are some compilation problems (at least in my machine) with unit SQLiteTable3.pas.

[dcc32 Error] SQLiteTable3.pas(169): E2003 Undeclared identifier: 'TSQLiteActionCode'
[dcc32 Error] SQLiteTable3.pas(171): E2005 'TSQLiteActionCode' is not a type identifier

This is because the following conditionals are defined early in the same unit

{$IFNDEF MSWINDOWS}
  {$DEFINE USE_SYSTEM_SQLITE}
{$ENDIF}

//then some lines after
{$IFDEF USE_SYSTEM_SQLITE}
type
  TSQLiteActionCode = type Integer;

I had to remove the SqliteTable3 unit in my uses list to compile it. However, removing the IFDEF (so that the USE_SYSTEM_SQLITE is always defined) seems to work.

[Stefan Glienke]

Yes, open a separate issue for that - seems that the ifdefs are not completely correct. When you do that please provide the Delphi version and the platform you are compiling for that causes the issue.

[Daniele Teti]

Did it https://bitbucket.org/sglienke/spring4d/issues/155/sqlitetable3pas-doesnt-compile-win32