[R] R and database access

[David James]

Hi,

Adam Witney wrote:
> Hi,
> 
> I am relatively new at R, but I would like to be able to directly access my
> MySQL and PostgreSQL databases from R. I have been looking around CRAN and
> searching through Google, however I am not sure what are the best options. I
> have seen
> 
> DBI         - not sure how this works
> Rdbi        - not sure how this works
> Rdbi.PgSQL  - can install, but fails on trying to load
> RODBC       - cannot connect (I cannot find any troubleshooting guides)
> RPgSQL      - can install, but fails on trying to load
> RMySQL      - installation fails

Historically, each database package has had its own set of
function (API) for interfacing with the database.  This means
that the function names and methods used to connect through ODBC are
completely different than, say, to  mSQL, Oracle or PostgreSQL.
The problem was recognized and a special interest group on databases
(R-SIG-DB) was formed to come up with a common database interface
(DBI) similar in spirit to Java's JDBC, Perl's DBI/DBI, ODBC,
Python's DB, etc.  The database interface package "DBI" reflects
the current proposal, and it is based on Tim Keitt's Rdbi plus
input from others.

As of today, no package in CRAN is yet DBI-compliant, but this is
simply due to time constraints on package developers and maintainers
who have to migrate their packages from existing individual APIs
to the DBI.  

With the release of R version 1.6.0, there will be DBI-compliant
packages for Oracle, MySQL, and SQLite (for beta versions go to
ftp://ftp.cran.r-project.org/pub/R/src/contrib/1.6.0/Other
and there are plans to port others, time permitting.

> 
> (System = Mac OSX 10.2)
> 
> Which is considered the most up to date way of achieving this connection?
> 

Hmmm, some of these packages have been used quite extensively (PgSQL
in particular).  Also I see you're using Jaguar (Mac OS X, 10.2),
so it's possible that some of the problems you've experienced are
due to the changes in the operating system, C libraries, etc.,
but hopefully these will be identified and addressed soon. 

> Thanks for any help
> 
> adam
> 
> -.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
> r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
> Send "info", "help", or "[un]subscribe"
> (in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch
> _._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._

-- 
David A. James
Statistics Research, Room 2C-253            Phone:  (908) 582-3082       
Bell Labs, Lucent Technologies              Fax:    (908) 582-3340
Murray Hill, NJ 09794-0636
-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-.-
r-help mailing list -- Read http://www.ci.tuwien.ac.at/~hornik/R/R-FAQ.html
Send "info", "help", or "[un]subscribe"
(in the "body", not the subject !)  To: r-help-request at stat.math.ethz.ch
_._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._._