Problem trying to display a data frame from an SQL Server query within a Shiny app

Question

I would like to query an SQL Server database within Shiny. The user must select some items with some widgets to build the SQL query, and then, triggered by an action button, the result of the query is stored as a data frame and used as input by a renderTable function. No matter what I do to fix it, I always get the message:

cannot coerce type 'closure' to vector of type 'character'.

Could you please give me some advice?

Here is my code:

library(shiny)
library(RODBC)

# Builds conection chain ----

conection <- paste0('driver={', DriverDB, '}; ',
                    'server=', myServerDB, '; ',
                    'database = ', myDataBase, '; ',
                    'uid = ', myUser, '; ',
                    'pwd = ', myPassword, '; ',
                    'trusted_connection = true')
     
# Define UI ----

ui <- fluidPage(
  titlePanel()),

  sidebarLayout(  
    sidebarPanel(          
      radioButtons(...),                   
      selectInput(...),               
      dateRangeInput(...),
      
      actionButton('execute_query', 'Execute query'),

    ),
    mainPanel(    
      tableOutput('result')       
    )
  )
)
# Define server logic ----

server <- function(input, output) {
  
  myQuery <- reactive({'builds query expression from widgets inputs'})  
  
  myData <- reactive({
    req(input$execute_query)
    result <- NULL
    channel_db <- odbcDriverConnect(conection)
    result <- sqlQuery(channel_db, myQuery)
    odbcClose(channel_db)
    result
  }) 
    
  output$result <- renderTable({myData()})  
}

# Run the app ----

shinyApp(ui = ui, server = server)

I have checked the validity of both SQL query and conection in R console, and they work fine.

Answer 1

Since myQuery is reactive data, you need to treat it like a function, just like you do for myData later.

Use:

  myData <- reactive({
    req(input$execute_query)
    result <- NULL
    channel_db <- odbcDriverConnect(conection)
    result <- sqlQuery(channel_db, myQuery()) # <-- the only change, add ()
    odbcClose(channel_db)
    result
  }) 

It might be useful to know that a "closure" is analogous to a "function". And, for all intents and purposes, reactive data and reactive components appear and behave like functions.