How to use SQL parameters in an R markdown SQL chunk

Question

What's the right way to include parameters in an Rmd SQL code chunk? Here's an example Rmd file:

---
title: "Rmd Example"
output:
  html_document:
    df_print: paged
  pdf_document: default
---
`r Sys.Date()`
This is an example of an Rmd file.
```{r}
library(tidyverse)
library(DBI)
library(odbc)
library(knitr)
options(connectionObserver = NULL)
con = dbConnect(odbc::odbc(), 
                dsn = "myDsn", 
                uid = "myUsername", 
                pwd = "myPassword")
d1=as.Date("2021-03-15")
```
Look for all encounters after `r d1`
```{sql, connection=con, output.var = "encounters"}
select *
from encounters
where date>?d1
```
There are `r nrow(encounters)` after `r d1` and they are
```{r}
kable(encounters)
```
This ends the document.

The ?d1 part in the SQL code chunk doesn't work because it's not a string. Pretend I'm getting d1 from some other source, it is something that can't be hard-coded into the SQL query itself. The normal way to interact with databases would be to parameterize this value, which as I understand it does two pretty important things:

ensure the parameter value has the correct datatype, and
prevent SQL injection by properly escaping characters

R does have the ability to support parameterization, for example

library(tidyverse)
library(DBI)
library(odbc)
options(connectionObserver = NULL)
con = dbConnect(odbc::odbc(), 
    dsn = "myDSN",
    uid = "myUsername", 
    pwd = "myPassword")
d1=as.Date("2021-03-15")
query="select * from encounters where date>:d1"
encounters=dbGetQuery(con,query,params=d1)

This R code runs fine. How can I get the R markdown SQL code chunk to accept an SQL parameter like this? The ?d1 syntax apparently only inserts text, it doesn't handle data types, and although the documentation isn't clear I'm doubtful it is properly escaping characters either.

To be clear I'm not looking for a funky hack to get the code to run. I can use straight R code instead of the SQL chunk--losing the syntax highlighting would be preferable to losing correct evaluation of parameters. I'm looking for the correct way to put SQL parameters in an SQL chunk.

You could try casting the variable as the correct data type inside your SQL statement, but I think the easier solution is to use the dbplyr package for this: dbplyr.tidyverse.org — Ricky
– Ricky, Commented Mar 21, 2021 at 14:44

Guillaume · Accepted Answer · 2021-03-27 14:34:59Z

Maybe this is a solution for you, using glue_sql, it works on my computer

https://community.rstudio.com/t/using-multiple-r-variables-in-sql-chunk/2940/13

Rmd chunks:

library(DBI)
library(glue)
library(RSQLite)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE)

cyl_int <- c(4L, 6L)
cyl_sql <- glue_sql("{cyl_int*}")

  SELECT * FROM mtcars
  WHERE cyl IN (?cyl_sql)
  LIMIT 3

For a date column, you may have to format it correctly according to your database or use to_date(etc, '%d%...') Here in sqlite it's easy with ISO-8601 dates, an example:

library(DBI)
library(glue)
library(RSQLite)

con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
mtcars$DATE_1 <- seq(from = Sys.Date() - 15, to = Sys.Date() + nrow(mtcars) -16, by = 1) %>% as.character()
dbWriteTable(con, "mtcars", mtcars, overwrite = TRUE)

cyl_int <- c(4L, 6L)
cyl_sql <- glue_sql("{cyl_int*}")

d_date_sql <- glue_sql("{Sys.Date()*}", .con = con)

Note that you have to specify .con parameter for character parameters, as said here

  SELECT cyl, mpg, DATE_1 FROM mtcars
  WHERE DATE_1 >= ?d_date_sql
  ORDER BY DATE_1

Collectives™ on Stack Overflow

How to use SQL parameters in an R markdown SQL chunk

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related