Computing: Website and Database Programming

Web development environment setup on MS Windows.

7.4. Firebird database server.
7.4.0. Firebird distributions.
"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. 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." This is how Firebird is described on the Firebird page of the Free Pascal wiki.
Firebird offers both embedded (file-based, as for example SQLite) and client-server (as MySQL, MariaDB, PostgreSQL...) databases. This tutorial is about client-server (TCP/IP based) databases only. It describes the installation of Firebird 4.0.2 (64bit) on Windows 10. Installation on other Windows releases should be similar. Concerning older versions of Firebird, the content of this tutorial might not always apply.
Firebird may be downloaded free of charge from the Firebird Download page. I myself have downloaded the Windows 64bit installer, filename: Firebird-
7.4.1. Installing Firebird.
Double-click the installer to launch the setup wizard. After the choice of the language, the license agreement, the display of the installation instructions and the choice of the installation folder (default for Firebird 4.0: C:\Program Files\Firebird\Firebird_4_0), you come to the Components selection window (screenshot on the left). In our case, the default option Full installation of Server and development tools is all ok. The next window allows you to configure Additional tasks (screenshots on the right), in particular to choose between 3 different server architectures. For "normal" use, the default option SuperServer mode should be selected. It's possible to run Firebird as an application, but you should let the default option to run it as a service. Starting it with Windows or not depends on your personal preferences. Copy Firebird client library to <system> directory should be checked (this will, for example, allow to develop a Lazarus application that accesses a Firebird database without having to copy the client into the project output folder). Legacy Interbase support is normally not needed (let the checkbox unselected).
Firebird installation: Component selection
Firebird installation: Additional tasks configuration
In the next window, you are asked to set the password of the Firebird super-user, who is actually called SYSDBA, then you pass to the Ready to install window, where a summary of your installation choices is displayed. Push the Install button to do the installation. After a window showing further information about the installed version of Firebird, you come to the last screen, where you can choose to now start or not the server. Push the Finish button to terminate the setup wizard.
You can start and stop the Firebird server in Windows Services. Here you can also change the server's properties, in particular if you want to start it automatically with Windows or manually.
The Firebird default server instance in 'Windows Services'
7.4.2. ISQL and third-party GUI clients.
Firebird ships with the command line client ISQL (Interactive SQL utility). You can use it to enter SQL statements concerning administration (e.g. creating users), data definition (e.g. creating a database or its tables), data manipulation (e.g. selecting or inserting some records), and other. The connection is done either by the command CONNECT (for an existing database), or the command CREATE DATABASE. This is a particularity of Firebird (compared to MySQL and other RDBMS): The client connects to a given database rather than to the server. With either of these two commands, a user name and a password have to be specified. It is also possible to specify a given role (cf. section 9.4).
The typical command to connect with ISQL to a database (here with the super-user to the "employee" database):
    CONNECT employee USER SYSDBA PASSWORD password;
