How to make selectInput() pass strings to query

Question

I am using selectInput() to fetch a list of months from a database and using them to show a drop down menu on shiny.

month <- dbGetQuery(con, statement = 
      paste( "select distinct month
                from table
                order by month
                            "))
selectInput("month", 
    "month", 
    sort(unique(month$month)),
    selected = NULL)

I am then using the above input in another query:

server <- shinyServer(function(input, output) {

output$graph <- renderPlot({

    ctab <- dbGetQuery(con, statement = 
          paste("select colum1, column2
                from table
                where month_of_year_name = ",input$month,"
                "))
    output$graph2 <- renderPlot({

    })
})

The problem is that the selectInput gives the month as Jan and I'd like it to give it as 'Jan'.

Benjamin · Accepted Answer · 2018-02-13 11:38:54Z

2

I would recommend using sqlInterpolate to protect against SQL Injection attacks

server <- shinyServer(function(input, output) {

  output$graph <- renderPlot({

    ctab <- dbGetQuery(con, 
                       statement = 
                         sqlInterpolate(con, 
                                        "select colum1, column2
                                        from table
                                        where month_of_year_name = ?month",
                                        month = input$month))
  })
})

answered Feb 13, 2018 at 11:38

Benjamin

17.3k8 gold badges53 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

kanataki Over a year ago

would sqlinterpolate still work inside a reactive function?

Benjamin Over a year ago

Yes. It will. It just needs a string to form the statement and the values to bind

Florian · Accepted Answer · 2018-02-13 10:13:31Z

1

You can simply put ' in your strings to be pasted, when you use " to encapsulate strings. Example:

ui <- fluidPage(
  selectInput('month','months: ', c('Jan','Feb','Mar')),
  h3('old_query'),
  textOutput('old_query'),  
  h3('new_query'),
  textOutput('new_query')
)

server <- shinyServer(function(input, output) {

  output$old_query <- renderText({
    paste("select colum1, column2
                               from table
                               where month_of_year_name = ",input$month,"")

  })

  output$new_query <- renderText({
    paste("select colum1, column2
          from table
          where month_of_year_name = '",input$month,"'")

  })
})

shinyApp(ui,server)

Hope this helps!

answered Feb 13, 2018 at 10:13

Florian

25.6k5 gold badges56 silver badges87 bronze badges

1 Comment

kanataki Over a year ago

I had also tried using the concat() fubnction to wrap the months in quotes while fetching them but your way is much neater

Pork Chop · Accepted Answer · 2018-02-13 16:01:43Z

0

Personally I like using regex to create my queries, I think it gives very good flexibility and its quite readable

data <- reactive({
  query <- 'select colum1, column2 from table where month_of_year_name = "MONTH"'
  query <- gsub("MONTH",input$month,query)
  query <- noquote(query)
  dbGetQuery(con,query)
})

output$graph <- renderPlot({
  data()
})

answered Feb 13, 2018 at 16:01

Pork Chop

29.5k5 gold badges69 silver badges82 bronze badges

Collectives™ on Stack Overflow

How to make selectInput() pass strings to query

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related