Firebird in action

From Free Pascal wiki
Jump to: navigation, search

This is a guide about using Firebird in Lazarus/FPC, mostly about using Firebird with SQLDB (the FPC/Lazarus built-in database library). Other access methods are described further down the page.

Contents


Overview

Firebird is an open source, free database server that has been in use and developed for decades (it developed out of the Interbase 6 database that was open sourced by Borland). It includes rich support for SQL statements (e.g. INSERT...RETURNING), stored procedures, triggers, etc. You can write compiled UDFs (User-Defined Functions) libraries for the server in FreePascal if you want to extend the already extensive function list of Firebird.

Firebird offers both embedded (file-based) and client-server database - usable without having to change a single line of code in FPC/Lazarus. If used as an embedded database, it offers a richer SQL support than SQLite as well as seamless migration to a client-server database, although SQLite is a quite capable embedded database itself.

The latest stable version, Firebird 2.5, runs on Windows (32- and 64-bit), various Linux versions (32- and 64-bit), Solaris (Sparc and Intel), HP-UX (PA-Risc) and OSX.

It is not available on Windows CE/Windows mobile, and is being ported to Android at the moment.

Support for Firebird in FPC's SQLDB is quite good, comparable to the level of postgres PostgreSQL support. The database requires very little manual DBA work once it is set up, making it ideal for small business use or embedded use. It can grow to terabyte scale given proper tuning, although PostgreSQL may be a better choice for such large environments.

The sections below will cover FPC/Lazarus support for Firebird/Interbase using the SQLDB components included with FPC/Lazarus.

Documentation

Official documentation is included in FPC 2.6.2+: SQLDB documentation for IBConnection

Tutorial

See

Client/server and embedded installation

Firebird can run in client/server and embedded mode.

Client/Server means that you have a physical Firebird server somewhere: either on your local machine or another machine reachable over your network. Connections to the server go through TCP/IP; when specifying the connection, the hostname contains a name or IP address. The Firebird DLL you need for this is fbclient.dll (along with its support files).

Embedded Firebird means that your application loads the Firebird DLLs to access a Firebird database on the local machine. When specifying the connection, the hostname is always empty. The Firebird DLL you need for this is fbembed.dll (along with its support files). See the wiki page on Firebird embedded for more details.

Note that fbembed.dll can be used both for client/server and embedded use, so installing only this dll may be a smart thing to do.

Windows

Win64: please see warning here on not using certain FPC/Lazarus Win64 versions.

On Windows: you must have fbclient.dll (or fbembed.dll) and its support dlls installed in:

  • the executable output directory/the application directory (e.g. lib/something under your project directory)
  • or a directory in your PATH, e.g. the system directory:
    • c:\windows\system32 if you have a 32 bit OS or a 64 bit OS with a 64 bit DLL (yes, Microsoft name their directories strangely)
    • c:\windows\syswow64 for the 32 bit DLL if you have an x64 Windows and are developing 32 bit applications

The bitness of the dll must match your application: use a 32 bit library for a 32 bit compiled program and a 64 bit library for a 64 bit program.

Unix/Linux/OSX

On Linux/OSX/FreeBSD, the Firebird client library should be installed (e.g. by your package manager; install the regular package and the -dev package), or they should be placed in the library search path.

FPC searches for the most common library names (e.g. libfbclient.so.2.5, libgds.so, and libfbembed.so.2.5; please check ibase60.inc if your version is different). If you want to, you can explicitly specify the library name. There are 2 ways for this:

  • use the TSQLDBLibraryLoader component from sqldblib (FPC 2.7.1), see SQLDBLibraryLoader. Works for all SQLDB connector components.
  • call
    function InitialiseIBase60(Const LibraryName : AnsiString) : integer;
    with the correct library name (you may need to use unit ibase60dyn for this).

Connection examples

Example for client/server:

Hostname: 192.168.1.1
* The database is on the server with IP address 192.168.1.1. 
DatabaseName: /interdata/example.fdb  
* The name of the database file is "example.fdb" in the /interdata directory of the server (the machine with IP address 192.168.1.1).
Username: SYSDBA
Password: masterkey

