Run SQL script from R with variables defined in R-CodePudding

I have an SQL script which I need to run using R Studio. However, my SQL script has one variable that is defined in my R environment. I am using dbGetQuery; however, I do not know (and I didn't find a solution) how to pass these variables.

library(readr)    
library(DBI)    
library(odbc)    
library(RODBC)

#create conection (fake one here)
con <- odbcConnect(...)

dt = Sys.Date()   

df = dbGetQuery(.con, statement = read_file('Query.sql'))

The file 'Query.sql' makes reference to dt. How do I make the file recognize my variable dt?

CodePudding user response：

There are several options, but my preferred is "bound parameters".

If, for instance, your 'Query.sql' looks something like

select ...
from MyTable
where CreatedDateTime > ?

The ? is a place-holder for a binding.

Then you can do

con <- dbConnect(...) # from DBI
df = dbGetQuery(con, statement = read_file('Query.sql'), params = list(dt))

With more parameters, add more ?s and more objects to the list, as in

qry <- "select ... where a > ? and b < ?"
newdat <- dbGetQuery(con, qry, params = list(var1, var2))

If you need a SQL IN clause, it gets a little dicey, since it doesn't bind things precisely like we want.

candidate_values <- c(2020, 1997, 1996, 1901)
qry <- paste("select ... where a > ? and b in (", paste(rep("?", length(candidate_values)), collapse=","), ")")
qry
# [1] "select ... where a > ? and b in ( ?,?,?,? )"
df <- dbGetQuery(con, qry, params = c(list(avar), as.list(candidate_values)))