Query on button press in Shiny app (R)

Question

I am making an interactive Shiny app that searches a MySQL database on my computer. I can access the database and properly execute queries in R scripts and static Shiny apps, but I run into trouble when trying to execute queries only when a user clicks on an action button.

The problems occur when I wrap my queries in Shiny's event triggered functions, like observeEvent(). For example, the following code runs and retrieves the right set of data:

shinyServer(function(input, output, session){
    con <- dbConnect(RMySQL::MySQL(), dbname = "testdb", user='testuser', password = "testpassword")
    tableList <- dbListTables(con)
    output$out <- renderPrint(tableList) #this always prints

    df <- data.frame()
    #observeEvent(input$button, {
        quer <- paste("select id, dept, course_code from main_course_info
                       where dept in (select id from dept_relations 
                                      where deptName = 'CMPE')")
        df <- data.frame(dbGetQuery(con, quer))
        output$table <- renderTable({df})
     #})
    dbDisconnect(con)
 })#shinyServer

With the observeEvent function commented out, the query runs perfectly and the retrieved table is displayed. But when I try to hold the query off until input$button changes, i.e the button gets pressed, i get the following error upon pressing the button:

 Warning in run(timeoutMs) :
   Unhandled error in observer: internal error in RS_DBI_getConnection: corrupt connection handle
 observeEvent(input$button)

I need this button functionality to work, because my goal is to have the specifics of the query decided by user inputs, which would be brought together and executed once the user has made their selections and hit the action button. However I cannot even get a predefined query to run with observeEvent, which is integral to the design of my app.

Note that the first output, a renderPrint of the list of tables in the database, always prints regardless of the success or failure of the button. This means that the connection is working at least until it gets sent to observeEvent's handler argument.

Not sure what's going on, but I would put a `browser()` call inside the `observeEvent` so that you can see what the connection looks like. Also , see what happens if you initialize the connection again inside the `observeEvent`? None of these are solutions, just trying to help debug. — DeanAttali, Jun 16 '15 at 00:39

score 0 · Answer 1 · edited May 23 '17 at 10:27

0

You not put the query that fetches the data into reactive expression? Also have a look here: Get value from reactive context in R shiny based on user input

EDIT:

Define the connection Globally, put con <- "" before shinyServer(function(input, output, session){} so it will only open one connection when you start the app and then reuse this connection every time you query.

con <- dbConnect(RMySQL::MySQL(), dbname = "testdb", user='testuser', password = "testpassword")

my_data <- reactive({ 
  if(input$button == 0)
  {
    return(0)
  }
  isolate({
    input$button
    quer <- paste("select id, dept, course_code from main_course_info where dept in (select id from dept_relations where deptName = 'CMPE')")
    df <- data.frame(dbGetQuery(con, quer))
    df
  })
})
output$table <- renderTable({my_data()})

edited May 23 '17 at 10:27

Community

1
1

answered Jun 16 '15 at 11:49

Pork Chop

28,528
5
63
77

This worked temporarily if i also re-established the connection inside the reactive statement (but outside of isolate), however it gives me a 'too many connections' error after a few uses. I tried disconnecting the connection and reconnecting within the reactive statement, but that gives me the corrupt connection handle error. – OmarSands Jun 16 '15 at 19:21
1

You should put con <- outside shinyServer(function(input, output, session){} so it opens only one connection! – Pork Chop Jun 17 '15 at 06:57
Somehow that doesn't work, the only way it functions is if I begin and end the connection within the `my_data <- reactive({})` function. It also just won't work inside an `observeEvent` function either. I got it to do what I want, but it's definitely inefficient since I will have several other reactive values that create and end connections now as well. – OmarSands Jun 17 '15 at 20:00

Query on button press in Shiny app (R)

1 Answers1