Access H2 database tables with RJDBC

Question

I am trying to access a h2 database with RJDBC package in R.

I am able to create a connection, but not able to extract or query the tables.

drv <- JDBC("org.h2.Driver",
            "<path to driver>/jdbc/h2/h2.jar")

conn <- dbConnect(drv = drv ,
                  # url = "jdbc:h2:/<path to database>/testdb.mv.db",
                  # url = "jdbc:h2:/<path to database>/testdb.mv.db;DB_CLOSE_DELAY=-1",
                  # url = "jdbc:h2:/<path to database>/testdb.mv.db;DB_CLOSE_ON_EXIT=FALSE",
                  url = "jdbc:h2:/<path to database>/testdb.mv.db;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;DATABASE_TO_UPPER=false",
                  user = "",
                  password =  "")

The list of tables is shown along with their columns or fields, but I am not able to extract any table as a data.frame.

# The following work
dbListTables(conn)
dbListObjects(conn)
dbIsReadOnly(conn)
dbGetTables(conn)

dbDataType(conn, "TABLE1")
dbExistsTable(conn, "TABLE1")
dbGetFields(conn, "TABLE1")
dbGetInfo(conn, "TABLE1")

# The following don't work
data <- dbGetQuery(conn, "SELECT * FROM TABLE1")

dbListFields(conn, "TABLE1")

dbReadTable(conn, "TABLE1")

I am getting the error

Unable to retrieve JDBC result set
JDBC ERROR: Table "TABLE1" not found

I have tried the solutions in h2-in-memory-database-table-not-found without any success.

Access H2 database tables with RJDBC

0 Answers0