Shiny dashboard data refresh trigger

Question

I am importing some data from a MySQL table, but so far for checking the data updates I was just reloading the app. I would like for this to happen dynamically but I am not sure how to implement the invalidateLater function on my code or if I should even go for it, because I am seeing people recommending ReactivePoll. Basically I am receiving data into my database from 5 to 5 minutes and I want to check the database in a certain interval too and update the number showed in my Value Box.

Thanks!

     shinyServer(function(input, output, session) {

      conn <- dbConnect(RMySQL::MySQL(), 
                        dbname="db", 
                        host="localhost", 
                        user="root", 
                        password="password")

      table<- dbReadTable(conn = conn, name ='table1', value= as.data.frame(table))

      prd_number <- dbFetch(dbSendQuery(conn, "SELECT obj FROM table1 ORDER BY id DESC LIMIT 1"))


      output$prdnumber <- shinydashboard::renderValueBox({
        shinydashboard::valueBox(prd_number, "Number of products", icon=icon("tags"), color="purple"
        )
      })
   })

UPDATE

I accomplished what I wanted but now I have:

a curiosity: is it necessary to define a new reactivePoll for each different query? (eg: a reactivePoll function for getting a certain field from the db and another reactivePoll function for getting the whole table from the db)
an error: more exactly unrecognized MySQL field type 7 in column 0 imported as character . I know this is due to the fact that I have timestamps in my table, but I not sure how to deal with them. I saw one solution might be to convert it to a UNIX timestamp but i want to print the date to the dashboard so it's not a good choice.

server.R

  prd_number <- reactivePoll(20000, session, checkFunc = function(){  

  conn <- dbConnect(RMySQL::MySQL(), 
                    dbname="db", 
                    host="localhost", 
                    user="root", 
                    password="password")

  val1 <- dbGetQuery(conn, "SELECT obj FROM table ORDER BY id DESC LIMIT 1")

  val2 <- dbGetQuery(conn, "SELECT date FROM table ORDER BY id DESC LIMIT 1")

  output$prdnumber <- shinydashboard::renderValueBox({
    shinydashboard::valueBox(val1, "Number of items", icon=icon("tags"), color="purple"
    )
  })

  output$update <- shinydashboard::renderValueBox({
    shinydashboard::valueBox(val2, "Last update", icon=icon("tags"), color="lime"
    )
      })
 dbDisconnect(conn)
  })

this could help: https://stackoverflow.com/questions/41424045/in-shiny-app-how-to-refresh-data-from-mysql-db-every-10-minutes-if-any-change-o — MLavoie, Aug 07 '18 at 21:59
@MLavoie thank you very much for the reply. I am getting close to what I want but I still have some erros unfortunately. — cartita, Aug 08 '18 at 15:26
@MLavoie sorry for the late response. I updated the post. Thanks! — cartita, Aug 09 '18 at 21:30

JohnCoene · Answer 1 · 2018-08-09T22:38:17.303

This is not how you'd use reactivePoll I believe, reactivePoll is, well a reactive.

reactivePoll runs checkFunc at every intervalMillis, if checkFunc returns a different value than previously then valueFunc is ran.

Also never update your output inside your reactives, move them outside of the reactive.

server <- function(input, output, session){
  prd_number <- reactivePoll(
    20000, # checkFunc is ran very 2 min
    session,
    checkFunc = function(){
      Sys.time() ## CHANGE THIS (see below)
    },
    valueFunc = function(){
      conn <- dbConnect(
        RMySQL::MySQL(), 
        dbname="db", 
        host="localhost", 
        user="root", 
        password="password"
      )

      val1 <- dbGetQuery(conn, "SELECT obj FROM table ORDER BY id DESC LIMIT 1")

      val2 <- dbGetQuery(conn, "SELECT date FROM table ORDER BY id DESC LIMIT 1")

      dbDisconnect(conn)
      return(list(val1 = val1, val2 = val2)) # return both values
    }
  )

  output$prdnumber <- shinydashboard::renderValueBox({
    shinydashboard::valueBox(prd_number()$val1, "Number of items", icon=icon("tags"), color="purple")
  })

  output$update <- shinydashboard::renderValueBox({
    shinydashboard::valueBox(prd_number()$val2, "Last update", icon=icon("tags"), color="lime")
  }) 
}

In the above, change the checkFunc. It currently returns the current time so every 2 min valueFunc is ran. You do not want that.

In valueFunc, you should ping the database with a cheap SQL statement to check if the table(s) of interest have been updated: i.e.: return last row => if the last row has changed, valueFunc is ran and your outputs (that use prd_number) will be updated.

Shiny dashboard data refresh trigger

1 Answers1