title: How to make Shiny reactivity work with SQL database?
body: <p>Alright, I modified the script following @Pork Chop advice:</p>
  
  <p><strong>server.R</strong></p>
  
  <pre><code>library(shiny)
  library(DT)
  library(RMySQL)
  
  con &lt;- dbConnect(MySQL(), user="myuser", host="myhost", dbname="mydb")
  
  shinyServer(function(input, output) { 
  
                  sqlOutput &lt;- reactive({
                      sqlInput &lt;- paste0("select * from mydb.mytable",
                             " where value &lt; ", input$value,
                             ";")
                      dbGetQuery(con, sqlInput)
                  })
  
                  output$table &lt;- DT::renderDataTable(sqlOutput(), server=TRUE, rownames=FALSE, filter="top", options=list(pageLength=10))
  
                  output$download &lt;- downloadHandler("filtered.data.txt", content = function(file) {
                                             rows &lt;- input$table_rows_all
                                             write.table(sqlOutput()[rows, ], file, sep="\t", quote=FALSE, col.names=TRUE, row.names=FALSE)
                  })
  
  })
  </code></pre>
  
  <p>The DataTable now works!</p>
  
  <p>However when I try to download the displayed data, I get a file with only column names and no data. According to the <a href="http://rstudio.github.io/DT/shiny.html" rel="nofollow noreferrer">DT docs</a>, input$table_rows_all should contain the row indices of the displayed table.</p>
  
  <p>What's wrong?</p>
  
  <hr>
  
  <p>I'm having troubles with Shiny reactivity and a MySQL database.</p>
  
  <p>In short, I get an input value from the user, create an SQL query, capture the output and display it as a DataTable.</p>
  
  <p>The output can be further filtered using the DataTable column filters and the user should be able to download the filtered dataset.</p>
  
  <p><strong>server.R</strong></p>
  
  <pre><code>library(shiny)
  library(DT)
  library(RMySQL)
  
  con &lt;- dbConnect(MySQL(), user="myuser", host="myhost", dbname="mydb")
  
  shinyServer(function(input, output) {
  
  
                  sqlInput &lt;- reactive({
                      paste0("select * from mydb.mytable",
                             " where value &lt; ", input$value,
                             ";")
                  })
  
                  sqlOutput &lt;- reactive({
                      dbGetQuery(con, sqlInput)
                  })
  
                  output$table &lt;- DT::renderDataTable(sqlOutput, server=TRUE, rownames=FALSE, filter="top", options=list(pageLength=10))
  
                  output$download &lt;- downloadHandler("filtered.data.txt", content = function(file) {
                                             rows &lt;- input$table_rows_all
                                             write.table(sqlOutput[rows, ], file)
                  })
  
  })
  </code></pre>
  
  <p>Instead of the DataTable, I get this error:</p>
  
  <p><a href="https://i.stack.imgur.com/EFnZH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/EFnZH.png" alt="enter image description here"></a></p>
  
  <p>This works as expected if I embed <code>sqlInput</code> and <code>sqlOutput</code> within a reactive expression in <code>DT::renderDataTable()</code>, but then I'm not able to refer to <code>sqlOutput</code> from within <code>downloadHandler()</code> (<code>object 'sqlOutput' not found</code>). I thought this was the perfect use case for using <code>reactive()</code> but I can't get it to work.</p>
  
  <p>What's the best way to make this work?
  Any help is much appreciated, thanks!</p>