R shiny selectinput with database values

Question

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"),

divibisan · Accepted Answer · 2018-04-16 20:43:54Z

4

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

edited Apr 16, 2018 at 20:43

answered Apr 13, 2018 at 13:59

divibisan

12.2k11 gold badges44 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ChinB Over a year ago

Thanks to both of you..they work great!..i am using updateSelectInput in my code..however there is a strange thing happening with the dropdowns - i see the column name in the filters too..updated the code in the main post for review.

J0ki · Accepted Answer · 2018-04-13 13:49:53Z

1

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

answered Apr 13, 2018 at 13:49

J0ki

2922 silver badges20 bronze badges

Collectives™ on Stack Overflow

R shiny selectinput with database values

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related