Pass input$dates[1] into a .sql script used in shiny app

[dougfir]

For my shiny app I have a .sql file. Works fine with no variables e.g.

    query <- read_lines("data.sql") %>% 
        glue_collapse(sep = "\n") %>% 
        glue_sql(.con = con)
    rawd <- dbGetQuery(con, query)

Where data.sql might look like:

select *
from schema.table
where date between '2021-06-01' and '2021-06-10'

This works, in this form everything is fine so var. Also, from charts elsewhere in my app, I know that input$dates[1] and input$dates[2] are valid inputs that are currently working sound.

I'd like to make the dates dynamic based on user input. Tried:

select *
from schema.table
where date between {input$dates[1]} and {input$dates[1]}

When I try to run this I get this error message:

Listening on http://127.0.0.1:5439
Warning: Error in : Can't access reactive value 'dates' outside of reactive consumer.
ℹ Do you need to wrap inside reactive() or observer()?
  62: <Anonymous>
Error : Can't access reactive value 'dates' outside of reactive consumer.
ℹ Do you need to wrap inside reactive() or observer()?

I tried wrapping all the input$dates inside of reactive:

select *
from schema.table
where date between {reactive(input$dates[1])} and {reactive(input$dates[1])}

But then I got:

Listening on http://127.0.0.1:5439
Warning: Error in as.vector: cannot coerce type 'closure' to vector of type 'character'
  56: as.character.default
  54: .transformer
  53: <Anonymous>
Error in as.vector(x, "character") : 
  cannot coerce type 'closure' to vector of type 'character'

How can I use a date input within a .sql file within my shiny app?

[dougfir]

I was able to get this working by making the query itself (query <- read_lines("data.sql") %>% ...) reactive.