How to create a driver for a DBI connection?

[gregorypenn]

I am attempting to use RJDBC for the first time, so this issue may boil down to user error and me not having sufficiently understood the documentation I've read, i.e. man pages for RJDBC functions.

I'm trying to connect to an Access database, and my understanding is that the first step is to create a driver that can be used with dbConnect(). I have installed Java, RJDBC, and the JDBC Access drivers (I'm not sure which one of them is needed and would prefer that one be selected automatically, if possible). I think that I have an appropriate Access JDBC driver, because the application DbSchema is able to connect to and query my database. Here's some system and environment info, in case it's relevant. I'm using an M2 Mac running MacOS 14.2, R version 4.3.2, RJDBC 0.2.10.

  % java -version
  openjdk version "21.0.1" 2023-10-17
  OpenJDK Runtime Environment Homebrew (build 21.0.1)
  OpenJDK 64-Bit Server VM Homebrew (build 21.0.1, mixed mode, sharing)

  % ls /Library/Java/Extensions/AccessJdbcDriver
  commons-lang3-3.8.1.jar   hsqldb-2.5.0.jar    ucanaccess-5.0.1.jar
  commons-logging-1.2.jar   jackcess-3.0.1.jar

I'm not sure which of the drivers in AccessJdbcDriver I should use, but when I connect to the database with DbSchema, it appears that it is using ucanaccess-5.0.1.jar, which I think has the class name net.ucanaccess.jdbc.UcanaccessDriver, but I'm not sure that's actually the case. If there's a way to get the class name from the driver's .jar file, then I'd like to know about it.

What I've tried...

The man page gives three ways to specify a driver, and says the second is unreliable, so I'm just trying 1 and 3...

1. Specify full name of driver class and path. (Again, I'm not positive about the name.)

drv <- RJDBC::JDBC("net.ucanaccess.jdbc.UcanaccessDriver", "/Library/Java/Extensions/AccessJdbcDriver/ucanaccess-5.0.1.jar")
#> Error in RJDBC::JDBC("net.ucanaccess.jdbc.UcanaccessDriver", "/Library/Java/Extensions/AccessJdbcDriver/ucanaccess-5.0.1.jar"): java.lang.NoClassDefFoundError: com/healthmarketscience/jackcess/util/ErrorHandler

I'm not sure what to do about the "no class definition error" here.

3. Use findDrivers()

drv <- RJDBC::JDBC(RJDBC::findDrivers(classPath = "/Library/Java/Extensions/AccessJdbcDriver/ucanaccess-5.0.1.jar"))
#> Error in .jcall("java.util.ServiceLoader", "Ljava/util/ServiceLoader;", : java.util.ServiceConfigurationError: java.sql.Driver: no caller to check if it declares `uses`

Again, I'm not understanding anything useful from the error message. Any guidance will be appreciated.

[s-u]

This is a question really for your driver - it seems that you are using this driver: https://ucanaccess.sourceforge.net/site.html which appears somewhat more complicated that normal drivers and links to instructions here - option 2 which suggest that you need all the jar files, not just the one you specified. Given the listing of your files, I'd assume that something like this may work:

jars <- Sys.glob("/Library/Java/Extensions/AccessJdbcDriver/*.jar")
drv <- RJDBC::JDBC("net.ucanaccess.jdbc.UcanaccessDriver", jars)

[gregorypenn]

@s-u Thanks! Your suggestion worked, with JDBC returning an S4 JDBCDriver object. After some further trial and error, I got my connection established with conn <- dbConnect(drv, paste0("jdbc:ucanaccess://", <path-to-dbfile>)). I'll close this issue as completed. May I suggest adding this oddity to your documentation, even though it's due to a quirk in the driver? It took me quite a while to figure it out, and it was the availability of JDBC drivers for Access that brought me to RJDBC in the first place, so this might benefit others as well.

[gregorypenn]

@s-u correctly identified this issue as hinging on an oddity with the drivers. RJDBC works as intended when the drivers are bundled into a single object with Sys.glob. Closing as completed.