Introduction to the FireBird Interbase Database

Questions

When connecting to a remote FB server with Delphi + ZeosLib, since ZConnection has a Hostname property, do I need to specify the hostname in the connection string? servername:/filesystem-path/database-file

How to add a trigger to the server so that it will autoincrement ID's in a table?

Introduction

Firebird is a fork (followed by a rewrite in C++) from the Interbase 6.0 code base that was open-sourced by Borland in 2000 but not followed upon by Borland. More on the history of Interbase here and here.

By default, the server listens on TCP3050. The network protocol is pretty chatty, so when accessing the server through the Net, you might want to use some middleware, write a Web-based app, use web services (SOAP, XMLRPC, etc.), or try Firebird 2.x.

The main files that make up Firebird:

More information:

Setup

  1. Download and run whichever installer matches your OS, eg. Firebird-2.x_Win32.exe
  2. To connect from Delphi, users recommend either ZeosLib (open-source), or dbExpress + UpScene driver (commercial). Here's how to install ZeosLib:

Here are the files that make up Firebird:

Server

To install the server, it is recommended that you run the installer instead of copying an existing install from another host. Once the installer is done, and you are running NT/W2K/XP, you should have two services registered: "Firebird Guardian Service", and "Firebird Server". Some applets are available that install in Control Panel to offer an easier access to the Firebird server.

To check that the server is running, go to Firebird's bin/ directory and run the CLI client isql.exe using the example database:

c:\Program Files\Firebird\Firebird_2_1\bin>isql.exe localhost:C:\Program Files\Firebird\Firebird_2_1\examples\empbuild\EMPLOYEE.FDB -u sysdba -pas masterkey

SQL > SHOW TABLE country;

SQL > SELECT * FROM country;

SQL > QUIT;

Client

The default option only installs the server. Rerun the installer, and choose "Installation of Client tools for Developers and database administrators".

To install the client, run the installer, and select "CLIENT ONLY". As of May 2004, there is no setup program to install just the client.

The default administrative account is sysdba, with password masterkey (masterke, actually, since passwords are eight-characters long). Change this ASAP using the gsec.exe command-line utility ("gsec -user sysdba -password masterkey"; once in the shell, "modify sysdba -pw mynewpassword", followed by "quit").

A test database ./examples/employee.gdb is available. If you are running the server under XP or ME, rename the extension from gdb to fdb (more information on this issue in the release notes.)

To connect to a Firebird database using a command-line interface, use ./bin/isql ("interactive SQL utility"). Once in the shell, run CONNECT "C:\FIREBIRD\EXAMPLES\EMPLOYEE.GBD" user 'SYSDBA' password 'masterkey'; . If you forget to end a statement with a semi-colon, the prompt changes to CON>. Just type the missing semi-colon to get back to the SQL prompt. To check that it's working, type SELECT * FROM RDB$RELATIONS; which lists all the rows in the system database (The identifiers for the metadata of a database begin with RDB$. Do not edit the metadata yourself.) End the session with QUIT; .

To create a database, launch isql, and type the following: CREATE DATABASE 'C:\FIREBIRD\TEST.FDB' page_size 8192 'SYSDBA' password 'masterkey'; .

Embedded server

The Windows Embedded Server is a Superserver engine plus client rolled into one library, called fbembed.dll. It is available as a separate download package. The embedded server (introduced with Firebird 1.5) was specifically designed to facilitate deployment of applications that ship with a Firebird server included. Installation is just a matter of unpacking the DLL and a few other files to the desired location. The security database is not used at all: anyone can connect to any database, as long as the user running the application has filesystem-level access rights to the database(s) in question. The embedded server has no facility to accept any network connections. Only true local access is possible, with a connect string that doesn't contain a host name (not even localhost). In Firebird 2.0, use of the embedded server as a network client is no longer possible.

http://www.firebirdsql.org/manual/ufb-cs-embedded.html

Programming

Summary of the Connectivity Approaches:

Connecting from Delphi

A list of drivers that connect directly to FB (no ADO, ODBC) can be found here:

Here's how to install the ZeosLib and configure it to work with Delphi2007:

  1. Install the Firebird client DLLs
  2. Download and unzip the package into a directory that won't move afterwards (or you'll have to reinstall)
  3. Open the Delphi IDE, and choose Tools > Options > Delphi Options > Library Win 32: In Library Path, add the path to where you unzipped the Zeos package, and packages\Delphi11\build
  4. Open packages\delphi11\ZeosDbo.groupproj, right-click each package below in this order, and choose Compile:
    1. ZCore.bpl
    2. ZParseSql.bpl
    3. ZPlain.bpl
    4. ZDbc.bpl
    5. ZComponent.bpl
    6. ZComponentDesign.bpl
  5. Close the Zeos project, and create a new VLC project: You should have a "Zeos Access" tab

