This article is part of a series that includes SQLite, MariaDB, Firebird, Oracle RDBMS, Microsoft SQL Server, HSQLDB, 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).
The postgresql drivers can be installed from the platform’s default package repositories using the following steps as root. As the postgresql service was missing on this test environment, let’s install it too:

# apt install postgresql

A systemd service has been created and started, check it:

# systemctl status postgresql
● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Mon 2021-08-23 19:04:20 CEST; 1min 15s ago
   Main PID: 10528 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 4659)
     Memory: 0B
        CPU: 0
     CGroup: /system.slice/postgresql.service

Check its processes:

# ps -ef | grep postgres
postgres     682       1  0 Oct16 ?        00:00:02 /usr/lib/postgresql/13/bin/postgres -D /var/lib/postgresql/13/main -c config_file=/etc/postgresql/13/main/postgresql.conf
postgres     807     682  0 Oct16 ?        00:00:00 postgres: 13/main: checkpointer 
postgres     808     682  0 Oct16 ?        00:00:04 postgres: 13/main: background writer 
postgres     809     682  0 Oct16 ?        00:00:04 postgres: 13/main: walwriter 
postgres     810     682  0 Oct16 ?        00:00:01 postgres: 13/main: autovacuum launcher 
postgres     811     682  0 Oct16 ?        00:00:01 postgres: 13/main: stats collector 
postgres     812     682  0 Oct16 ?        00:00:00 postgres: 13/main: logical replication launcher

The processes run under the postgres account.
Install the ODBC drivers:

# apt install odbc-postgresql

Check this step:

# odbcinst -q -d
...
[PostgreSQL ANSI]
[PostgreSQL Unicode]

Two drivers have been installed, one for the ANSI character encoding and one for Unicode; check the Unicode one:

# odbcinst -q -d -n 'PostgreSQL Unicode'
[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

Verify that the default native administration tool, psql, is there:

# psql -V
# psql (PostgreSQL) 13.3 (Debian 13.3-1)

Switch to the postgres account and create the database:

# su – postgres
$ createdb sampledb

Launch psql and create the tables for postgresql using the scripts here:
As those are small files, copying and pasting their content into psql will do just fine.
Also, populate the tables for postgresql using the data here and test:

$ psql sampledb
sampledb=# 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   | Oxford
                          |            |            | Science Park                  | 
 China                    | CN         |            |                               | 
(6 rows)

To test ODBC, first edit ~/.odbc.ini and add the postgresql database details:

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

Verify that the edition was successful:

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

Next, launch isql against that database:

$ export LD_LIBRARY_PATH=/usr/lib/x86_64-linux-gnu/odbc:$LD_LIBRARY_PATH
$ isql -v mypostgresqldb
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> help
+------------------+-------------+-------------+------------+---------+
| TABLE_QUALIFIER  | TABLE_OWNER | TABLE_NAME  | TABLE_TYPE | REMARKS |
+------------------+-------------+-------------+------------+---------+
| sampledb         | public      | countries   | TABLE      |         |
| sampledb         | public      | departments | TABLE      |         |
| sampledb         | public      | dependents  | TABLE      |         |
| sampledb         | public      | employees   | TABLE      |         |
| sampledb         | public      | jobs        | TABLE      |         |
| sampledb         | public      | locations   | TABLE      |         |
| sampledb         | public      | regions     | TABLE      |         |
+------------------+-------------+-------------+------------+---------+
SQLRowCount returns 7
7 rows fetched
SQL> quit

The ODBC connectivity to postgresql is confirmed for the postgres account; this is fine for that user but we want the database to be usable by the debian test account too. To this effect, use the following steps from with psql:

sampledb=# CREATE ROLE debian superuser;
CREATE ROLE
sampledb=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO debian;
GRANT
sampledb=# ALTER ROLE debian with login;
ALTER ROLE
sampledb=# q

Back as root, become debian and test the accessibility of the sampledb:

# su - debian
$ psql sampledb
sampledb=# select count(*) from employees;
 count 
-------
    40
(1 row)

Check with isql:

$ isql -v mypostgresqldb
+---------------------------------------+
| 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')
+---------------------------+-------------+-----------+-----------------------+----------------------+
| 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               |
| China                     | CN          |           |                       |                      |
+---------------------------+-------------+-----------+-----------------------+----------------------+ 
SQLRowCount returns 6
6 rows fetched

Note that the order of the result set may differ in other data sources; the SQL standard does not define the result set’s order and the ORDER BY clause should be used to enforce one if needed.
Finally, let’s verify the ODBC connectivity through pyodbc:

$ python3
import pyodbc 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

# connect directly using the DRIVER definition, no DSN;
cnxn = pyodbc.connect('DRIVER={PostgreSQL Unicode};Direct=True;Database=sampledb;String Types= Unicode')

# using the DSN is OK too:
# cnxn = pyodbc.connect('DSN=mypostgresqldb')
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)

Note that we used the PostgreSQL Unicode driver, not the ANSI one because the latter gives the error below:

pyodbc.Error: ('07006', '[07006] Received an unsupported type from Postgres. (14) (SQLGetData)')

postgreslq databases can now be used locally by any ODBC client application, e.g. any python program with the pyodbc module, or a desktop application such as LibreOffice. In the case of python, many native modules for postgresql are available but they require ad hoc function calls whereas ODBC lets one use the same statements with any database target, which simplifies the maintenance of ODBC applications.
Instructions for the other data sources can be accessed through the following links:
SQLite
HSQLDB
MariaDB
Firebird
Oracle
Microsoft SQLServer for Linux
MongoDB
Excel

Post Views: 8,147
Thumbnail [60x60]
by
Oracle Team