CODEWITHSUNDEEP
sqlrshiny
Tim Ikata
Tim Ikata

Reputation: 221

I want to write an arbitrary SQL query with R shiny multiple

In this code, multiple selected columns of slectinput are fetched from SQL.

ui.R

shinyUI(
  fluidPage(
    selectInput("select","select", 
                choices = c("ID","Name","CountryCode","District","Population"),
                multiple = TRUE),
    textOutput("sql_"),
    tableOutput("table")
  )
)

server.R

shinyServer(function(input, output) {
  
    output$table <- renderTable({
      sql <- paste('SELECT ', paste0("\"", input$select, "\"", collapse = ","),' FROM City;',seq = "")
      output$sql_ <- renderText(sql)
      query <- sqlInterpolate(pool, sql)
      dbGetQuery(pool, query)
    })
})

global.R

library(shiny)
library(DBI)
library(pool)

pool <- dbPool(
  drv = RMySQL::MySQL(),
  dbname = "shinydemo",
  host = "shiny-demo.csa7qlmguqrf.us-east-1.rds.amazonaws.com",
  username = "guest",
  password = "guest"
)

I think the sql syntax is correct. But I can't get it table. For some reason I get the column name. Is there a solution?

Upvotes: 1

Views: 238

Answers (1)

Pork Chop
Pork Chop

Reputation: 29417

This is because you are putting it into quotes, try this:

   q <- eventReactive(input$select,{
        query <- 'SELECT VARIABLES FROM City'
        query <- gsub("VARIABLES",paste0(input$select,collapse = ","),query) 
        query
    })

    output$sql_ <- renderText({
        q()
    })

    output$table <- renderTable({
        query <- sqlInterpolate(pool, q())
        dbGetQuery(pool, query)
    })

Upvotes: 1

Related Questions