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

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
[MySQL]
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
[PostgreSQL]
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
[gregtest]
Driver = /usr/lib/odbc/libmyodbc.so
SERVER = localhost
PORT = 3306
DATABASE = gregtest
OPTION = 3
USER = majorpayne
PASSWORD = ******** < replace with password!

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

# query system dsn’s
odbcinst -q -s

#connect
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:
http://thompsonng.blogspot.com/2014/10/odbc-08001unixodbccould-not-connect-to.html

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:

[psql_template]
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

[ets_norion]
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.

 

 

 

 

 

Posted in Databases, Linux, Ubuntu Tagged with: , , , ,
  • Wb4alm

    Thank you!  I am not a LINUX guru, but have been trying to figure out how to install the mysql / odbc  stuff under LINUX for some time now.  Now I can proceed with my project….

  • Franck Saiag

    I was going crazy with this thing; you helped me get a step closer to my goal; THANKS A LOT!

  • Yaziciemre

    Hello I am getting
    odbcinst: SQLInstallDriverEx failed with Invalid install path.
    Can you help me to fix?

    • Anon

      Try preceding the command with sudo

  • Ma Mattana

    Hello,
    Guide work thx :)

  • Emil

    This guide helped me get odbc working. Thansk a lot!

  • suresh

    Did you try with postgresql-9.2 and unixodbc-2.3.1

    • gregthegeek

      Sorry, I have not tested the newer versions yet.

  • Artem Tsushko

    Thank you very much! Your example complements the official documentation very well.
    http://manpages.ubuntu.com/manpages/trusty/man1/odbcinst.1.html

  • Navin Kumar

    isql is connecting but its not working in php. Please email me about this to me (gnavinkumarbe@gmail.com)