Another example for client/server:

Hostname: dbhost
* The database is on the server with the host name dbhost
DatabaseName: F:\Program Files\firebird\examples\employee.fdb  
* The name of the database file is "employee.fdb" in the Program Files\firebird\examples directory on the F: drive of dbhost.
Username: SYSDBA
Password: masterkey

An embedded example:

Hostname: <empty string>
* Leaving the hostname empty selects embedded use.
DatabaseName: test.fdb
* The database file is "test.fdb" in the directory where the application runs (make sure fbembed.dll is in the application executable directory)
Username: SYSDBA
* On embedded, you do have to specify a username...
Password: <empty string>
* ... but it doesn't matter what password you give.

Troubleshooting client/server access issues

Make sure you started the Interbase/Firebird server on the server IP/hostname you specified. You can test connectivity by telnetting to the machine. Firebird usually listens on port 3050:

telnet 192.168.1.1 3050

You should see something, maybe just a blank screen, but you can type something. This means you can send data to the Firebird database.

Manually specifying InsertSQL, UpdateSQL, DeleteSQL

In complex cases, you might want to change the SQLQuery component's autogenerated InsertSQL, UpdateSQL and DeleteSQL statements. See Working_With_TSQLQuery#TSQLQuery.InsertSQL.2C_TSQLQuery.UpdateSQL_and_TSQLQuery.DeleteSQL:_Basic_Use_of_Parameters

Monitoring Events

Warning-icon.png

Warning: TFBEventMonitor requires FPC version 2.6.3 or higher

TFBEventMonitor in the FBEventMonitor unit is used to monitor named events sent from the database server. Events are typically generated by triggers on certain database operations.

Example: Updating a memo with events received. Monitoring events E1 through E7.

procedure TForm1.StartMonitor;
begin
  EventsM:=TFBEventMonitor.create(Self);
  EventsM.Connection:=IBConnection1;
  EventsM.Events.CommaText:='"E1",E2,E3,E4,"E5","E6","E7"'; //quotes are ignored
  EventsM.OnEventAlert:=OnFBEvent;
  EventsM.RegisterEvents;   // will activate IBConnection1 if not connected
end;
 
procedure TForm1.StopMonitor;
begin
  EventsM.UnRegisterEvents;  
  EventsM.Free;
end;
 
procedure TForm1.OnFBEvent(Sender: TObject; EventName: string;
  EventCount: longint; var CancelAlerts: boolean);
begin
  Memo1.Lines.Add(EventName+' occurred '+IntToStr(EventCount)+' times.');
end;

The event monitor stops monitoring when the linked TIBConnection is disconnected and will not restart automatically. Use the TIBConnection.AfterConnect and TIBConnection.BeforeDisconnect event handlers to start and stop the monitoring automatically.

The database server sends events to all connections registered for these events except for those created by the connection itself. Events are sent after a transaction is committed. Rolling back a transaction will cancel all events created in that transaction. fbeventstest.pp in the fcl packages/fcl-db/examples/ directory is a test/demo program that creates a stored procedure to send an event with an arbitrary name and uses a second connection to monitor the events sent.

Warning-icon.png

Warning: TFBEventMonitor creates a thread to monitor incoming events. On Unix include cthreads or any other thread manager.

Creating objects programmatically

While you can use tools such as Flamerobin to create tables etc, you can also create these programmatically/dynamically, which could be handy when letting your programs update existing database schemas to a new schema.

You can use e.g. TSQLQuery.ExecSQL to perform this task:

Query.ExecSQL('CREATE TABLE TEST(ID INTEGER NOT NULL, TESTNAME VARCHAR(800))');
// You need to commit the transaction after DDL before any DML - SELECT, INSERT etc statements.
// Otherwise the SQL won't see the created objects

You can also run a series of DDL statements at once, e.g. a dump of a database generated by FlameRobin's Database Properties/DDL: FlameRobinDatabasePropertiesDDL.png To do this, use the TSQLScript object, which you link to your existing connection and transaction objects, e.g. something like:

uses
...sqldb, TIBConnection,...
const
  DBSchemaFile='dbreporter.sql';
