Pass R variable to RODBC's sqlQuery?

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.

Duplicate of [How to add a dynamic value into RMySQL getQuery](http://stackoverflow.com/questions/4290672/how-to-add-a-dynamic-value-into-rmysql-getquery) and [Dynamic string in R](http://stackoverflow.com/questions/3416973/dynamic-string-in-r) and [RSQLite query with user specified variable in the WHERE field](http://stackoverflow.com/questions/3449666/rsqlite-query-with-user-specified-variable-in-the-where-field) — Joshua Ulrich, Dec 01 '10 at 23:31

score 21 · Accepted Answer · answered Dec 01 '10 at 23:16

21

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.

answered Dec 01 '10 at 23:16

Dirk Eddelbuettel

360,940
56
644
725

4

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. – Ken Williams Jan 03 '11 at 17:30
6

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))` – mac Jul 05 '12 at 21:41

score 3 · Answer 2 · edited Jun 20 '20 at 09:12

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!

score -2 · Answer 3 · edited Nov 12 '16 at 05:21

-2

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)

edited Nov 12 '16 at 05:21

Serjik

10,543
8
61
70

answered Feb 08 '16 at 21:06

RIOT

7

score -2 · Answer 4 · edited Nov 12 '16 at 05:25

-2

try with this

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

edited Nov 12 '16 at 05:25

Serjik

10,543
8
61
70

answered Dec 23 '10 at 05:03

patricio

1

3

`Error: object 'fn' not found` – Marek Dec 27 '10 at 13:04
1

I suspect it's using [`gsubfn`](https://cran.r-project.org/web/packages/gsubfn/index.html). – r2evans Jul 27 '16 at 19:51

Pass R variable to RODBC's sqlQuery?

4 Answers4

Side-note and hat-tip to another R chap

Linked

Related