Using Firebird: (work in progress)

Firebird server runs on a number of platforms, including:

The Firebird Embedded Server is a special variant which contains both the client and the server functionality. You can ship it with your application, unpack it, and it’s ready to roll. You’ll learn more about its up- and downsides later on in this guide.

A remote workstation or a local client requires only the shared client library — a dynamic link library on Windows and a shared object on other platforms — and an application program which can pass and receive parameters to and from the library’s interface.

Generally, you would also install a copy of the client library on the host server, for use with several of the Firebird command-line utilities and/or any server-based management programs you might use. Many of these utilities can also be run remotely, however. A remote system administrator can manage some of the essential services provided by these utilities by accessing them through a host service controller process.

For Java connectivity, Firebird provides the JDBC/JCA-compliant Jaybird driver. Client applications written against Jaybird can run on any Java-enabled platform, even those that don’t support Firebird server. The legacy InterClient Java driver is no longer maintained, due to its severe limitations.

The table below gives a quick overview of the main differences between Classic and Superserver. These differences will be discussed in more detail in the subsections that follow.

In abstract terms, neither architecture is a clear winner. One architecture generally outshines the other under specific workload conditions:

Besides Superserver and Classic, there’s Firebird Embedded Server for Windows, which you can download as a separate package. This is not really a different architecture, but a Firebird client plus Superserver rolled into one DLL for ease of deployment. Although it has a number of downsides, it may be an attractive option if you want to include Firebird with your Windows application. More on Embedded Server in the client-server chapter.

Disk space requirements vary somewhat according to platform, architecture and Firebird version.

In addition, third-party database management utilities will require 1 Mb to several dozen Mb, depending on which one(s) you choose.

The Firebird client library files are fbclient.dll (Windows), libfbclient.so (Linux network client) and libfbembed.so (Linux local client with embedded engine, Classic only). In order not to break certain existing applications, a copy of fbclient.dll with the old name gds32.dll can be installed on Windows machines. On Linux, legacy libgds* symlinks are installed automatically.

The server’s job is to

Clients' requests result in the server performing tasks such as

Starting at version 1.5, you can have multiple Firebird servers running on the same machine. Firebird 1.5 servers can also coexist with a Firebird 1.0 or InterBase server. Some extra configuration steps are required though.

One thing to be aware of is that Firebird 1.5 under Windows needs the CreateInternalWindow configuration parameter to be set to 0 in order to run multiple servers. As a side effect, the local connection protocol is disabled (you can still connect to local databases via localhost though). This limitation no longer exists in Firebird 2, and the CreateInternalWindow parameter has been removed in that version.

Server binaries on Windows are called fbserver.exe (Superserver) and fb_inet_server.exe (Classic). On Linux, the same names are used, but without the .exe extension.

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.

You can have multiple embedded servers running at the same time, and you can have multiple apps connecting to the same embedded server. Having a regular server already running isn’t a problem either. However, an embedded server locks a database file for its own exclusive use after successful connection. This means that you cannot access the same database from multiple embedded server processes simultaneously (or from any other servers, once an embedded server has locked the file).

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

Needless to say, this is not for your regular client-server database usage, as it bypasses a lot of security features. It’s using Firebird as a desktop database system.

First, download the Embedded server kit from SourceForge. It’s typically named Firebird-n.n.n.xxxx_embed_win32.zip, with n.n.n.xxxx the Firebird version and build number.

After unzipping, you’ll find the embedded server fbembed.dll in the root directory of the package, along with some other files. Additionally, there are three subdirectories doc, intl and udf.

To make your application work with the embedded server:

The most important benefit of all this is that you can easily pack the Firebird Embedded files with your application and have them installed or unzipped at your users' computers without having to perform a separate Firebird install there (with all the additional worries of sufficient privileges, possible interference with already present Firebird or InterBase servers, etc. etc.). You can also have several embedded server versions (say 1.5.3 and 2.0) running on the same computer without the need for special configuration steps to keep them out of each other’s way.

The Linux Classic server comes with a client library called libfbembed.so which is used for local connections. This library contains a full Firebird engine, which explains why it’s so much bigger than the Superserver client library. Local connections through this library are part of the user application process, not of a separate server process. Therefore, the user of the library must have filesystem-level access rights to the database — just as with Windows Embedded. So yes, this is a true embedded server.

There are also some differences. First, Linux Classic doesn’t require an exclusive lock on the databases it opens. The database remains accessible from other clients. A further — very important — difference is that Linux Classic validates every login against the security database: no connections with phony user names or passwords here! Finally, you can’t just ship libfbembed.so with your application and use it to connect to local databases. Under Linux, you always need a properly installed server, be it Classic or Super.

