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?