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!

apt-get update
apt-get install pgsql

Create Language

Example plpgsql

su postgres
createlang plpgsql template1
exit

Change authentication method

We need to edit file pg_hba.conf to change authentification method for accessing PostgreSQL database.

cp /etc/postgresql/pg_hba.conf /etc/postgresql/pg_hba.confbak
vi  /etc/postgresql/pg_hba.conf

Find this section

# TYPE  DATABASE  USER  IP-ADDRESS  IP-MASK  METHOD
# Database administrative login by UNIX sockets
local  all  postgres        ident sameuser
#
# All other connections by UNIX sockets
local  all  all        ident sameuser
#
# All IPv4 connections from localhost
host  all  all  127.0.0.1  255.255.255.255  ident sameuser
#
# All IPv6 localhost connections
host  all  all  ::1  ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff  ident sameuser
host  all  all    ::ffff:127.0.0.1/128  ident sameuser
#
# reject all other connection attempts
host  all  all  0.0.0.0  0.0.0.0  reject

Edit that section to

# TYPE  DATABASE  USER  IP-ADDRESS  IP-MASK  METHOD
# Database administrative login by UNIX sockets
local  all  postgres        ident sameuser
#
# All other connections by UNIX sockets
local  all  all        password
#
# All IPv4 connections from localhost
host  all  all  127.0.0.1  255.255.255.255  password
#
# All IPv6 localhost connections
host  all  all  ::1  ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff  password
host  all  all    ::ffff:127.0.0.1/128  password
#
# reject all other connection attempts
host  all  all  0.0.0.0  0.0.0.0  reject

Restart PostgreSQL Server

/etc/init.d/postgresql restart

Create a New Database

Example wordpress

su postgres
createdb -T template1 wordpress
exit

Create a New User

Example: User supriyadisw with password cak3p

su postgres
createuser supriyadisw -P
Enter password for new user: cak3p [enter]
Enter it again: cak3p [enter]
Shall the new user be allowed to create databases? (y/n) y [enter]
Shall the new user be allowed to create more new users? (y/n) n [enter]
CREATE USER
exit

Login to PostgreSQL

pgsql -U supriyadisw wordpress [enter]
Password: cak3p [enter]

Good Luck

aptitude install pciutils