Unlike MySQL, Firebird requires specifying the full path to the database file

DATABASE_PATH=localhost:/usr/local/db/testdb.gdb

USERNAME=milanb

PASSWORD=*****

And unlike MySQL again, the connection and the database selection occur in one step.

As an example of a trigger, you can use a generator to create a unique index in a table. Many tables use a unique index as a primary key. InterBase doesn't have an AutoInc field. Because multiple clients cannot generate unique identifiers, you can rely on the server to do this. Almost all SQL servers offer a counter you can call to ask for a new ID, which you should later use for the table. InterBase calls these automatic counters generators.

(How to create an autoincrement column?) "Firebird does support autoincrement columns via BEFORE INSERT TRIGGERs and GENERATORs. Generators are also named SEQUENCES in Firebird 2.0 and above - and are compliant to the SQL standard."

A generator can either be called with each INSERT, or be located in a trigger in the server:

CREATE TABLE test (field1 integer not null, field2 char(10), PRIMARY KEY (field1));
CREATE GENERATOR gen_test_id;
INSERT INTO test (field1, field2) VALUES (gen_id(gen_test_id, 1), 'testme');

Finally, Firebird stores the entire database in a single file, which has default extension .gdb.

Here's how to use the ZeosLib components to connect to the FB server, create a database, create a table, add a couple of records, SELECT them into a dataset with a query, attach this dataset to a datasource, connect this datasource to a NextDBGrid data-aware grid, let the user make changes through the grid, and save those changes back to the database with UpdateSQL:

 

Note: As of May 2009, ZeosLib seems like a bit of a messy project, so if you want to save time, you might want to check out commercial alternatives such as IBDAC:

Connecting from VB

Javier Soques' fbdll4vb based on the IBPP C++ client lets you access a Firebird database directly, without any need for ODBC or ADO.

Here's a version of fbdll4vb.dll using the fbembed.dll. Please note that the fbembed.dll and it's relative file are in the same directory of the vb projects.

Architecture

Classic Server

Classic architecture, the design in InterBase 4.0 and earlier, was process-based. For every client connection, a separate server process was started to execute the database engine, and each server process had a dedicated database cache. The server processes contended for access to the database, so a Lock Manager subsystem was required to arbitrate and synchronize concurrent page access among the processes. If you have only a few connections, the Classic Server is fine, and uses less ressources than the newer, Super Server architecture.

Super Server

More recently a multi-threaded architecture has been implemented where all of the separate server tasks share the same address space, and where possible multiple threads can be used in a single client request. In this mode the server runs as a privileged user (usually as either a root/firebird/interbase or interbas user) and only that privileged user needs to have access to the Firebird/Interbase system files.

SuperServer is a multi-client, multi-threaded implementation of the InterBase server process. This implementation replaces the "Classic" implementation used for previous versions of InterBase.

The Classic implementation predates the SuperServer implementation, and the SuperServer implementation is the future of InterBase.

Classic configuration is used on operating systems that currently don't have the technology for threaded applications, which is required for SuperServer. InterBase also distributes the Classic version on platforms that have threading technology, but which benefit from the low-profile implementation.

SuperServer has a greater ability to meet the demands of a growing multi-user system, while retaining good performance and efficiency. SuperServer is implemented in InterBase product on all platforms where it is technically practical. It is the intention that SuperServer is the future direction of InterBase on all platforms.

SuperServer v's Classic Architecture - The InterBase SuperServer Architecture

Embedded version

Embedded applications work by accessing API-like functions via internal macro calls. You use a specialised form of SQL (called ESQL) to code static queries into your source. You pass your sources through a precompiler, which parses out the ESQL and replaces it with host code for onward compilation with your other sources to generate your embedded server application object.

The name of the precompiler is gpre - you will find it in the /bin directory, along with the other tools. The bad news for you is that it works only with C/C++ and Cobol as host languages - no VB, not even plain ol' BASIC.

You could try writing your own precompiler for VB. AFAIK, the gpre sources are in the Firebird tree, in the gpre branch. At the following link you will find some function and migration specs for gpre:

http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_func_design

You should also be able to find the Embedded SQL Guide amongst the IB6 beta docs set at the InterBase Downloads page of the same site.

----------------------------------------------------------

Firebird 1.5 Embedded Server notes (Beta and RC builds)

