Get SQL code from SQL chunk with variables evaluated

Is it possible to get the SQL code from a SQL chunk in rmarkdown, with the variables (?myvar) and parameters (@myparam) evaluated? See the following example, adapted from Rmd file with embedded SQL chunk. Possible to move the SQL to external file then source? - #3 by cderv

---
title: "Get SQL with evaluated variables from SQL chunks"
output: html_document
---
  
In order to provide a reproducible example, we use dplyr and dbplyr with `tbl_memdb` to load a table into a memory db.

```{r}
library(dplyr)
library(dbplyr)
iris_db <- tbl_memdb(iris)
# we isolate the connection object 
con <- src_memdb()$con
species <- "setosa"
```

We check that it works with SQL chunk to filter data into an R object

```{sql, connection=con, output.var="iris_subset"}
SELECT * FROM iris WHERE Species = ?species
```

Now I would like to get the SQL command above, with the `species` variable evaluated, i.e.

```sql
SELECT * FROM iris WHERE Species = 'setosa'
```

Or what I would get from 

```{r}
glue::glue_sql("SELECT * FROM iris WHERE Species = {species}")
```

I tried the `cat` engine like so

```{cat, engine.opts = list(file = "iris_subset.sql", lang = "sql")}
SELECT * FROM iris WHERE Species = ?species
```

but it writes `?species` as is and its value. 

I am not sure what is the desired output.

You want to have the ?species replace in the output code chunk while the chunk still be evaluated ?

What is the desired result ? Also you mention @myparam. Can you illustrate ?

glue_sqlis working right ?

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'

I'd like to see the actual SQL command that is sent to the DB.

glue_sql() is working right, but I would have to convert all ?myvars to {myvars} (and handle ids and string literals and so forth).

This feature does not exist yet, it would need to be added into knitr, maybe using a chunk option.

Can you open a feature request into knitr repo ? Thanks !