ODBC on OSX 10.7 for Postgresql is broke……a suggested fix

Recently I was needing to connect to a Postgresql database (specifically EMC’s Greenplum), from my Mac Air running OSX 10.7 Lion.  Greenplum provides four different drivers in their connectivity kit:

psqlodbc-08.02.0500 
psqlodbc-08.03.0400 
psqlodbc-08.04.0200 
psqlodbc-09.00.0200

I created an odbc.ini and odbcinst.ini files that have all the appropriate settings, tried each of the drivers, and go the same result:

nettles$ iodbctest
iODBC Demonstration program
This program shows an interactive SQL processor
Driver Manager: 03.52.0607.1008

Enter ODBC connect string (? shows list): ?

DSN | Driver 
——————————————————————————
greenplum | psqlodbc-09.00.0200 

Enter ODBC connect string (? shows list): greenplum
1: SQLDriverConnect = [iODBC][Driver Manager]dlopen(/usr/local/greenplum-connectivity-4.2.1.0-build-2/drivers/odbc/psqlodbc-09.00.0200/unixodbc-2.2.12/psqlodbcw.so, 6): no suitable image found. Did find:
/usr/local/greenplum-connectivity-4.2.1.0-build-2/drivers/odbc/psqlodbc (0) SQLSTATE=00000
2: SQLDriverConnect = [iODBC][Driver Manager]Specified driver could not be loaded (0) SQLSTATE=IM003

A suggestion was made, that all of these libraries were quite old and to try psqlodbc-09.00.0200.  I skipped that and went directly to plan B, which was to use a different package.  The package that most use with OSX is unixodbc, specifically something like unixodbc-2.2.12.

In searching the net, I found that just about everyone running Lion, and possibly even Snow Leopard, has had issues with the ODBC postgresql drivers.  It seems they have been broken for some time, and you will see people get errors very similar to the above.  The solution I found is Actual ODBC Driver for Open Source Databases from Actual Technologies.  For $34.95 you get a plug and play driver that comes with a nice updated ODBC Manager.  Actual Technologies makes the ODBC Manager available to all for free in case you want it without purchasing the product.

The driver still uses the ODBC framework your probably used to.  Tools such as isql and iodbctest should work just fine.  I was able to connect using R’s RODBC library to my external Greenplum database just fine:

> library(RODBC)
> ch <- odbcConnect(“greenplum”,uid=”gpadmin”,pwd=”password”)
> odbcGetInfo(ch)
DBMS_Name      DBMS_Ver               Driver_ODBC_Ver Data_Source_Name
“PostgreSQL”       “8.2.1953722927”  “03.00”                      “greenplum”
Driver_Name       Driver_Ver               ODBC_Ver                Server_Name
“atopnsrc.so”        “3.1.8”                       “03.52.0000”          “gp-single-host”

If you actually are able to get ODBC working for postgresql on OSX, please update me so I can update this post.  I have read enough up to this point to be pretty convinced its broke.  I would especially be interested in any feedback good or bad for anyone who has tried psqlodbc-09.00.0200.

This entry was posted in Data Analytics. Bookmark the permalink.

Leave a Reply