Re-compile RODBC using Actual Technologies SQL Server driver on OS X 10.10.3

Question

I am trying to re-compile the RODBC package so that I can connect to MS SQL Server from R on OS X 10.10.3. I am trying to modify the instructions I found here here, but I am not sure where I should point the $ DYLD_LIBRARY_PATH= to (I've tried both /Library/ODBC/ and /Library/ODBC/Actual SQL Server.bundle/Contents/MacOS/ but wasn't able to connect to the database in either case). Has anyone done this successfully? Also, once recompiled, should I be using odbcconnect or odbcdriverconnect? I've successfully connect/executed queries using R from a Windows VM on the same machine using the odbcconnect command. Thanks in advance!

Answer 1

I suppose someone can report me for posting something that is "not an answer" but I think I found some material in the R-SIG-Mac mailing list archives from 18 months ago that might or might not be useful. It does specifically address where its author thought that the binaries should be located. It's way too long to post as a comment:

From:   Marc Schwartz <marc_schwartz@me.com>
Subject:    Re: [R-SIG-Mac] RODBC not connecting from my Mac
Date:   October 30, 2013 8:35:24 AM PDT
To:     r-sig-mac@r-project.org R <r-sig-mac@r-project.org>

"Hi All,

I wanted to follow up on this thread, as the information that I provided above was not complete, as I now know from conversations with Prof. Ripley, information found online by searching in the Apple Developer Forums and an e-mail exchange with Actual Technologies' (AT) tech support.

Beginning with OS X 10.6 (Snow Leopard), Apple removed the ODBC Administrator GUI application from the default OS installation, as I noted above. The Apple version was and is still available as a separate download from Apple.com and the AT folks began to support and release and updated version called ODBC Manager, as I also noted.

However, that was only part of the story as it turns out.

Beginning with OS X 10.8 (Mountain Lion), Apple began the process of deprecating direct support for iODBC. The header files for iODBC that were included with XCode on 10.8 in the SDK tree apparently had indications of this, but it was not overtly documented anywhere else, including the XCode release notes. The binary dylib files for iODBC were still present in /usr/lib on 10.8, so if you wanted to use, for example, the RODBC binary for OS X on 10.8, there was not an issue. You still need the ODBC Administrator/Manager GUI to set up the DSN configuration of course, unless you manually set up the configuration files.

There is not a specific indication as to why, but an entry from August of 2012 in the Apple Dev Forums by an Apple rep indicated that a decision had been made by Apple to engage in the deprecation of the default installation of iODBC on OS X. This reply was to another user who had noted the header file content indicating this and posted a query. The Apple rep suggested that users begin to consider going to iODBC.org for OS X binaries where available or the source code moving forward. Alternatively, users should consider moving to other ODBC tools such as unixODBC or the use of native (non-ODBC) drivers for the data sources of interest.

With 10.9 (Mavericks), Apple has now completely removed iODBC from the default OS X installation. In the latest version of XCode, the binary dylib files for iODBC are present in the SDK tree for 10.9, but the header files have been removed, causing problems for folks who need to build iODBC dependent applications from source. There are indications however, that at some point in the future, even the iODBC dylib binary files will be removed from the XCode distribution. Near term at least, one could feasibly copy/move the dylib files from the SDK tree in 10.9 to /usr/lib, or simply symlink to them, but of course it means installing XCode just for that purpose, if you have no other need for it.

After I upgraded from 10.8 to 10.9 last week, I had no issues in using the RODBC binary with the AT driver to access our Oracle server. Thus, I had no initial basis to think that there was anything wrong on 10.9.

However, what I was not aware of at the time and now am, is that AT recently updated their ODBC driver installation package to include the iODBC binaries, specifically because of Apple's prior iODBC deprecation decision. If they are not present on the system to which the AT drivers are being installed (eg. 10.9), the iODBC binaries in the AT installation package will be installed in /usr/lib to enable the AT drivers to function. This is important for AT, since their drivers are compiled to use iODBC and not unixODBC. I had coincidentally updated the AT driver installation on my MacBook Pro after updating to 10.9 last week, which biased my perspective of iODBC support on 10.9.

Thus, as Mikkel noted in the original post, folks need to be aware that iODBC has been completely removed from OS X 10.9.

As the Apple rep noted in his response on the Dev Forums, options include going to iODBC.org directly if you wish to continue to use iODBC with ODBC drivers on OS X. If you elect to purchase and use the AT ODBC drivers, as I have done for Oracle, the iODBC binaries will now be installed on your system if not otherwise present. The AT installation packages also include the ODBC Manager GUI.

Alternatives would, of course, be to shift to unixODBC as Mikkel has done, being careful that whatever ODBC drivers you elect to use are compatible. If you elect to go down this path, as I noted above in my initial reply, you would need to compile the RODBC package from source, since it is compiled for OS X against the iODBC libs by default.

Thanks to Prof. Ripley for his communications and to the AT support folks for their clarifications on the current situation and their community support.

Regards,

Marc Schwartz"

Answer 2

Because: a) OP asked me to clarify my comment, b) this is too long to post as a comment, and c) may be helpful to someone in the future, I'm posting this as an answer, even though it's not about RODBC per se

This is how I connect to SQL Server from R on OS X. You can get the jar from Microsoft

library(RJDBC)

# jar location
classPath <- 'YOUR_DIR/sqljdbc_4.0/enu/sqljdbc4.jar'
stopifnot(file.exists(classPath))

# init driver
drv <- JDBC(driverClass='com.microsoft.sqlserver.jdbc.SQLServerDriver',
            classPath=classPath)

# connect to db (replace YOUR_DB, user, pword as needed, obviously!)
conn <- dbConnect(drv, "jdbc:sqlserver://YOUR_DB", user, pword)

# now you are ready to run your version of sql_statement
results <- dbGetQuery(conn, sql_statement)

The relevant bits of my sessionInfo() call:

R version 3.1.2 (2014-10-31)
Platform: x86_64-apple-darwin13.4.0 (64-bit)

locale:
[1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] RJDBC_0.2-4      rJava_0.9-6      DBI_0.3.1