Difference between revisions of "Firebird embedded"

From Lazarus wiki
m (Running FPC database test suite)
(Finished dbtestframework info; warning for Win64)
Line 11: Line 11:
 
* only single user access
 
* only single user access
 
* need to distribute some libraries along with your program when distributing it (unless you can statically link them)
 
* 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. [http://bugs.freepascal.org/view.php?id=21581 Create database bug report].
 +
Suggested to use a 32 bit FPC and Lazarus on Windows 64 for now.
  
 
== Installation ==
 
== Installation ==
Line 80: Line 84:
  
 
==== Programmatically ====
 
==== 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.
+
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.
This procedure could be called when initializing your form (FormShow event) or whenever you want to initiate the database connection:
+
A similar procedure could be called in Lazarus applications when initializing your form (FormShow event).
<syntaxhighlight>
 
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;
 
</syntaxhighlight>
 
  
 
== Running FPC database test suite ==
 
== Running FPC database test suite ==
Line 167: Line 116:
 
</syntaxhighlight>
 
</syntaxhighlight>
  
Then create the test database:
+
=== 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'''
 
'''isql'''
 
<syntaxhighlight lang="sql">
 
<syntaxhighlight lang="sql">
 
create database 'test.fdb' user 'SYSDBA' password 'masterkey' page_size 16384 default character set UTF8;
 
create database 'test.fdb' user 'SYSDBA' password 'masterkey' page_size 16384 default character set UTF8;
 
exit;
 
exit;
 +
</syntaxhighlight>
 +
... or this program:
 +
<syntaxhighlight>
 +
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.
 
</syntaxhighlight>
 
</syntaxhighlight>
  
=== Firebird libraries and databse ===
+
=== Running the test suite ===
Copy over the Firebird embedded libraries and other files for your architecture into the test directory.
+
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.
Then create a test database using isql or using the code below:
 
* to do: write code
 
  
Finally compile and run the database test program ''insert name''...
 
* finish this
 
  
 
== External references ==
 
== External references ==

Revision as of 13:45, 27 March 2012

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. Suggested to use a 32 bit FPC and Lazarus on Windows 64 for now.

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

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=

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