R shiny selectinput with database values

1
votes

I am trying to fetch the values in the dropdown from database:-

Server.R


sqlOutput<- reactive({

  sqlInput<- paste("select distinct ASSET_CLASS from DUMMY_TABLE")

  dbGetQuery(con, sqlInput)
})  

UI.R

selectInput('pick_assetclass',label ='Asset Class',choices=sqlOutput(),selected = NULL, multiple = FALSE,width="450px"),

Could you please let me know the error in the code?

Error message:-

Error in lapply(obj, function(val) { : 
  could not find function "sqlOutput"

Thanks, This is the edited code as per your valuable answers. I see the column names in the dropdown alongwith the filtered values. Could you let me know how to resolve it?

Server.R

sqlOutputAssetClass <- reactive({

      sqlInputAssetClass<- paste("select distinct ASSET_CLASS from DUMMY_TABLE",sep="")

    dbGetQuery(con, sqlInputAssetClass)
   })

    sqlOutputFeedSrcSys <- eventReactive(input$pick_assetclass,({

      sqlInputFeedSrcSys<- paste("select distinct FEED_SRC_SYS from DUMMY_TABLE where ASSET_CLASS=","'",input$pick_assetclass,"'",sep="")

      dbGetQuery(con,sqlInputFeedSrcSys)
    }) )        

   observe ({
    updateSelectInput(session,"pick_assetclass","ASSET CLASS",
                       choices = sqlOutputAssetClass()
     )
   })

   observe ({
    updateSelectInput(session,"pick_feedsrcsys","FEED SOURCE SYSTEM",
                       choices = sqlOutputFeedSrcSys()
     )
   })

UI.R

selectInput('pick_assetclass',label ='Asset Class',choices=NULL,selected = NULL, multiple = FALSE,width="450px"),
   selectInput('pick_feedsrcsys',label ='Feed Src Sys',choices=NULL,selected = NULL, multiple = FALSE,width="450px"),
2
rshiny

2 Answers

1
votes

Hi to do that you will need a observe or renderUI, I prefer observe so let's see:

sqlOutput<- reactive({

  sqlInput<- paste("select distinct ASSET_CLASS from DUMMY_TABLE")

  dbGetQuery(con, sqlInput)
})  

observe ({

updateSelectInput(session, "pick_assetclass",
                               choices = sqlOutput()
      )

})

Hope it helps. (You will need to add session to the function server like this)

shinyServer(

  function(input, output, session) {
2
votes

The problem is that in a shiny app, the ui function runs before the server function. This makes sense from a design standpoint because the inputs need to exist for R to make sense of reactive expressions in the server code, but it causes problems when the UI depends on variables generated in server.

The simplest solution is to replace your selectInput with a uiOutput and move the selectInput into a renderUI function in your server code:

UI:

uiOutput('ui_assetClass')

Server:

output$ui_assetClass <- renderUI({
    selectInput('pick_assetclass',
                label ='Asset Class',
                choices=sqlOutput(),
                selected = NULL, multiple = FALSE,width="450px"),
)}

EDIT: Your problem with the column names appearing in the list is likely due to dbGetQuery returning a named vector. Just strip the names with unname and it should work. This question deals with a similar problem: Column Names in Dropdown R Shiny SelectInput from data base