Firebird embedded

From Lazarus wiki
Revision as of 10:14, 26 March 2012 by BigChimp (talk | contribs)
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)

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

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 snippet below shows how you can detect missing databases and create them on the fly. This procedure could be called when initializing your form (FormShow event) or whenever you want to initiate the database connection:

uses  ...sqldb, IBConnection {for Firebird}, ibase60dyn {for embedded firebird declaration}, DB...
...
procedure TForm1.ConnectionSetup;

const
  DatabaseFile = 'DATABASE1.FDB';
var
  DBParams: TStringList; //Store firebird-specific parameters
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
  IBConnection1.HostName := ''; //must be empty for embedded Firebird; must be filled for client/server Firebird
  IBConnection1.DatabaseName := DatabaseFile; //Filename of Firebird database
  // 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.
  IBConnection1.Username := 'SYSDBA';
  IBConnection1.Password := 'masterkey'; //default password for SYSDBA
  IBConnection1.Charset := 'UTF8'; //Send and receive string data in UTF8 encoding
  IBConnection1.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)
    IBConnection1.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.
  // In real applications, you probably won't store the db in the app directory,
  // but in a documents directory - or connect to a server.

  // If you use client/server, you obviously don't need this part of the code.
  if (FileExists(DatabaseFile)=false) then
  begin
      // Create the database as it doesn't exist
      SQLTransaction1.Active:=false;
      IBConnection1.Transaction:=SQLTransaction1;
      IBConnection1.CreateDB; //Create the database file.

      // Now create the required table:
      IBConnection1.Open;
      SQLTransaction1.StartTransaction;
	  // some sample code. You can create your own desired tables, views, stored procedures, etc, here...
      IBConnection1.ExecuteDirect('CREATE TABLE Table1 (id VARCHAR(255), name VARCHAR(255));');
      SQLTransaction1.Commit;
      IBConnection1.Close;
  end;

  //Finally switch on connection
  IBConnection1.Connected := True;
  IBConnection1.KeepConnection := True;
end;

Running FPC database test suite

It can be useful to run the FPC database test suite (in packages\fcl-db\tests) when you are hunting bugs, trying to find out how something works, or improving the database code. Copy database.ini.txt to database.ini, then make sure you have

[Database]
type=interbase

at the top. In the interbase section, enter something like this:

; Interbase or Firebird database:
[interbase]
connector=sql
connectorparams=interbase
; Database name/path (note: database needs to exist already)
name=test.fdb
; Default username/password for Interbase/Firebird
; is sysdba/masterkey. Change to your situation.
user=sysdba
password=masterkey
; Hostname:
; if using Firebird embedded: leave empty for embedded; 
; else: specify IP address or DNS name
hostname=

Then create the test database: isql

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

Finally compile and run the database test program insert name...

  • finish this

External references

Earlier version of Firebird embedded on Linux