Dany64 1 Posted October 7 Good morning to all, i have a very strange problem working with FireDac rewriting an old project written with Delphi XE and recompiled with Delphi10.3 In short, the program save user and connection data on a table in a mobile device, the saved data are used to establish a database connection to a remote server. Here the procedure i use to create database and tables Const DataBaseName = 'TestDB.ib'; TabellaConnessioni = 'TabellaConnessioni'; TabellaUtenti = 'TabellaUtenti'; procedure Test Var SL : TStringList; SqlStr : string; I : Integer; Begin DBPath:= TPath.GetDocumentsPath; DBPath := DBPath + PathDelim + 'interbase' + PathDelim + DataBaseName; // Con1 is FireDac FDconnection Con1.Params.Values['Database'] := DBPath; Con1.Params.Values['CreateDatabase'] := 'True'; Con1.Params.Values['OpenMode'] := 'ReadWrite'; Con1.Open; // Try to create tables SL:=TStringList.Create; FXDati.Con1.GetTableNames('','','',SL); if FileExists(Con1.Params.Database) then begin SL:=TStringList.Create; FXDati.Con1.GetTableNames('','','',SL); if SL.Count=0 then begin SqlStr:=''; SqlStr:='CREATE TABLE ' + TabellaConnessioni + ' ('; SqlStr:=SqlStr + 'RecordID Int AUTO_INCREMENT,'; SqlStr:=SqlStr + 'NomeConnessione CHAR(255),'; SqlStr:=SqlStr + 'IPAddress CHAR(255),'; SqlStr:=SqlStr + 'Port Int,'; SqlStr:=SqlStr + 'DataCreazioneRecord DATE'; SqlStr:=SqlStr + ')'; FQ.SQL.Clear; FQ.SQL.Add(SqlStr); FQ.ExecSQL; // Error // Creo il DB degli utenti abilitati ... SqlStr:=''; SqlStr:='CREATE TABLE ' + TabellaUtenti + ' ('; SqlStr:=SqlStr + 'RecordID Int AUTO_INCREMENT,'; SqlStr:=SqlStr + 'Utente CHAR(255),'; SqlStr:=SqlStr + 'Password CHAR(50),'; SqlStr:=SqlStr + 'ConnessionePreferita Int,'; SqlStr:=SqlStr + 'DataCreazioneRecord DATE'; SqlStr:=SqlStr + ')'; FQ.SQL.Clear; FQ.SQL.Add(SqlStr); FQ.ExecSQL; end else begin // Aggiungo tabelle nuove o campi nuovi alle tabelle esistenti .. for I:=0 to SL.Count - 1 do SqlStr:=SL[I]; end; FreeAndNil(SL) end; FreeAndNil(SL) End; The DB is created and i try to create tables but when i try to execute the first sql query i get this error: Project VM75AndroidClient2.apk raised exception class EIBNativeException with message '[FireDAC][Phys][IB]Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char 47 AUTO_INCREMENT'. and i'm not able to create table(s). In FDConnection sql dialect is set to 3, driver used is IB and all the others properties are as defaut. As last information, i set permission with one component called MobilePermission (https://github.com/adrianosantostreina/MobilePermissions or getit) and set the default permission. To be honest these procedures used in Delphi 10.3 procedure DisplayRationale(Sender: TObject; const APermissions: TArray<string>; const APostRationaleProc: TProc); procedure CheckPermissionRequestResult(Sender: TObject; const APermissions: TArray<string>; const AGrantResults: TArray<TPermissionStatus>); does not works anymore because change something. Now i'm using delphi 11.3, all help is absolutely wlcomed. Thank you all can give me some help ... Share this post Link to post
Dmitry Arefiev 105 Posted October 7 InterBase does not support "AUTO_INCREMENT". Share this post Link to post
Lars Fosdal 1792 Posted October 7 3 hours ago, Dmitry Arefiev said: InterBase does not support "AUTO_INCREMENT". It seems you can emulate auto increment, using a trigger and a generator? Share this post Link to post
corneliusdavid 214 Posted October 7 5 hours ago, Dany64 said: rewriting an old project Does "rewriting" include changing the database or SQL? Or are you simply upgrading to a newer version of Delphi? @Dmitry Arefiev is correct that the problem is that "AUTO_CORRECT" is not a recognized keyword in Interbase. @Lars Fosdal is also correct that it can be emulated. My point is that your problem is not with Delphi, it's with the SQL, which leads me to question what all you're doing in the rewrite. Share this post Link to post
Dany64 1 Posted October 7 Hi Dmitry, thank you for reply. To be honest in my last project i have not any field setted with autoincrement. Do you have any idea on how to create an autoinc field via runtime? (trigger and generator?) Thank you Daniele Share this post Link to post
Dany64 1 Posted October 7 41 minutes ago, corneliusdavid said: Does "rewriting" include changing the database or SQL? Or are you simply upgrading to a newer version of Delphi? @Dmitry Arefiev is correct that the problem is that "AUTO_CORRECT" is not a recognized keyword in Interbase. @Lars Fosdal is also correct that it can be emulated. My point is that your problem is not with Delphi, it's with the SQL, which leads me to question what all you're doing in the rewrite. I'm rewriting all the program, the last one has a form where i insert the ip address, port, username and password each time the program starts. The servers where i need to connect are only two but with different connection parameters. My idea was create a database where store the informations .... but in android is not so easy .... I made some test with Samsung S10+ updated to last android version and "play" with database creating database, tables, add, modify, and delete record with a lot of problem (for example i'm not able to drop the database or tables). @Lars Fosdal, i readed it but is enougth do a sql string in a fdquery component?? Share this post Link to post
Dmitry Arefiev 105 Posted October 7 This is about Firebird, but is equally applicable to InterBase: https://www.firebirdfaq.org/faq29/ Share this post Link to post
Dany64 1 Posted October 8 Thank's for all reply, attached a very simple project that create and delete a database. Even "createdb" seem to work, each timwe i run this test the db file (this file exist) always is not present. In the same time i'm not able to drop the data base, i get this erros: Case 1 : with this procedure procedure EraseDB; begin if not FileExists(DBPath) then Exit; DM.Con1.Connected:=False; DM.FQR1.SQL.Clear; //DM.FQR1.SQL.Add('DROP SCHEMA ' + DBPath); DM.FQR1.SQL.Add('DROP DATABASE ' + DBPath); DM.FQR1.ExecSQL; end; i get this error Project DB_TEST.apk raised exception class EIBNativeException with message '[FireDAC][Phys][IB]I/O error "open" for file "/data/user/0/com.embarcadero.DB_TEST/files/interbase/TestDB.ib" database or file exists'. With this procedure procedure EraseDB; begin if not FileExists(DBPath) then Exit; // DM.Con1.Connected:=False; DM.FQR1.SQL.Clear; //DM.FQR1.SQL.Add('DROP SCHEMA ' + DBPath); DM.FQR1.SQL.Add('DROP DATABASE ' + DBPath); DM.FQR1.ExecSQL; end; i get this error: Project DB_TEST.apk raised exception class EIBNativeException with message '[FireDAC][Phys][IB]Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char 6 DATABASE'. Can you help me in order to get "workable" this test ? Thank you very much PS: TMobilePermisison is available on getit or here https://github.com/adrianosantostreina/MobilePermissions DB_TEST_2024-10-08.zip Share this post Link to post