JDBC: JDBC engine

In RJDBC: Provides Access to Databases Through the JDBC Interface

JDBC engine

Description

JDBC creates a new DBI driver that can be used to start JDBC connections.

findDrivers attempts to find and load all JDBC 4 drivers on the class path.

Usage

  JDBC (driverClass = "", classPath = "", identifier.quote = NA)
  findDrivers(classPath = "", service = "java.sql.Driver", loader = NULL)

Arguments

driverClass	name of the Java class of the JDBC driver to load or a one of Java driver instances from findDrivers.
classPath	character vector, class path that needs to be appended in order to load the desired JDBC driver. Usually it is the path to the JAR file containing the driver and all necessary dependencies. It can be a vector and all paths are expanded.
identifier.quote	character to use for quoting identifiers in automatically generated SQL statements or NA if the back-end doesn't support quoted identifiers. See details section below.
service	string, name of the services class (for JDBC always "java.sql.Driver")
loader	Java class loader to use during the look-up or NULL for the default one

Details

JDBC function has two purposes. One is to initialize the Java VM and load a Java JDBC driver (not to be confused with the JDBCDriver R object which is actually a DBI driver). The second purpose is to create a proxy R object which can be used to a call dbConnect which actually creates a connection.

JDBC requires a JDBC driver for a database-backend to be loaded. Usually a JDBC driver is supplied in a Java Archive (jar) file. The path to such a file can be specified in classPath. The driver itself has a Java class name that is used to load the driver (for example the MySQL driver uses com.mysql.jdbc.Driver), this has to be specified in driverClass.

Modern drivers (those supporting JDBC 4) may use Java Service Provider interface for discovery and those can be found using the findDrivers() function which returns a list of drivers. You can pass any of the returned elements as classDriver. Note that the discovery is dynamic, so you can use rJava::.jaddClassPath(...) to add new locations in which Java will look for driver JAR files. However, only drivers providing JSP metadata in their JAR files can be found. JSP was introduced in Java 1.6 so findDrivers() only works on Java 1.6 or higher.

There are currently three different ways to specify drivers:

1. dbConnect(JDBC("my.Driver.Class", "driver.jar"), ...) is the most explicit way where the specified driver class is used and expected to be found on the class path. This always works, but the user has to know the full name of the driver class.

2. dbConnect(JDBC(, "driver.jar"), ...) omits the driver class which means JDBC will try to find the driver using the DriverManager. This only works if the JVM has been loaded with the driver when initialized, so this method is discouraged as it is in general very unreliable. The DriverManager never updates the list of drivers, so once your driver is not found, there is nothing you can do about it.

3. dbConnect(JDBC(findDrivers("driver.jar")[[1]]), ...) uses findDrivers() (see details above) to find all available drivers and then passes the needed driver (in this example the first one) to JDBC(). You don't need to repeat teh class path in this case as it is already set by findDrivers(). It is best to look at the output to see which drivers have been found, but if you pass the list, the first driver is used. Note that if you print the driver you will see the class name so you can also use this information in the first method above instead.

If you have issues loading your driver (e.g., you get ClassNotFound errors), make sure you specify all dependencies of your driver, not just the main JAR file. They all must be listed on the class path. Also make sure your JVM is supported by the driver, trying to load drivers with too old JVM versions also leads to ClassNotFound errors (as the loader will ignore calsses it cannot load). You can always enable debugging information in the rJava class loader using .jclassLoader()$setDebug(1L) for more verbose output that may help in your troubleshooting.

Due to the fact that JDBC can talk to a wide variety of databases, the SQL dialect understood by the database is not known in advance. Therefore the RJDBC implementation tries to adhere to the SQL92 standard, but not all databases are compliant. This affects mainly functions such as dbWriteTable that have to automatically generate SQL code. One major ability is the support for quoted identifiers. The SQL92 standard uses double-quotes, but many database engines either don't support it or use other character. The identifier.quote parameter allows you to set the proper quote character for the database used. For example MySQL would require identifier.quote="`". If set to NA, the ability to quote identifiers is disabled, which poses restrictions on the names that can be used for tables and fields. Other functionality is not affected.

As of RDJBC 0.2-2 JDBC-specific stored procedure calls starting with {call and {?= call are supported in the statements.

Value

JDBC returns a JDBCDriver object that can be used in calls to dbConnect.

findDrivers returns a list of Java object references to instances of JDBC drivers that were found. The list can be empty if no drivers were found. Elements can be used as the driverClass in calls to JDBC.

See Also

dbConnect

Examples

## Not run: 
drv <- JDBC("com.mysql.jdbc.Driver",
  "/etc/jdbc/mysql-connector-java-3.1.14-bin.jar", "`")
conn <- dbConnect(drv, "jdbc:mysql://localhost/test")
dbListTables(conn)
data(iris)
dbWriteTable(conn, "iris", iris)
dbGetQuery(conn, "select count(*) from iris")
d <- dbReadTable(conn, "iris")

## End(Not run)

RJDBC documentation built on March 24, 2022, 5:07 p.m.