Connecting R to embedded H2 database on RStudio

Question

I'm having a hard time figuring out how to connect my R code to an embedded H2 database. It seems like RH2 is the tool to go with (library is installed) but there is no working example on the documentation nor on stackexchange or on Google - at least I didn't find one (besides this). Since I'm doing my babysteps with R, a working example to connect and retrieve data would be great!

The code must be something like

library(RH2)
options(RH2.jars = "C:\\h2\\bin\\h2-1.4.187.jar")
myH2 <- H2(driverClass="org.h2.Driver",
   identifier.quote="\"", jars = getOption("RH2.jars"))
con <- dbConnect(myH2,
   url = "jdbc:h2:C:\\data\\sample.h2.db", 
   user = "admin", password = "ultrasafe123")
dataFrame = fetch(dbGetQuery(con, "select * from TABLENAME"))

Currently I'm getting:

Error in .jfindClass(as.character(driverClass)[1]) : 
    class not found` from `driverClass="org.h2.Driver"`

but that's exactly how it is shown in the documentation. Did I miss something else? A library? Including library(RJDBC)didn't help either.

I'm accessing the very same database with other tools (inclunding NetBeans 8.0.2. and DbVisualizer 9.2.8) successfully. I thought all it takes is the H2 driver file (`C:\h2\bin\h2-1.4.187.jar`)? Am I wrong? — Boern, Jul 07 '15 at 18:49
So you have installed it, but you still have not given any details about its version or location on your computer, so I don't think you have provided enough information to judge the appropriateness of your attempts to construct a driver and connection . — IRTFM, Jul 07 '15 at 19:28
The line `options(RH2.jars = "C:\\h2\\bin\\h2-1.4.187.jar")` tells the location and version (`1.4.187`) of the driver? What's missing? RStudio is `0.99.451` — Boern, Jul 08 '15 at 08:23

score 3 · Accepted Answer · answered Jul 27 '15 at 08:19

I finally solved it for myself:

library(RH2)
myH2 <- H2('org.h2.Driver', 'C:/h2/bin/h2-1.4.187.jar')
## location of h2 file: C:\data\data.h2.db 
con <- dbConnect(myH2, "jdbc:h2:C:/data", "user", "password")
s <- "select * from TABLE WHERE COLUMN = 'value'"
result = dbGetQuery(con, s)
dbDisconnect(con)

Connecting R to embedded H2 database on RStudio

1 Answers1