How do I install RODBC on Mac with unixodbc and freetds?

Question

After a fairly extensive search, I noticed that a number of people are having a hard time finding a start-to-finish guide that answers this question. (At least one question notes that a solution exists, but the proposed solution does not get around the fact that, by default, RODBC attempts to compile against iODBC, which is not included with Yosemite.) I just went through this process, so I thought I would document it here in the hope that it will benefit others. I am connecting to a SQL Server database.

Answer 1

Using Homebrew as my OS X package manager, I can install RODBC with the following steps (assuming I have already installed R).

1. Install unixodbc:

   $ brew install unixodbc
   

2. Install freetds (replacing /usr/local/Cellar/unixodbc/2.3.2_1 with your unixodbc directory, if necessary):

   $ brew install --with-tdsver=8.0 --with-msdblib --with-unixodbc=/usr/local/Cellar/unixodbc/2.3.2_1 freetds
   

3. Configure your freetds installation (the following is a minimal configuration file):

   freetds.conf

   # server specific section
   [global]
   ;       tds version = 8.0
   ;       dump file = /tmp/freetds.log
   ;       debug flags = 0xffff
   ;       timeout = 10
   ;       connect timeout = 10
           text size = 64512
   
   [TESTSQL]
           # insert the actual host below
           host = <xxx.xx.x.xx>
           port = 1433
           tds version = 8.0
   

4. Test the freetds config:

   $ tsql -H `<xxx.xx.x.xx>` -p 1433 -U `<username>` -P `<password>`
   

   locale is "en_US.UTF-8"
   locale charset is "UTF-8"
   using default charset "UTF-8"
   1> exit
   

5. Configure your unixodbc installation (the following is a minimal configuration file):

   $ sudo vim /usr/local/Cellar/unixodbc/2.3.2_1/etc/odbcinst.ini
   

   odbcinst.ini

   [MSSQL]
   Description   = Microsoft SQL Server driver
   Driver        = /usr/local/Cellar/freetds/0.95.18/lib/libtdsodbc.so
   

   (and another minimal installation file):

   $ sudo vim /usr/local/Cellar/unixodbc/2.3.2_1/etc/odbc.ini
   

   odbc.ini

   [ODBC Data Sources]
   TESTSQL     = Test database
   
   [TESTSQL]
   Driver      = MSSQL
   Servername  = TESTSQL
   Port        = 1433
   Database    = TMSEPRD
   TDS_Version = 8.0
   

6. Test the new configuration with isql:

   $ isql TESTSQL `<username>` `<password>`
   

   +---------------------------------------+
   | Connected!                            |
   |                                       |
   | sql-statement                         |
   | help [tablename]                      |
   | quit                                  |
   |                                       |
   +---------------------------------------+
   SQL> quit
   

7. Create a symbolic link to the files in your home directory:

   $ ln -vs /usr/local/Cellar/freetds/0.95.18/etc/freetds.conf ~/.freetds.conf
   $ ln -vs /usr/local/Cellar/unixodbc/2.3.2_1/etc/odbc.ini ~/.odbc.ini
   $ ln -vs /usr/local/Cellar/unixodbc/2.3.2_1/etc/odbcinst.ini ~/.odbcinst.ini
   

8. Find and modify your RProfile file by appending the following line(s) of code to the file (replacing /usr/local/include with the include directory that contains your sql.h and sqlext.h files; the second line may be unnecessary if the directory does not exist):

   $ vim /Library/Frameworks/R.framework/Versions/3.2/Resources/library/base/R/Rprofile
   Sys.setenv(ODBC_INCLUDE="/usr/local/include")
   Sys.setenv(ODBC_LIBS="/usr/local/lib")
   

9. Now download the RODBC package source (which you an download here) to your Downloads folder.

10. Open a new R console session and install the package (replacing RODBC_1.3-12.tar.gz with the name of your package source):

    install.packages("~/Downloads/RODBC_1.3-12.tar.gz", repos=NULL, type="source")

The package should now work:

> library(RODBC)
> myconn <- odbcConnect("TESTSQL", uid="<userid>", pwd="<password>")

Thanks to Jared Folkins and Gabi Huiber for help with figuring out what directories R looks in by default for the requisite files for RODBC.