Connect a MSSQL database via RODBC on Ubuntu 14.04

This post is just a personal reminder, on internet you will find quite a lot of stuff, every solution might even work, but I prefer to deal with the minimum number of configuration files so:

sudo apt-get update
sudo apt-get upgrade
sudo apt-get install r-base
sudo apt-get install libiodbc2-dev libiodbc2 unixodbc unixodbc-dev

Unfortunately there is an incompatibility between libiodbc2 and tdsodbc. The installation of the latter will remove the fist. A dirty (very dirty) workaround is to copy the library and restore it later. If not, RODBC will refuse to load (or if you prefer, will complain about the missing driver, you choose!)

cp /usr/lib/libiodbc.so.2.1.19 /tmp
sudo apt-get install freetds-dev freetds-bin freetds-common libct4 libsybd5 tdsodbc sqsh libsybdb5
sudo cp /tmp/libiodbc.so.2.1.19 /usr/lib/libiodbc.so.2.1.19
cd /usr/lib/
sudo ln -s libiodbc.so.2.1.19 libiodbc.so.2

edit sudo vim /etc/odbcinst.ini file which contains the drivers definition for the ODBC connections and write on it something like:

[FreeTDS]
Description = FreeTDS for MSSQL
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
FileUsage = 1

Now you can open R, install RODBC package, load it and start using the ODBC connection.

I really do not like DSN, so the above instructions will work withodbcDriverConnect() function only. In case do a google research for the DSN definition.

library('RODBC')
data_odbc <- odbcDriverConnect(connection="server=[HOST];database=[DB];uid=[USER]pwd=[PASS];Port=1433;driver=FreeTDS;TDS_Version=7.0;")
res <- sqlQuery(data_odbc, '[YOUR SQL QUERY]')

 

Blog tags
Disqus Comments