FireDAC SQLite error upon table open

[at3s 4]

Few months ago I prepared small demo application used FireDAC SQLite connection on an Android system.

And it worked fine in an application built in Delphi 10.3.

Then I updated Delphi to 10.4.

And now I got:

Project xxx.apk raised exception class ESQLiteNativeException with message ''. 

and after that:

[FireDAC][Phys][SQLite] ERROR: no such table: items.

Even table 'items' exists in the database file and I did not any changes in sources.

 

Anyone had the same issue?

Edited August 24, 2020 by at3s

[JDS2018 1]

Keep all The Query components in data-module, if you keep FDconnection and Query or table in another form this error gives, i had the same issue. so then i kept all tables in side Data-module , its work fine.

[at3s 4]

9 hours ago, JDS2018 said:

Keep all The Query components in data-module, if you keep FDconnection and Query or table in another form this error gives, i had the same issue. so then i kept all tables in side Data-module , its work fine.

Thank you for your answer.

Unfortunately it didn't fix my issue.

 

Probably I have to describe more details here.

My SQLite database has predefined tables inside with the data.

It's added to deploy to an Android device into 'assets\internal'.

But when I did:

FDConnection.Connected := True;
FDQuery.Open; 

the application raised an exception [FireDAC][Phys][SQLite] ERROR: no such table: items.

 

But when I've added code below:

procedure Tdm.FDConnectionBeforeConnect(Sender: TObject);
begin
  {$IF DEFINED(iOS) or DEFINED(ANDROID)}
  FDConnection.Params.Values['Database'] :=
      TPath.Combine(TPath.GetDocumentsPath, 'db.s3db');
  {$ENDIF}
end;

procedure Tdm.FDConnectionAfterConnect(Sender: TObject);
begin
  FDConnection.ExecSQL('CREATE TABLE IF NOT EXISTS items (id INTEGER PRIMARY KEY, image_name TEXT, name TEXT)');
end;

FDQuery opens properly.

It seems, that database file was not deployed to the Android device.

 

What I did wrong?

[mausmb 13]

Hi,

 

You didn't specify the path. 

 

assests\internal is for deployment

 

TPath.GetDocumentsPath, 'db.s3db' is for PATH

 

br,

M

[at3s 4]

2 minutes ago, mausmb said:

Hi,

 

You didn't specify the path. 

 

assests\internal is for deployment

 

TPath.GetDocumentsPath, 'db.s3db' is for PATH

 

br,

M

Well, I found solution. It was my bad.

I had

FDConnection.Params.OpenMode = omCreateUTF8

So changing it to omReadWrite rather is a solution.

[sjordi 42]

Use the FDConnection BeforeConnect event

   procedure TdbModule.fdConnectionBeforeConnect(Sender: TObject);
   {$IF DEFINED(MACOS)}
   var
      resourcesPath : String ;
      newPath       : String ;
      position      : Integer ;
   {$ENDIF)}
   begin
      // Get the correct Win, OSX, iOS or Android DB path
      fdConnection.Params.Values['Database'] := 'mydb.s3db' ;
      {$IF DEFINED(iOS) or DEFINED(ANDROID)}
         fdConnection.Params.Values['ColumnMetadataSupported'] := 'False' ;
         fdConnection.Params.Values['Database'] := TPath.Combine(TPath.GetDocumentsPath, 'mydb.s3db') ;
      {$ELSEIF DEFINED(MACOS)}
         resourcesPath := paramstr(0) ;
         position :=  Pos( '/Contents', resourcesPath ) ;
         newPath := paramstr(0).SubString(0, position )+'Contents/Resources/mydb.s3db' ;
         fdConnection.Params.Values['Database']:= newPath ;
      {$ENDIF}
   end;

Don't forget to add the db to the Project Deployment window and set Remote Path for each platform:

macOS: Contents\Resources\

iOS: StartUp\Documents\ (this one will show up the DB in FileSharing under iTunes or your device finder  - You can download, erase, add the file to the app)

iOS: Library/Application Support/[your app directory] for files you want accessible from your app but not from the user (not shared)

Android: .\assets\internal