R help - Oct 2004 - JDBC DB access in R

Hello, i see packages referencing using ODBC access to databases via R, are
there any packages that use JDBC to submit SQL to a database and return into
a data frame?  

-zubin

The J is for Java (I presume, you did't actually say), and R is written in
C.  So `all' you need to do is to call a Java wrapper from R and call JDBC
from that.  I believe it has been done via RSJava.

But if all you want to do is to retrieve a dataframe, isn't this rather 
hard work?

On Fri, 22 Oct 2004, zubin wrote:
> Hello, i see packages referencing using ODBC access to databases via R, are
> there any packages that use JDBC to submit SQL to a database and return
into
> a data frame?  
-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595

Hi.  One thing you might also want to consider is the RDBI/RdbiPgSQL suite
at GRASS found at http://grass.itc.it/statsgrass/r_and_dbms.html.  These are
PostgreSQL drivers.  For MySQL, just use the DBI and RMySQL found on CRAN at
http://cran.at.r-project.org/src/contrib/PACKAGES.html.  I primarily run R
on Win32, FreeBSD and Mac OSX.  The DBI PostgreSQL drivers work impressively
well on the FreeBSD/Mac OSX platforms, (which are "kissing cousins",
per
se.)  As Prof Ripley alluded to, using Java sounds hard just to retrieve a
dataframe and there are, IMHO, far easier ways to pull data.

On Win32, RODBC works well, however I found absolutely no joy on FreeBSD and
PostgreSQL.  I have used RODBC on Win32 to pull MS SQL, MS Access, Teradata,
etc. predicated that there are appropriately installed ODBC drivers for them
on your PC.  FYI, I work for a really large bank and my group is basically a
"SAS shop" as well.  While SAS is great at some things, I argue R is
better
for others.  So, while as not to start a thread on R vs. SAS merits, I wish
you luck integrating R in your work/workplace.  It's a great/powerful
analysis language/package whose adoption I hope continues to expand.


Charles


-----Original Message-----
Thanks for the reply - thru R i have large data sets in oracle, mssql,
postgres and we have jdbc drivers for all of them - most of these systems
are on UNIX/LINUX and ODBC is not used - we use JDBC to hit the DB's usually
in java applications we have.  other than that we are a large SAS shop and i
am trying to find an alternative to SAS, i think R has a good chance.

For R and SQL - i assume using the wrapper below is the easiest way to hit
databases via SQL - are there any other ways i am missing?  or is JDBC route
the easiest when ODBC is not available?

not sure if R contains some standard database routines that i am not seeing?

also

does R have some good data manipulations function - if so, could you point
me in the right direction (similiar to DATA steps in SAS)

-thx!



	[[alternative HTML version deleted]]