Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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.

like image 391
Ray Avatar asked Dec 01 '10 23:12

Ray


1 Answers

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.

like image 187
Dirk Eddelbuettel Avatar answered Oct 13 '22 00:10

Dirk Eddelbuettel