Firebird 3 Quick Start Guide

It is almost always advisable to uninstall any previous Firebird installations completely (after you’ve read the next paragraph!) and also hunt the Windows system dirs for old copies of gds32.dll and fbclient.dll. If you’re using Linux, the uninstall scripts should have removed any copies and/or symlinks in /usr/lib[64], but it won’t hurt to look if anything named libfbclient. or libgds. is still lying around.

Furthermore, you should be aware that Firebird 3 won’t open databases that were created by older versions. So before taking down your existing setup, you should back up all your databases in order that you can restore them later under Firebird 3.

You may also want to back up your old security database security2.fdb. Firebird 3 comes with an SQL script security_database.sql (located in misc/upgrade/security) that will upgrade the old security database to Firebird 3, preserving all information except SYSDBA's and except any passwords. For more information, see Compatibility Issues :: Upgrading a v.2.x Security Database in the Firebird 3 Release Notes.

The Firebird server — and any databases you create or connect to — must reside on a hard drive that is physically connected to the host machine. You cannot locate components of the server, or any database, on a mapped drive, a filesystem share or a network filesystem. (Well, you can, but you shouldn’t, and this technique isn’t covered here.)

Although it is possible to install Firebird by a filesystem copying method — such as untarring a snapshot build or decompressing a structured .zip archive — it is strongly recommended that you use the distributed release kit (.exe for Windows, .rpm for Linux), especially if this is the first time you install Firebird. The Windows installation executable, the Linux rpm program and the install.sh script in the official .tar.gz for various Posix platforms all perform some essential setup tasks. Provided you follow the installation instructions correctly, there should be nothing for you to do upon completion but log in and go!

Some installers ask you to choose between Classic, SuperClassic and Superserver mode. What are they?

Each mode is fully stable and there is no reason to categorically prefer one to the other. Of course, you may have your own specific considerations. When in doubt, just follow the installer default for now. Changing the server mode later can be done via the configuration file firebird.conf and requires a restart but not reinstallation.

Make sure you run the installer program as Administrator (i.e. right-click on the executable and choose “Run as Administrator”) or you may run into permission problems later!

On Windows server platforms Firebird will run as a system service by default, but during installation you can also choose to let it run as an application. Don’t do this unless you have a compelling reason.

The installer will also ask if you want to enable authorization for legacy (i.e. pre-3.0) Firebird clients. If security is a concern (as it should be), don’t allow this or allow it only temporarily while you upgrade your existing clients to Firebird 3.0. The legacy connection method sends passwords over the wire unencrypted; it also limits the usable length of the password to 8 characters.

During installation you have the option of providing a password for Firebird’s superuser, SYSDBA. Firebird passwords may be up to 255 bytes long, but due to the nature of the hashing algorithm the “effective length” is around 20 bytes, so it’s not very useful to enter a password that’s much longer than that. Notice however that if you do enter such a password, you must supply it in its full length every time you connect — it won’t work if you truncate it to the first 20 characters!

In all cases, read the Release Notes for the Firebird version you’re going to install. There may be significant variations from release to release of any Posix operating system, especially the open source ones. Where possible, the build engineers for each Firebird version have attempted to document any known issues.

Aside from being packaged with the download kits, Release Notes for all officially released versions of Firebird can also be found at https://www.firebirdsql.org/en/release-notes/.

If you have a Linux distribution that supports rpm installs, consult the appropriate platform documentation for instructions about using RPM Package Manager. In most distributions you will have the choice of performing the install from a command shell or through a GUI interface.

For Linux distributions that cannot process rpm programs, and for Unix flavours for which no .rpm kit is provided, use the .tar.gz kit. Quite often, installation is just a matter of untarring the archive and running install.sh. In some cases, the Release Notes or packed Readmes may instruct you to edit the scripts and make some manual adjustments.

The ping command — available on most systems — is a quick and easy way to see if you can connect to a server machine via the network. For example, if your server’s IP address in the domain that is visible to your client is 192.13.14.1, go to a command shell on the client machine and type the command

substituting this example IP address with the IP address that your server is broadcasting. If you are on a managed network and you don’t know the server’s IP address, ask your system administrator. Of course, you can also ping the server by its name, if you know it:

If you are connecting to the server from a local client — that is, a client running on the same machine as the server — you can ping the virtual TCP/IP loopback server:

or

If you have a simple network of two machines linked by a crossover cable, you can set up your server with any IP address you like except 127.0.0.1 (which is reserved for a local loopback server) and, of course, the IP address which you are using for your client machine. If you know the “native” IP addresses of your network cards, and they are different, you can simply use those.

Once you have verified that the server machine is reachable from the client, you can go on to the next step.

Most — but not all — installation packages start up the Firebird server as one of the final steps during installation, and also make sure that Firebird is started at every reboot.

After being launched, the Firebird server should be running:

The following sections show you how to test the server on each platform.

At present, no separate installation program is available to install only the client pieces on a Windows machine. If you are in the common situation of running Windows clients to a Linux or other Unix-like Firebird server (or another Windows machine), you need to download the full Windows installation kit that corresponds to the version of Firebird server you install on your server machine.

