[FR] Run SQL chunk with DBI option `immediate = TRUE` ?

[JrmFRL]

I understand from the documentation that DBI is used in backend to run SQL chunk.

I try to reproduce the behaviour of running of R instruction similar to:

DBI::dbGetQuery(con, statement = "SELECT * FROM mytable LIMIT 10", immediate = TRUE)

but I could not find any option to pass in the sql chunk header, such as:

```{sql, output.var = "data", connection = "con", immediate = TRUE}
SELECT * FROM mytable LIMIT 10
```

I tried many things such as:

• immediate = "TRUE/True"
• params = parameters with parameters <- list(immediate = TRUE) ...

but could not success.

Is there a way to achieve this I have not thought about ? Thank you !

[AWKruijt]

Found this while looking for that exact same desired functionality - an ' immediate = T' chunk option would be amazing.

[cderv]

@JrmFRL this is not supported to passe any option to dbGetQuery. Support should be added.

Just some notes if someone wants to tackle this, if possible, by a PR:

• This would be in knitr where the sql engine lives (called eng_sql) : https://github.com/yihui/knitr/blob/master/R/engine.R
• Query are indeed executed using dbGetQuery(): https://github.com/yihui/knitr/blob/8c6ba6cec8a270352b2359142c8535bcadfb4d68/R/engine.R#L569-L574
• The code should be adapted there with probably a new knitr option (or rather a new engine.opts value) to retrieve.

Anyone willing to help with this is welcomed! Thanks !

[AWKruijt]

So...I'm really craving this immediate option for SQL chunks and tried to have a go at this myself yet I seem to lack the insight on how to get Rstudio to actually use a modified engine.

The edited engine.R file can be found here in this forked repos: https://github.com/AWKruijt/knitr_add_sql_immediate. Not sure if this is actually all that is required to get it to work (I am probably being wonderfully naive now) but I think it shouldn't require much more than this either?

Yet, as said, I seem unable to get the thing to test run >.< If someone could give it a spin that would be awesome.

[AWKruijt]

Big update I think :) Using a knitr::knit_engines$set()-style test I think I managed to iron most details out now. See latest commit at this forked repos: https://github.com/AWKruijt/knitr_add_sql_immediate. In addition to the option immedate, I've also added and option replace which (if TRUE) adds code to remove an existing temporary table before executing an "into" query. My attempt at custom error handling in case of attempt to replace a non-temporary table doesn't yet work however.

Not sure what the next step is now? Should I initiate a PR?

[cderv]

Hi @AWKruijt,

Thanks for your work on this feature! This sounds great!

Not sure what the next step is now? Should I initiate a PR?

Yes please open a PR, it will be easier for us to review and test ! Thanks a lot!