library(DBI)
library(RSQLite)
library(dplyr)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
iris_id <- iris |>
mutate(id = row_number())
dbWriteTable(con, "iris_id", iris_id)
params <- list(id = c(5,6,7))
q <- "SELECT COUNT(*) FROM iris_id WHERE id IN ($id)"
res <- dbSendQuery(con, q)
dbBind(res, params)
dbFetch(res)
As per documentation, this performs the query once per entry in params$id and returns c(1,1,1).
This also doesn't work, because this query is actually WHERE id IN ('5,6,7'):
id <- c(5L,6L,7L)
stopifnot(is.integer(id))
params <- list(id = paste(id, collapse=","))
res <- dbSendQuery(con, q)
dbBind(res, params)
dbFetch(res)
The answer to question [0] suggests using positional ? and pasting a list of ? together. However, this loses the possibility of using named params, which would be beneficial if I have multiple parameters. Is there another way?
[0] Passing DataFrame column into WHERE clause in SQL query embedded in R via parametrized queries
library(dbplyr)
translate_sql(id %in% c(4L,5L,6L))