How do I substitute values from the R session into SQL bind variable placeholders?

Question

I want to re-use raw SQL within an R script. However, SQL has variable binding that lets us parameterize the query.

Is there a quick way to directly substitute values from the R session into bind variable placeholders when using SQL within dbplyr?

I guess it doesn't have to be dbplyr, but that's what I was using.

I recall that RMarkdown supports an SQL engine that lets a chunk with SQL bind variables to values in the (Global?) environment. (Search for text "If you need to bind the values of R variables into SQL queries" in that page.) Based on this, it seems that someone has already set up a way to do easy variable binding.

For example, code below makes the program "Oracle SQL Developer" prompt me to enter a value for :param1 when I run it.

select 
  * 
from 
  ( select 'test' as x, 'another' as y from dual )
where x = :param1 and y = :param2

I would like to take that same code in R and run it with some parameters. This does not work, but it's kind of what I imagine might work if there was a function to do it:

# Assume "con" is a DB connection already established to an Oracle db.
tbl( con, 

  args_for_oracle_sql( 

    "select 
      * 
    from 
      ( select 'test' as x, 'another' as y from dual )
    where x = :param1 and y = :param2 ", 

    # Passing the named parameters
    param1 = "test", 

    param2 = "another" 

  ) 
)


# Here's another interface idea that is perhaps similar to 
# what is shown here for SQL: https://bookdown.org/yihui/rmarkdown/language-engines.html#sql

raw_sql <- "
    select 
      * 
    from 
      ( select 'test' as x, 'another' as y from dual )
    where x = :param1 and y = :param2 "

# Set variables that match parameter names in the current environment.
param1 <- "test"
param2 <- "another"

tbl( con, 

  exc_with_args_for_oracle_sql( 

    # Pass raw SQL with the ":param1" markers       
    sql = raw_sql, 

    # Pass the function an environment that contains the values 
    # of the named parameters in the SQL. In this case, the 
    # current environment where I've set these values above. 
    env_with_args = environment()

  ) 
)

By the way, I'm not sure which of the following libraries are needed, but this is what I load:

library(RODBC)
library(RODBCext)
library(RODBCDBI)
library(DBI)
library(dplyr)
library(dbplyr)
library(odbc)

score 2 · Answer 1 · answered Aug 23 '19 at 18:33

Use the build_sql() function from dbplyr (for strings)

library(DBI)
library(dbplyr)
library(odbc)
param1 = readline("What is the value of param1 ?")  # base R
param1 = rstudioapi::askForPassword("What is the value of param1 ?") # RStudio
param2 = readline("What is the value of param2 ?")  # 

con = dbConnect('XXX') # Connection
# write your query (dbplyr)
sql_query = build_sql("select 
      * 
    from 
      ( select 'test' as x, 'another' as y from dual )
    where x = ",param1," and y = ", param2, con = con)

df = dbGetQuery(con,sql_query)

How do I substitute values from the R session into SQL bind variable placeholders?

1 Answers1