Getting Started With Firebird

- Installation

- Starting the Database at Boot Time

- Creating a Database
- Accessing a Database
- Accessing a Database With SQL Relay
- Firebird Quirks

Installation

I've successfully installed virtually every version of Firebird from 1.0.0 on up on Linux. I've never tried to install them on any other platform.

There are 2 versions of Firebird: Classic Server (CS) and Super Server (SS). Classic Server only allows a single connection while Super Server allows multiple connections. I recommend using Super Server and this document describes its installation.

Binary and source distributions of Firebird in many different formats (including tar.gz and RPM) for many different platforms are available at the Firebird web site.

The tar.gz distributions come with an install script. The RPM distributions can be installed using rpm -i.

Firebird distributions prior to version 1.5 create a directory called /opt/interbase. Firebird versions 1.5 and higher create /opt/firebird.

Firebird versions 1.5 and higher rpm's leave /opt/firebird owned by root, with -r-xr-xr-x permissions. This is wrong and will cause the server not to start. Change the permissions and ownership as follows (as root):

> chmod 775 /opt/firebird
> chown firebird:firebird /opt/firebird

Many versions of Firebird require libstdc++.so.5 but most Linux distributions ship with a more recent version. If your system doesn't have /usr/lib/libstdc++.so.5 then you may need to install a compatibility library. On Fedora, you need to install compat-libstdc++-33 as follows (as root):

> yum install compat-libstdc++-33

Starting the Database at Boot Time

The package distributions of Firebird install a script which starts the database at boot time and stops it at shutdown time. If you compiled from source, you'll need to install a script like one of the following to start/stop the database at boot/shutdown time.

For Firebird version 1.5 or higher, use the following script:

>     #!/bin/sh

>     case "$1" in
>       start)
>             su -l firebird "/opt/firebird/bin/fbmgr -start -forever"
>             ;;
>       stop)
>             su -l firebird "/opt/firebird/bin/fbmgr -shut -user sysdba -password changeme"
>             ;;
>       *)
>             echo $"Usage: $0 {start|stop}"
>             exit 1
>     esac

>     exit 0

For Firebird prior to version 1.5, use the following script:

>     #!/bin/sh

>     case "$1" in
>       start)
>             /opt/interbase/bin/ibmgr -start
>             ;;
>       stop)
>             /opt/interbase/bin/ibmgr -shut -user sysdba -password changeme
>             ;;
>       *)
>             echo $"Usage: $0 {start|stop}"
>             exit 1
>     esac

>     exit 0

Install this script and run it with the "start" option to start up the database. Running it with the "stop" option shuts the database down. To access a database, it must be running.

