Firebird Adapters (Firedac and UIB) dont returns the autogenerated IDs
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;
Comments (13)
-
repo owner -
repo owner Two things:
- when using FD you need to set the QueryLanguage of the IDBConnection to qlFirebird
- the Sequence attribute needs to be on the class instead of the field
-
reporter 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;
-
reporter - attached TOMSORDERS_DEVELOPMENT.zip
The firebird 2.5 database used for the test
-
repo owner Works for me (using UIB from https://github.com/hgourvest/uib.git)
-
reporter - changed status to closed
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
-
repo owner 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
-
reporter 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;
-
repo owner 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.
-
reporter 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.
-
repo owner 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.
-
reporter -
repo owner - changed version to 1.2
- Log in to comment
Can you please attach the FB database you are using?