[SOLVED] Create R Shiny app with dynamic RJDBC connection

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