What path is SQLite database deployment to Android device?

Question

Having followed the DocWiki, I'm deploying my SQLite DB to assets\internal\

and used the following code :

SQLConnection1.Params.Values['Database'] := 
TPath.Combine(TPath.GetDocumentsPath, 'myDB.db');

However, when I try and access one of the tables it doesn't exist.

What is the correct setup for deployment/access for SQLite?

Solution 1

I just went through deployment of an SQLite database to an android app. Here's what I've learned.

Instad of deploying my database with the app, I create it on connect and then create the tables if they don't exist.

I also use the TFDConnection component instead of the TSQLConnection component.

So on TFDConnection BeforeConnect:

  {$IF DEFINED(IOS) or DEFINED(ANDROID)}

  FDConnection1.Params.Values['Database'] :=
    TPath.GetDocumentsPath + PathDelim + 'MyDatabase.s3db';

  {$ENDIF}

And on TFDConnection AfterConnect:

FDConnection1.ExecSQL('CREATE TABLE IF NOT EXISTS MyTable (myField1 TEXT NOT NULL)');

I just tested this method on the emulator, and my Droid X.

Also make sure you're including the TFDGUIxWaitCursor, and TFDPhysSQLiteDriverLink components.

Solution 2

Correct set of Deployment (All configurations - Android Platform):

Local Name: myDB.db
Remote Path: .\assets\internal\
Remote Filename: myDB.db

Event BeforeConnection have to cantain follow code:

procedure TDM.conSQLiteBeforeConnect(Sender: TObject);
var
    dbPath: string;
begin
{$IF DEFINED(iOS) or DEFINED(ANDROID)}
    dbPath := TPath.Combine(TPath.GetDocumentsPath, 'myDB.db');
{$ENDIF}
    FDConnection1.Params.Values['Database'] := dbPath;
end;

Solution 3

Add uses “System.IOUtils”

In Connecting button:

var
  DbYol : string;
begin

DbYol := System.IOUtils.TPath.GetDocumentsPath + PathDelim + 'SqliteDers.s3db';

With FDConnection1 do
begin
LoginPrompt := False;
Params.Clear;
Params.Values['Database'] := DbYol; 
Params.Values['DriverID'] := 'SQLite';
Params.Values['CharacterSet'] := 'utf8'; 
Connected := True; 
// Create new database if no exist
end;

With FDQuery1 do
begin
Active := False; 

Connection := FDConnection1;
SQL.Clear;
SQL.Add( 'CREATE TABLE IF NOT EXISTS DERS (' );
SQL.Add( ' ADI nvarchar(10)');
SQL.Add( ' );');
ExecSQL; 
göre ayarlıyoruz
SQL.Clear;
SQL.Add( 'SELECT');
SQL.Add( ' ADI' );
SQL.Add( ' FROM DERS' );
Active := True;
end;

This is good example with details.

http://www.brsatalay.com/xe5-mobil-uygulamada-sqlite-veritabani-kullanimi

Solution 4

I've found that when you make DB changes and deploy those changes, the changes sometimes do not take effect on the device (only tested on ANDROID). Uninstalling the app from the device itself and doing a "Fresh" deploy/install will ensure your changed SQLITE DB gets deployed with the App.

** i.m.o - you should only do this for testing/debugging. I totally agree with @FLDelphi 's post and will be implementing his method in my APP once done ***

View more solutions