In-body chunck options cause syntax error in sql chunks

[andresrcs]

In-body chunck options in sql chunks executed interactivley get recognised but they also get included in the query causing a syntax error. Consider this example:

---
title: "Example"
output: html_document
date: "2022-07-11"
---

```{r}
library(DBI)
db = dbConnect(RSQLite::SQLite(), dbname = "sql.sqlite")
```

```{sql}
#| connection = db

SELECT 'test'
```
#> Error: unrecognized token: "#"
#> Failed to execute SQL chunk

Even if I try to creat a .sql file with the cat engine the problem is the same

```{cat}
#| engine.opts = list(file = "tbl.sql", lang = "sql")

SELECT 'test'
```

```{sql}
#| connection = db,
#| code = readLines("tbl.sql")
```

The resulting tbl.sql file has this content

#| engine.opts = list(file = "tbl.sql", lang = "sql")

SELECT 'test'

Notice how the chunk options get included, which causes a syntax error.

Is there any special consideration for using in-body chunck options interactivley with engines other than R? Or is this a bug? Kniting the document works as expected. I'm using RStudio Server 2022.07.0 Build 548

[cderv]

@andresrcs

Is there any special consideration for using in-body chunck options interactivley with engines other than R? Or is this a bug? Kniting the document works as expected. I'm using RStudio Server 2022.07.0 Build 548

I think this is an option in the IDE not correctly handling this specific options for non R engine. Running interactively a code chunk will not run in through knitr so something that works with knitr won't necessary work interactively.

• It seems cat engine is taking correctly information in the option but option are not removed from chunk content
• It seems that sql engine does not work interactively in IDE

To prevent parsing issue, the inline chunk should use valid comment syntax, so it would use SQL comment BTW

```{sql}
--| connection = db

SELECT 'test'
```

but error is

Error in .rs.runSqlForDataCapture("--| connection = db\n\nSELECT 'test'",  : 
  The 'connection' option (DBI connection) is required for sql chunks.
Failed to execute SQL chunk

So IDE is not correctly supporting those.

You could probably open an issue in the RStudio IDE repo

[andresrcs]

Thanks, I will file an issue on GitHub. I just wanted to be sure this was indeed an issue and not an oversight or bad understanding of the feature on my part.

[andresrcs]

@cderv

To prevent parsing issue, the inline chunk should use valid comment syntax, so it would use SQL comment BTW

But using #| with sql chunks interactively works, the connection is recognized, the problem is that the option gets included into the sql query and sent to the sql engine causing a syntax error.

I have also seen examples of sql chunks using #| in articles written by RStudio people so I thought the use is universal for all engines regardless of the specific comment syntax, although, it is not explicit if the examples are meant to be run interactively or not.

[cderv]

@andresrcs

I have also seen examples of sql chunks using #| in articles written by RStudio people so I thought the use is universal for all engines regardless of the specific comment syntax, although, it is not explicit if the examples are meant to be run interactively or not.

Yes knitr will always try #| syntax after trying the comment character syntax from the language engine. So for sql it will try --| and then if not found, try #|. Using the comment char syntax allows to correct get the header ignore in the language.

But anyway, I don't what the IDE assumes for this chunk header with comments. It seems both issue are related:

• When using #| the parameter is correctly read from the option, but the IDE does not remove the option header, and # is invalid comment char in SQL so it errors.
• When using --| , it would be sent correctly and ignore in SQL but the IDE does not correctly parse the options from this lines as knitr is doing.

In both case, it seems to me related to better support by RStudio IDE for this new syntax in non R engine.

[andresrcs]

I'm linking to the GitHub issue for completeness

In-body chunk options not working properly for non R engines when executed interactively