Firebird provides the capability to embed SQL statements in applications written in C/C++ and some other programming languages. The code is then passed through gpre, the pre-processor, which substitutes the embedded SQL statements with equivalent host language code that calls functions in Firebird’s client API library. The gpre pre-processor generates a file that the host language compiler can compile.

A special, extra subset of SQL-like source commands is available for this style of application, which are pre-processed into internal macro calls to the API. Known as Embedded SQL (ESQL), it provides a simpler, high-level language syntax for the programmer, that gpre can interpret and re-code according to the more complex structure of the equivalent API calls.

Some queries have to be run in exactly the same form every time they are needed. Queries like this are good candidates for embedding in the host language and pre-processing by gpre. The pre-processor turns them into API function calls, giving a somewhat better performance than SQL that has to be interpreted at runtime.

But many applications need to build queries that are at least partially dependent on information provided by the user — freely entered in a text box, or selected from a list of options. This is called Dynamic SQL or DSQL; that is, SQL code whose form is not (or not exactly) known at design time. DSQL can be embedded and preprocessed too, but some additional requirements and restrictions apply. More on this — again — in the InterBase Embedded SQL Guide.

Delphi and C++ data access components provide properties and methods to analyse and parse DSQL request statements and manage the results passed back. Applications that use ODBC or other generic interfaces always work with DSQL statements, even if the user doesn’t always see them. Query-by-example and other visual query tools for instance provide the user with a convenient, easy to use, and often “SQL-free” interface to extract, modify or delete data from the database. Yet the underlying code translates the user’s input into DSQL statements, which are subsequently passed to the ODBC (or other) layer.

Component interfaces provide methods and properties for building and preparing SQL template statements, allowing you to use placeholders for value criteria. At run-time, the application supplies input parameters of the appropriate data type to complete the statement. Provision is made also for retrieving output parameters from statements that return results after they are executed.

With the rise of rapid application development (RAD) tools in the past decade, the encapsulation of the API functions in suites of components presents a variety of attractive application development options for Firebird developers.

The Firebird client program supports two discrete application programming interface (API) modules. The most important is the core API, through which all database work is performed. A much smaller API module, the Services API, provides functions for accessing various command-line and other utilities from application programs. Developers can write high-level programming or script language applications that populate the data structures and call the API functions directly.

Firebird’s procedural language (PSQL) implements extensions to its SQL language, providing conditional logic, flow control structures, exception handling (both built-in and user-defined), local variables, an event mechanism and the capability to accept input arguments of almost any type supported by Firebird. It implements a powerful flow control structure for processing cursors which can output a dataset directly to client memory without the need to create temporary tables. Such procedures are called from the client with a SELECT statement and are known to developers as selectable stored procedures. Procedures that don’t return a dataset (although they may return result variables) are called executable stored procedures; they are called with EXECUTE PROCEDURE.

Stored procedures can call other stored procedures and can be recursive. All stored procedure execution, including selection of data sets from procedures and embedded calls to other procedures, is under the control of the single transaction that calls it. Accordingly, the work of a stored procedure call will be cancelled totally if the client rolls back the transaction.

Triggers are special procedures created for specific tables, for automatic execution during the process of committing DML work to the server. Any table can have any number of triggers to be executed before or after inserts, updates and deletions. Execution order is determined by a position parameter in the trigger’s declaration. Triggers have some PSQL extensions not available to regular stored procedures or to dynamic SQL, most notably the context variables OLD and NEW which, when prefixed to a column identifier, provide references to the existing and requested new values of the column. Triggers can call stored procedures, but not other triggers.

Work performed by triggers will be rolled back if the transaction that prompted them is rolled back.

Stored procedures and triggers cannot start transactions, since they are under transaction control themselves.

PSQL does not allow the execution of DDL (Data Definition Language) statements: it is strictly meant to operate on data, not on the structure of your database. Although you can circumvent this limitation with the EXECUTE STATEMENT syntax introduced in Firebird 1.5, it is generally considered unwise to do so. (Just because we give you a spade, it doesn’t mean that you have to dig your own grave.)

By design, in order to preserve its small footprint, Firebird comes with a very modest arsenal of internally-defined (native) data transformation functions. Developers can write their own very precise functions in familiar host-language code such as C/C++, Pascal or Object Pascal to accept arguments and return a single result. Once an external function — UDF — is declared to a database, it becomes available as a valid SQL function to applications, stored procedures and triggers.

Firebird supplies two libraries of ready-to-use UDFs: ib_udf and fbudf. Firebird looks for UDF libraries in its own UDF subdirectory or in other directories specified in the Firebird configuration file. In Firebird 1.5 and upward this is done with the UDFAccess parameter; in earlier versions with external_function_directory.