Accessing Firebird with ODBC

A guide to setting up Firebird and ODBC on a Linux system. This guide was created using Debian Etch, some details may vary for other distributions.

Names

A few names will be used throughout this guide. These are for you to pick.

• <sysdbapassword> The administrator password for this install of Firebird.
• <username> Name for a regular database user.
• <password> Password for the regular database user.
• <DSN_name> Name for the ODBC description of the database.

Installing software

• Install unixodbc* packages
• Install firebird2-classic-server (set SYSDBA password)
• Install firebird2-dev
• Optional: Install firebird2-utils-classic

Note: It's also possible to use firebird2-super-server. I don't fully understand the difference.

Creating a database

Note: The commands run as root here would be better run as the 'firebird' user.

Note 2: Retrospectively, using this guide for Ubuntu would have probably been better. Aside from one or two package names, it should work on Debian.

# gsec -user sysdba -pass <sysdbapassword> -add <username> -pw <password>
$ cd somewhere/nice
# /usr/lib/firebird2/bin/isql (NOT the same as /bin/isql)
SQL> create database 'test.fdb' user '<username>' password '<password>';
SQL> connect test.fdb user <username> password <password>;

Data can then be added with normal SQL commands

Setting up unixODBC

Drivers

• Download the Firebird ODBC version 2 source for Linux
• In OdbcJdbc/Builds/Gcc.lin run make -f makefile.linux
• Copy the resulting Release/libOdbcFb32.so into /usr/lib/odbc

Database

Use ODBCConfig. Create new DSN (user or system).

Source: The unixODBC User Manual.

Driver:

• Driver: /usr/lib/odbc/libOdbcFb32.so
• Setup: /usr/lib/odbc/libOdbcFb32.so

Data source:

First, select the driver just created and click 'OK'.

• Name: <DSN_name>
• Dbname: localhost:/path/to/database
• User: <username>
• Password: <password>

(All of the above can be done with an .ini file.)

Connecting to the database

isql -v <DSN_name>

(It may be required to use LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/odbc)

Running the above command as either root or firebird will provide an ODBC SQL console

Programming (C)

Reference

• The unixODBC site has a bunch of good reference. Click 'Manuals' on the left. (The ODBC Programming Tutorial worked fine for me, but the Easysoft ones do look more professional).
• The MSDN ODBC Programmer's Reference is the authoritative reference for the ODBC API, but there may be some differences between this and unixODBC.