This is in fact a simplification, as with the "complete" command you also specify the way how you want the client to connect (cf. the Firebird documentation for details).
Please, note that the commands in ISQL are multiple line statements. Hitting ENTER does not automatically execute the statement, but does a line feed and in the new line (having the prompt CON> instead of SQL> that indicates the first line of the statement) where you can continue the statement text. To terminate a statement, use a semicolon (;). If now you hit ENTER, the statement is executed. To exit the ISQL utility, use QUIT; or EXIT;. The difference is that with EXIT all open transactions are committed before the program terminates.
The screenshot below shows the execution of some SQL statements in ISQL.
Executing SQL statements in the command line tool ISQL
Note: In ISQL, transactions are normally not automatically committed. So after, for example, executing an UPDATE statement, you have to execute the statement COMMIT; to physically change the database content.
There is no GUI client available from the Firebird developers themselves, but there are several third party desktop and web applications. I did not take the time to try to evaluate them, but more or less randomly selected one of them to install, actually FlySpeed SQL Query. "With FlySpeed SQL Query you can browse and edit data in grid form or via the customizable form view, quickly find and filter data, and set up master-detail views. The visual query designer lets you create new SQL queries by drag'n'drop, and represent off-the-shelf queries visually. It makes SQL query analysis, creation, and modification much simpler. Using FlySpeed SQL Query, you can build parameterized queries, and browse data from linked tables via foreign keys.", they describe the application on their website. You can download FlySpeed SQL Query for free from the ActiveDBsoft website).
The first time that you launch the application, you are asked for the connection parameters. In the Selecting the connection type window (screenshot on the left), select Direct database server connection (other possibilities are OLE DB and ODBC) and choose Firebird as connection type (FlySpeed SQL Query may be used with most actual databases). In the Connection properties window (screenshot on the right), select the TCP/IP protocol, set the host to localhost and the port to port 3050 (Firebird default port). Enter SYSDBA as user name and also enter their password. Trying out the connection with these settings will fail with the message I/O Error during "CreateFile (open)". The reason for this is that it's mandatory to specify a database; "employee" in our case.
FlySpeed SQL Query: Configuration - Connection type selection
FlySpeed SQL Query: Configuration - Direct Firebird server connection properties (TCP/IP)
In the last window of the New connection wizard, you can specify a name for the connection; for example: "Firebird (employees)", and choose if you want to open this connection automatically when the application starts. In the application start-up window, you can create a new connection and edit an existing one using the items of the Connection menu. You can choose a given connection from the drop-down list, and then clicking the Connect link to connect. If the password has not been stored with the other connection properties, you'll have to enter it now.
FlySpeed SQL Query: Connecting to the 'employee' sample database
Double-clicking a table in the left pane opens it in Query Builder. A visual tool to build queries, they say ... I didn't try out this feature, but I entered all my SQL manually, by choosing Query > New text query from the menu.
FlySpeed SQL Query: Table opened in 'Query Builder'
Clicking the Execute button executes the query, the default being a SELECT * FROM <table>. You can work on the displayed dataset a little bit in the same way as in MySQL Workbench or similar applications.
FlySpeed SQL Query: Tabular display of the query result dataset
7.4.3. Creating Firebird users.
User management of a Firebird database isn't so simple as it is with MySQL, where you can give a user any global, database or table related privileges by doing the corresponding selections in the administration part of a graphical interface such as MySQL Workbench or phpMyAdmin. With Firebird, you'll have to create the users and define their privileges by entering the corresponding SQL statements. This requires that you understand the basics of Firebird security. Here a (largely simplified) introduction to Firebird security; for details, cf. the Firebird Language Reference, that may be downloaded in PDF format from the Firebird website.
As we have seen, the Firebird super-user is called SYSDBA. This user has full privileges on all databases, including those where security related data is stored. You can say that SYSDBA can "do what they want", what is practical, of course, but on the other hand is dangerous because, if you don't pay attention, you can easily corrupt or destroy entire databases.
Regular users must be created using the CREATE USER ... statement. Once created, a user can connect to a database by specifying their password. However, this is all they can do. What I mean is that at this stage the user can't access any object in the database. To access an object in the database, the user must have privileges for this object. These privileges may be to only read data (SELECT), to change data (INSERT, UPDATE, DELETE), to create, change and drop database objects (CREATE DATABASE, CREATE TABLE, ALTER ...), and others. You give privileges to a user using the GRANT ... TO USER ... statement.
So, basically a standard user has only those privileges that they have been granted. In other words: A newly created user has no privileges until they have been granted to them. However, it's a little bit more complicated:
  • The database owner (user, who created the database) has automatically full administrative privileges on that database.
  • All users are automatically granted the privileges of a special user called PUBLIC. So, if user PUBLIC has full access to a given database (what should, of course, never be the case in a real life situation), then creating a new user without specifying any grants, will nevertheless give them full administrative privileges on that database.
Another important thing to understand is the concept of roles. A role is a set of privileges, a user has, when connecting to a database. Here an example: Suppose that we had created a user called "user1" and a role called "access_employees" and that this role has been granted the privileges SELECT, INSERT, UPDATE, DELETE on all tables of the "employee" database. Also suppose that we had granted the role "access_employees" to user "nemo". In order to manipulate the records in any table of the "employee" database, we could connect in ISQL using the command:
    CONNECT employee USER nemo PASSWORD password ROLE access_employees;
