R Shiny SQL reactive query (selectInput not displaying)

Question

I would like to build a shiny app which will allow user to choose the table name from database, and their further plotting etc. I stuck at the point of retrieving the table names from database. I cannot use the tableList which i have created using dbListTables(con,schema="K") as a choice for the selectInput widget. I do not get any error or warning, widget just does not appear at all.

My code:

library(ROracle)
library(shiny)


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

    con <- dbConnect(dbDriver("Oracle"),"xxx/K",username="user",password="pwd")
    tableList <- dbListTables(con,schema="K")
    output$out <- renderPrint(tableList) 

    df <- data.frame()
    quer <- paste("select * from K.", input$tabnames)
    df <- data.frame(dbGetQuery(con, quer))
    output$table <- renderTable({df})
    session$onSessionEnded(function() { dbDisconnect(con) })
  })

ui_panel <- 
  tabPanel("Test",
           sidebarLayout(
             sidebarPanel( 
             ),
             mainPanel(
               selectInput("tabnames","tabnames", choices=as.list(tableList)),
               tableOutput("out"),
               tableOutput("table")
             )
           )
  )


ui <- shinyUI(navbarPage("Test",ui_panel))

runApp(list(ui=ui,server=server))

Thanks for any tipps

[SOLVED] the part for the selectizeInput i solved by placing it on the server side:

library(ROracle)
library(shiny)
library(DT)


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

    con <- dbConnect(dbDriver("Oracle"),"xx/K",username="user",password="pwd")
    tableList <- dbListTables(con,schema="K")

    updateSelectizeInput(session, "tabnames", server = TRUE, choices = tableList)

    sqlOutput <- reactive({
      sqlInput <- paste("select * from K.",input$tabnames)
      dbGetQuery(con, sqlInput)
    })

    output$table <- DT::renderDataTable(sqlOutput(), server=TRUE, rownames=FALSE, filter="top", options=list(pageLength=10))

    session$onSessionEnded(function() { dbDisconnect(con) })
  })

ui_panel <- 
  tabPanel("Test",
           sidebarLayout(
             sidebarPanel( 
             ),
             mainPanel(
               selectizeInput("tabnames",label = "server side", choices = NULL),
               tableOutput("out"),
               tableOutput("table")
             )
           )
  )


ui <- shinyUI(navbarPage("Test",ui_panel))

runApp(list(ui=ui,server=server))

I additionally made the reactive SQL query. Than i choosed the table from selectizeInput to display, [NOT SOLVED] however it shows me an error:

Error in .oci.GetQuery(conn, statement, data = data, prefetch = prefetch,  : 
  ORA-00903: invalid table name

Than smthg has to be wrong with my SQL Query (Thanks for the tipps here!) How its even possible if i choosed the table name from the dbListTables? Any ideas?

What do you get when run dbListTables(con,schema="K") outside shiny? — danas.zuokas
You need reactive df <- data.frame(dbGetQuery(con, quer)) df=reactive({dbGetQuery(con,paste0("select * from K.", input$tabnames) )}) and use df() and delete: df <- data.frame() and quer <- paste("select * from K.", input$tabnames) — Batanichek
also you need to render output$sinput=renderUI({selectInput("tabnames","tabnames", choices=as.list(tableList))}) on server side and use as uioutput("sinput") on UI side — Batanichek
Thanks for the tipps!I solved the selectizeInput choices, however the sql query does not seem to work (I updated my question). ` sqlInput <- paste("select * from K.",input$tabnames)`, whereas K is a scheme. Thanks for additional solutions to the problem! — Mal_a
debug your code..error told you what you need "invalid table name", print your sqlInput before get it and look what bad — Batanichek

Mal_a Mal_a · Accepted Answer · 2016-02-16T07:47:46

I have solved my second question! The problem was very small on the side of ui, instead of dataTableOutput, i had tableOutput, so the ui should look like this:

ui_panel <- 
  tabPanel("Test",
           sidebarLayout(
             sidebarPanel( 
             ),
             mainPanel(
               selectizeInput("tabnames",label = "server side", choices = NULL),
               tableOutput("out"),
               dataTableOutput("table")
             )
           )
  )

Thanks for all the help!

R Shiny SQL reactive query (selectInput not displaying)

1 Answers