Fortunately, once you have the kit, the Windows client-only install is a breeze. Just run the installation program and when you arrive at the “Select Components” screen, choose one of the client-only options from the drop-down list or uncheck the “Server Components” checkbox.

A small-footprint client install program for Linux clients is not available either. Additionally, some Posix flavours — even within the Linux constellation — have somewhat idiosyncratic requirements for filesystem locations. For these reasons, not all *x distributions for Firebird even contain a client-only install option.

For most Linux flavours, the following procedure is suggested for a manual Firebird client-only install. Log in as root for this.

Instead of copying the files from a server, you can also pull them out of a Firebird tar.gz kit. Everything you need is located in the /opt/firebird tree within the buildroot.tar.gz archive that’s packed inside the kit.

One Firebird account is created automatically as part of the installation process: SYSDBA. This account has all the privileges on the server and cannot be deleted. Depending on version, OS, and architecture, the installation program will either

If the password is masterkey and your server is exposed to the Internet at all — or even to a local network, unless you trust every user with the SYSDBA password — you should change it immediately. Fire up isql or another Firebird client and connect to a database. In this example, the “employee” example database is used, because its alias is always present in a freshly installed Firebird setup:

If you do this in isql, it should respond with:

Now alter the sysdba password:

The SET keyword is optional, and instead of USER SYSDBA you can also use CURRENT USER, which always refers to the user you are logged in as.

If the command succeeds, you won’t get any feedback. Instead, isql will just print the next “SQL>”-prompt, thus indicating that all is well and your further input is awaited.

Please notice that unlike “regular” user names, Firebird passwords are always case sensitive.

Firebird allows the creation of many different user accounts. Each of them can own databases and also have various types of access to databases and database objects it doesn’t own.

Assuming you are connected to a database as SYSDBA, you can add a user account as follows:

The full range of user management commands is:

Tags are optional key-value pairs that can be freely defined by the user. The key (tag name) must be a valid SQL identifier, the value a non-NULL string of at most 255 bytes.

Only SYSDBA and co-admins can use all these commands. Ordinary users can change their own parameters (such as password, name parts and tags, but not active/inactive) using ALTER USER name or ALTER CURRENT USER. It is not possible to change an account name.

Examples:

Firebird user accounts are kept in a security database, which normally resides in the installation directory and is called security3.fdb (alias: security.db). Except in the case of so-called embedded connections (more about those later in this guide), connecting to a database always involves the security database, against which the user credentials are verified. Of course this is done transparently; the user doesn’t have to make an explicit connection to the security database.

However, in Firebird 3 this is not the end of the story. Firebird now allows the use of multiple security databases on a system, each security database governing a specific set of databases. A database can even act as its own security database.

Showing how to set this up is outside the scope of this Quick Start Guide. You can find full details in the Release Notes, chapter Security. But it is important to realise that if a system has multiple security databases, managing user accounts while connected to a database will always affect the accounts in the security database that governs that specific database. To be on the safe side, you may want to connect to the security database itself before issuing your user management commands. Connecting to the security database used to be forbidden in recent versions of Firebird, but is now once again possible, albeit by default only locally (which means that even the localhost route is blocked).

Note: What follows here is not essential knowledge for beginners. You can skip it if you like and go on to the Security section.

In Firebird 2.5 and up, SYSDBA (and others with administrator rights) can appoint co-administrators. This is done with the GRANT ADMIN ROLE directive:

The first command creates user bigbill as a Firebird administrator, who can add, alter and drop users. The second command grants administrator privileges to the existing user littlejohn.

To revoke administrator privileges from an account, use ALTER USER …​ REVOKE ADMIN ROLE.

An explicit local connection string consists of the path + filename specification in the native format of the filesystem used on the server machine, for example

Many clients also allow relative path strings (e.g. “..\examples\empbuild\employee.fdb”) but you should use these with caution, as it’s not always obvious how they will be expanded. Getting an error message is annoying enough, but applying changes to another database than you thought you were connected to may be disastrous.

Instead of a file path, the local connection string may also be a database alias that is defined in databases.conf, as mentioned earlier. The format of the alias depends only on how it’s defined in the configuration file, not on the server filesystem. Examples are:

Upon receiving a local connection string, the Firebird client will first attempt to make a direct, embedded connection to the database file, bypassing authentication but respecting the SQL privileges and restrictions of the supplied user and/or role name. That is, if the Engine12 provider is enabled in firebird.conf or databases.conf — which it is by default. If the database file exists, but the connection fails because the client process doesn’t have the required access privileges to the file, a client-server connection is attempted (by the Loopback provider), in this order:

You can force Firebird to use a certain protocol (and skip the embedded connection attempt) by prepending the protocol in URL style:

A TCP/IP connection string consists of:

Examples:

Notice how the aliased connection strings don’t give any clue about the server OS. And they don’t have to, either: you talk to a Linux Firebird server just like you talk to a Windows Firebird server. In fact, specifying an explicit database path is one of the rare occasions where you have to be aware of the difference.

