Setting up R to use the ODBC Driver

Introduction

R is a language and environment for statistical computing and graphics. It provides a wide variety of statistical and graphical techniques. More information on R is available at www.r-project.org.

The Actual Technologies OBDC drivers can be used by R 2.0 and later. In order to use an ODBC driver with R on OS X, the RODBC package must be re-built with the iODBC libraries on your Mac. In order to do this, you must have the OS X Developer Tools installed. The Developer Tools CD is provided by Apple with every new Macintosh and copy of OS X.

Download and Install RODBC

• Make sure you have R installed (available at http://cran.cnr.berkeley.edu/bin/macosx/)
  
  You can check which version of R you have installed by looking at the version number printed in the R console window when you first start the R application. The version number reported when you select the "About R" menu item is misleading - it refers to the GUI version only (1.01), not the R application itself.
  
  
• Open a Terminal window by launching the Terminal application found in the /Applications/Utilities folder
  
  
• Download the RODBC package by entering the following command in the Terminal window:
  
  curl -O http://cran.cnr.berkeley.edu/src/contrib/RODBC_1.1-3.tar.gz
  
  
• Install a specially built version of the RODBC package by entering the following command in the terminal window (all on one line):

LIBS='-framework iODBC' PKG_CFLAGS='-I/Library/Frameworks/iODBC.framework/Headers' R CMD INSTALL RODBC_1.1-3.tar.gz

• should see some process related messages in the terminal window. You may see some warnings - this is normal.

Load the RODBC Package

• Launch (or re-launch) the R application (normally in the /Applications folder)
  
  
• Select "Package Manager" from the "Packages & Data" menu
  
  
• In the R Package Manager window, make sure the checkbox next to the RODBC package is enabled (the status should be "loaded")
  
  
• Close the R Package Manager window

Test the Connection

• Make sure you have a DSN configured for your database using the Apple ODBC Administrator (for OS X 10.4 "Tiger") or OpenLink / iODBC Administrator (for OS X 10.2 or 10.3) in the /Applications/Utilities folder
  
  
• In the R console window, use one of the ODBC connection functions to connect to your database. For example, if you have a DSN named "NorthWind" to connect to the NorthWind sample database on MS SQL Sever, you might enter:
  
  channel <- odbcConnect("NorthWind", uid="my_user_id", pwd="my_password")

If you see no error message, then you are connected. You can use the other RODBC package functions to fetch and update data in your database.