-----------------------------------------------------------

1. GENERIC INFORMATION

  The embedded server is a fully functional server linked  as a dynamic library (fbembed.dll). It has exactly the  same features as the usual server and exports the   standard Firebird API entrypoints.

2. ISSUES AND LIMITATIONS

  2.1. Registry

    The Firebird registry entries are ignored. The root     directory of the embedded server is the directory of     its binary file (library).

  2.2. Database access

    Only true local access is allowed. The embedded server     doesn't have any support for remote protocols, so even    access via "localhost" won't work.

  2.3. Authentication and security

    The security database (namely security.fdb) is not used     in the embedded server and hence is not required. Any     user is able to attach to any database. Since both     the server and the client run in the same address space,     the security becomes just an agreement between both     sides which can be easily compromised.

    But note that SQL privileges are still checked.

 2.4. Compatibility

    You may run any number of applications with the embedded     server without any conflicts. Having IB/FB server running    is not a problem either.

    But you should be aware that you cannot access single     database from a number of the embedded servers     simultaneously, because they have SuperServer architecture     and hence exclusively lock attached databases.

3. USAGE

  Just copy fbembed.dll into the directory with your   application. Then rename it to either fbclient.dll or   gds32.dll depending on your database connectivity software.   Then start your application and it will use the embedded   server as a client library and will be able to access   local datasases. You should also copy firebird.msg and   firebird.conf (if necessary) to the same directory.

  If external libraries are required for your application,   then you should have them separately. Most probably, it   will be INTL support (fbintl.dll) or UDF libraries. To   be able to use them, you should place them into the   directory tree which emulates the Firebird server one,   i.e. has subdirectories like /intl or /udf. Then open   your firebird.conf and set RootDirectory to the   aforementioned directory tree. Example:

  /my_app/app.exe

  /my_app/gds32.dll

  /my_app/firebird.conf

  /my_app/fb/firebird.msg

  /my_app/fb/intl/fbintl.dll

  /my_app/fb/udf/fbudf.dll

 

  firebird.conf:

  RootDirectory = /my_app/fb

FireBird vs. MySQL

InterBase and MySQL, a Technical Comparison by Bill Todd

Q&A

How to drop a table?

drop table mytable;

How to create a database at runtime?

"Firebird doesn't provide a way to create database using SQL. You need to either use the Services API, or external tool. As API for database creation is often not available in libraries, you can call Firebird's bin\isql.exe tool to do it for you", eg.

CREATE DATABASE 'C:\dbases\database.fdb' user 'SYSDBA' password 'masterkey';

Note: In Windows at least, the filename is in capital letters.

How to connect to a remote Firebird server and open a database?

Strangely enough, Firebird requires telling the server the absolute path to the database file, eg.

sofa:D:\Misc\Friends\Rich\Lenders.fdb

where "sofa" is the hostname.

In addition, connecting to a database requires a user/password, and be granted permissions to work with whatever the database contains (tables, indexes, etc.)

Any book on Interbase/Firebird?

Helen Borrie's "The Firebird Book: a Guide for Database Developers" (2004).

What is FB 1.5?

A rewrite in C++ of 1.0, which was written in C.

Which flavor of SQL does Firebird support?

Three kinds. If you are not familiar with them, go for dialect #3.

Direct File Access?

Where the client application access the .gdb/fdb files directly instead of through the Firebird server.

Can I disable the default force writes on Windows?

Don't even think about it. Synchronous writes are good for you.

Any tool to replicate a database?

Take a look at IBReplicator.

Support

You can purchase support from IBPhoenix

IBObjet?

IBObjects data access components for InterBase® & Firebird. InterBase Objects 

Not long after the release of Delphi 2, Jason discovered that the best capabilities of InterBase were virtually inaccessible to the RAD developer because of the constraints imposed by the BDE. In 1996 he determined to start his own suite of components and data-aware GUI controls to bypass the BDE and work directly with the InterBase API. He chose to develop a data access hierarchy that was independent of the TDataset and the standard Delphi data-aware visual controls, thus affording himself the opportunity to design a library of components and visual controls specifically for the client/server architecture, which could be fully customized through succeeding releases of InterBase.

Performance (IBO vs BDE)

BDE?

Is direct access to InterBase's Application Programming Interface (API) better than using the BDE and, if so, why?

Borland Database Engine (BDE)

