Table of Contents

22.9.2 ODBC driver for the DBMS SQLite

22.9.2.1 Installation

The special ODBC driver 'libsqliteodbc' for the DBMS SQLite can be installed via the console. Enter the following line in a terminal:

$ sudo apt-get install libsqliteodbc

This will show you which ODBC drivers have also been installed:

$ find /usr/lib -name '*odbc*.so'
/usr/lib/gambas3/gb.db.odbc.so
...
/usr/lib/x86_64-linux-gnu/odbc/libsqlite3odbc.so
...
/usr/lib/x86_64-linux-gnu/odbc/libsqlite3odbc-0.9996.so

22.9.2.2 Configuration

The special ODBC drivers are always configured in the system-wide file with the path /etc/odbcinst.ini. After installing the ODBC driver 'libsqliteodbc' for the DBMS SQLite, the following additional content already exists in the configuration file /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

The names of the ODBC drivers are in square brackets, which are required when configuring the data source name (DataSourceName).

22.9.2.3 Configuration of a data source name or DataSourceName (DSN)

A data source name (DSN) is configured in the system-wide file /etc/odbcinst.ini or locally in the file /etc/odbc.ini. You must specify the path to the SQLite database file as an absolute value. You can freely specify the DSN name. However, it may only appear once in the configuration file. You can reference the ODBC driver `SQLite3` from the file /etc/odbcinst.ini in the hidden configuration file ~/.odbc.ini as follows:

[dsn_sl3_contacts]
Description	= SQLite DB `contacts`
Driver		= SQLite3
Database 		= /home/hans/DBSQLite3/contacts.sqlite
ReadOnly	  	= No

To inspect the ODBC drivers, you can again use the console programme 'odbcinst'. The options -q and -d list the names of all (special) ODBC drivers that have registered with unixODBC so far:

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

The command odbcinst -q -s, on the other hand, lists all ODBC data source names (DSN) that you have defined in the configuration file ~/.odbc.ini:

$ odbcinst -q -s
[dsn_pg_contacts]
[dsn_sl3_contacts]

22.9.2.4 Testing the ODBC configuration - SQLite

The odbc-capable test programme `isql` is chosen again. Use the -m15 option to specify the column width in the display:

$ isql -v -m15 dsn_sl3_contacts
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT vorname AS Vorname, nachname AS Nachname, Gebdatum AS Geburtstag 
     FROM contacts 
     WHERE Gebdatum >= '1989-01-01';
+----------------+----------------+-----------+
| Vorname        | Nachname       | Geburtstag|
+----------------+----------------+-----------+
| Lutz           | Lama           | 1989-02-25|
| Emil           | Elch           | 1989-04-18|
| Clara          | Chamäleon      | 1990-05-28|
| Stephanie      | Storch         | 1989-05-10|
| Paul           | Pferd          | 1990-03-01|
| Walter         | Wisent         | 1989-05-14|
| Ernst          | Esel           | 1989-01-18|
| Bernd          | Bisam          | 1990-09-09|
| Stefan         | Spatz          | 1989-11-11|
| Xenia          | Xanter         | 1989-04-03|
+----------------+----------------+-----------+
SQLRowCount returns 0
10 rows fetched
SQL> quit
$ 

You can find the SQLite database contacts.sqlite in the download area so that you can reproduce the test. You must copy this file into the existing directory /home/hans/DBSQLite3.

Download