What do you think will happen if we connect without explicitly specifying the role? As "nemo" has been granted the "access_employees" role, would they still be authorized to manipulate the data in the "employee" database? No! If they tried, Firebird would answer with a no permission error message. This seems not logical, but it's just a question of correctly understanding the Firebird security concepts. Granting a role to s user means not less and not more than that this user is authorized to connect with this role. It doesn't tell anything about the connection itself. Thus, if "nemo" has been granted the "access_employees" role and connects with this role (for example by specifying ROLE access_employees in the connect command), they may access the data. If no role is specified, "nemo" is not authorized to access the data, even if they have been granted the "access_employees" role. Except, and that's a nice thing up from Firebird version 4, the "access_employees" role has been granted as default role.
A regular, authenticated user has no privileges on any database object until they are explicitly granted to that individual user, to a role granted to the user as a default role, or to all users bundled as the user PUBLIC.
Now its time to create the users for our "employee" database (you can do it using ISQL or FlySpeed SQL Query). As I usually do with MySQL databases, I also did with Firebird: Creating a read-only user "nemo", who can read (select) all data from all databases, and a read-write user "allu", who can manipulate (select, insert, update, delete) all data from all databases.
The important thing to know is that user PUBLIC has been granted full access to the "employee" database, so any user created would automatically inherit these grants. Thus, the first step is to revoke the privileges of user PUBLIC:
Second step: Lets create the two users:
    CREATE USER nemo PASSWORD 'nemo';
    CREATE USER allu PASSWORD 'Password';
Third step: Lets create two roles "ro_user" and "rw_user" and grant them the privileges to select resp. fully access all records in the database. These privileges may be granted using two predefined system privileges:
    CREATE ROLE ro_user
    CREATE ROLE rw_user
And forth and last step: Lets grant these roles as default roles to our users:
    GRANT DEFAULT ro_user TO USER nemo;
    GRANT DEFAULT rw_user TO USER allu;