var
  FBScript:TSQLScript;
  ScriptText:TStringList;
  TranWasStarted: boolean;
begin
  TranWasStarted:=FTran.Active; //Ftran is the transaction, defined somewhere in our class
  if not TranWasStarted then FTran.StartTransaction;
  FBScript:=TSQLScript.Create(nil);
  ScriptText:=TStringList.Create;
  try
    if not fileexists(DBSchemaFile) then 
      raise Exception.CreateFmt('dbreporter: could not load database schema file %s',[DBSchemaFile]);
    ScriptText.LoadFromFile(DBSchemaFile);
    FBScript.DataBase:=(FConn as TIBConnection);
    FBScript.Transaction:=FTran;
    FBScript.Script:=ScriptText;
    // Now everythin is loaded in, run all commands at once:
    FBScript.Execute;
    //... and then commit to make them stick and show them to the SQL that comes
    // after the commit
    FTran.Commit;
  finally
    FBScript.Free;
    ScriptText.Free;
  end;
  // Make sure we leave the transaction state as we found it, handy for switchnig
  // between explicit start/commit transaction and commitretaining:
  if TranWasStarted then FTran.StartTransaction;

Database Administration

Note-icon.png

Note: TFBAdmin requires FPC version 2.6.2 or higher. Graphical components are included in at least Lazarus 1.2+

TFBAdmin in the FBAdmin unit is used for the administration of the database. It provides the following functionality:

  • add, modify or delete database users
  • backup and restore databases using single or multiple (split) files
  • get general database information
  • get the database log file listing important database events and errors

There is also a Lazarus component and demo that allows use of the FPC TFBAdmin component.

Example: Creating a multi file backup of a database on a linux server; 3 backup files, size limited to 600MB; backup progress shown in a TMemo:

procedure TForm1.logadm(Sender: TObject; msg: string; IBAdminAction: string);
begin
  Memo1.Lines.add(IBAdminAction+' : '+msg);
end;
 
procedure TForm1.backup;
var
  Admin:TFBAdmin;
  sl:TStringList;
begin
  Admin:=TFBAdmin.Create(self);
  sl:=TStringList.create;
  try
    Admin.UseExceptions:=true;
    Admin.Host:='192.168.2.98';
    Admin.Protocol:=IBSPTCPIP;
    Admin.User:='sysdba';
    Admin.Password:='masterkey';
    Admin.Connect;
    Admin.OnOutput:=@logadm;
    sl.Add('/home/firebird/test.bak1');
    sl.Add('/home/firebird/test.bak2');
    sl.Add('/home/firebird/test.bak3');
    Admin.BackupMultiFile('/home/firebird/test.gdb',sl,600000000,[IBBkpVerbose]);
  finally
    sl.Destroy;
    Admin.Destroy; //disconnects automatically
  end;
end;

A demo program is also available in the examples directory of the fcl-db package.

Common problems and solutions

Sometimes using Firebird in Lazarus seems to be tricky. Please find solutions below.

Attempted update of read-only column / COMPUTED BY fields

If you have COMPUTED BY fields (server-side calculated fields) in your Firebird table, SQLDB will not pick up that these are read only fields (for performance reasons).

In this case, auto-generated INSERTSQL,UPDATESQL statements can lead to error messages like "attempted update of read-only column". The solution is to manually specify that the field in question may not be updated after setting the TSQLQuery's SQL property, something like:

// Disable updating this field or searching for changed values as user cannot change it
sqlquery1.fieldbyname('full_name').ProviderFlags:=[];

Bigint: lost precision

If you use the bigint datatype (64 bit signed integer) in Firebird, please use .AsLargeInt instead of .AsInteger for parameters:

// Assuming ID is bigint here
sqlquery1.sql.text := 'insert into ADDRESS (ID) values (:ID);
// Use this:
sqlquery1.params.parambyname('ID').aslargeint := <some qword or 64 bit integer variable>;
//Don't use this:
//sqlquery1.params.parambyname('ID').asinteger := <some qword or 64 bit integer variable>;
...

