RJDBC - Allows the use of JDBC to access databases from R
RForge.net

RJDBC

About RJDBC
GIT access
Download/Files
News
Check results
Package R docs

About
  RJDBC is a package implementing DBI in R on the basis of JDBC. This allows the use of any DBMS in R through the JDBC interface. The only requirement is working Java and a JDBC driver for the database engine to be accessed.
Download
  The latest RJDBC release is available from CRAN, use:

install.packages("RJDBC",dep=TRUE)

Latest development build is always available on the RForge files page or via SVN.

Documentation
  The primary documentation are the R man pages, an example follows below.
Example
 

library(RJDBC)
drv <- JDBC("com.mysql.jdbc.Driver",
           "/etc/jdbc/mysql-connector-java-3.1.14-bin.jar",
           identifier.quote="`")
conn <- dbConnect(drv, "jdbc:mysql://localhost/test", "user", "pwd")

Loads a JDBC driver for MySQL (adjust the path to the driver's JAR file as necessary) and connects to the local database "test". The connection handle conn is used for all subsequent operations.

(Note: Windows users can use drv<-JDBC("sun.jdbc.odbc.JdbcOdbcDriver") to use Sun's JDBC to ODBC Bridge with "jdbc:odbc:..." URL.)

In order to alow more complex names for tables and columns, it is a good idea to set identifier.quote to the quote character supported by the database for quoting identifiers. The default (NA) does not quote identifiers, but this limits the names that can be used, affecting dbReadTable and dbWriteTable.

dbListTables(conn)
data(iris)
dbWriteTable(conn, "iris", iris, overwrite=TRUE)
dbGetQuery(conn, "select count(*) from iris")
d <- dbReadTable(conn, "iris")

RJDBC supports prepared statements and argument substitution, so it is possible to run queries like:

dbGetQuery(conn, "select count(*) from iris where Species=?", "setosa")

Note that the life time of a connection, result set, driver etc. is determined by the lifetime of the corresponding R object. Once the R handle goes out of scope (or if removed explicitly by rm) and is garbage-collected in R, the corresponding connection or result set is closed and released. This is important for databases that have limited resources (like Oracle) - you may need to add gc() by hand to force garbage collection if there could be many open objects. The only exception are drivers which stay registered in the JDBC even after the corresponding R object is released as there is currently no way to unload a JDBC driver (in RJDBC).

DBI Extensions
  RJDBC extends the DBI with a few additions that seemed resonable to us:
  • dbSendUpdate(conn, statement, ...)
    This function is analogous to dbSendQuery, but works with DBML statements and thus doesn't return a result set. It is more efficient than dbSendQuery. In addition, as of RJDBC 0.2-9 it supports vectors in prepared statements which allows bulk-inserts.
     
  • Pattern matching in dbListTables and dbListFields
    It is possible to use patterns to narrow the search for tables and fields as the following examples show:
    > conn=dbConnect(drv, "jdbc:mysql://localhost/mysql", "user", "pwd")
    > dbListTables(conn)
     [1] "columns_priv"              "db"                       
     [3] "func"                      "help_category"            
     [5] "help_keyword"              "help_relation"            
     [7] "help_topic"                "host"                     
     [9] "proc"                      "procs_priv"               
    [11] "tables_priv"               "time_zone"                
    [13] "time_zone_leap_second"     "time_zone_name"           
    [15] "time_zone_transition"      "time_zone_transition_type"
    [17] "user"                     
    > dbListTables(conn, "help%")
    [1] "help_category" "help_keyword"  "help_relation" "help_topic"   
    > dbListFields(conn, "help_category")
    [1] "help_category_id"   "name"               "parent_category_id"
    [4] "url"               
    > dbListFields(conn, "help_category", "n%")
    [1] "name"
    
Type Handling
  Type-handling is a rather complex issue, especially with JDBC as different databases support different data types. RJDBC attempts to simplify this issue by internally converting all data types to either character or numeric values. When retrieving results, all known numeric types are converted to R's numeric representation and all other types are treated as characters. When assigning parameters in parametrized queries, numeric, integer and character are the types used. Convenience methods like dbReadTable and dbWriteTable can only use the most basic SQL types, because they don't know what DBMS will be used. Therefore dbWriteTable uses only INTEGER, DOUBLE PRECISION or VARCHAR(255) to create the table. For all other types you'll have to use DBML statements directly.
Contact, License
  RJDBC is released under GPL, see the SVN repository for the current sources. The maintainer is Simon Urbanek