Pass R variable to RODBC's sqlQuery?

sql r rodbc
Ray picture Ray · Dec 2, 2010 · Viewed 31.1k times · Source

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.

Answer

Dirk Eddelbuettel picture Dirk Eddelbuettel · Dec 2, 2010

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.

Related questions

sqlSave in R to create and save a dataframe to an sql table

Hi I am using R to save a data frame into a DB2 SQL table. I seem to be able to create the table skeleton, but not append the data to the table - >df <- read.csv("…

Read More
RODBC sqlSave table creation problems

I'm having trouble creating a table using RODBC's sqlSave (or, more accurately, writing data to the created table). This is different than the existing sqlSave question/answers, as the problems they were experiencing were different, I can create tables whereas …

Read More
update an SQL table via R sqlSave

I have a data frame in R having 3 columns, using sqlSave I can easily create a table in an SQL database: channel <- odbcConnect("JWPMICOMP") sqlSave(channel, dbdata, tablename = "ManagerNav", rownames = FALSE, append = TRUE, varTypes = c(DateNav = "datetime")) odbcClose(…

Read More