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:
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-22.214.171.124-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-126.96.36.199-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:
> ch <- odbcConnect(“greenplum”,uid=”gpadmin”,pwd=”password”)
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.