... otherwise you might get errors like duplicate PK (primary key) - if using the bigint as a primary key.

INSERT INTO...RETURNING problems/Cursor is not open =

If you try to select SQL (e.g. Query.Open) with SQL like this:

INSERT INTO PEOPLE (NICKNAME) VALUES ('Superman') RETURNING ID

and get something like this error:

Database error:  : Fetch :
 -Dynamic SQL Error
 -SQL error code = -504
 -Invalid cursor reference
 -Cursor is not open(error code: 335544569)

while running FPC 2.6.0 (which is supplied with Lazarus 1.0) or lower, then you probably ran into an FPC SQLDB parser bug.

SQLDB thinks the statement you're running is a normal INSERT statement, which doesn't return data. Obviously, it should return data. Newer FPC code has fixes for this.

If you're using generators/sequences for your primary keys (like many do), a workaround is to first get the next sequence number:

SELECT NEXT VALUE FOR GEN_PEOPLEID FROM RDB$DATABASE /* If your generator name is GEN_PEOPLEID */

then use that to do a regular INSERT. see FAQ entry

Advanced transactions

Sources for this information/further reading:

Transaction isolation levels

If you want to, you can change the transaction isolation levels by adding a line in the transaction's Parameters property:

  • isc_tpb_read_committed: you see all changes committed by other transactions
  • isc_tpb_concurrency: also called Snapshot: you see database as it was when the transaction started. Has more overhead than isc_tpb_read_committed. Better than ANSI Serializable because it has no phantom reads.
  • isc_tpb_consistency: also called Table Stability: stable, serializable view of data, but locks tables. Unlikely you will need this

Example:

SQLTransaction1.Params.text:='isc_tpb_read_committed';

You can also add additional parameters that have an effect on the transaction (taken from the ibconnection.pp source file and [1]):

Access mode

This allow reads only or read/write

  • isc_tpb_read: read permission
  • isc_tpb_write: read+write permission

Lock resolution

  • isc_tpb_nowait: if another transaction is editing the record then don't wait
  • isc_tpb_wait: if another transaction is editing the record then wait for it to finish. Can mitigate "live locks" in heavy contention ([2]). See below for timeout value.

Table reservation

Deals with locking entire tables.

  • isc_tpb_shared: first specify this, then either lock_read or lock_write for one or more tables. Shared read or write mode for tables.
  • isc_tpb_protected: first specify this, then either lock_read or lock_write for one or more tables. Lock on tables; can allow deadlock-free operation at the cost of delayed transactions
  • isc_tpb_lock_read: Set a read lock. Specify which table to lock, e.g. isc_tpb_lock_read=CUSTOMERS
  • isc_tpb_lock_write: Set a read/write lock. Specify which table to lock, e.g. isc_tpb_lock_read=CUSTOMERS

Combinations:

  • Shared, lock_write: write transactions with concurrency or read committed isolation

can update the table. All transactions can read the table

  • Shared, lock_read: any transaction can read or update
  • Protected, lock_write: Other transactions cannot update the table. Only concurrency and

read committed transactions can read the table

  • Protected, lock_read: Other transactions cannot update the table. Any transaction can

read the table

Record versions

This setting is apparently only relevant for isc_tpb_read_committed isolation mode for records being modified by other transactions:

  • isc_tpb_no_rec_version: only newest record version is read. Can be useful for batch/bulk insert operations together with isc_tpb_read_committed)
  • isc_tpb_rec_version: the latest committed version is read, even when the other transaction has other uncommitted changes note: verify this. More overhead than isc_tpb_no_rec_version

Various options

