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.
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.
It is assumed you have the following installed and running:
- Xcode with command line tools installed (needed to compile the ODBC drivers and RODBC)
- RStudio (optional, but so much better than the standard R GUI)
- The database (in my case PostgreSQL, with developer libraries, so that the compiles will work. I installed mine using Homebrew)
This turns out to be easy, just use Homebrew:
$ brew update $ brew install unixodbc
Install the PostgreSQL ODBC Driver
The best way to install this is to download and compile it manually. Download the latest driver file from the postgres file browser and then (only commands shown):
$ tar zxvf psqlodbc-09.02.0100.tar.gz $ cd psqlodbc-09.02.0100/ $ ./configure $ make $ sudo make install
Setup the ODBC Driver
Establish the driver in
1 2 3 4 5 6
And set up a connection in
1 2 3 4 5 6 7 8 9 10 11 12
You could also set the connection in
~/.odbc.ini but I find system level connections work better, especially when moving code into production.
Test the connection using
$ isql -v database1
and you should see
+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
Ok, ODBC is working and set up.
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.
To compile and install it properly, first do:
This tells the compiler to use the unixODBC ODBC libraries installed by Homebrew in
/usr/local/lib and not the iODBC ones installed by Apple in
Then manually compile and install RODBC (you need the full path to the downloaded file for it to work):
$ R CMD INSTALL /Users/Hiltmon/Downloads/RODBC_1.3-8.tar.gz
After a pile of configure and compile messages, you should see:
** R ** inst ** preparing package for lazy loading ** help *** installing help indices ** building package indices ** installing vignettes 'RODBC.Rnw' ** testing if installed package can be loaded * DONE (RODBC)
All the installer does is test that the library loads. To see if it actually works, launch RStudio and type:
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.