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

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

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).

• 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"