For completeness, some more options appearing in the Firebird/Interbase FPC code. You will likely only ever use isc_tpb_no_auto_undo to speed up batch inserts/edits.

  • isc_tpb_exclusive (apparently translates to protected in Firebird, see [3])
  • isc_tpb_verb_time (Related to deferred constraints, which could execute at verb time or commit time. Firebird: not implemented, always use verb time)
  • isc_tpb_commit_time (Related to deferred constraints, which could execute at verb time or commit time. Firebird: not implemented, always use verb time)
  • isc_tpb_ignore_limbo (ignores the records created by transactions in limbo. Limbo transactions are failing two-phase commits in multi-database transactions. Unlikely that you will need this feature)
  • isc_tpb_autocommit (autocommit this transaction: every statement is a separate transaction. Probably specifically for JayBird JDBC driver.)
  • isc_tpb_restart_requests (apparently looks for requests in the connection which had been active in another transaction, unwinds them, and restarts them under the new transaction.)
  • isc_tpb_no_auto_undo (disable transaction-level undo log, handy for getting max throughput when performing a batch update. Has no effect when only reading data.)
  • isc_tpb_lock_timeout (specify number of seconds to wait for lock release, if you use isc_tpb_wait. If this value is reached without lock release, an error is reported.)

Firebird and ISO transactions

Firebird transaction do not map 1 to 1 to ISO/ANSI transaction levels. An approximation is:

  • ISO Read Committed=READ COMMITTED+RECORD_VERSION
  • ISO Read Committed=READ COMMITTED+NO RECORD_VERSION
  • ISO Repeatable Read=SNAPSHOT (also known as CONCURRENCY)
  • ISO Serializable=SNAPSHOT TABLE STABILITY (also known as CONSISTENCY)

Common combinations

Default is (probably, will have to check) read committed.

Batch/bulk insert

isc_tpb_read_committed and isc_tpb_no_rec_version could be a good combination: it allows other transactions to function while the batch is going on.

Read only transaction

If you want to only have read access to the database, you can do so by setting these transaction parameters:

  • isc_tpb_read
  • isc_tpb_read_committed
  • isc_tpb_rec_version
  • nowait

This combination will not block garbage collection, which is a good thing. Source: [4]

Note: the Firebird FAQ indicates you will need write access to the database file even if you only read from it, unless you set the database read-only flag (e.g. using gfix).

FBLib Firebird Library

FBLib is an open source library, for direct access to Firebird Relational Database from Borland Delphi, Kylix, Free Pascal and Lazarus.

Current features include:

  • Direct Access to Firebird 1.0.x 1.5.x, 2.x Classic or SuperServer
  • Multi platform [Win32, Gnu/Linux, FreeBSD)
  • Automatic select client library 'fbclient' or 'gds32'
  • Query with parameters
  • Support SQL Dialect 1/3
  • LGPL license
  • Extract Metadata
  • Simple Script Parser
  • Only 100-150 KB added into final executable
  • Supports BLOB Fields
  • Export Data to HTML or SQL Script
  • Service manager (backup, restore, fix…)
  • Events Alerter

You can download documentation on FBLib's website.

FBLib is no longer maintained by the original authors. As the tiOPF framework uses this library, their maintainers have continued fixing things. It is recommended to use the updated version via svn: [5]

IBX

Please see IBX.

Zeos

Please see ZeosDBO.

Links and more information

The list below shows links to more information on Firebird and related tools.

Lazarus Firebird samples

Tools

  • FreePascal FreePascal site
  • Lazarus Lazarus site
  • FlameRobin Flamerobin site Open source GUI tool to manage Firebird, available for Linux, Windows and Mac OSX. Highly recommended.
  • ibconsole : Tool to manage Firebird an Interbase Databases with a GUI, available for Windows and Linux
  • Lazarus Data Desktop - included in the Lazarus repository (found in the 'tools/lazdatadesktop/' directory)
  • tiSQLEditor - included in the "Support Apps" directory of the tiOPF repository. It is a tool used to write SQL with some code completion support, can run scripts, execute upgrade scripts for applications from one build to a later build, has various handy copy and paste functions for Object Pascal applications, has many features that are useful to tiOPF (creates Object Pascal code templates from query results, for tiOPF's visitor classes), export query results to CSV etc.

Firebird

  • The Firebird RDBMS Firebird site. This site also contains a lot of documentation on Firebird.
  • Firebird FAQ [7]. Handy site that shows e.g. differences with other RDBMS.
  • Firebird embedded on Linux MWA Firebird Personal info. Site dedicated to the IBX for Lazarus components; the Firebird setup should apply to the FPC SQLDB components as well.
Personal tools