How to configure unixODBC

You can connect custom-built shell script with any database using unixODBC an implementation of the Open Database Connectivity standard and I will show you how to do this for SQLite and PostgreSQL databases.

Essential packages

Install unixodbc package and notice that the odbcinst package will be installed as dependency. You need both of these packages and additional drivers to make things work together.

$ sudo apt-get install unixodbc
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
  libltdl7 libodbc1 odbcinst odbcinst1debian2
Suggested packages:
  libmyodbc odbc-postgresql tdsodbc unixodbc-bin
The following NEW packages will be installed:
  libltdl7 libodbc1 odbcinst odbcinst1debian2 unixodbc
0 upgraded, 5 newly installed, 0 to remove and 0 not upgraded.
Need to get 777 kB of archives.
After this operation, 1,488 kB of additional disk space will be used.
Do you want to continue? [Y/n] Y
Get:1 http://ftp.task.gda.pl/debian stretch/main amd64 libltdl7 amd64 2.4.6-2 [389 kB]
Get:2 http://ftp.task.gda.pl/debian stretch/main amd64 libodbc1 amd64 2.3.4-1 [214 kB]
Get:3 http://ftp.task.gda.pl/debian stretch/main amd64 odbcinst1debian2 amd64 2.3.4-1 [75.2 kB]
Get:4 http://ftp.task.gda.pl/debian stretch/main amd64 odbcinst amd64 2.3.4-1 [43.5 kB]
Get:5 http://ftp.task.gda.pl/debian stretch/main amd64 unixodbc amd64 2.3.4-1 [54.7 kB]
Fetched 777 kB in 0s (979 kB/s)
Selecting previously unselected package libltdl7:amd64.
(Reading database ... 27875 files and directories currently installed.)
Preparing to unpack .../libltdl7_2.4.6-2_amd64.deb ...
Unpacking libltdl7:amd64 (2.4.6-2) ...
Selecting previously unselected package libodbc1:amd64.
Preparing to unpack .../libodbc1_2.3.4-1_amd64.deb ...
Unpacking libodbc1:amd64 (2.3.4-1) ...
Selecting previously unselected package odbcinst1debian2:amd64.
Preparing to unpack .../odbcinst1debian2_2.3.4-1_amd64.deb ...
Unpacking odbcinst1debian2:amd64 (2.3.4-1) ...
Selecting previously unselected package odbcinst.
Preparing to unpack .../odbcinst_2.3.4-1_amd64.deb ...
Unpacking odbcinst (2.3.4-1) ...
Selecting previously unselected package unixodbc.
Preparing to unpack .../unixodbc_2.3.4-1_amd64.deb ...
Unpacking unixodbc (2.3.4-1) ...
Processing triggers for libc-bin (2.24-11+deb9u1) ...
Setting up libltdl7:amd64 (2.4.6-2) ...
Processing triggers for man-db (2.7.6.1-2) ...
Setting up libodbc1:amd64 (2.3.4-1) ...
Setting up odbcinst1debian2:amd64 (2.3.4-1) ...
Setting up odbcinst (2.3.4-1) ...
Setting up unixodbc (2.3.4-1) ...
Processing triggers for libc-bin (2.24-11+deb9u1) ...

Display current configuration.

$ odbcinst -j
unixODBC 2.3.4
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/milosz/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

System-wide data sources will be stored in /etc/odbc.ini, user data sources in ~/.odbc.ini and available drivers in /etc/odbcinst.ini.

SQLite database configuration

Install ODBC driver for SQLite embedded database.

$ sudo apt-get install libsqliteodbc 
Reading package lists... Done
Building dependency tree       
Reading state information... Done
Suggested packages:
  unixodbc-bin
The following NEW packages will be installed:
  libsqliteodbc
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
Need to get 84.2 kB of archives.
After this operation, 224 kB of additional disk space will be used.
Get:1 http://ftp.task.gda.pl/debian stretch/main amd64 libsqliteodbc amd64 0.9995-1 [84.2 kB]
Fetched 84.2 kB in 0s (291 kB/s)   
Selecting previously unselected package libsqliteodbc:amd64.
(Reading database ... 27941 files and directories currently installed.)
Preparing to unpack .../libsqliteodbc_0.9995-1_amd64.deb ...
Unpacking libsqliteodbc:amd64 (0.9995-1) ...
Setting up libsqliteodbc:amd64 (0.9995-1) ...
odbcinst: Driver installed. Usage count increased to 1.
    Target directory is /etc
