Pass R variable to RODBC's sqlQuery? [duplicate]

Question

Is there any way to pass a variable defined within R to the sqlQuery function within the RODBC package?

Specifically, I need to pass such a variable to either a scalar/table-valued function, a stored procedure, and/or perhaps the WHERE clause of a SELECT statement.

For example, let:

x <- 1 ## user-defined

Then,

example <- sqlQuery(myDB,"SELECT * FROM dbo.my_table_fn (x)")

Or...

example2 <- sqlQuery(myDB,"SELECT * FROM dbo.some_random_table AS foo WHERE foo.ID = x")

Or...

example3 <- sqlQuery(myDB,"EXEC dbo.my_stored_proc (x)")

Obviously, none of these work, but I'm thinking that there's something that enables this sort of functionality.

Stupor

Duplicate of How to add a dynamic value into RMySQL getQuery and Dynamic string in R and RSQLite query with user specified variable in the WHERE field – Immunoreaction

Answer 1

Build the string you intend to pass. So instead of

example <- sqlQuery(myDB,"SELECT * FROM dbo.my_table_fn (x)")

do

example <- sqlQuery(myDB, paste("SELECT * FROM dbo.my_table_fn (", 
                                x, ")", sep=""))

which will fill in the value of x.

Archidiaconal

Keep in mind, though, the dire consequences if your x value is something like col); DELETE * FROM my_table_fn; SELECT * FROM my_table_fn (col. Which is why it's much better to use placeholders than paste(). Some drivers support placeholders, some don't. – Eolande

expanding (maybe improving) on @Dirk's answer, you can use sprintf() to build your string, I find it much easier to read than a paste(), e.g. example <- sqlQuery(myDB, sprintf("SELECT * FROM dbo.my_table_fn (%s)", x)). For one-variable substitutions, the difference between sprintf() and paste() is not a big deal, but for multiple-variable substitutions, it becomes more important, e.g. sqlQuery(myDB, sprintf("SELECT * FROM orders WHERE order_date >= '%s' AND order_date < '%s' AND customer_id = %s", start.date, end.date, customer.id)) – Blanka

Answer 2

If you use sprintf, you can very easily build the query string using variable substitution. For extra ease-of-use, if you pre-parse that query string (I'm using stringr) you can write it over multiple lines in your code.

e.g.

q1 <- sprintf("
               SELECT basketid, count(%s)
               FROM %s
               GROUP BY basketid
              "
              ,item_barcode
              ,dbo.sales
              )
q1 <- str_replace_all(str_replace_all(q1,"\n",""),"\\s+"," ")
df <- sqlQuery(shopping_database, q1)

Side-note and hat-tip to another R chap

Recently I found I wanted to make the variable substitution even simpler by using something like Python's string.format() function, which lets you reuse and reorder variables within the string

e.g.

$: w = "He{0}{0}{1} W{1}r{0}d".format("l","o")
$: print(w)
"Hello World"

However, this function doesn't appear to exist in R, so I asked around on Twitter, and a very helpful chap @kevin_ushey replied with his own custom function to be used in R. Check it out!

Greyhound

Answer 3

try with this

x <- 1
example2 <- fn$sqlQuery(myDB,"SELECT * FROM dbo.some_random_table AS foo WHERE foo.ID = '$x'")

Sager

Error: object 'fn' not found – Honig

I suspect it's using gsubfn. – Gorblimey

Answer 4

With more variables do this:

  aaa <- "
      SELECT   ColOne, ColTwo 

FROM    TheTable 

 WHERE  HpId =  AAAA            and

  VariableId = BBBB     and 

  convert (date,date )  < 'CCCC'
  "


--------------------------

  aaa <- gsub ("AAAA",  toString(111),aaa)

  aaa <- gsub ("BBBB",  toString(2222),aaa)

  aaa <- gsub ("CCCC",  toString (2016-01-01) ,aaa)

Meanie