Setting up ODBC for SQLite on Ubuntu

Linux SQL

First install the SQLiteODBC and unixODBC packages. Have a quick look at the documentation for unixODBC and SQLiteODBC.

$ sudo apt-get install libsqliteodbc unixodbc

After the install you’ll have a a /etc/odbcinst.ini file which will look something like this:

[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

Next you need to set up a DSN entry for your SQLite database. You’ll edit your ~/.odbc.ini file to resemble this:

[passwd]
Description = Password Database
Driver = SQLite3
Database = /home/colliera/passwd.sqlite
Timeout = 2000

Obviously the name, description and path for your database file will differ.

Right, we’re ready to use isql to open the database and execute queries.

$ isql -m20 passwd
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select username, UID, GID from passwd limit 10;
+---------------------+---------------------+---------------------+
| username            | UID                 | GID                 |
+---------------------+---------------------+---------------------+
| root                | 0.0                 | 0.0                 |
| daemon              | 1.0                 | 1.0                 |
| bin                 | 2.0                 | 2.0                 |
| sys                 | 3.0                 | 3.0                 |
| sync                | 4.0                 | 65534.0             |
| games               | 5.0                 | 60.0                |
| man                 | 6.0                 | 12.0                |
| lp                  | 7.0                 | 7.0                 |
| mail                | 8.0                 | 8.0                 |
| news                | 9.0                 | 9.0                 |
+---------------------+---------------------+---------------------+
SQLRowCount returns 0
10 rows fetched

Categorically Variable