0

is there any way to backup and restore SQLite database in android using Delphi 10.2 Firemonky FireFac. I am using the following code but it is not generating any backup file and i want to save backup file in SDcard(currently try to save in current directory);

procedure TFMain.Button1Click(Sender: TObject);
var pathname : string;
begin
{$IFDEF ANDROID}
pathname := System.IOUtils.TPath.GetDocumentsPath;
{$ENDIF}
FDConnection1.DriverName := 'SQLite';
FDConnectionMain.Params.Values['Database'] := TPath.Combine(TPath.GetDocumentsPath, 'ConceptDB_Backup.s3db');
FDConnection1.Open();
    FDSQLiteBackup1.Database := System.IOUtils.TPath.Combine(pathname, 'ConceptDB.s3db');
    FDSQLiteBackup1.DestDatabaseObj := FDConnection1.CliObj;
    FDSQLiteBackup1.Backup;
    ShowMessage('Backup Successfully');
end;
Fiaz
  • 30
  • 2
  • 9
  • 1
    I'm not sure if `smCreate` (default) mode is a right choice for a just created backup database. I would expect exception though.. Still, why not just fill `Database`, `DestDatabase` properties and call `Backup`? – Victoria Sep 11 '17 at 12:35
  • @Victoria file now created, but how can i save backup file to SDcard and how to restore procedure execute – Fiaz Sep 11 '17 at 13:06
  • Ehm, by changing `DestDatabase` path to that SD card? And there is no restore in SQLite. Backup API exists mainly for doing backups over opened databases and produces ready to open databases. – Victoria Sep 11 '17 at 14:56
  • @Victoria i try to give Sdcard path Permission Denied and try to copy the database to SDcard same error (https://stackoverflow.com/questions/30693009/how-to-save-files-on-external-sd-with-delphi-xe8) i think other people facing the same issue. – Fiaz Sep 11 '17 at 15:27

1 Answers1

4

How to backup SQLite database?

You have a typo in your code. You've defined database name for a different object. I don't know the meaning of those objects, but if you're about to backup an opened database (which SQLite backup API is primarily for) into a file defined by name, then you can write code like this:

procedure TForm1.Button1Click(Sender: TObject);
var
  BackupPath: string;
  BackupFile: string;
begin
  BackupPath := System.IOUtils.TPath.GetDocumentsPath;
  BackupFile := System.IOUtils.TPath.Combine(BackupPath, 'MyBackup.db');

  FDSQLiteBackup1.DatabaseObj := FDConnection1.CliObj as TSQLiteDatabase;
  FDSQLiteBackup1.DestDatabase := BackupFile;
  FDSQLiteBackup1.Backup;
end;

Or define source and target database by file names:

procedure TForm1.Button1Click(Sender: TObject);
var
  CommonPath: string;
  SourceFile: string;
  BackupFile: string;
begin
  CommonPath := System.IOUtils.TPath.GetDocumentsPath;
  SourceFile := System.IOUtils.TPath.Combine(CommonPath, 'MySource.db');
  BackupFile := System.IOUtils.TPath.Combine(CommonPath, 'MyBackup.db');

  FDSQLiteBackup1.Database := SourceFile;
  FDSQLiteBackup1.DestDatabase := BackupFile;
  FDSQLiteBackup1.Backup;
end;

The whole thing is about 2 properties and one method call. You always specify either source object or source file name, and target object or target file name.

How to restore SQLite database?

Backup files created by the SQLite backup API (which is used by TFDSQLiteBackup component) are just database copies, so they don't need to be processed anyhow more and can be opened the same way as their original.

How to write a file to an external storage?

Using external storage (if that's what you mean by SD card) for your datatabase backup files might be done if you keep the Write external storage option in your project settings enabled, and for the backup file build a path e.g. by using this code (it implements the getExternalStorageDirectory method). Your code might become (it's untested):

uses
  Androidapi.Helpers,
  Androidapi.JNIBridge,
  Androidapi.JNI.JavaTypes;

type
  JEnvironment = interface;

  JEnvironmentClass = interface(JObjectClass)
  ['{D131F4D4-A6AD-43B7-B2B6-A9222BC46C74}']
    function _GetMEDIA_BAD_REMOVAL: JString; cdecl;
    function _GetMEDIA_CHECKING: JString; cdecl;
    function _GetMEDIA_EJECTING: JString; cdecl;
    function _GetMEDIA_MOUNTED: JString; cdecl;
    function _GetMEDIA_MOUNTED_READ_ONLY: JString; cdecl;
    function _GetMEDIA_NOFS: JString; cdecl;
    function _GetMEDIA_REMOVED: JString; cdecl;
    function _GetMEDIA_SHARED: JString; cdecl;
    function _GetMEDIA_UNKNOWN: JString; cdecl;
    function _GetMEDIA_UNMOUNTABLE: JString; cdecl;
    function _GetMEDIA_UNMOUNTED: JString; cdecl;
    function _GetDIRECTORY_ALARMS: JString; cdecl;
    function _GetDIRECTORY_DCIM: JString; cdecl;
    function _GetDIRECTORY_DOCUMENTS: JString; cdecl;
    function _GetDIRECTORY_DOWNLOADS: JString;
    function _GetDIRECTORY_MOVIES: JString; cdecl;
    function _GetDIRECTORY_MUSIC: JString; cdecl;
    function _GetDIRECTORY_NOTIFICATIONS: JString; cdecl;
    function _GetDIRECTORY_PICTURES: JString; cdecl;
    function _GetDIRECTORY_PODCASTS: JString; cdecl;
    function _GetDIRECTORY_RINGTONES: JString; cdecl;
    {class} function init: JEnvironment; cdecl;
    {class} function getDataDirectory: JFile; cdecl; 
    {class} function getDownloadCacheDirectory: JFile; cdecl;
    {class} function getExternalStorageDirectory(): JFile; cdecl;
    {class} function getExternalStoragePublicDirectory(type: JString): JFile; cdecl;
    {class} function getExternalStorageState(path: JFile): JString; cdecl; 
    {class} function getExternalStorageState: JString; cdecl;
    {class} function getRootDirectory: JFile; cdecl;
    {class} function getStorageState(path: JFile): JString; cdecl; 
    {class} function isExternalStorageEmulated: Boolean; cdecl;
    {class} function isExternalStorageEmulated(path: JFile): Boolean; cdecl;
    {class} function isExternalStorageRemovable(path: JFile): Boolean; cdecl;
    {class} function isExternalStorageRemovable: Boolean; cdecl;
    {class} property MEDIA_BAD_REMOVAL: JString read _GetMEDIA_BAD_REMOVAL;
    {class} property MEDIA_CHECKING: JString read _GetMEDIA_CHECKING;
    {class} property MEDIA_EJECTING: JString read _GetMEDIA_EJECTING;
    {class} property MEDIA_MOUNTED: JString read _GetMEDIA_MOUNTED;
    {class} property MEDIA_MOUNTED_READ_ONLY: JString read _GetMEDIA_MOUNTED_READ_ONLY;
    {class} property MEDIA_NOFS: JString read _GetMEDIA_NOFS;
    {class} property MEDIA_REMOVED: JString read _GetMEDIA_REMOVED;
    {class} property MEDIA_SHARED: JString read _GetMEDIA_SHARED;
    {class} property MEDIA_UNKNOWN: JString read _GetMEDIA_UNKNOWN;
    {class} property MEDIA_UNMOUNTABLE: JString read _GetMEDIA_UNMOUNTABLE;
    {class} property MEDIA_UNMOUNTED: JString read _GetMEDIA_UNMOUNTED;
    {class} property DIRECTORY_ALARMS: JString read _GetDIRECTORY_ALARMS;
    {class} property DIRECTORY_DCIM: JString read _GetDIRECTORY_DCIM;
    {class} property DIRECTORY_DOCUMENTS: JString read _GetDIRECTORY_DOCUMENTS;
    {class} property DIRECTORY_DOWNLOADS: JString read _GetDIRECTORY_DOWNLOADS;
    {class} property DIRECTORY_MOVIES: JString read _GetDIRECTORY_MOVIES;
    {class} property DIRECTORY_MUSIC: JString read _GetDIRECTORY_MUSIC;
    {class} property DIRECTORY_NOTIFICATIONS: JString read _GetDIRECTORY_NOTIFICATIONS;
    {class} property DIRECTORY_PICTURES: JString read _GetDIRECTORY_PICTURES;
    {class} property DIRECTORY_PODCASTS: JString read _GetDIRECTORY_PODCASTS;
    {class} property DIRECTORY_RINGTONES: JString read _GetDIRECTORY_RINGTONES;
  end;

  [JavaSignature('android/os/Environment')]
  JEnvironment = interface(JObject)
  ['{83A2E94E-7D8E-432F-BE21-AEC2115015BE}']
  end;

  TJEnvironment = class(TJavaGenericImport<JEnvironmentClass, JEnvironment>);

function GetExtStgPath: string;
var
  Path: JFile;
begin
  Path := TJEnvironment.JavaClass.getExternalStorageDirectory;
  Result := JStringToString(Path.getAbsolutePath);
end;

function IsExtStgWritable: Boolean;
var
  State: JString;
begin
  State := TJEnvironment.JavaClass.getExternalStorageState;
  Result := State.equals(TJEnvironment.JavaClass.MEDIA_MOUNTED);
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  SourcePath: string;
  SourceFile: string;
  BackupPath: string;
  BackupFile: string;
begin
  if not IsExtStgWritable then
    raise Exception.Create('The external storage is not writable!');

  SourcePath := System.IOUtils.TPath.GetDocumentsPath;
  SourceFile := System.IOUtils.TPath.Combine(SourcePath, 'MySource.db');
  BackupPath := GetExtStgPath;
  BackupFile := System.IOUtils.TPath.Combine(BackupPath, 'MyBackup.db');

  FDSQLiteBackup1.Database := SourceFile;
  FDSQLiteBackup1.DestDatabase := BackupFile;
  FDSQLiteBackup1.Backup;
end;
Victoria
  • 7,822
  • 2
  • 21
  • 44
  • Here {class} function getExternalStoragePublicDirectory(type: JString): JFile; cdecl; i got this [DCC Error] Unit1.pas(63): E2029 Identifier expected but 'TYPE' found and change with {class} function getExternalStoragePublicDirectory(): JFile; cdecl;. File now saving to internal Storage instead of external – Fiaz Sep 12 '17 at 12:51
  • 1
    I'll try to setup environment for Android and test this. But it takes me some time. I don't think that applications have access to the whole external storage though, so I would beware of using hardcoded paths and use that [Environment](https://developer.android.com/reference/android/os/Environment.html) interface. I'll try to go deeper :) – Victoria Sep 12 '17 at 14:06
  • i find this solution (https://stackoverflow.com/questions/36766016/how-to-get-sd-card-path-in-android6-0-programmatically/40205116#40205116) but don't know how to use in Delphi might be it gives you an idea how to use it. – Fiaz Sep 12 '17 at 14:32
  • It wants FDSQLiteBackup1.DriverLink , what should I set? – djdance May 17 '20 at 13:12