UnixODBC CLI Install and Configuration

Posted by mpapet on Wed 7 Feb 2007 at 12:55

For those of you that may not know what unixodbc does, "ODBC is an open specification for providing application developers with a predictable API with which to access Data Sources. Data Sources include SQL Servers and any Data Source with an ODBC Driver." They include a text file driver as an example of a non-SQL source. Two examples are Asterisk and OpenOffice.org.

Unixodbc allows cross-platform use of databases with many bridges available in many popular programming languages.

Installing and configuring isn't necessarily as simple as "apt-get install unixodbc". This HOWTO was written on Etch and may vary if you are using a different version.

Step 1: Download Packages

Running: apt-get install unixodbc libmyodbc odbc-postgresql \ odbcinst1debian1

will get you the ODBC binaries, database drivers for MySQL and PostgreSQL and a Debian helper application for ODBC respectively.

Step 2: Add odbcinst.ini Records

As the root user, check for two empty text files /etc/odbc.ini and /etc/odbcinst.ini. If they aren't there, then create them. (eg. "touch /etc/odbcinst.ini")

Create a directory for the odbcinst.ini scripts:

username@host:~$ mkdir  /home/username/odbc

Step 3: Adding ODBC Instances

In order to minimize entry-error, I use text files to load ODBC instances. Odbcinst's error messages are a bit cryptic so I control errors using a separate file for each database type.

As root create a file name pgsql and paste the following into it.

[PostgreSQL]
Description     = PostgreSQL driver for Linux & Win32
Driver          = /usr/lib/odbc/psqlodbca.so
Setup           = /usr/lib/odbc/libodbcpsqlS.so
FileUsage       = 1

If you are on another version double-check your library names.

Name the file pgsql so the following command should work.

odbcinst -i -d -f /home/username/odbc/pgsql

You can create another file for MySQL with the following contents:

[MySQL]
Description     = MySQL driver for Linux & Win32
Driver          = /usr/lib/odbc/libmyodbc3_r-3.51.11.so
Setup           = /usr/lib/odbc/libodbcmyS.so
FileUsage       = 1

To use it run, as root: "odbcinst -i -d -f /home/username/odbc/mysql".

With PostgreSQL and MySQL done, lets create a sample ODBC connection!

Step 4: Create ODBC Connection

Create another text file and copy the text below into it. I called it asterisk. Copy the contents of this file into odbc.ini. (Note, in theory "odbcinst -i -s -f /path/to/file" writes the contents of the named file into /etc/odbc.ini. I could not get it to do so and there were no error messages) So, copy and paste the following into odbc.ini too

[asterisk]
Description = MySQL Asterisk
Driver      = MySQL
SERVER      = localhost
USER        = username
PASSWORD    = password
PORT        = 3306
DATABASE    = asterisk
Option      = 3

Make sure your database permissions are configured to allow the login/connection before testing.

Step 5 Test Connection:

As the root enter run:

 isql asterisk

This should put you into the asterisk database!

The unixodbc site was very helpful to me. Check the manuals page for useful how-to's and additional driver configuration information.

This article can be found online at the Debian Administration website at the following bookmarkable URL (along with associated comments):

http://www.debian-administration.org/article/UnixODBC_CLI_Install_and_Configuration