Passing query as variable to Rmarkdown sql chunk
Looks like Using R variables in queries applies some kind of escaping and can therefore only be used in cases like the example from the documentation (SELECT * FROM trials WHERE subjects >= ?subjects) but not to dynamically set up the whole query.
Instead, the code chunk option can be used to achieve the desired behavior:
The example uses the SQLite sample database from sqlitetutorial.net. Unzip it to your working directory before running the code.
```{r}library(DBI)db <- dbConnect(RSQLite::SQLite(), dbname = "chinook.db")query <- "SELECT * FROM tracks"``````{sql, connection=db, code = query}```
I haven't been able to determine a way to print and execute in the same chunk however with a few extra lines of code it is possible to achieve my desired output.
Printing is solved by CL.'s answer and then I can use EXEC to run the code.
```{sql, code = query}``````{sql, connection = svr}EXEC (?query)```