Create/Drop DataBase/Table at runtime on Android an Delhi 11.x

[Dany64 1]

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 ...

[Dmitry Arefiev 105]

InterBase does not support "AUTO_INCREMENT".

[Lars Fosdal 1793]

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?

[corneliusdavid 220]

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.

[Dany64 1]

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  

[Dany64 1]

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?? 

[Dmitry Arefiev 105]

This is about Firebird, but is equally applicable to InterBase:

https://www.firebirdfaq.org/faq29/

[Dany64 1]

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