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