Update best practices to load Oracle Database on datatable reactively on Shiny Server

[gabrieldev]

I created this topic to comment on the best practices of loading data from a database into a datatable reactively.
Soon I will post my doubts / difficulties.

[gabrieldev]

Option 1.

# 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")

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)
}


CheckDatabase <- function () {
  cons <- dbListConnections(drv)
  error_exists <- FALSE
  if (length(cons) > 0) {
    for (con in cons) {
      tryCatch({
        dbGetQuery(con, "SELECT * FROM TABLE WHERE ROWNUM < 2")
        print("--------------------------------------------------------------------------------")
        break
      }, error=function(cond) {
        con <- connection()
        return (con)

        # error_exists <- TRUE
      },warning = function(cond){
        # error_exists <- TRUE
        con <- connection()
        return (con)

      })
    }


  } else {
    con <- connection()
  }
  return(con)
}

server.R

rvv$df <- tbl(CheckDatabase(), sql('SELECT * FROM TABLE')) %>% head(input$nrows) %>% collect() %>%
    select(specific_fields_from_results)