Installing the ODBC drivers for HSQLDB

This article is part of a series that includes SQLite, Postgresql, Firebird, Oracle RDBMS, Microsoft SQL Server, 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 user debian, follow the documentation here and here to get and install hsqldb for Linux. Here are the needed steps:

$ export workdir=~/odbc4gawk
$ mkdir $workdir
$ cd $workdir
$ wget https://sourceforge.net/projects/hsqldb/files/hsqldb/hsqldb_2_6/hsqldb-2.6.0.zip/download
$ mv download hsqldb-2.6.0.zip
$ unzip hsqldb-2.6.0.zip

As root, install a system-wide JDK from the official packages repository:

# apt install openjdk-17-jdk
# java -version
openjdk version "17-ea" 2021-09-14
OpenJDK Runtime Environment (build 17-ea+19-Debian-1)
OpenJDK 64-Bit Server VM (build 17-ea+19-Debian-1, mixed mode, sharing)

No special action is needed for the ODBC drivers because hsqldb starting in v2.0 can use the PostgreSQL ones, a clever decision; why reinventing the wheel when a few modifications are enough to make HSQLDB compatible with mainstream ODBC drivers ? Moreover, the choice of those drivers make sense as PostgreSQL is here to stay. If needed, please refer to the article Installing the ODBC drivers for PostgreSQL for step by step instructions. Later, we will only have to create a DSN for the hsqldb database.
As debian, start the database server process and send it to the background:

$ cd $workdir/hsqldb-2.6.0/hsqldb/lib
$ java -cp ../lib/hsqldb.jar org.hsqldb.server.Server --database.0 file:sampledb --dbname.0 xdb &

Connect to the local hsqldb service using the hsqldb’s provided administration application:

$ java -jar ../lib/sqltool.jar --inlineRc=url=jdbc:hsqldb:localhost/runtime,user=sa

Note that the tool uses JDBC for the connection, the java counterpart to ODBC. From that tool, create the tables for hsqldb using the Oracle script here; save it into the text file create_tables_hsqldb.sql, edit it and change occurrences of NUMBER to INT and occurrences of VARCHAR2 to VARCHAR using your favorite text editor. Finally, execute it:

i /home/debian/odbc4gawk/create_tables_hsqldb.sql

Populate the tables for hsqldb using the Oracle script here; save it into the text file populate_tables_hsqldb.sql and execute it unchanged.

i /home/debian/odbc4gawk/populate_tables_hsqldb.sql

Check the data:

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                      [null]                                    [null]
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

Fetched 6 rows.

The data are OK.
Configure an ODBC DSN by editing the user’s DSN file:

$ vi ~/.odbc.ini
...
[myhsqldb]
Description=My Postgresql sample database
Driver=PostgreSQL Unicode
Database=sampledb

On line 4, we have specified that the PostgreSQL ODBC driver is to be used.
Check the DSN:

$ odbcinst -q -s
...
[myhsqldb]

$ odbcinst -q -s -n myhsqldb
...
[myhsqldb]
Description=My Postgresql sample database
Driver=PostgreSQL Unicode
Database=sampledb

Try a hsqldb connection to the hsqldb database via ODBC using the native administrative tool isql:

isql -v myhsqldb
+---------------------------------------+
| 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          |
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
| 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       |
| United Kingdom                          | UK        | UK        | 8204 Arthur St                          | London        |
| United Kingdom                          | UK        | UK        | Magdalen Centre, The Oxford Science Park| Oxford        |
| China                                   | CN        |           |                                         |               |
+-----------------------------------------+-----------+-----------+-----------------------------------------+---------------+
SQLRowCount returns 6
6 rows fetched

It looks good. Test now the DSN from a python script using the pyodbc module:

$ python3
import pyodbc 
cnxn = pyodbc.connect(DSN='myhsqldb')
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:
('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')
('United Kingdom', 'UK', 'UK', '8204 Arthur St', 'London')
('United Kingdom', 'UK', 'UK', 'Magdalen Centre, The Oxford Science Park', 'Oxford')
('China', 'CN', None, None, None)

Everything is OK. hsqldb is now fully accessible from ODBC under the debian account.
Instructions for the other data sources can be accessed through the following links:
SQLite
Firebird
MariaDB
PostgreSQL
Oracle
Microsoft SQLServer for Linux
MongoDB
Excel