[Rd] [R] RODBC ERROR on Rcmdr install

[Marc Schwartz (via MN)]

John et al,

First, RODBC is indeed quite useful under Linux and I use it to connect
to an Oracle 10g server running on RHEL.  Oracle provides FREE Linux
ODBC drivers that support this functionality:

http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/linuxsoft.html

That same scenario will be in place for any database provider that
offers a native Linux ODBC driver for their application. unixODBC by
itself is only half of the solution.

With respect to Excel and Access under Linux, there are several options,
none of which include ODBC, since a certain large software company in
the northwestern U.S. has no incentive to provide ODBC drivers for
competitive OSs.

For Excel, one can of course export data to ASCII files, which includes
the use of OO.org's Calc application under Linux, which in turn supports
the reading and writing of Excel format files.  In addition, there is
the 'gdata' package on CRAN which provides the read.xls() function.

For Access, about the only things that I am familiar with under Linux
are the MDB Tools package:

  http://mdbtools.sourceforge.net

and the Easysoft ODBC-ODBC bridge product:

http://www.easysoft.com/products/data_access/odbc_odbc_bridge/index.html

The former is in some stage of being incorporated into OO.org's suite of
applications to enable the manipulation of Access MDB files within that
environment. More information on that is available here:

  http://dba.openoffice.org/

For dBase, the only things that I have seen besides R's 'foreign'
package are the DataDirect ODBC suite:

  http://www.componentsource.com/products/515702/18763/summary.html

and the 'dbf' converter:

  http://berg-systeme.de/dbf.html


With respect to the detection of unixODBC and the requisite client
drivers, there are multiple system and user specific files where such
information may be available. These include:

/etc/odbc.ini

/etc/odbcinst.ini

~/.odbc.ini (User specific)

These would correspond to System and User DSN's.

The use or lack thereof for any of these files, will be highly dependent
upon how the drivers were installed.

One of the other solutions that is evolving is JDBC, which is Java
based. However, I have seen mixed reports of success using that
interface, and I don't recall seeing any support for this in R as of
yet, though I may certainly be wrong.

You might want to move this thread yet again, over to the r-sig-DB list,
where you may find more robust solutions and a DB focused constituency.

HTH,

Marc Schwartz

On Mon, 2006-10-02 at 09:10 -0400, John Fox wrote:
> Dear Vittorio,
> 
> Thanks for the clarification. This raises two questions: (1) If a *nix
> system has an ODBC driver, can one then read Excel, Access, and dBase data
> sets via RODBC (which is what the Rcmdr menu item in question provides for)?
> (2) If so, is there a way for me to detect whether unixODBC is present, or
> would I have to rely, e.g., on a user-set option?
> 
> I'm moving this message to the r-devel list.
> 
> Regards,
>  John
> > -----Original Message-----
> > From: r-help-bounces at stat.math.ethz.ch 
> > [mailto:r-help-bounces at stat.math.ethz.ch] On Behalf Of vittorio
> > Sent: Saturday, September 30, 2006 12:40 PM
> > To: r-help at stat.math.ethz.ch
> > Subject: Re: [R] RODBC ERROR on Rcmdr install
> > 
> > Alle 22:55, venerdì 29 settembre 2006, John Fox ha scritto:
> > > As I understand it, RODBC isn't useful on non-Windows 
> > systems, since 
> > > the necessary ODBC drivers aren't available. (Someone will 
> > correct me, 
> > > I'm sure, if I don't have that entirely straight.) The 
> > RODBC package 
> > > is used in the Rcmdr to read Excel and some other files 
> > under Windows; 
> > > in the latest version of the Rcmdr, you won't even see this 
> > menu item 
> > > in non-Windows systems.
> > 
> > As a matter of fact RODBC can be profitably used under *nix 
> > OS together with unixODBC to connect to many DBs.
> > I've been using RODBC with unixODBC on linux, freebsd and win 
> > xp  to connect smoothly to postgresql, mysql  and oracle 
> > (somewhat tricky to me under *nix, you need ** to buy ** a 
> > driver) and I know that connections are possible to many 
> > other *nix DBs under *nix itself. 
> > 
> > Ciao
> > Vittorio
> >