This article is part of a series that includes SQLite, Postgresql, Microsoft SQLServer, Oracle RDBMS, HSQLDB, MariaDB, MongoDB, and Excel. The goal is to set up a self-standing environment for testing an ODBC extension for gawk presented here to be completed. Refer to SQLite for installing the required ODBC Driver Manager.
The test system is a debian v11 (bullseye).
As root, install the Firebird RDBMS and its ODBC drivers from the official repositories:

# apt install firebird3.0-server

A systemd service was set up and launched:

# systemctl status firebird3.0.service
● firebird3.0.service - Firebird Database Server ( SuperServer )
     Loaded: loaded (/lib/systemd/system/firebird3.0.service; enabled; vendor preset: enabled)
     Active: active (running) since Mon 2021-10-18 15:00:25 CEST; 50min ago
    Process: 546 ExecStart=/usr/sbin/fbguard -daemon -forever (code=exited, status=0/SUCCESS)
   Main PID: 576 (fbguard)
      Tasks: 4 (limit: 4659)
     Memory: 10.1M
        CPU: 75ms
     CGroup: /system.slice/firebird3.0.service
             ├─576 /usr/sbin/fbguard -daemon -forever
             └─577 /usr/sbin/firebird

Oct 18 15:00:23 debian systemd[1]: Starting Firebird Database Server ( SuperServer )...
Oct 18 15:00:25 debian systemd[1]: Started Firebird Database Server ( SuperServer ).

The service runs as the newly created firebird account:

# ps -ef | grep firebird
firebird   28053       1  0 15:15 ?        00:00:00 /usr/sbin/fbguard -daemon -forever
firebird   28054   28053  0 15:15 ?        00:00:00 /usr/sbin/firebird

Create the symlink below:

# ln -s /usr/lib/x86_64-linux-gnu/libfbclient.so.3.0.7 /usr/lib/x86_64-linux-gnu/libgds.so

Get and install the binaries ODBC drivers for Firebird:

# wget https://sourceforge.net/projects/firebird/files/firebird-ODBC-driver/2.0.5-Release/OdbcFb-LIB-2.0.5.156.amd64.gz/download
# gunzip OdbcFb-LIB-2.0.5.156.amd64.gz
# tar xvf OdbcFb-LIB-2.0.5.156.amd64
# cp libOdbcFb.so /usr/lib/x86_64-linux-gnu/odbc/.

Edit the odbcinst.ini file:

# vi /etc/odbcinst.ini
...
[Firebird]
Description=InterBase/Firebird ODBC
Driver=/usr/lib/x86_64-linux-gnu/odbc/libOdbcFb.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libOdbcFb.so
Threading=1
FileUsage=1
CPTimeout=
CPReuse=

Check the ODBC driver file /etc/odbcinst.ini:

# odbcinst -q -d
...
[Firebird]

# odbcinst -q -d -n Firebird
[Firebird]
Description=InterBase/Firebird ODBC
Driver=/usr/lib/x86_64-linux-gnu/odbc/libOdbcFb.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libOdbcFb.so
Threading=1
FileUsage=1
CPTimeout=
CPReuse=

As debian, create the sampledb database using the native administrative tool isql-fb:

$ cd $workdir
$ mkdir firebird
$ cd firebird
$ isql-fb -user SYSDBA -password 'SYSDBA'

Use CONNECT or CREATE DATABASE to specify a database

SQL> create database "/home/debian/odbc4gawk/firebird/sampledb.fb" page_size 8192 user SYSDBA password 'SYSDBA';
SQL> commit;
SQL> quit;

As there are no sample scripts specifically for Firebird, use the postgresql’s ones to create the tables but first replace the “SERIAL” keyword in the “CREATE TABLE” statements with the equivalent firebird’s syntax “INT GENERATED BY DEFAULT AS IDENTITY”. Use your favorite text editor or even sed for this. Save the above file as create_tables_postgresl.sql. With the vi editor, this could be done as shown below:

$ vi create_tables_postgresl.sql
:1,$s/ SERIAL / INT GENERATED BY DEFAULT AS IDENTITY /g
:w create_tables_firebird.sql
:q

Launch again isql-fb with a connection to the newly created database and copy/paste the statements from create_tables_firebird.sql as they are just a few of them:

$ isql-fb /home/debian/odbc4gawk/firebird/sampledb.fb -u sysdba -p sysdba
Database: /home/debian/odbc4gawk/firebird/sampledb.fb, User: SYSDBA
SQL> show tables;

There are no tables in this database
Paste the create table statements here.
Also, copy/paste the statements from here and append a final commit; to confirm the INSERT. Finally, check the data:

SELECT
        c.country_name,
        c.country_id,
        l.country_id,
        l.street_address,
        l.city
FROM
        countries c
LEFT JOIN locations l ON l.country_id = c.country_id
WHERE
        c.country_id IN ('US', 'UK', 'CN');
Output:
COUNTRY_NAME                             COUNTRY_ID COUNTRY_ID STREET_ADDRESS                           CITY                           
======================================== ========== ========== ======================================== =====================
China                                    CN                                                                          
United Kingdom                           UK         UK         8204 Arthur St                           London                         
United Kingdom                           UK         UK         Magdalen Centre, The Oxford Science Park Oxford                         
United States of America                 US         US         2014 Jabberwocky Rd                      Southlake                      
United States of America                 US         US         2011 Interiors Blvd                      South San Francisco            
United States of America                 US         US         2004 Charade Rd                          Seattle                        
SQL> 

Configure an ODBC DSN by editing the user’s DSN file:

