SQL 将 R 变量传递给 RODBC 的 sqlQuery？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4330072/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pass R variable to RODBC's sqlQuery?
提问by Ray
Is there any way to pass a variable defined within R to the sqlQuery function within the RODBC package?
有没有办法将 R 中定义的变量传递给 RODBC 包中的 sqlQuery 函数？
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.
具体来说，我需要将这样的变量传递给标量/表值函数、存储过程和/或 SELECT 语句的 WHERE 子句。
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.
显然，这些都不起作用，但我认为有一些东西可以实现这种功能。
采纳答案by Dirk Eddelbuettel
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.
这将填充 的值x。
回答by jonsedar
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.
如果使用 sprintf，则可以使用变量替换非常轻松地构建查询字符串。为了更易于使用，如果您预先解析该查询字符串（我使用的是 stringr），您可以将它写在代码中的多行中。
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
另一个 R 章的旁注和帽子提示
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
最近我发现我想通过使用 Python 的 string.format() 函数之类的东西使变量替换更简单，它允许您在字符串中重用和重新排序变量
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_usheyreplied with his own custom functionto be used in R. Check it out!
然而，这个函数在 R 中似乎不存在，所以我在 Twitter 上四处询问，一个非常有帮助的小伙子@kevin_ushey回复了他自己的自定义函数，以便在 R 中使用。看看吧！
回答by RIOT
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)
回答by patricio
try with this
试试这个
x <- 1
example2 <- fn$sqlQuery(myDB,"SELECT * FROM dbo.some_random_table AS foo WHERE foo.ID = '$x'")
