Firebird embedded

From Free Pascal wiki
Jump to navigationJump to search

What is it

Firebird embedded is a special version of the Firebird database server. It lets you run your programs without a dedicated database server. Instead, you add a library (DLL/.so/.dylib) to your application) and let your application access your Firebird .fdb database file using that library.

This is similar to the way programs like Microsoft Access and LibreOffice Base can work. Advantages:

  • runs on many architectures (Linux, OSX, Windows, FreeBSD, Solaris)
  • no separate server setup necessary, reducing complexity
  • you can easily switch from embedded to full client/server - without changing a line of code (except your connection string). This is probably a nice advantage compared to sqlite.

Disadvantages:

  • only single user access
  • need to distribute some libraries along with your program when distributing it (unless you can statically link them)

Warning: Windows x64

Warning: FPC x64 does not work well with the Firebird embedded dll. You can get errors/exceptions from the IBConnection object even if you write correct code (see e.g. Create database bug report. Workarounds:

  1. use a 32 bit FPC and Lazarus on Windows 64
  2. compile 64 bit FPC with -dTEST_WIN64_SEH in order to use the new Structured Exception Handling (SEH) code on Win64. Tested on FPC 2.7.1; works.

Installation

Firebird embedded and regular Firebird client libraries are different files. Firebird embedded can always serve as a regular client, making it a logical choice for inclusion in your installation.


Windows

Download and unzip the Firebird embedded kit. Make sure the .dll and .manifest files are both in your project directory (or your Lazarus root directory - useful if you program extensively with Firebird embedded) and in your executable output directory. Note that bitness must match: you must use the 64 bit embedded version if you compile your program for 64 bit, and 32 if you're writing 32 bit programs. Also make sure the the .dlls and .manifest files are placed in your project output (executable) directory, and distribute them with your applications, along with the license files.

As an indication, the minimum necessary files for Firebird 2.5 are: fbembed.dll firebird.msg ib_util.dll icudt30.dll icuin30.dll icuuc30.dll IDPLicense.txt IPLicense.txt Microsoft.VC80.CRT.manifest msvcp80.dll msvcr80.dll

Linux

For Debian, probably similar for Ubuntu: Get library files as well as the symlink: e.g. /usr/lib/x86_64-linux-gnu/libfbembed.so linking to /user/lib/x86_64-linux-gnu/libfbembed.so.bla.x.y

aptitude install libfbembed2.5 firebird-dev
  • todo: finish this

See How to setup Firebird embedded on Linux or Mac? for hints on working with embedded Firebird on Linux or Apple OSX.

OSX

  • todo: write this

FreeBSD

  • to do: write this

Tricks

As mentioned, Firebird embedded can work like a regular Firebird client. Therefore, using the embedded Firebird library instead of the client/server Firebird library can help make your application more flexible.

Use

Once you have the proper Firebird embedded libraries in the right path (see above), you need to specify that you are connecting to an embedded instead of a client server environment. Leave the hostname property of the IBConnection object empty. The rest of the properties work like in the client/server version, e.g. the DatabaseName is the full path to your .fdb file - or just the database name if it is in the same directory as fbembed.dll. Additionally, older versions of FPC may require that you set the UseEmbeddedFirebird property to true. This property is not present in the SQLDB units, but you need to add ibase60dyn to your uses clause.

Creating databases

Using Firebird ISQL

You can create databases by using the Firebird isql application (e.g. as provided in the regular Firebird (client) package). On Windows, this executable should be in the same directory as your fbembed.dll. Note: isql expects the fbclient library, not fbembed; you can copy and rename fbembed, e.g. into fbclient.dll on Windows, to fix this. Run isql, then do:

create database 'employee.fdb' user 'SYSDBA' password 'masterkey' page_size 16384 default character set UTF8;
exit;

Dropping/deleting a database would basically mean deleting the file, but you can also run: isql

connect 'employee.fdb' user 'sysdba' password 'masterkey';
drop database;
commit;
exit;

Programmatically

You can also create a database from within your program. The program in the Running FPC database test suite section below shows how you can detect missing databases and create them on the fly. A similar procedure could be called in Lazarus applications when initializing your form (FormShow event).


Running FPC database test suite

It can be useful to run the FPC database test framework (in packages\fcl-db\tests) when you are hunting bugs, trying to find out how something works, or improving the database code.

Telling the framework which database to use

You will need to edit the db test framework .ini file to tell the test executable that it needs to use your embedded database. See Databases#Running_FPC_database_tests for instructions on how to do this.

Creating the test database

If you have set up the framework to use Firebird, you will need to prepare the environment for testing.

Copy over the Firebird embedded libraries and other files for your architecture into the test directory.

Now create the test database... either using isql: isql

create database 'test.fdb' user 'SYSDBA' password 'masterkey' page_size 16384 default character set UTF8;
exit;

... or this program:

program firebirdembeddedtest;
{$mode objfpc}{$H+}

uses 
  {$IFDEF UNIX}{$IFDEF UseCThreads}
  cthreads,
  {$ENDIF}{$ENDIF} 
  Classes, SysUtils,
  sqldb, IBConnection {for Firebird}, ibase60dyn {for embedded firebird declaration};

const
  // Name for the database. Be sure to specify the same name in your database.ini
  DatabaseFile = 'test.fdb'; //Sensible name for a test database, right?
var
  DBParams: TStringList; //Store firebird-specific parameters
  Fire: TIBConnection;

begin
  //Connection to Firebird database
  // The next line is needed for older FPC versions; newer versions will first look for fbembed.dll in the application directory automatically
  UseEmbeddedFirebird:=true; // Using embedded (and fbembed.dll) or regular client/server (fbclient.dll), requires ibase60dyn in uses
  Fire:=TIBConnection.Create(nil);
  try
    Fire.HostName := ''; //must be empty for embedded Firebird; must be filled for client/server Firebird
    Fire.DatabaseName := DatabaseFile;
    // Username and password do not matter for authentication, but you do get authorizations in the database
    // based on the name (and optionally role) you give.
    Fire.Username := 'SYSDBA';
    Fire.Password := 'masterkey'; //default password for SYSDBA
    Fire.Charset := 'UTF8'; //Send and receive string data in UTF8 encoding
    Fire.Dialect := 3; //Nobody uses 1 or 2 anymore.
    DBParams:=TStringList.Create;
    try
      DBParams.Add('PAGE_SIZE=16384'); //I like a large page size (used when creating a database)
      Fire.Params := DBParams;
    finally
    DBParams.Free;
    end;

    // Find out if there is a database in the application directory.
    // If not, create it. Note: this may fail if you don't have enough permissions.

    // If you use client/server, you obviously don't need this part of the code.
    if (FileExists(DatabaseFile)=false) then
    begin
        writeln('File '+DatabaseFile+' does not exist.');
        writeln('Creating a Firebird embedded database...');
        // Create the database as it doesn't exist
        try
          Fire.CreateDB; //Create the database file.
        except
          on E: Exception do
          begin
            writeln('ERROR creating database. Probably problems loading embedded library:');
            writeln('- not all files present');
            writeln('- wrong architecture (e.g. 32 bit instead of 64 bit)');
            writeln('Exception message:');
            writeln(E.ClassName+'/'+E.Message);
          end;
        end;
        Fire.Close;
    end;
  finally
    Fire.Free;
  end; 
end.

Running the test suite

Finally compile and run the dbtestframework.pas database test program. It should generate status output on the console and XML output in files with test results that can be analysed and sent to developers.

External references

Earlier version of Firebird embedded on Linux