odbcinst: Driver installed. Usage count increased to 1.
    Target directory is /etc

Display configured ODBC drivers.

$ odbcinst -q -d
[SQLite]
[SQLite3]

These drivers are defined in /etc/odbcinst.ini configuration file.

$ cat /etc/odbcinst.ini
[SQLite]
Description=SQLite ODBC Driver
Driver=libsqliteodbc.so
Setup=libsqliteodbc.so
UsageCount=1

[SQLite3]
Description=SQLite3 ODBC Driver
Driver=libsqlite3odbc.so
Setup=libsqlite3odbc.so
UsageCount=1

Configure system-wide data source.

$ cat << EOF | sudo tee  /etc/odbc.ini
[internaldb]
Driver   = SQLite3
Database = /var/db/internal.db
NoCreat  = 1
EOF

Confirm that you can connect to the defined data source.

$ echo | isql internaldb -b

You can now execute query using Open Database Connectivity abstraction layer.

$ echo "select name from sqlite_master where type='table';" | isql internaldb -b -L40
+-----------------------------------------+
| name                                    |
+-----------------------------------------+
| users                                   |
| log                                     |
+-----------------------------------------+
SQLRowCount returns 0
2 rows fetched

Additional information is located in /usr/share/doc/libsqliteodbc/REABME.gz file.

PostgreSQL database configuration

Install ODBC driver for PostgreSQL database.

$ sudo apt-get install odbc-postgresql
Reading package lists... Done
Building dependency tree       
Reading state information... Done
Suggested packages:
  unixodbc-bin
The following NEW packages will be installed:
  odbc-postgresql
0 upgraded, 1 newly installed, 0 to remove and 0 not upgraded.
Need to get 273 kB of archives.
After this operation, 1,043 kB of additional disk space will be used.
Get:1 http://ftp.task.gda.pl/debian stretch/main amd64 odbc-postgresql amd64 1:09.05.0400-2 [273 kB]
Fetched 273 kB in 0s (514 kB/s)     
Selecting previously unselected package odbc-postgresql:amd64.
(Reading database ... 29793 files and directories currently installed.)
Preparing to unpack .../odbc-postgresql_1%3a09.05.0400-2_amd64.deb ...
Unpacking odbc-postgresql:amd64 (1:09.05.0400-2) ...
Setting up odbc-postgresql:amd64 (1:09.05.0400-2) ...
odbcinst: Driver installed. Usage count increased to 1.
    Target directory is /etc
odbcinst: Driver installed. Usage count increased to 1.
    Target directory is /etc

Display configured ODBC drivers.

$ odbcinst -q -d
[PostgreSQL ANSI]
[PostgreSQL Unicode]

These drivers are defined in /etc/odbcinst.ini configuration file.

$ cat /etc/odbcinst.ini
[PostgreSQL ANSI]
Description=PostgreSQL ODBC driver (ANSI version)
Driver=psqlodbca.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

[PostgreSQL Unicode]
Description=PostgreSQL ODBC driver (Unicode version)
Driver=psqlodbcw.so
Setup=libodbcpsqlS.so
Debug=0
CommLog=1
UsageCount=1

Configure system-wide data source.

$ cat << EOF | sudo tee /etc/odbc.ini
[localdb]
Description         = PostgreSQL
Driver              = PostgreSQL Unicode
Trace               = No
TraceFile           = /tmp/psqlodbc.log
Database            = localdb
Servername          = localhost
UserName            = milosz
Password            = password
Port                = 5432
ReadOnly            = No
RowVersioning       = No
ShowSystemTables    = No
ShowOidColumn       = No
FakeOidIndex        = No
ConnSettings        =
EOF

Confirm that you can connect to the defined data source.

$ echo | iusql localdb -b

You can now execute query using Open Database Connectivity abstraction layer.

$ echo "select tablename  from pg_tables where schemaname='public'"| iusql localdb -b
+----------------------------------------------------------------+
| tablename                                                      |
+----------------------------------------------------------------+
| users                                                          |
| log                                                            |
+----------------------------------------------------------------+
SQLRowCount returns 2
2 rows fetched

Additional information is located inside /usr/share/doc/odbc-postgresql/docs/ directory.

Milosz Galazka's Picture

About Milosz Galazka

Milosz is a Linux Foundation Certified Engineer working for a successful Polish company as a system administrator and a long time supporter of Free Software Foundation and Debian operating system.

Gdansk, Poland https://sleeplessbeastie.eu