How to install RODBC on Mac using unixodbc and freetds?
After searching quite extensively, I noticed that many people find it difficult to find a getting started guide that answers this question. (At least one question notes that a solution exists, but the proposed solution does not wrap around the fact that by default RODBC tries to compile with iODBC, which is not included in Yosemite.) I just went through this process, so I thought I would document it here, hoping it will benefit others. I am connecting to a SQL Server database.
source to share
Using Homebrew as the package manager on OS X, I can install RODBC
with the following steps (assuming I have already installed R ).
-
Install
unixodbc
:$ brew install unixodbc
-
Install
freetds
(if necessary, replacing/usr/local/Cellar/unixodbc/2.3.2_1
with a directoryunixodbc
):$ brew install --with-tdsver=8.0 --with-msdblib --with-unixodbc=/usr/local/Cellar/unixodbc/2.3.2_1 freetds
-
Customize your installation
freetds
(following minimal config 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
-
Check your configuration
freetds
:$ 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
-
Customize your installation
unixodbc
(following minimal config 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 setup 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
-
Test your new configuration with
isql
:$ isql TESTSQL `<username>` `<password>`
+---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL> quit
-
Create a symbolic link to 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
-
Find and modify the file
RProfile
by adding the following line (s) of code to the file (replacing/usr/local/include
with the include directory containing your filessql.h
andsqlext.h
), the second line may not be needed 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")
-
Now upload the package source
RODBC
(which you download here ) to your Downloads folder. -
Open a new R console session and install the package (replacing
RODBC_1.3-12.tar.gz
with your package source name):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 helping figure out what the default R directories look like for the required files for RODBC.
source to share