A NetBEUI (named pipes) connection string consists of:

Examples:

Local URL-style connection strings have already been introduced.

A remote URL-style connection string consists of:

Examples:

Since XNET is a purely local protocol, you can’t have remote connection strings starting with xnet://.

Please be aware that some third-party client programs may have different requirements for the composition of connection strings. Refer to their documentation or online help to find out.

Firebird ships with a text-mode client named isql (Interactive SQL utility). You can use it in several ways to connect to a database. One of them, shown below, is to start it in interactive mode. Go to the directory where the Firebird tools reside (see Default disk locations if necessary) and type isql (Windows) or ./isql (Linux) at the command prompt.

[In the following examples, means “hit Enter”]

At this point, isql will inform you that you are connected:

You can now continue to play about with the employee database. With isql you can query data, get information about the metadata, create database objects, run data definition scripts and much more.

To get back to the OS command prompt, type:

You can also type EXIT instead of QUIT, the difference being that EXIT will first commit any open transactions, making your modifications permanent.

Some GUI client tools take charge of composing the CONNECT string for you, using server, path (or alias), user name and password information that you type into prompting fields. Supply the various elements as described in the preceding topic.

To create a database interactively using the isql command shell, type isql (Windows) or ./isql (Linux) at the command prompt in the directory where the Firebird tools are.

[In the following examples, means “hit Enter”]

Now you can create your new database interactively. Let’s suppose that you want to create a database named test.fdb and store it in a directory named data on your D drive:

The database will be created and, after a few moments, the SQL prompt will reappear. You are now connected to the new database and can proceed to create some test objects in it.

But to verify that there really is a database there, let’s first type in this query:

Although you haven’t created any tables yet, the screen will fill up with a large amount of data! This query selects all of the rows in the system table RDB$RELATIONS, where Firebird stores the metadata for tables. An “empty” database is not really empty: it contains a number of system tables and other objects. The system tables will grow as you add more user objects to your database.

To get back to the command prompt type QUIT or EXIT, as explained in the section on connecting.

In Firebird 3, if you try to create a database other than in embedded mode as someone who is not a Firebird admin (i.e. SYSDBA or an account with equal rights), you may be in for a surprise:

Non-admin users must explicitly be granted the right to create databases by a Firebird admin:

After that, they can create databases.

Notice that with a serverless connection, i.e. without specifying a host name or protocol before the database name (and Engine12 enabled!), Firebird won’t deny any CREATE DATABASE statement. It will only fail if the client process doesn’t have sufficient rights in the directory where the database is to be created.

Firebird accords with the SQL standard by truncating the result (quotient) of an integer/integer calculation to the next lower integer. This can have bizarre results unless you are aware of it.

For example, this calculation is correct in SQL:

If you are upgrading from an RDBMS which resolves integer/integer division to a float quotient, you will need to alter any affected expressions to use a float or scaled numeric type for either dividend, divisor, or both.

For example, the calculation above could be modified thus in order to produce a non-zero result:

In SQL, NULL is not a value. It is a condition, or state, of a data item, in which its value is unknown. Because it is unknown, NULL cannot behave like a value. When you try to perform arithmetic on NULL, or involve it with values in other expressions, the result of the operation will almost always be NULL. It is not zero or blank or an “empty string” and it does not behave like any of these values.

Below are some examples of the types of surprises you will get if you try to perform calculations and comparisons with NULL.

The following expressions all return NULL:

You might have expected 6 from the first expression and “Home sweet” from the third, but as we just said, NULL is not like the number 0 or an empty string — it’s far more destructive!

The following expression:

will return NULL if either FirstName or LastName is NULL. Otherwise it will nicely concatenate the two names with a space in between — even if any one of the variables is an empty string.

Now let’s examine some PSQL (Procedural SQL) examples with if-constructs:

Firebird is installed with forced writes (synchronous writes) enabled by default. Modifications are written to disk immediately upon posting.

It is possible to configure a database to use asynchronous data writes — whereby modified or new data are held in the memory cache for periodic flushing to disk by the operating system’s I/O subsystem. The common term for this configuration is forced writes off (or disabled). It is sometimes resorted to in order to improve performance during large batch operations.

One of the restore options in the gbak utility (gbak -rep[lace_database]) allows you to restore a gbak file over the top of an existing database. It is possible for this style of restore to proceed without warning while users are logged in to the database. Database corruption is almost certain to be the result.

If is practicable to do so, it is recommended to restore to spare disk space using the gbak -c option and test the restored database using isql or your preferred admin tool. If the restored database is good, shut down the old database (you can use the gfix command-line tool for this; see Firebird Database Housekeeping Utility (HTML) or Firebird Database Housekeeping Utility (PDF)). Make a filesystem copy of the old database just in case and then copy the restored database file(s) over their existing counterparts.

If you do not block access to users while performing a restore using gbak -rep then users may be able to log in and attempt to do operations on data. Corrupted structures will result.