---
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.
You can in fact use sql.show_interpolated = TRUE to do this I think
---
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", sql.show_interpolated = TRUE}
SELECT * FROM iris WHERE Species = ?species
```
```{r}
head(iris_subset)
```
On rOpenSci's slack channel, Jenny Bryan pointed me at {glue}'s glue_sql engine. This is indeed somewhat easier to debug, because I can just copy paste the code from the glue_sqlchunk into the glue::glue_sql() function. However, if the code contains quotation marks (i.e. quoting DB object names or strings in the SQL code) then I'd have to escape those quotes before.
In principle yes. Does it work without knitting the (whole) document successfully / actually sending the SQL code to the DB?
I guess my main use-case is when I need to debug my SQL code (variables quoted correctly? correct values? etc.). And the document wouldn't knit successfully, if the SQL code is not working correctly, I suppose?
(Sorry, I guess I wasn't aware of all requirements when I posted this initially.)
I think what I want is