UnixODBC example setup and configuration on Ubuntu Lucid with Mysql and PostgreSQL using the command line only 10

Below is a very quick example of what I did to configure a system DSN connection to a Mysql and PostgreSQL database using UnixODBC drivers on Ubuntu Lucid server from command line only.


# Do the obvious, install mysql an postgresql first. :)

# install unixodbc
apt-get install unixodbc unixodbc-dev libmyodbc odbc-postgresql

# cd to /etc/ODBCDataSources

# setup mysql driver, make myodbc_template file
Description = MySQL driver
Driver = /usr/lib/odbc/libmyodbc.so
Setup = /usr/lib/odbc/libodbcmyS.so

#inst template driver
odbcinst -i -d -f myodbc_template

# setup pg driver, make pgodbc_template file
Description = PostgreSQL driver for Linux & Win32
Driver = /usr/lib/odbc/psqlodbca.so
Setup = /usr/lib/odbc/libodbcpsqlS.so

#inst template driver
odbcinst -i -d -f pgodbc_template

# query available/installed drivers
odbcinst -q -d
#should list Mysql and PostgreSQL

# make sample dsn template called gregtest_dsn
Driver = /usr/lib/odbc/libmyodbc.so
SERVER = localhost
PORT = 3306
DATABASE = gregtest
USER = majorpayne
PASSWORD = ******** < replace with password!

# install dsn
odbcinst -i -s -l -f gregtest_dsn

# query system dsn’s
odbcinst -q -s

isql -v gregtest

# should connect and be able to run sql statements! :)

Make more DSN’s for other databases by adding more template files and repeating the steps above.




Edit 7/7/11:

I think pgsql odbc should not be /usr/lib/odbc/libodbcpsql.so , but rather /usr/lib/odbc/psqlodbca.so.
I have not tested, but I later realized libodbcpsql.so does not exist.  Just an FYI.


EDIT March 6th 2016:

Been a long time since I had to play with ODBC stuff. I’ve had a hard time with PostgreSQL!  Today I had to test some ODBC Postgres connection on Ubuntu Precise 12.04.  Its getting pretty old now, though!  But after some struggling, I FINALLY found out why Postgres would not connect.  My situation was that I could connect just fine using “psql” command line client for Postgres, but “isql” for ODBC would not work. I kept getting errors like this:

[08001][unixODBC]Could not connect to the server;
Could not connect to remote socket immedaitely
[ISQL]ERROR: Could not SQLConnect

It would never connect to the server.  I finally found out on another blog:

Wow that was hard to find!!!  For PostgreSQL you need to have “Servername”  NOT “Server”.  OMG!!!

Just some specs, for help to others.  I have a PG server running on another host on the network with version 9.5 (on a Mac actually).  And I do have the pg_hba.conf and ports listening and roles setup, so make sure you have that covered and psql works.  On the Ubuntu 12.04 box I have all the packages for postgres client and odbc for postgres.  All that is version 9.1.   Here’s my current /etc/odbcinst.ini and /etc/odbc.ini sections on the client host:

Driver = /usr/lib/x86_64-linux-gnu/odbc/psqlodbca.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libodbcpsqlS.so
Descrption = PostgreSQL Driver
UsageCount = 1

Driver = psql_template
Servername = norion-eth
Port = 5432
Database = ets
User = myuser
Password = xxxxx
Trace = Yes
TraceFile = /tmp/psql-odbc.log


That’s about it!  There’s not a lot of help out there for PostgreSQL ODBC stuff.  everything I find is similar issues and people reporting some similar troubleshooting and issues, but seems like hardly any one is posting how they solved it, or they give up.  I know I did years back.  I could not get this to work on 12.04 , gave up and went all to Mysql.  Which really make some mad, because now I’m regretting that I didn’t pursue this further.