Some new information. I tried to copy paste your reprex to see if that worked and it did not:
---
title: "Use external SQL file for 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
We check that it works with SQL chunk to filter data into an R object
SELECT * FROM iris WHERE Species == "setosa"
The object setosa exists
glimpse(setosa)
Know, let see how to load the SQL command from a file to a chunk then execute to save in a R object. We can do it through the power of knitr.
For the demo, we write some SQL in a script versicolor.sql using the cat engine that can write the content of a chunk to file.
SELECT * FROM iris WHERE Species == "versicolor"
and we check that the file exists with some content
readLines("versicolor.sql")
Now we can use this script to fill the content of the chunk and execute it like any sql chunk. We use the knitr chunk option code that allow to fill the content of a chunk programmatically.
The chunk is executed as any other SQL chunk. We check that versicolor data exists
glimpse(versicolor)
When I try to run the first chunk:
> library(dplyr)
> library(dbplyr)
> iris_db <- tbl_memdb(iris)
Error: Table `iris` exists in database, and both overwrite and append are FALSE
The second chunk works, a new variable 'setosa' appears in my environment after running:
{sql, connection=con, output.var="setosa"}
SELECT * FROM iris WHERE Species == "setosa"
The next chunk, readLines, works:
readLines("versicolor.sql")
[1] "SELECT * FROM iris WHERE Species == \"versicolor\""
The cat chunk works. When I run it a new .sql file 'versicolor.sql' appears:
{cat, engine.opts = list(file = "versicolor.sql", lang = "sql")}
SELECT * FROM iris WHERE Species == "versicolor"
The next chunk is the sql chunk that reads the lines from the newly created versicolor.sql file:
{sql, connection = con, code=readLines("versicolor.sql"), output.var="versicolor"}
This chunk does not run. Same issue as I'm having with the script above.
sessionInfo()
R version 3.6.0 (2019-04-26)
Platform: x86_64-redhat-linux-gnu (64-bit)
Running under: Amazon Linux 2
Matrix products: default
BLAS/LAPACK: /usr/lib64/R/lib/libRblas.so
locale:
[1] LC_CTYPE=en_US.UTF-8 LC_NUMERIC=C LC_TIME=en_US.UTF-8 LC_COLLATE=en_US.UTF-8 LC_MONETARY=en_US.UTF-8 LC_MESSAGES=en_US.UTF-8
[7] LC_PAPER=en_US.UTF-8 LC_NAME=C LC_ADDRESS=C LC_TELEPHONE=C LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C
attached base packages:
[1] stats graphics grDevices utils datasets methods base
other attached packages:
[1] dbplyr_1.4.2 dplyr_0.8.3
loaded via a namespace (and not attached):
[1] Rcpp_1.0.3 rstudioapi_0.10 knitr_1.27 magrittr_1.5 tidyselect_0.2.5 bit_1.1-15.1 R6_2.4.1 rlang_0.4.2 fansi_0.4.1
[10] blob_1.2.0 tools_3.6.0 packrat_0.5.0 xfun_0.12 utf8_1.1.4 cli_2.0.1 DBI_1.1.0 bit64_0.9-7 assertthat_0.2.1
[19] digest_0.6.23 tibble_2.1.3 crayon_1.3.4 purrr_0.3.3 vctrs_0.2.1 zeallot_0.1.0 memoise_1.1.0 glue_1.3.1 RSQLite_2.2.0
[28] compiler_3.6.0 pillar_1.4.3 backports_1.1.5 pkgconfig_2.0.3