Note that the /etc/hosts.equiv file must contain an entry for localhost and possibly localhost.localdomain for the database to start properly and for the "local" tools (such as ibmgr/fbmgr and gsec to function.

Creating a Database

After installation, Firebird is ready to use but to do any useful work, you'll have to create a database.

At this point, you should set up your environment. The Firebird client is called isql. Note that unixODBC and Sybase have a client called isql as well which could be installed in /usr/bin, /usr/local/bin, /opt/sybase*/bin or some other path. If you have unixODBC or Sybase installed, you'll have to decide which client you want the isql command to run. If you want it to run the Firebird client, follow the instructions below. Otherwise, you'll have to run the Firebird isql client by its full pathname.

If you are using Firebird prior to version 1.5, in the configuration script for your shell, add /opt/interbase/bin to your PATH environment variable. If you are using Firebird version 1.5 or higher, add /opt/firebird/bin to your PATH environment variable. You can add the paths as follows.

For Bourne shells:

>     PATH=$PATH:/opt/firebird/bin
>     export PATH

or

>     PATH=$PATH:/opt/interbase/bin
>     export PATH

For C-shells:

>     setenv PATH ${PATH}:/opt/firebird/bin

or

>     setenv PATH ${PATH}:/opt/interbase/bin

Now log out and log back in and you can access the database using the isql client.

Under Firebird 2.0, the installation process creates a security database called security2.fdb containing host and user privileges and an administrative user named SYSDBA with a randomly generated password. This password is listed in SYSDBA.password.

Under Firebird 1.5, the installation process creates a security database called security.fdb containing host and user privileges and an administrative user named SYSDBA with a randomly generated password. This password is listed in SYSDBA.password.

Under Firebird prior to version 1.5, the installation process creates a security database named isc4.gdb containing host and user privileges and an administrative user named SYSDBA with a password of either changeme or masterkey.

Firebird user names are case insensitive and can be up to 31 characters long. Firebird passwords are case sensitive and can be up to 32 characters long, but only the first 8 characters are actually used.

Use the gsec utility to change the SYSDBA password to newpassword and create a new user named testuser with password testpassword. gsec must be run as root, from the directory containing isc4.gdb. A gsec session follows.

Note that the /etc/hosts.equiv file must contain an entry for localhost and possibly localhost.localdomain for the gsec to function.

For Firebird verison 2.0 or higher (replace password with the password in the SYSDBA.password file)...

>     [root@localhost root]# cd /opt/firebird
>     [root@localhost root]# ./bin/changeDBAPassword.sh
>     Please enter current password for SYSDBA user: password
>     Please enter new password for SYSDBA user: newpassword
>     GSEC> Warning - maxiumum 8 significant bytes of password used
>     GSEC>

>     Running ex to modify /etc/rc.d/init.d/firebird
>     [root@localhost firebird]# ls -l security2.fdb
>     -rw-rw-rw-    1 root     root       618496 Feb 18 14:29 security2.fdb
>     [root@localhost firebird]# /opt/firebird/bin/gsec -user SYSDBA -password newpassword
>     GSEC> add testuser -pw testpassword
>     Warning - maximum 8 significant bytes of password used
>     GSEC> quit

For Firebird verison 1.5 or higher, but below 2.0 (replace password with the password in the SYSDBA.password file)...

>     [root@localhost root]# cd /opt/firebird
>     [root@localhost root]# ./bin/changeDBAPassword.sh
>     Please enter current password for SYSDBA user: password
>     Please enter new password for SYSDBA user: newpassword
>     GSEC> Warning - maxiumum 8 significant bytes of password used
>     GSEC>

>     Running ex to modify /etc/rc.d/init.d/firebird
>     [root@localhost firebird]# ls -l security.fdb
>     -rw-rw-rw-    1 root     root       618496 Feb 18 14:29 security.fdb
>     [root@localhost firebird]# /opt/firebird/bin/gsec -user SYSDBA -password newpassword
>     GSEC> add testuser
>     GSEC> modify testuser -pw testpassword
>     Warning - maximum 8 significant bytes of password used
>     GSEC> quit

For Firebird prior to verison 1.5...

>     [root@localhost root]# cd /opt/interbase
>     [root@localhost interbase]# ls -l isc4.gdb
>     -rw-rw-rw-    1 root     root       618496 Feb 18 14:29 isc4.gdb
>     [root@localhost interbase]# /opt/interbase/bin/gsec
>     GSEC> modify sysdba -pw newpassword
>     Warning - maximum 8 significant bytes of password used
>     GSEC> add testuser
>     GSEC> modify testuser -pw testpassword
>     Warning - maximum 8 significant bytes of password used
>     GSEC> quit

Now that the SYSDBA password has been changed in the database, you should update the start/stop script with the new password as well.

Though you can create tables in the isc4.gdb database using the SYSDBA user, it's not a good idea. You should create a new database using a regular user. The following sequence of commands creates a database.

For Firebird version 1.5 or higher, these commands create a database called /opt/firebird/testdb.gdb which is owned by testuser.

>     [user@localhost user]$ isql -u testuser -p testpassword
>     Use CONNECT or CREATE DATABASE to specify a database
>     SQL> create database '/opt/firebird/testdb.gdb';
>     SQL> quit;

For Firebird prior to version 1.5, these commands create a database called /opt/interbase/testdb.gdb which is owned by testuser.

>     [user@localhost user]$ isql -u testuser -p testpassword
>     Use CONNECT or CREATE DATABASE to specify a database
>     SQL> create database '/opt/interbase/testdb.gdb';
>     SQL> quit;

Firebird databases are referenced by the pathname of the database file. If you try to create a database using a file name that already exists or if you forget to enclose the file name in single quotes, you'll get a cryptic error indicating that the 'database' token is unknown. So, if you get a message like that, make sure that you enclosed the file name in single quotes and make sure that the database file that you're trying to create doesn't already exist.

Now you can log into the database as testuser.

Use the following command for Firebird 1.5 or higher.

> isql -u testuser -p testpassword /opt/firebird/testdb.gdb

Use the following command for Firebird prior to version 1.5.

> isql -u testuser -p testpassword /opt/interbase/testdb.gdb

The only users that can drop a database are the user that owns the database and the SYSDBA user. If you want to drop the database, you can do so using isql as follows for Firebird version 1.5 or higher...

>     [user@localhost user]$ isql -u testuser -p testpassword /opt/firebird/testdb.gdb
>     Database:  /opt/firebird/testdb.gdb, User: testuser
>     SQL> drop database;
>     SQL> quit;

Or as follows for Firebird prior to version 1.5.

>     [user@localhost user]$ isql -u testuser -p testpassword /opt/interbase/testdb.gdb
>     Database:  /opt/interbase/testdb.gdb, User: testuser
>     SQL> drop database;
>     SQL> quit;

To delete a user, use the gsec utility as the root user and delete the appropriate user as follows for Firebird version 1.5 or higher.

>     [root@localhost root]# cd /opt/firebird
>     [root@localhost firebird]# ls -l isc4.gdb
>     -rw-rw-rw-    1 root     root       626688 Feb 18 20:43 isc4.gdb
>     [root@localhost firebird]# /opt/firebird/bin/gsec
>     GSEC> delete testuser
>     GSEC> quit

Or as follows for Firebird prior to version 1.5.

>     [root@localhost root]# cd /opt/interbase
>     [root@localhost interbase]# ls -l isc4.gdb
>     -rw-rw-rw-    1 root     root       626688 Feb 18 20:43 isc4.gdb
>     [root@localhost interbase]# /opt/interbase/bin/gsec
>     GSEC> delete testuser
>     GSEC> quit

This should be enough to get you started. To set up more complex configurations, consult the Firebird documentation.

Accessing a Database

To access a database called /opt/firebird/testdb.gdb on the local machine as the testuser user with password testpassword, use the following command.

> isql -u testuser -p testpassword /opt/firebird/testdb.gdb

To connect to a database on a remote machine, prepend the database filename with the remote hostname, followed by a colon. For example, to access a database called /opt/firebird/testdb.gdb on the remote machine testhost as the testuser user with password testpassword, use the following command.

> isql -u testuser -p testpassword testhost:/opt/firebird/testdb.gdb

Once you're connected to the database, the isql client prompts you to enter a query. Queries may be split across multiple lines. To run a query, end it with a semicolon. To exit, type quit;.

A sample isql session follows.

>     [user@localhost user]$ isql -u testuser -p testpass /opt/firebird/testdb.gdb
>     Database:  /opt/firebird/testdb.gdb, User: testuser
>     SQL> create table testtable (
>     CON> col1 char(40),
>     CON> col2 integer
>     CON> );
>     SQL> show table testtable;
>     COL1                            CHAR(40) Nullable
>     COL2                            INTEGER Nullable
>     SQL> insert into testtable values ('hello',50);
>     SQL> insert into testtable values ('hi',60);
>     SQL> insert into testtable values ('bye',70);
>     SQL> select * from testtable;

>     COL1                                             COL2
>     ======================================== ============

>     hello                                              50
>     hi                                                 60
>     bye                                                70

>     SQL> update testtable set col2=0 where col1='hi';
>     SQL> select * from testtable;

>     COL1                                             COL2
>     ======================================== ============

>     hello                                              50
>     hi                                                  0
>     bye                                                70

>     SQL> delete from testtable where col2=50;
>     SQL> select * from testtable;

>     COL1                                             COL2
>     ======================================== ============

>     hi                                                  0
>     bye                                                70

>     SQL> commit;
>     SQL> drop table testtable;
>     SQL> quit;

Accessing a Database With SQL Relay

Accessing Firebird from SQL Relay requires an instance entry in your sqlrelay.conf file for the database that you want to access. Here is an example sqlrelay.conf which defines an SQL Relay instance called firebirdtest. This instance connects to the /opt/firebird/testdb database as the user testuser with password testpassword.

    <?xml version="1.0"?>
    <!DOCTYPE instances SYSTEM "sqlrelay.dtd">
    <instances>

            <instance id="firebirdtest" port="9000" socket="/tmp/firebirdtest.socket" dbase="firebird" connections="3" maxconnections="5" maxqueuelength="0" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass">
                    <users>
                            <user user="firebirdtest" password="firebirdtest"/>
                    </users>
                    <connections>
                            <connection connectionid="firebirdtest" string="user=testuser;password=testpassword;database=/opt/firebird/testdb.gdb;dialect=3" metric="1"/>
                    </connections>
            </instance>

    </instances>

Note that in this example, the database parameter of the string attribute in the connection tag references the local database /opt/firebird/testdb.gdb. If you want to access a remote database, prepend the database filename with the remote hostname followed by a colon. For example: testhost:/opt/firebird/testdb.gdb.

Now you can start up this instance with the following command.

> sqlr-start -id firebirdtest

To connect to the instance and run queries, use the following command.

> sqlrsh -id firebirdtest

The following command shuts down the SQL Relay instance.

> sqlr-stop firebirdtest

Firebird Quirks

In most databases, you can create, alter or drop databse objects such as tables, indices or stored procedures at will. In Firebird, you should create, alter or drop databse objects when you have exclusive access to the databse.

Basically, you should shut down SQL Relay or any other clients, use isql to create, alter or drop the objects, then start SQL Relay again. If you don't do this, you'll get confusing results.