When i use a SQL-connection in a reactive Datatable in Shiny. Should i disconnect the connection anywhere? And if yes, at what point? Or should i use pool?
I asked this on twitter Link to Tweet and some fellow #rstats-people pointed me to this board, so we can talk about the best way to approach this.
Here´s some code i worte as example
library("shiny")
library("shinydashboard")
library("tidyverse")
library("RMariaDB")
library("DBI")
library("DT")
ui <- dashboardPage(
dashboardHeader(title = "Basic dashboard", titleWidth = 450),
dashboardSidebar(disable = TRUE),
dashboardBody(
fluidRow(
box(
DT::DTOutput('mytable') #dataTableOutput
)
)
)
)
server <- function(input, output, session){
# Run every 30 seconds
QueriedData <- reactivePoll(30000,session,
#A function whose values over time will be tested for equality; inequality indicates that the underlying value has changed and needs to be invalidated and re-read using valueFunc
checkFunc = function(){
# connect
con <- DBI::dbConnect(RMariaDB::MariaDB(),
#RMySQL::MySQL(),
host = '192.168.0.0',
user = 'xkcd',
password = 'correcthorsebatterystaple',
dbname = 'mydb')
# This returns the current rowcount of the mysqltable
rowcount <- dbGetQuery(con, "SHOW TABLE STATUS;") %>% filter(Name == "mysqltable") %>% pull(Rows)
},
valueFunc = function() {
test_db <- dbReadTable(con, "mysqltable")
})
output$mytable <- DT::renderDT({
test_db <- QueriedData() %>% as.data.frame()
DT::datatable(test_db)
})
}
shinyApp(ui, server)