# database.R
# Database options
options(user_db = Sys.getenv("user_db"))
options(pass_db = Sys.getenv("pass_db"))
options(host_db = Sys.getenv("host_db"))
options(port_db = Sys.getenv("port_db"))
options(name_db = Sys.getenv("name_db"))
drv <- dbDriver(drvName = "Oracle")
# Function that connect on db
connection <- function() {
connect.string <- paste("(DESCRIPTION=",
"(ADDRESS=(PROTOCOL=tcp)(HOST=", options()$host_db, ")(PORT=", options()$port_db, "))",
"(CONNECT_DATA=(SERVICE_NAME=", options()$name_db, ")))", sep = "")
pool <- dbPool(drv, dbname = connect.string, user = options()$user_db, password = options()$pass_db,
minSize = 10,
maxSize = Inf, # this could have been omitted since it's the default
idleTimeout = 3600000 ) # one hour)
return(pool)
}
shiny::onStop(function(){
print("call pool stop")
poolClose(pool)
})
CheckDatabase <- function () {
cons <- dbListConnections(drv)
if (length(cons) > 0) {
for (con in cons) {
tryCatch({
dbGetQuery(con, "SELECT idt FROM schema.table WHERE ROWNUM <= 4")
break
}, error=function(cond) {
con <- connection()
return (con)
},warning = function(cond){
con <- connection()
return (con)
})
}
} else {
con <- connection()
}
return(con)
}
# server.R
source('database.R')
con <- CheckDatabase()
df <- tbl(CheckDatabase(), sql('SELECT * FROM schema.table))
If I don't take a while to perform the query, or use the checkDatabase() function, it works until I'm inactive for a while, then I get the error.
After a certain time, about 10 minutes or less, when I go to make a query in the DB, I receive:
ORA-03114: not connected to ORACLE
How can I validate the connection?
Because I already tried like this:
dbIsValid(con), but I get that it's an oracle database connection and it doesn't work, like this:
unable to find an inherited method for function ‘dbIsValid’ for signature ‘"OraConnection"’