shiny and SQL server are linked.
We succeeded in specifying search conditions by input from shiny.
Isn't it possible to select the column names to be obtained in this way in the output from shiny?
shinyUI(
fluidPage(
selectInput("select","select", choices = c("CountryCode","District","NAME")),
textInput("ID","ID"),
actionButton("go", "go"),
tableOutput("table"),
tableOutput("tablee")
)
)
shinyServer(function(input, output) {
observeEvent(input$go,{
output$table <- renderTable({
sql <- 'SELECT ?select FROM City;'
query <- sqlInterpolate(pool, sql, select = input$select)
dbGetQuery(pool, query)
})
})
output$tablee <- renderTable({
sql <- "SELECT * FROM City WHERE ID = ?ID;"
query <- sqlInterpolate(pool, sql, ID = input$ID)
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"
)
This answer covers your first query attempt:
observeEvent(input$go,{
output$table <- renderTable({
sql <- 'SELECT ?select FROM City;'
query <- sqlInterpolate(pool, sql, select = input$select)
dbGetQuery(pool, query)
})
})
No, you can't do this, because prepared statements in SQL only can use placeholders for data, not for database objects (e.g. table and column names). You will have to use paste to build the query:
observeEvent(input$go,{
output$table <- renderTable({
sql <- paste("SELECT", input$select, "FROM City;")
dbGetQuery(pool, sql)
})
})
But note that this approach may be prone to SQL injection, assuming that input$select would be coming from the outside. A typical workaround to this problem would be to instead have some number of prepared statements ready, and then to select the appropriate one based on the input from the outside, for example:
observeEvent(input$go,{
output$table <- renderTable({
sql1 <- "SELECT name FROM City;"
sql2 <- "SELECT state FROM City;"
query <- ifelse(input$select == "name", sql1, sql2)
dbGetQuery(pool, query)
})
})