Search code examples
rdatabaseerror-handlingrjdbc

R - handle error when accessing a database

I'm trying to automate data download from db using RJDBC using a for loop. The database im using automatically closes the connection after every 10mins, so what i want to do is somehow catch the error, remake the connection, and continue the loop. In order to do this i need to capture the error somehow, the problem is, it is not an r error so none of the commands trycatch and similar works. I just get a text on the console telling me:

Error in .jcheck() : No running JVM detected. Maybe .jinit() would help.

How do i handle this in terms of:

if (output == ERROR) {remake connection and run dbQuery} else {run dbQuery}

thanks for any help

Solution

  • You could use the pool package to abstract away the logic of connection management.
    This does exactly what you expect regarding connection management with DBI.
    It should work with RJDBC which is an implentation of DBI, but I didn't test it with this driver.

    libray(pool)
library(RJDBC)

conn <- dbPool(
  drv = RJDBC::JDBC(...),
  dbname = "mydb",
  host = "hostadress",
  username = "test",
  password = "test"
)
on.exit(poolClose(conn))

dbGetQuery(conn, "select... ")