[R-SIG-Mac] RODBC not connecting from my Mac

[Marc Schwartz]

> On 25/10/2013 13:54, Marc Schwartz wrote:
>> 
>> On Oct 13, 2013, at 11:41 AM, Mikkel Grum <mi2kelgrum at yahoo.com> wrote:
>> 
>>> iODBC appears no longer to come standard with OSX, so I installed unixodbc and set it up following instructions here: http://www.boriel.com/en/2013/01/16/postgresql-odbc-connection-from-mac-os-x/
>>> 
>>> I connected to my remote database with isql -v mydsn. No problem. Then I tried from R:
>>> 
>>>> library(RODBC)
>>>> pg <- odbcConnect("mydsn")  # waited for a couple of minutes before pressing Ctrl-C
>>> ^C
>>> There were 50 or more warnings (use warnings() to see the first 50)
>>>> warnings()[1:2]
>>> Warning messages:
>>> 1: In odbcDriverConnect("DSN=mydsn") :
>>>   [RODBC] ERROR: state IM002, code 1606406032, message [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded
>>> 2: In odbcDriverConnect("DSN=mydsn") :
>>>   [RODBC] ERROR: state IM002, code 1606406032, message [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded
>>> 
>>> It looks like RODBC might only work with iODBC on the Mac. Is that the case? I haven't been able to configure iODBC correctly, and therefore haven't been able to test whether that would work with RODBC. Any chance that I can get RODBC to work with unixodbc? Any other information that would be useful in resolving it?
>>> 
>>>> sessionInfo()
>>> R version 3.0.2 (2013-09-25)
>>> Platform: x86_64-apple-darwin10.8.0 (64-bit)
>>> 
>>> locale:
>>> [1] C/UTF-8/C/C/C/C
>>> 
>>> attached base packages:
>>> [1] stats     graphics  grDevices utils     datasets  methods   base
>>> 
>>> other attached packages:
>>> [1] RODBC_1.3-8
>>> 
>>> Regards
>>> Mikkel
>> 
>> 
>> First, no need to cross-post to three groups. I am replying to R-SIG-Mac only here.
>> 
>> iODBC is still available on OSX. Apple removed the ODBC Administrator application back on Snow Leopard. A newer ODBC Manager application is freely available for download here:
>> 
>>   http://www.odbcmanager.net/index.php
>> 
>> It is supported by Actual Technologies, which offers for purchase, ODBC drivers on OSX for various data sources. I use their driver for Oracle.
>> 
>> If you want to use the older Apple ODBC Administrator application, it is still available here:
>> 
>>   http://support.apple.com/kb/DL895
>> 
>> If you want to stay with unixODBC, you may want to read through the vignette for RODBC that Prof. Ripley has written and pay attention to the Installation section on page 22:
>> 
>>   http://cran.r-project.org/web/packages/RODBC/vignettes/RODBC.pdf
>> 
>> which has some hints that should be helpful, such as using:
>> 
>>   --with-odbc-manager=odbc
>> 
>> when installing RODBC from source. Yes, RODBC will, by default, use iODBC on OSX, since that is the default ODBC installation on OSX. Thus, the precompiled binary for RODBC will not work for what you are attempting to do.
>> 
>> Regards,
>> 
>> Marc Schwartz


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