<!--
Please keep the below portion in your issue. Your issue will be closed if …any of the boxes is not checked (i.e., replace `[ ]` by `[x]`). In certain (rare) cases, you may be exempted if you give a brief explanation (e.g., you are only making a suggestion for improvement). Thanks!
-->
@cderv asked me to open this issue over at [RStudio Community](https://community.rstudio.com/t/get-sql-code-from-sql-chunk-with-variables-evaluated/136982?u=dpprdan)
I would like to get the code from a SQL chunk in rmarkdown, with the variables (`?myvar`) and parameters (`@myparam`) evaluated. So, when I have a chunk like this
````
```{sql, connection=con, output.var="iris_subset"}
SELECT * FROM iris WHERE Species = ?species
```
````
I would like to get this SQL string back (i.e. the string should contain `'setosa'`, not `?species`):
```
SELECT * FROM iris WHERE Species = 'setosa'
```
In other words, I'd like to see the SQL command that would actually be sent to the DB (preferably without sending it, though this could be optional).
This would be helpful when debugging SQL code that has variables/parameters, because one could see for example, whether the variables are quoted correctly (depending on the requirement as strings or as DB objects, e.g. columns) or contain the expected values.
I suspect this to apply to other engines as well. For example, I've tried the [{glue}'s `glue_sql` engine](https://glue.tidyverse.org/articles/engines.html) as an alternative. I do get the evaluated SQL code with that engine when the document is knitted successfully, but that does not help when debugging, because the document does not knit successfully. (See the following on the differences in the SQL code from the `sql` and the `glue_sql` engines).
<details>
<summary>Sidenote: Different behaviour of vars in `sql` vs `glue_sql` chunks</summary>
The following rmarkdown document
````rmarkdown
---
title: "Untitled"
output: github_document
---
```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
library(DBI)
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
mtcars$model <- rownames(mtcars)
DBI::dbWriteTable(con, "mtcars", mtcars)
```
```{r}
var <- "mpg"
tbl <- "mtcars"
num <- 150
```
```{sql, connection = con, output.var="mt_sql"}
SELECT `model`, `hp`, ?var
FROM ?tbl
WHERE ?tbl.`hp` > ?num
```
```{glue_sql, connection = con, output.var="mt_glue_sql"}
SELECT `model`, `hp`, {`var`}
FROM {`tbl`}
WHERE {`tbl`}.`hp` > {num}
```
````
renders as:
> Untitled
> ================
>
> ``` r
> var <- "mpg"
> tbl <- "mtcars"
> num <- 150
> ```
>
> ``` sql
> SELECT `model`, `hp`, ?var
> FROM ?tbl
> WHERE ?tbl.`hp` > ?num
> ```
>
> ``` sql
> SELECT `model`, `hp`, `mpg`
> FROM `mtcars`
> WHERE `mtcars`.`hp` > 150
> ```
Note that the `sql` chunk in the output still has `?var`, `?tbl` and `?num`, whereas in the `glue_sql` chunk it's `mpg`, `mtcars` (both quoted) and `150`, respectively. I don't know if that is a bug or a feature - let me know in case I should open a separate issue for that.
</details>
<details>
<summary>Sidenote: `glue_sql` chunks might be slightly easier to debug than `sql` at the moment</summary>
In general, `glue_sql` is somewhat easier to debug, because I could copy+paste the code from the chunk into the `glue_sql()` function and the the SQL code back. In practice, I might have to deal with quoted object names in the SQL code and therefore escape all double or single quotation marks.
</details>
---
By filing an issue to this repo, I promise that
- [x] I have fully read the issue guide at https://yihui.org/issue/.
- [x] I have provided the necessary information about my issue.
- If I'm asking a question, I have already asked it on Stack Overflow or RStudio Community, waited for at least 24 hours, and included a link to my question there.
- If I'm filing a bug report, I have included a minimal, self-contained, and reproducible example, and have also included `xfun::session_info('knitr')`. I have upgraded all my packages to their latest versions (e.g., R, RStudio, and R packages), and also tried the development version: `remotes::install_github('yihui/knitr')`.
- If I have posted the same issue elsewhere, I have also mentioned it in this issue.
- [x] I have learned the Github Markdown syntax, and formatted my issue correctly.
I understand that my issue may be closed if I don't fulfill my promises.