Search code examples
rshinyshinydashboardrjdbc

Create R Shiny app with dynamic RJDBC connection

I would like to find a similar function to reactiveFileReaderfunction from R Shiny pacakge to dynamically read data from an Oracle database using RJDBC driver.

Please find an example below to explain my issue:

Suppose my_data.csv is a extraction of my MY_ORACLE_TABLE (which is hosted on my oracle database).

In the first case below, when I manualy change the values of my_data.csv, my Shiny app automaticaly updates:

server <- function(input, output) {
  output$table1 <- DT::renderDataTable({

    reactiveFileReader(1000,session = NULL,filePath = "my_data.csv",readFunc = read.csv2)()     

    })

}

But in the second case below, supposing MY_ORACLE_TABLE updates, I have to reload my Shiny app URL to make the display update:

server <- function(input, output) {
  output$table1 <- DT::renderDataTable({

    reactive({dbGetQuery(con, "SELECT * FROM MY_ORACLE_TABLE})()      

    })

}

Any tips ?

Solution

  • The problem is that

    dbGetQuery(con, "SELECT * FROM MY_ORACLE_TABLE")

    does not depend on any reactive context and will not automatically be re-run when something in your database changes.

    One solution is to wrap your query in shiny::reactivePoll(), which is actually used to implement shiny::reactiveFileReader()

    For more information, see:

    https://shiny.rstudio.com/reference/shiny/1.0.3/reactivePoll.html