Skip to content

RJDBC to the Rescue

August 4, 2008

I have been using the statistical language R for some time now to do some of my intensive off-line calculations.  The great thing(s) about R is that it’s fast, well documented, extensive, and open source.

However, I regularly run into problems configuring and connecting to Oracle databases, mainly because the API’s are extremely obtuse, the code for these systems are closed, and licences are extremely expensive.  Even the ODBC drivers (a universal database connectivity layer) are pretty difficult to find (at least for my OSX platform).  The whole process for getting everything working is usually pretty convoluted.

However, the RJDBC package for R simplifies everything by using a Java database connector.  Rather than setting up ODBC profiles, downloading drivers, etc.  Everything can be handled in R by including the package, setting pointers to the JDBC class package (Oracle class package available for the mac here), and formatting a single connection string, like so:

library(RJDBC)

# point the R package at the appropriate java driver class, as well as the class file
drv<-JDBC(“oracle.jdbc.driver.OracleDriver”, “/location/of/ojdbc14.jar”)
# the “easy” connection string spec for oracle db’s
conn<-dbConnect(drv, “jdbc:oracle:thin:@<host>:<port default=1521>:<SID>”,”username”,”password”)

# etc…
query = “SELECT * FROM A TABLE”
dbGetQuery(conn, query)

And that’s it!  Maybe this will save someone else some time and headaches. Arguably, the use of an additional Java layer may negatively affect performance in some cases, but in my case it is not noticeable.

One Comment
  1. Excellent! This saved me from ROracle.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: