CODEWITHSUNDEEP
rrsqlite
davide
davide

Reputation: 325

RSQLite parametrized query

I am using the library RSQLite and I am struggling to understand how to use the params argument of the dbGetQuery() function.

Following the documentation, I am able to parametrize the WHERE statement of the query, but I am unable to do the same for the table name or for the columns. 

library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ":memory:")

dbWriteTable(con, "mtcars", mtcars)

dbGetQuery(con, "SELECT COUNT(*) FROM mtcars WHERE cyl = ?", params = list(1:8))

dbGetQuery(con, "SELECT * FROM ?", params = list("mtcars"))
dbGetQuery(con, "SELECT ? FROM mtcars", params = list("cyl"))

dbDisconnect(con)

What is wrong with the last two queries? I know it is possible to use different approaches (paste(), glue::glue(), etc.), just wanted to understand the problem.

Thank you

Upvotes: 2

Views: 289

Answers (1)

Xavier Prudent
Xavier Prudent

Reputation: 1742

According to this documentation (march 2023), you can send parametrized sql queries to sqlite using the ":x" notation rather than the standard SQL "?" one

he is an example:

dbGetQuery(con, "SELECT COUNT(*) FROM mtcars WHERE cyl = ?", params = list(1:8))

and the link https://cran.r-project.org/web/packages/RSQLite/vignettes/RSQLite.html

look for "queries"

Upvotes: 1

Related Questions