SQL errors while rendering Quarto

[tsadigov]

Hello! I am trying to use SQL chunks within the Quarto document. When I use R Markdown with SQL chunks, I get no error while knitting it. Once I switch the same code from rmd extension to qmd, I get errors in SQL chunks while rendering qmd file. Particularly, I get the following error:

"Error: No method for S4 class:SQLiteConnection
Execution halted"

Does anyone know what is happening? Any help is appreciated.

Here is the R Markdown file that knits:

---
title: "SQL in R Markdown"
author: "Tural Sadigov"
date: "2022-09-18"
output: html_document
---

```{r libs}
library(tidyverse)
library(DBI)
library(RSQLite)
library(dbplyr)
```

```{r}
con <- DBI::dbConnect(RSQLite::SQLite(), 
                      dbname = ":memory:")
copy_to(dest = con, 
        df = mtcars, 
        name = "mtcars")

dbListTables(con)
```

```{sql, connection=con}
SELECT *
FROM mtcars
```

Here is the Quarto , qmd, file that gives the error .

---
title: "SQL in Quarto"
author: "Tural Sadigov"
date: "2022-09-18"
output: html_document
---

```{r libs}
library(tidyverse)
library(DBI)
library(RSQLite)
library(dbplyr)
```

```{r}
con <- DBI::dbConnect(RSQLite::SQLite(), 
                      dbname = ":memory:")
copy_to(dest = con, 
        df = mtcars, 
        name = "mtcars")

dbListTables(con)
```

```{sql, connection=con}
SELECT *
FROM mtcars
```

[gsapijaszko]

@tsadigov

```{sql, connection=con}
SELECT *
FROM mtcars

Change it to:

```{sql}
#| connection: con
SELECT *
FROM mtcars

For some reasons quarto doesn't like the old style of chunk options.

Regards,
Grzegorz

[tsadigov]

Thank you, that solved the issue.

[cderv]

This was reported in Quarto

`Error: No method for S4 class:duckdb_connection` when incorporating SQL chunks with quarto website

and this is now fixed - there should not be an error.

However, using the in-chunk YAML syntax is the recommended way to set chunk option with Quarto, so no need to change back

[gsapijaszko]

Thanks Christophe for letting know.

I have discovered {sql} chunks just by curiosity, if quarto would do fancy formatting any SQL expression. Then it requested a valid connection. I have added one, then it started throw a SQL errors . Ha, have discovered it executes the code (and is doing a fancy formatting as well ).

Regards,
Grzegorz