Include an SQL code chunk into R script to be converted to a Markdown report

Question

I would like to include an SQL code chunk into an RMarkdown report. I don't plan to actually run any SQL code in there, so the chunk will have eval=FALSE, eg:

```{sql, eval=FALSE}
select * from mtcars
where car = 'abc'
```

Now, I normally generate R Markdown reports using rmarkdown::render inside R scripts (as opposed to directly running *.Rmd) as described here. Is it possible to include an SQL code chunk into an R script to be converted to a Markdown report? Here is something I would naively use to achieve what I need:

#+ sql, eval=FALSE
select * from mtcars
where car = 'abc'

This is only needed to show a nicely highlighted code without running it, but rendering fails. I am getting the following error:

Error in parse(text = x, keep.source = TRUE) : :88:15: unexpected symbol

Seems like the only way to do so is to comment the SQL code out:

#+
# select * from mtcars
# where car = 'abc'

But I would like to have a properly highlighted code in my report. Is this possible in the setup I described?

Answer 1

In older versions of rmarkdown, your code worked fine without any changes. However, in more recent versions it is throwing the error you mentioned. One way, that works for me to get around it is to use a backtick before and after your query. Addtionally, setting engine = 'sql' will add formatting.

#+ sql, eval = FALSE,  engine = 'sql'
`SELECT * FROM mtcars
WHERE car = 'abc'`

Answer 2

Per @user2554330's answer, try the mysql keyword instead of the sql keyword.

```{mysql eval=FALSE}
select * from mtcars
where car = 'abc'
```

Reason:

• If you use the mysql keyword, the markdown engine will respect eval=FALSE.