Is it possible to cache the result of a SQL chunk in R Markdown?


I wanted to cache the result of a SQL chunk as well as its output.var, but I couldn't. Knitting the .Rmd like bellow ends up with an error object 'select_1_result' not found at the second run:

title: "sql chunk"
output: html_document

```{r setup, include=FALSE}
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
knitr::opts_chunk$set(echo = TRUE, connection = "con")

```{sql select_1, output.var="select_1_result", cache=TRUE}

```{r print_result,  error=TRUE}

It's strange. I guess this had been possible in the past, at least at the moment of this SO was answered:

Now I use a work around of using an external file and it's just fine, but am I wrong at some point?

```{sql select_1, output.var="select_1_result", eval=!file.exists("/path/to/cache_file")}

```{r write_feather, eval=!file.exists("/path/to/cache_file")}
feather::write_feather(select_1_result, "/path/to/cache_file")

```{r read_feather, eval=!exists("select_1_result")}
select_1_result <- feather::read_feather("/path/to/cache_file")


Hi, I just tested your script on my computer and got an error as well when I run it for the second time.

Maybe the next step is to open an issue in the knitr GitHub repo?

cc @yihui


Thanks for comfirming! Will file a issue.


Update: It has been fixed in the development version of knitr:


It has been filed here: (although it is really a knitr issue). I don't have time to look into it yet. If you are able to figure out a solution, I'll be happy to review a pull request. Thanks!


Thanks, I'll look into this. Sorry that I missed that issue...

Anyway, I'm glad to know this is not that I'm doing something wrong :slight_smile: