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/README.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.