selectInput dropdown options using PostgreSQL

[suveer.daswani]

Hello, I am trying to create a selectInput drop down option in my app. However, I want to drop down options to be the observations from my local dataset (extracted through POSTREGSQL). Any helpful ideas? I would really appreciate it.
Currently, I have the selectInput in my ui file and I also have the following pieces in my server file:

sqltrial <- function(){
con <- dbConnect(dbDriver('PostgreSQL'),
user='------',
password='-------',
dbname='--------',
host = '--------',
port = '------')
sqlOutputRootId <- reactive ({
sqlInputRootId <- paste("select distinct ROOTID from TABLE")
dbGetQuery(con, sqlInputRootId)
})
}

observe ({
updateSelectInput(session,"pickrootidd","Root ID",
choices = sqlOutputRootId())
})

Should choices be sqltrial instead?

I am not sure what the observe does? And, in the sqltrial function I create the connection. Then, I create the connection later as well when I write my query for the output. In that query I use the paste function and "input$pickrootidd" in the blank that I want to fill with the input. Do I need to create the connection twice? Can I just write the connection lines once after these lines (or do I have to embed it in a function like I currently do above in the function sqltrial?):

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

I am getting the errors:
Warning: Error in as.vector: cannot coerce type 'closure' to vector of type 'list'
Warning: Error in : object of type 'closure' is not subsettable

Thank you very much!

[andresrcs]

If I understand you correctly you want to do something like this

library(shiny)
library(odbc)

connection_string <- glue("Driver={{PostgreSQL ANSI}};\\
                              Uid={Sys.getenv('MY_UID')};\\
                              Pwd={Sys.getenv('MY_PWD')};\\
                              Server=localhost;\\
                              Port=5432;\\
                              Database=your_database;")
con <- dbConnect(odbc::odbc(), .connection_string = connection_string, encoding = "utf8")

sql_query <- "select distinct ROOTID from TABLE"
sqlInput <- dbGetQuery(con, sql_query)

ui <- fluidPage(
    selectInput(inputId = "choice",
                label = "Select:",
                choices = sqlInput$ROOTID),
    textOutput("choice")
)

server <- function(input, output) {
    output$choice <- renderText({
        input$choice
    })
}

shinyApp(ui = ui, server = server)