CODEWITHSUNDEEP
rshiny
kanataki
kanataki

Reputation: 433

How to make selectInput() pass strings to query

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'.

Upvotes: 1

Views: 684

Answers (3)

Pork Chop
Pork Chop

Reputation: 29417

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()
})

Upvotes: 0

Benjamin
Benjamin

Reputation: 17279

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))
  })
})

Upvotes: 2

Florian
Florian

Reputation: 25435

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)

enter image description here

Hope this helps!

Upvotes: 1

Related Questions