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