RJDBC - Allows the use of JDBC to access databases from R
About
RJDBC is an r package providing a database interfaces using 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. RJDBC uses the r-dbi interface which is also used by the packages rsqlite, rpostgresql, rmysql, and roracle.
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 allow 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).
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.
License
RJDBC is released under gnu gpl v2.
