Setting a relative path to sqlite database with Delphi and Firedac

Question

In replacement of my previous question which was confusing and poorly formulated, here is the "real" question.

I would like to known how to set, with Firedac, at runtime, a relative path to a sqlite database located in a subfolder of my application folder.

As Jerry Dodge stated :

Any application should never rely on writable data in the same directory anyway. Also, even if you did, you should make sure all your paths are relative to the application at least.

At the moment, the application I have in mind is portable and I would like the database file to be stored in a sub-folder of the main exe folder.

On the Form.Create event of my main form, is used first

path := ExtractFilePath(Application.ExeName);

And in then for FDConnection :

with FDConnection1 do begin
  Close;
  with Params do begin
    Clear;
    Add('DriverID=SQLite');
    Add('Database='+path+'Data\sqlite.db');
  end;
  Open;
end;

I keep on getting an error saying "unable to open database file".

I don't want to set the path to the database file in the FiredDac Connection Editor because then it would be absolute and bound to my machine, right ?

How could I set this path to the database file so that it would work in any configuration, wherever the user puts the application folder ?

Thank you all in advance

Math

Answer 1

As I found my own solution, I decided to post it here for future users who might encounter the same problem (that is to say a Delphi beginner level and the need to link a database file relative to their project exe file).

FIRST STEP was to add a data module to the project. This was done by going to File -> New -> Other -> Delphi Files -> Data Module

SECOND STEP Once the data module added to the project, as my main Application Form makes a call to the database on creation, I had to make sure the Data Module was created first. To achive that, I went to Project -> Options -> Forms and dragged the datamodule in first position of the list of auto created Forms

THIRD STEP was to drop a FDConnection on the datamodule and set all parameters EXCEPT database file.

FOURTH STEP was to add an OnCreate event to the datamodule, to specify the path to the database relative to the application exe and connect. It was done like this :

procedure TDataModule1.DataModuleCreate(Sender: TObject);
begin
  path := ExtractFilePath(ParamStr(0));

  FDConnection.Params.Add('Database='+path+'Database\sqlite.db');
  FDConnection.Connected := True;
end;

FIFTH AND FINAL STEP was to add the datamodule to the uses clause of all other units that needed a connection to the database.

I realize that this solution is far from perfect and that, as very experienced users already stated, storing the database in the same folder (or a sub-folder) as the main application Exe is not a good solution.

Also, I decided to connect to the database on DataModule creation, but another solution could be to connect on demand before triggering the queries and then disconnect. That's up to you and your needs

Thanks to all for your help, tips and advises

Math

PS : please notice I did not check my answer as accepted as the best answer, would not be fair right :-)

Answer 2

Introduction

IMHO

Database path and server name should not be hardcoded in applivcation.

Why ?

1. When you work on a project, you need to do many things on database connection, setting datasets, query etc. Usualy this is done on working database. Then server name and database path are different from those of the real database.
2. You should be able to set up server name and path to database easy and without to compile the project. This allows to set properly database connection params on a random computer.

Solution :

1. Setup the database connection component on design time, do not create it in runtime. Setup all parameters including server name , database path, charset etc. to your working copy of database. This will allow you to set up the other components associated with this database on design time. (In your answer I see you have done almost the same.)

2. Save server name, database path and any other parameter you want, to an exterrnal resource, ini file, windows registry or something else. Then get these parameters when application started or before connect to database.

   In your case, you use local server and the same path as application, so you don't need to store nothing.

Regarding the question

The code :

with FDConnection1 do begin
  Close;
  with Params do begin
    Clear;  <-- this removes all parameters
    Add('DriverID=SQLite');
    Add('Database='+path+'Data\sqlite.db');
  end;
  Open;
end;

removes all other parameters except DriverID and Database. Probably the error arise from that.

If you already setting all parameters in FDConnection:

Do not use:

FDConnection.Params.Add('Database='+path+'Data\sqlite.db');

This will add new parameter with the same name, but connection will use the first one.

This explains why everything works in your answer, because you did not set a parameter 'Database' on design time:

THIRD STEP was to drop a FDConnection on the datamodule and set all parameters EXCEPT database file.

Instead use :

FDConnection.Params.Database := 'Database='+path+'Data\sqlite.db'; 

You may use this for example in OnDataModuleCreate or FDConnectionBeforeConnect events

I hope this will be useful.

Answer 3

In android, I couldn't compile with fdConnection opened in design time, then certify it's closed. Your first line didn't work for me, but about takes the goal:

using following expression, it works for me:

FdConnection1.Params.Values['Database'] := GetHomePath
        + PathDelim + 'sqlite.db';

Look that I didn't use subfolder 'Data'. You can try simple first.

Answer 4

If your resources are light and read-olny for end-user, you could pack them direct into exe file. This would give you really portable application, which your user can run from arbitrary place, even from usb flash.

Go to Project->Resources and Images menu, add there you files.

You can access them in runtime with TStream:

var
  s:   TStream;
  {.....}
  s:=TResourceStream.Create(hinstance, 'myfile_1', RT_RCDATA);

Some resources you can handle direct in memory. As for sqlite database, you could copy it from app resources to user's documents path:

  MyAppPath := Tpath.GetDocumentsPath+'\MyAppName'; // you need System.IOUtils in uses  
  If not DirectoryExists(MyAppPath) then CreateDir(MyAppPath);
  MyDBPath := MyAppPath+'\Data\sqlite.db';
  If not FileExists(MyDBPath) then begin
    FL:=TFileStream.Create(MyDBPath ,fmCreate);
    FL.CopyFrom(s,0); // this will copy your db into 'sqlite.db' file 
  end;