Run SQL script from R with variables defined in R

Question

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?

Take a look at glue_sql: glue.tidyverse.org/reference/glue_sql.html. — Martin Gal
– Martin Gal, Commented Dec 14, 2021 at 23:31
FYI, a few things: (1) you define con and use .con (dot), probably a typo. (2) You appear to be using con created from RODBC but trying to use it in a DBI function. I have not used RODBC, but are you certain that is appropriate and functional? (3) In reality, you need RODBC (with odbcConnect and sqlQuery but no bound parameter support), or you need DBI and odbc (with dbConnect and dbGetQuery, with bound parameter support). You do not need both. — r2evans
– r2evans, Commented Dec 15, 2021 at 13:36

r2evans · Accepted Answer · 2021-12-15 14:08:55Z

3

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)))

edited Dec 15, 2021 at 14:08

answered Dec 14, 2021 at 23:47

r2evans

167k8 gold badges92 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Osvaldo Assunção Over a year ago

is there a way to adapt this solution if I have two variables?

r2evans Over a year ago

Yes, read the docs. Long story short: qry <- "select ... where a > ? and b < ?", then dbGetQuery(con, qry, params = list(var1, var2)).

majr · Accepted Answer · 2024-02-07 12:29:04Z

0

This is for readers in search of a solution. An alternative to the accepted answer is to use glue::glue_sql(), and the sqlhelper package supports both methods, see Interpolation and Binding. Assuming your dt variable is a string, your query.SQL file might look like:

SELECT {`dt`} FROM some_table

and you could execute it with:

sqlhelper::run_files("query.SQL")

(disclosure: I wrote sqlhelper)

answered Feb 7, 2024 at 12:29

majr

3831 silver badge8 bronze badges

Collectives™ on Stack Overflow

Run SQL script from R with variables defined in R

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related