Installation of Rodbc on Os X Yosemite

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

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

Install unixodbc:
```
$ brew install unixodbc
```

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

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

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

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

Test the new configuration with isql:

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

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

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

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")
```
Now download the RODBC package source (which you an download here) to your Downloads folder.
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.

Installation of RODBC/ROracle packages on OS X Mavericks

This answer will cover installing RODBC and ROracle on OS X, specifically on Mavericks and later.

Both packages now need to be built (compiled) from source. This means that the first thing you need is to download XCode and the associated "command line tools" so that you actually have a compiler. How you do this has changed with basically every version of OS X/XCode, but if you start Googling you will probably end up landing here.

Once you have that in place...

RODBC

Since OS X 10.9 (Mavericks) Apple stopped including the iODBC SQL header files along with the
"command line tools" that R users on OS X are accustomed to installing in order to build R
packages from source.

So if you try to build RODBC from source on Mavericks at this point you should get an error like:

configure: error: "ODBC headers sql.h and sqlext.h not found"

To fix this, you need to download the latest version of iODBC (www.iodbc.org),
and then unzip the file and set your header and library search paths
to the location where you put the unzipped package. If you don't know how
to set your search paths, you could just put the header files (sql.h and sqlext.h) in the /usr/include directory,
and the libiodbc.a file in the /usr/lib directory.

Then you should be able to do

install.packages("RODBC",type = "source")

without any problems. Presumably this would work as well with the other major open source ODBC project, unixODBC, as well, but I have not tried that.

I have tested this (and the homebrew method in another answer) on El Capitan and both still work.

ROracle

This is more complicated. However, ROracle can be installed on OS X (I have tested this on Mountain Lion through El Capitan). Since ROracle relies on the Oracle Instant Client, there are no binaries available for
any platform. There are detailed installation instructions for the package here.

For OS X, we need to (mostly) follow the Linux instructions.

First, download the appropriate Oracle Instant Client, as well as the SDK, as directed in the Linuz instructions.

The Linux install instructions mention installing the client from an RPM, which will place everything "in the right place". I wasn't able to figure out whether that even applied for OS X, so I simply unzipped the client in a directory I created and placed the SDK inside that directory in /sdk.

The Linux install instructions then direct us to create a symbolic link for libclntsh.so.11.1. Since
this is OS X, it's actually called libclntsh.dylib.11.1. So we need to cd to the directory where
we unzipped the client and then run

ln -s libclntsh.dylib.11.1 libclntsh.dylib

On OS X, we then set the DYLD_LIBRARY_PATH, not the LD_LIBRARY_PATH:

export DYLD_LIBRARY_PATH=/scratch/instantclient_11_2:$DYLD_LIBRARY_PATH

using whatever path is correct for your machine.

I was never able to get ROracle to compile with an OCI_LIB environment variable. Instead,
I used the --with-oci-lib compiler flag option:

R CMD INSTALL --configure-args='--with-oci-lib=/scratch/instantclient_11_2' ROracle_1.1-11.tar.gz

again using whatever path and ROracle version number is appropriate.

If you are connecting to an Oracle database using a tnsnames.ora file, you'll also need to
set a TNS_ADMIN environment variable to point to the location of that file.

One last gotcha: you may find (as I did) that this will all work only if you launch R
from the command line (i.e. Terminal). Specifically, if you launch either RGui.app or
RStudio.app from the GUI, and then try to run library(ROracle) you will get an error
saying something to the effect of

> library("ROracle")
Error in dyn.load(file, DLLpath = DLLpath, ...) : 
  unable to load shared object '/Library/Frameworks/R.framework/Versions/3.1/Resources/library/ROracle/libs/ROracle.so':
  dlopen(/Library/Frameworks/R.framework/Versions/3.1/Resources/library/ROracle/libs/ROracle.so, 6): Library not loaded: /ade/b/3071542110/oracle/rdbms/lib/libclntsh.dylib.11.1
  Referenced from: /Library/Frameworks/R.framework/Versions/3.1/Resources/library/ROracle/libs/ROracle.so
  Reason: image not found
Error: package or namespace load failed for ‘ROracle’

This will happen even though Sys.getenv("DYLD_LIBRARY_PATH") faithfully reports
the correct path.

For a while, I hacked together a "solution" where I created a shell script that launched
RStudio/RGui upon startup, and just had to remember to always subsequently launch them via

open -a R.app
open -a RStudio.app

The problem apparently has to do with how environment variables are made available
to GUI launched applications in OS X. As is usually the case, I eventually found the
solution on StackOverflow. Editing the
/etc/launchd.conf file solved this problem, so ROracle now loads even when RStudio/RGui
are launched from the Finder. Though note here that the /etc/launchd.conf solution is no longer supported in Yosemite. That answer suggests you now need to set up a start-up plist file just to set the environment for launchctl on startup.

El Capitan Update for ROracle

I've now gone through the crucible on this one in El Capitan and it works as described above, but only if you disable System Integrity Protection first! Doing that is fairly quick, and instructions are easily found via Google.

Phew.

RODBC on macOS Catalina

I had the same issue connecting with Impala on Mac after some upgrades. The workaround was to make sure RODBC used unixodbc instead of iODBC as follows:

install unixodbc. I used brew.
Remove RODBC with remove.packages("RODBC")
Reinstall RODBC from source and specify the path to unixodbc lib and include
with
install.packages("RODBC", type = "source", configure.args = c("--with-odbc-include=/usr/local/include/","--with-odbc-lib=/usr/local/lib/") )

On my computer unixodbc is in /usr/local

RODBC to connect to SQL Server on Mavericks

The only reason this requires more legwork at the moment than usual is that there isn't (yet) an RODBC binary for Mavericks.

Even so, you will need a driver and to set up a DSN. I have always used the drivers from Actual Technologies (not free) and have been very happy with them. Otherwise you'll have to download and compile iODBC or unixODBC from source.

However, you'll still need to compile RODBC form source, and that will require the iODBC header files, which Apple apparently no longer supplies.

If you download the latest iODBC source and then either set your search paths to the location of the sources, or simply place the header files in /usr/include and the libiodbc.a file in /usr/lib. Then you should be able to compile RODBC from source.

Finally, aside from the temporary hiccup regarding the lack of a Mavericks binary for RODBC, all of this is explained in incredible detail in the RODBC vignette.