On walkabout in life and technology

Setup ODBC for R on OS X

Sep18th, 201312:59 pm

Hi Folks, Please do not follow this advice, its an old article that somehow has stayed way past its welcome in the search engines. Use the RPostgresSQL package that connects directly using the C drivers, its a lot faster to set up and run. ~Hilton.

At work, we use R to analyze data and calculate risk. The data is in a PostgreSQL database, so we use the RODBC package to access the database from R.

But this does not work under Macintosh OS X.

For two reasons.

One, it uses the iODBC libraries that come with OS X and these do not work out of the box. And two, even if you do install the ODBC Administrator tool and configure iODBC, it does not work with unicode drivers and databases.

Compile and install RODBC

Warning: If you install RODBC the usual way, i.e. packages.install("RODBC"), you will get a version compiled with the non-working iODBC libraries. If you have already done this, remove.packages("RODBC") in R to get rid of it.

The goal here is to link RODBC with the installed unixODBC library to make it work.

Test

All the installer does is test that the library loads. To see if it actually works, launch RStudio and type:

library("RODBC")
odbcDataSources()

The response should be:
database1 “PostgreSQL”

If you get an error message that contains [iODBC] in it, or a message that says named character(0), it means you are using the wrong library version (the default and not the newly compiled one). Remove RODBC and start again.

Let me know if this works for you too.

Off to write some database queries now.

Follow the author as @hiltmon on Twitter and @hiltmon on App.Net. Mute #xpost on one.

Comments

The Hiltmon

Hilton Lipschitz (@hiltmon) is CTO of an up and coming Hedge Fund, who loves to craft brilliant and delightful web, iPhone, iPad and Macintosh software products. Before this he was an independent software consultant, designer and developer at Noverse LLC.