The screenshot shows the connection of user "nemo", the successful selection of some data from the "customer" table, but a "no permission" error message when they try to update a record in this table.
Firebird (ISQL): Accessing the 'employee' database as a read-only user
7.4.4. Accessing Firebird from PHP.
When writing this tutorial, I used Apache Lounge 2.4.46 and PHP 8.0.0; the tutorial may not apply to older (or newer) versions of PHP. If you need more information or help with Apache or PHP, please have a look at part 1 resp. part 2 of this tutorial.
Connecting to a Firebird database using PHP is really easy. The client DLL php_pdo_firebird.dll is included with the PHP distribution and all you have to do is to enable the extension in php.ini. Just uncomment the following line:
Be sure to restart Apache to make the changes take effect!
Here a simple script to test the connection. I called it firebird.php and placed it in the /php directory of my webserver.
            <title>PHP-Firebird test</title>
                $dsn = 'firebird:dbname=localhost:employee'; $username = 'nemo'; $password = 'nemo';
                try {
                    // Connect to database
                    $dbh = new \PDO($dsn, $username, $password, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]);
                    // Execute query
                    $sql = "SELECT count(*) FROM customer";
                    $query = $dbh->query($sql);
                    // Get the result
                    $count = 0;
                    if ($row = $query->fetch(\PDO::FETCH_OBJ)) {
                        $count = $row->COUNT;
                    echo "Number of customers in database 'employee' = $count";
                catch (\PDOException $e) {
                    echo $e->getMessage();
To run the script, type localhost/php/firebird.php in your webbrowser's address field. The screenshots below show the situation where I forgot to restart Apache after having enabled the Firebird extension (on the left), a failure of the connection, because the Firebird server was offline (in the middle), and a successful connection with return of the number of customers in the "employee" database (on the right).
Firebird access from PHP: Failure, because forgotten to restart Apache after enabling the extension
Firebird access from PHP: Failure, because server is offline
Firebird access from PHP: Successful query of the 'employee' database
7.4.5. Accessing Firebird from Perl.
There is a DBD driver available for Firebird, however I did not succeed to install it.
The screenshots show my trial to install the Firebird DBD driver from CPAN: On the left, you can see, how I searched for the driver and tried to install one of the search results, using the commands:
    i /firebird/
    install DAM/DBD-Firebird-1.34.tar.gz
On the right, you see the last screen of the installation process: Failure during the make_test command!
Accessing Firebird with Perl: Searching CPAN for a DBD driver
Accessing Firebird with Perl: Failure of the DBD driver installation
Must the make_test fail because the embedded version of Firebird is not installed? Or, is it my fault because I omit something (DBI version, setting the password to access the databases created during test)? I did not further investigate the problem. First, Firebird is just a try-out (for doing real things, I use MySQL), and second, I succeeded to access Firebird from Perl using ODBC.
7.4.6. Accessing Firebird from Python.
There are several Python drivers for Firebird available. I actually use FDB, version 2.0.2 (have a look at the FDB website for details). You can install it using PIP. In command prompt, enter:
    python -m pip install fdb
Accessing Firebird from Python: Installing the FDB driver using PIP
Here an elementary Python script (I named it and placed it in my /cgi-bin directory) to test the connectivity to the "employee" database. Please, note that the script doesn't do any error handling. If the server is offline (and also in other error conditions), a white page will be displayed in the web browser.
    import fdb
    print("Content-type: text/html")
    print("<head><title>Python-Firebird test</title></head>")
    # Connect to database 'employee'
    db = fdb.connect(
        host='localhost', database='employee',
        user='nemo', password='nemo'
    cursor = db.cursor()
    # Read the number of records in the 'customer' table
    cursor.execute("SELECT count(*) FROM customer")
    count = cursor.fetchone()[0]
    # Print result onto webpage
    print("<p>Number of customers in database 'employee' = ", count, "</p>")
    # Disconnect from database
To run the script, type localhost/cgi-bin/ in your browser. The screenshot shows a successful connection with the display of the number of customers.
Accessing Firebird from Python: Successful query of the 'employee' database
7.4.7. Accessing Firebird from Lazarus/Free Pascal.
To connect to a Firebird database with Free Pascal applications using a TIBConnection, please have a look at my Using Firebird databases with Lazarus/Free Pascal tutorial in the Lazarus/Free Pascal Programming section.
7.4.8. Accessing Firebird using ODBC.
An ODBC driver uses the Open Database Connectivity (ODBC) interface by Microsoft that allows applications to access data in database management systems (DBMS) using SQL as a standard for accessing the data. ODBC permits maximum interoperability, i.e. a single application can access different DBMS. Application end users have just to add ODBC database drivers to link the application to their choice of DBMS, for example a Firebird ODBC driver for Firebird. Official ODBC documentation can be found at the MSDN ODBC site.
To connect to a Firebird database using ODBC, we need:
  • An ODBC Driver Manager.
  • A Firebird ODBC driver.
The driver manager is part of Microsoft Windows (cf. below), the driver has to be installed manually.
You can find links to download an ODBC driver for Firebird on the Firebird ODBC page. I actually use Firebird ODBC (64bit). Download the full installer and run it. In the Select components window, choose Developer install.
Installing the Firebird ODBC driver
MS Windows includes the ODBC Data Source Administrator, a built in ODBC Driver Manager, which allows DSNs to be set up, and other configuration. You can find it in Control Panel > Administrative Tools. There are two ODBC Driver Managers available: one for 32bit and one for 64bit data sources.
MS Windows ODBC Data Source Administrator in Administrative Tools
Open ODBC Data Sources (64bit). In the Drivers tab, you should see the Firebird driver that we've just installed.
Available ODBC drivers in MS Windows ODBC Data Source Administrator
One of the ways, an ODBC driver manager provides to make shortcuts for a set of parameters, is a DSN (DataSource Name): a system or user defined data source, identified by a (unique) name. DSNs can be configured using the ODBC Data Source Administrator (or by manually editing the odbc.ini file or the registry). The parameters in a DSN can always be combined with additional parameters in the connection string (for example to specify a password). By setting up a DSN, the connection details can be verified to work within the manager dialog, and then the named DSN is all, that is needed, to use the connection later. The connection details are decoupled from the applications, as only the DSN name is used – the DSN acting as a go-between.
To set up a user DSN for our Firebase database "employee" (as a difference with other RDBMS, you'll have to create a DSN for each database – at least as far as I have understood), in ODBC Data Source Administrator, choose the User DSN tab and click the Add button. Select the Firebase/Interbase(r) driver.
Firebird with ODBC: Creating of a user DSN - Selecting the driver
In the Firebird ODBC setup window, choose a name for the connection (DSN); I chose "Firebird_employee". Enter a description if you like, enter the database name ("employee") and browse for the client DLL (fbclient.dll); you must use the DLL located in System32, the one located in SysWOW64 does not work (?). To test the connection, enter an username and their password, then push the Test connection button.
Firebird with ODBC: Creating of a user DSN - Firebase ODBC setup for 'employee' database
Back in the Firebird ODBC setup window, remove user name and password (we will provide these information within the application) and push the OK button. The new connection Firebird_employee should now be listed with the other user DSN.
ODBC connection from Perl.
Connecting from Perl using ODBC requires the module DBI::ODBC. No need to install it; it is included with DBI. The connection from Perl is somewhat special, as you have to indicate the driver name, not as expected the DNS. Here the code of a simple script (I called it and placed it in my /cgi-bin directory) that reads the number of customers in the "employee" database.
    use strict; use warnings;
    use CGI qw(:standard);
    use CGI::Carp qw(fatalsToBrowser);
    use DBI;
    my $driver='Firebird/InterBase(r) driver'; my $database='employee'; my $username = 'nemo'; my $passwd = 'nemo';
    # Print Web page header
    print header;
    # Connect to database 'employee'
    my $dbh = DBI->connect("dbi:ODBC:driver=$driver;Database=$database", $username, $passwd, { AutoCommit => 1, RaiseError => 1 })
        or die "Failed to connect to database: $DBI::errstr";
    # Read the number of records in the 'customer' table
    my $sql = "SELECT count(*) FROM customer";
    my ($count) = $dbh->selectrow_array($sql);
    # Print result onto simple Web page
    print "<html>";
    print "<head><title>Perl-Firebird test</title></head>";
    print "<body><p>Number of customers in database 'employee' = $count</p></body>";
    print "</html>";
    # Disconnect from database
To run the script, enter localhost/cgi-bin/ in your web browser's address field. The screenshot on the left shows the situation, where the Firebird server is offline. The screenshot on the right shows a successful query.
Accessing Firebird with Perl ODBC: Failure because the server is offline
Accessing Firebird with Perl ODBC: Successful query of the 'employee' database
ODBC connection from Lazarus/Free Pascal.
You can use the sample application, described in my tutorial Using Firebird databases with Lazarus/Free Pascal as a template to create the ODBC based application. All you'll have to do is to replace the TIBConnection object by a TODBCConnection object. You probably would like to give this object another name as the one used with TIBConnection, the corresponding statements in the methods TfFbSQL.btQueryClick and TfFbSQL.btExitClick having to be changed. Concerning the method TfFbSQL.btConnectClick, here is the code of the ODBC version (dbODBCConnection being the name of my TODBCConnection object):
    procedure TfFbSQL2.btConnectClick(Sender: TObject);
        if dbODBCConnection.Connected then
        // Set the connection parameters.
        dbODBCConnection.DatabaseName := 'Firebird_employee';
        dbODBCConnection.UserName := 'nemo';
        dbODBCConnection.Password := 'nemo';
        // Connect to the employee database
            edMess.Text := 'Connection to Firebird database "employee" = OK!';
            on E: ESQLDatabaseError do
                edMess.Text := E.Message;
The important thing here is that the value of <ODBCConnection-object>.DatabaseName has to be set to the name of the User DSN, not the name of the database (as a difference with ODBC for MySQL and other RDBMS, the database name is not required here, as it has been set in the properties of our Firebird User DSN).
The screenshots below show the situation after the "Connect" button has been pressed: On the left connection failure because the server is offline, on the right successful connection.
Accessing Firebird with Lazarus/Free Pascal ODBC: Failure because the server is offline
Accessing Firebird with Lazarus/Free Pascal ODBC: Successful query of the 'employee' database

If you find this text helpful, please, support me and this website by signing my guestbook.