Search code examples
sqlitedelphirelative-pathfiredac

Setting a relative path to sqlite database with Delphi and Firedac

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

Solution

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