How to pass input variable to SQL statement in R shiny?

Question

Usually, variables can be passed to SQL statements using paste. Interestingly this doesn't work with input variables in R shiny. Using the below code I get the following error message. How can I solve this?

Error in .getReactiveEnvironment()$currentContext() : Operation not allowed without an active reactive context. (You tried to do something that can only be done from inside a reactive expression or observer.)

--ui.R--

shinyUI(bootstrapPage(

  selectInput(inputId = "segment",
          label = "segment",
          choices = c(1, 2, 3, 4),
          selected = 1),

  plotOutput(outputId = "main_plot", height = "300px")

))


--server.R--

shinyServer(function(input, output) {

     database <- dbConnect(MySQL(), group= "zugangsdaten", dbname= 'database')

     input<- input$segment

     table <- dbGetQuery(database, statement = 
                      paste("
                      SELECT a,b FROM table1
                      WHERE id = ",input,"
                      AND created_at>='2015-08-01'
                      "))

  output$main_plot <- renderPlot({

    plot(a,b)

    })
})

have a look at my answer here stackoverflow.com/questions/28934967/… — Pork Chop
– Pork Chop, Commented Aug 17, 2015 at 10:57

Jon Calder · Accepted Answer · 2015-08-17 11:00:28Z

The data query needs to be evaluated in a reactive context.

One way would be to move the data query itself into the renderPlot() context e.g.

--server.R--

shinyServer(function(input, output) {

 database <- dbConnect(MySQL(), group= "zugangsdaten", dbname= 'database')

 output$main_plot <- renderPlot({

   table <- dbGetQuery(database, statement = 
              paste("
                SELECT a,b FROM table1
                WHERE id = ",input$segment,"
                AND created_at>='2015-08-01'
              "))

    plot(table$a,table$b)

 })

})

However, it's better to construct a reactive conductor for the data which can be evaluated once when any updates happen and re-used in multiple reactive end-points (see here for details).

This would look something like:

--server.R--

shinyServer(function(input, output) {

 database <- dbConnect(MySQL(), group= "zugangsdaten", dbname= 'database')

 table <- reactive({
            dbGetQuery(database, statement = 
              paste("
                SELECT a,b FROM table1
                WHERE id = ",input$segment,"
                AND created_at>='2015-08-01'
              ")
            )
          })

 output$main_plot <- renderPlot({

    plot(table()$a,table()$b)

 })

})

Pork Chop · Accepted Answer · 2015-08-17 13:58:09Z

1

For flexibility you can also use sub function to substitute part of the query string, this is quite clean approach

table <- reactive({ 
  my_query <- 'SELECT a,b FROM table1 WHERE id = SOMETHING AND created_at >= 2015-08-01'
  my_query <- sub("SOMETHING",input$segment,my_query)
  dbGetQuery(database,noquote(my_query))
})

edited Aug 17, 2015 at 13:58

answered Aug 17, 2015 at 11:04

Pork Chop

29.5k5 gold badges69 silver badges82 bronze badges

Collectives™ on Stack Overflow

How to pass input variable to SQL statement in R shiny?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related