The BDE has a life stream that goes back many years before the advent of RAD tools, predating even 16-bit Windows by some years. It first came to prominence as the engine behind Paradox 3.5 and Quattro Pro, was modified a little later to provide an API for DBaseIII-Plus, FoxBase (later DBaseIV and FoxPro) and some other ISAM databases, as well as an API to SQLLinks pass-through drivers for several SQL engines in the pre-Windows era. It moved onto the Windows platform with the first, unhappy, premature Paradox for Windows release. A little after that, at version 3.something, it redeemed its glorious DOS reputation with a very fast, stable release accompanying the 16-bit Paradox 5 for Windows.

From Delphi 1 forward, the BDE has continued to grow as an API layer between the RAD platform and native or open drivers for an increasing number of database platforms. Although it has not been free of aches and pains, it has had a significant part to play in the success of Delphi in the database arena over the past five years.

Several factors about a BDE implementation inhibit the developer from optimizing InterBase for performance and effectiveness. The BDE-plus-driver combination adds two layers of interface code to slow down the connection between the client application and InterBase's own API layer.

IBX?

Performance (IBX vs BDE) Converting a BDE Application to IBX

Two major suites of Delphi database components are available for InterBase - the InterBase Express set (IBX), that shipped as a Beta with Delphi 5, and the IB Objects set (IBO), which has been in production releases since 1997

Borland Delphi and C++Builder developers can now use the InterBase Express (IBX) components to build application with InterBase through the InterBase API, improving performance and capabilities.

During the period when Delphi 3 and 4 were the current releases, Greg Deatz and a group of helpers and testers developed the FreeIBComponents suite of data access classes, including datasets inheriting from the native VCL's TDataset, for connecting directly to the InterBase API.

Towards the end of 1998, with Delphi 5 on the drawing board, Borland/Inprise decided to license the rights to the FreeIB suite's data access classes and develop them to ship as components of the new version's VCL. The somewhat transformed FreeIB became part of the "Kinobi" project for InterBase 6. The developer was Ravi Kumar. A beta of the components, renamed InterBase Express, shipped with Delphi 5 in the Fall of 19

Introduction To InterBase Express (IBX)

MGA?

The InterBase server implements a Multi-Generational Architecture [MGA]. This MGA provides unique 'versioning' capabilities that result in high data availability for transaction processing users and decision-support users simultaneously.

Traditional database servers support the On-Line Transaction Processing (OLTP) model of database interaction, with many short, simple transactions. The InterBase MGA engine performs well on short OLTP-style transactions, but it excels in real world applications, outperforming other databases because concurrent long-duration, decision-support transactions do not degrade its performance.

UDF?

User-Defined Functions (UDFs) are libraries of functions that you can add to extend the set of functions that the InterBase server supports. The functions in your UDF library execute within the process context of the InterBase server.

A user defined function (UDF) in InterBase is merely a function written in any programming language that is compiled into a shared library. Under Windows platforms, shared libraries are commonly referred to as dynamic link libraries (DLL's).

FB Server, FB Client, FB IntraClient, Components(FBX), FB Documentation, ODBC, Replicator, FB Console?

The client component is just the library that surfaces the Application Programming Interface function structures to a client application, allowing it to connect to and communicate with the server.

Firebird has left InterClient and Interserver alone, because the amount of effort required to get it working and up to date with latest standards just wasn't considered worth it. Instead, a Client-Java project branch formed and has spent two years developing a totally new JDBC Type 4 driver. The driver is named JBird - pronounced "Jaybird".

Borland still markets its commercial InterClient 2 products.

There's no such thing as FBX. Borland still develops and markets the IBX components for its InterBase products and some versions are available free from their CodeCentral repository.

SQL Links?

Temp stuff

Server = ibserver OR fbserver.exe

Client = gds32.dll (if > 1.5, add fb32.dll or fbclient.dll)

Guardian = process watcher

Default accountr = sysdba/masterkey

To change password, gsec.exe -user sysdba -passwd mypass ; gsec modify sysdba -pw test

sample = employee.gdb

ibexpress = stuff for Delphi

isc4.gdb = security DB

isql.exe and wisql32.exe = command-line clients

either direct api calls, or through sql -> gpre

Different ways to access : BDE (middleware), embedded SQL, native API, ODBC, Java

Since 2K and XP can restart services that died, Guardian is not strictly necessary.

Q&A

What's the difference between Classic and Super Server?

gbak.exe, fbclient.dll, fbembed.dll?

How to deploy the server and the client?

Does the server require running an installer, or is there just a single EXE like the Fossil SCM?

How to use Firebird in a .Net application?

Can the server be located through a broadcast?

... so as to avoid hard-coding the IP/name or prompting the user for it.

Resources

Sites and documents

Useful tools