$ vi ~/.odbc.ini
...
[myfbdb]
Description=Firebird
Driver=Firebird
Dbname=/home/debian/odbc4gawk/firebird/sampledb.fb
User=SYSDBA
Password=SYSDBA
Role=
CharacterSet=UTF8
ReadOnly=No
NoWait=No

Check it:

$ odbcinst -q -s -n 
...
[myfbdb]
...

$ odbcinst -q -s -n myfbdb
[myfbdb]
Description=Firebird
Driver=Firebird
Dbname=/home/debian/odbc4gawk/firebird/sampledb.fb
User=SYSDBA
Password=SYSDBA
Role=
CharacterSet=UTF8
ReadOnly=No
NoWait=No

Try a connection to the firebird db via ODBC:

$ isql -v myfbdb sysdba sysdba
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT c.country_name, c.country_id, l.country_id, l.street_address, l.city FROM countries c LEFT JOIN locations l ON l.country_id = c.country_id WHERE c.country_id IN ('US', 'UK', 'CN');
Output:
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
| country_name                            | country_id| country_id| street_address                          | city          |
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
| China                                   | CN        |           |                                         |               |
| United Kingdom                          | UK        | UK        | 8204 Arthur St                          | London        |
| United Kingdom                          | UK        | UK        | Magdalen Centre, The Oxford Science Park| Oxford        |
| United States of America                | US        | US        | 2014 Jabberwocky Rd                     | Southlake     |
| United States of America                | US        | US        | 2011 Interiors Blvd                     | South San Francisco           |
| United States of America                | US        | US        | 2004 Charade Rd                         | Seattle       |
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
SQLRowCount returns 6
6 rows fetched

Test the DSN with pyodbc:

$ python3
Python 3.9.2 (default, Feb 28 2021, 17:03:44) 
[GCC 10.2.1 20210110] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyodbc 
>>> con = pyodbc.connect('DSN=myfbdb;UID=SYSDBA;PWD=SYSDBA;DBNAME=sampledb;')
Segmentation fault

Although isql has no problem with them, it looks like the official Firebird ODBC drivers don’t work well with pyodbc. The same behavior was noticed later with the gawk the extension (see article here …). For this reason, we tried the commercial drivers from Devart available here. After having installed them by following the clear and simple instructions and having created the DSN myfbdb_devart using those drivers, pyodbc could work fine.
After installation of those drivers, check the DSN definition:

$ odbcinst -q -s -n myfbdb_devart
[myfbdb_devart]
Description=Firebird
Driver=Devart ODBC Driver for Firebird
Database=/media/sf_customers/dbi/odbc4gawk/sampledb.fb
Port=3050
User=SYSDBA
Password=SYSDBA
Role=
CharacterSet=UTF8
ReadOnly=No
NoWait=No

And the drivers:

$ odbcinst -q -d -n "Devart ODBC Driver for Firebird"
[Devart ODBC Driver for Firebird]
Driver=/usr/local/lib/libdevartodbcfirebird.so

Retry the pyodbc module:

$ python3
Python 3.9.2 (default, Feb 28 2021, 17:03:44) 
[GCC 10.2.1 20210110] on linux
Type "help", "copyright", "credits" or "license" for more information.
import pyodbc 
cnxn = pyodbc.connect('DSN=myfbdb_devart;UID=SYSDBA;PWD=SYSDBA;DBNAME=sampledb;')
cursor = cnxn.cursor()      
cursor.execute("""SELECT
...         c.country_name,
...         c.country_id,
...         l.country_id,
...         l.street_address,
...         l.city
... FROM
...         countries c
... LEFT JOIN locations l ON l.country_id = c.country_id
... WHERE
...         c.country_id IN ('US', 'UK', 'CN')""")

row = cursor.fetchone() 
 while row:
...     print (row) 
...     row = cursor.fetchone()
... 
Output:
('China', 'CN', None, None, None)
('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London')
('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford')
('United States of America', 'US', 'US', '2014 Jabberwocky Rd', 'Southlake')
('United States of America', 'US', 'US', '2011 Interiors Blvd', 'South San Francisco')
('United States of America', 'US', 'US', '2004 Charade Rd', 'Seattle')

The Devart’s drivers work fine. However, let’s try to recompile the official ODBC drivers for Firebird from their source code available here. To compile them, follow the following steps:

$ apt install unixodbc-dev
$ apt install firebird-dev

Once downloaded and untarred, move the directory OdbcJdbc/Builds/Gcc.lin and compile the drivers:

$ export FBINCDIR=/usr/include/firebird
$ export FBLIBDIR=/usr/lib/x86_64-linux-gnu
$ make -e -d -f makefile.linux

The compiled libraries are put in OdbcJdbc/Builds/Gcc.lin/Release_x86_64. As root, install the shared library libOdbcFb.so in its usual sub-directory and make it readable for everyone:

# cp OdbcJdbc/Builds/Gcc.lin/Release_x86_64/libOdbcFb.so /usr/lib/x86_64-linux-gnu/odbc/.
# chmod a+r /usr/lib/x86_64-linux-gnu/odbc/libOdbcFb.so

With those recompiled drivers, isql still works and pyodbc still fails but only while executing the SELECT statement. As shown later, the gawk extension has no problem anymore: it works with the drivers from Devart as well as the recompiled drivers; thus, we still have the option of free drivers.
Despite pyodbc, Firebird is now fully accessible from any ODBC application under the debian account.
Instructions for the other data sources can be accessed through the following links:
SQLite
HSQLDB
MariaDB
PostgreSQL
Oracle
Microsoft SQLServer for Linux
MongoDB
Excel


Thumbnail [60x60]
by
Cesare Cervini