Creating an InterBase database on the fly with FireDAC

Recently I have been creating a number of applications using InterBase on iOS and Android using FireDAC components to talk to the database. One route to achieving this is by create the database file and table structure either via iSQL or using IBConsole GUI first before using it in your project. Once you have the database file (which is simple enough to do) you then have to deploy the file along with the application using the deployment wizard. This all adds a little size to the application package initially along with some extra configuration.

In the attempt to be lazy (and to save a few bytes in my application package), I decided that as most the time, I only really want to connect to a database that I then add the data to that maybe I should do this via code. This would save me having to deploy a database file and also setup the paths for deployment reducing my work upfront.

Luckily, this is easy to achieve with FireDAC.

Creating a blank database from the IDE

You can actually create a blank database file directly from the IDE by adding a TFDConnection to your application code. Double clicking on the connection opens the FireDAC Connection Editor. If you choose IB as the driver you will then see a list of values. You can set the username and password (e.g. SYSDBA / masterkey) to any registered local user. If you set the Database property to a local file path and then choose CreateDatabase and set it to True and "Test" the connection it will create a database file. Obviously, if the file exists it will error as the file is already there, but this is what you need to be able to do from code to get a blank database.

Creating a blank database on the fly

Understanding what properties need to be set, you can now call them directly from code at run time. To test this, I created a single TButton application with a TMemo to show output in a new FireMonkey Mobile Application.

Setting database file path

First task is setting the local file path at run time. There are two ways to do this with FireDAC. You can either set the path yourself e.g.

DBPath := TPath.GetDocumentsPath+PathDelim+'interbase'+PathDelim+'MyDB.ib'
FDConnection1.Params.Values['Database'] := DBPath;

or you can use ask FireDAC to sort out the documents path using $(doc) variable in the path string. This is useful if you want to hardcode this into the path property.

Setting CreateDatabase paramater

To avoid creating an error, we can set the CreateDatabase to True if the database file does not exist by using the function FileExists(FilePath : string) to check for the local file. This can be done as the database is local and we are running embedded.

    FDConnection1.Params.Values['CreateDatabase'] := BoolToStr(not FileExists(DBPath),True);

Creating a table, inserting values, reading them back.

Now there is access to a local database file, the next step is to create the table if it does not exist already. This can be done using calls to query the database meta data easily. While InterBase does not yet support language syntax for creating a table if it does not exist, you can collect the tables easily.

To try this on mobile a simple one button application with a memo for the output is enough

Using a TFDConnection, TFDGUIxWaitCursor and TFDPhysIBDriverLink I have also used TFDQuery and TFDTransaction to run the database transaction.

The following code block is my entire demo button click. You can see how it uses the FDConnection to GetTableNames into a StringList and then check for the value in the string list. Following that if the table doesn't exist, it creates the table and inserts a number of records using the block insert support that InterBase has that FireDAC supports. Once the data is inserted, then it is fetched all using the same query.

procedure TForm4.Button1Click(Sender: TObject);

var

  I: Integer;

  Tables : TStringList;

  DBPath: string;

begin

// Here to make it easier to debug to my standard local DB store on WIndows.

  {$IFNDEF MSWINDOWS}

  DBPath := TPath.GetDocumentsPath+PathDelim+'interbase'+PathDelim+'MyDB.ib'
  {$ELSE}

  DBPath := 'c:\data\MyDB.IB'
  {$ENDIF}

  try

    FDConnection1.Params.Values['Database'] := DBPath;

    FDConnection1.Params.Values['CreateDatabase'] := BoolToStr(not FileExists(DBPath),True);

    FDConnection1.Open;

    Tables := TStringList.Create;

    try

      FDConnection1.GetTableNames('','','',Tables); // Get Tables

      if Tables.IndexOf('FOO') = -1 then begin

        FDQuery1.SQL.Text := 'CREATE TABLE FOO (FOO_ID INTEGER)'
        FDQuery1.ExecSQL;

        FDQuery1.SQL.Text := 'INSERT INTO FOO (FOO_ID) VALUES (:FOO)'
        FDQuery1.Params.ArraySize := 10;

        for I := 0 to 9 do 

          FDQuery1.ParamByName('FOO').AsIntegers[I] := I;

        FDTransaction1.StartTransaction;

        try

          FDQuery1.Execute(FDQuery1.Params.ArraySize,0);

          FDTransaction1.Commit;

        except

          FDTransaction1.Rollback;

        end;

      end;

    finally

      Tables.Free;

    end;

    FDQuery1.SQL.Text := 'SELECT * FROM FOO'
    FDQuery1.Open;

    Memo1.BeginUpdate;

    try

      Memo1.Lines.Clear;

      while not FDQuery1.Eof  do begin

        Memo1.Lines.Add(FDQuery1.FieldByName('FOO_ID').AsString);

        FDQuery1.Next;

      end;

    finally

      Memo1.EndUpdate;

    end;

  except

    on e:exception do

      ShowMessage(e.Message);

  end;



end;

Running the application provides the following output

To Conclude

Using InterBase on mobile you can get a lot of benefits, especially when you are storing local data. Using the properties exposed in FireDAC you can easily create and manage a local database on the device without having to deploy one from the start. This helps with making deployment simpler. If you do need to deploy a database you can still do this as InterBase supports the same on disk structure for Windows, Mac, Linux, Solaris, Android and iOS.