How to make Shiny reactivity work with SQL database?

Question

Alright, I modified the script following @Pork Chop advice:

server.R

library(shiny)
library(DT)
library(RMySQL)

con <- dbConnect(MySQL(), user="myuser", host="myhost", dbname="mydb")

shinyServer(function(input, output) { 

                sqlOutput <- reactive({
                    sqlInput <- paste0("select * from mydb.mytable",
                           " where value < ", input$value,
                           ";")
                    dbGetQuery(con, sqlInput)
                })

                output$table <- DT::renderDataTable(sqlOutput(), server=TRUE, rownames=FALSE, filter="top", options=list(pageLength=10))

                output$download <- downloadHandler("filtered.data.txt", content = function(file) {
                                           rows <- input$table_rows_all
                                           write.table(sqlOutput()[rows, ], file, sep="\t", quote=FALSE, col.names=TRUE, row.names=FALSE)
                })

})

The DataTable now works!

However when I try to download the displayed data, I get a file with only column names and no data. According to the DT docs, input$table_rows_all should contain the row indices of the displayed table.

What's wrong?

I'm having troubles with Shiny reactivity and a MySQL database.

In short, I get an input value from the user, create an SQL query, capture the output and display it as a DataTable.

The output can be further filtered using the DataTable column filters and the user should be able to download the filtered dataset.

server.R

library(shiny)
library(DT)
library(RMySQL)

con <- dbConnect(MySQL(), user="myuser", host="myhost", dbname="mydb")

shinyServer(function(input, output) {


                sqlInput <- reactive({
                    paste0("select * from mydb.mytable",
                           " where value < ", input$value,
                           ";")
                })

                sqlOutput <- reactive({
                    dbGetQuery(con, sqlInput)
                })

                output$table <- DT::renderDataTable(sqlOutput, server=TRUE, rownames=FALSE, filter="top", options=list(pageLength=10))

                output$download <- downloadHandler("filtered.data.txt", content = function(file) {
                                           rows <- input$table_rows_all
                                           write.table(sqlOutput[rows, ], file)
                })

})

Instead of the DataTable, I get this error:

This works as expected if I embed sqlInput and sqlOutput within a reactive expression in DT::renderDataTable(), but then I'm not able to refer to sqlOutput from within downloadHandler() (object 'sqlOutput' not found). I thought this was the perfect use case for using reactive() but I can't get it to work.

What's the best way to make this work? Any help is much appreciated, thanks!

`sqlOutput` is a function so use it as so `sqlOutput()`, also for further reference look into [here](http://stackoverflow.com/questions/32048072/how-to-pass-input-variable-to-sql-statement-in-r-shiny/32048759#32048759) and [here](http://stackoverflow.com/questions/28934967/r-shiny-date-range-input-to-sql-query/28947097#28947097) — Pork Chop, Sep 16 '15 at 09:54
Thanks! That makes sense. I edited my question with a follow up. — enricoferrero, Sep 16 '15 at 10:37

score 0 · Accepted Answer · answered Sep 16 '15 at 10:51

0

1. sqlOutput is a function so change it to sqlOutput()

2. Try this, note this will export is as .csv hope its ok

output$download <- downloadHandler(filename = function() {paste(Sys.time(), ' Fltered_data.csv', sep='')}, content = function(file) {write.csv(sqlOutput()[input$table_rows_all, ], file, row.names = FALSE)})

answered Sep 16 '15 at 10:51

Pork Chop

28,528
5
63
77

I accepted this as it answers the original question. However the downloaded file is still an empty file with only the column names. Any idea what could be wrong? – enricoferrero Sep 16 '15 at 11:20

How to make Shiny reactivity work with SQL database?

1 Answers1

Linked