r - 将参数传递到 RMySQL dbSendQuery

Question

我在将值传递到 dbSendQuery 时遇到困难。下面是我的代码:

参数:

date_param <- '2014_02_02'

查询:

  pull <- dbSendQuery(test_db, "select distinct product_id, group 
                                 from test_table 
                                 where date between date_sub(",date_param,", interval 1 year) and ",date_param," ;")

pulled_data <- fetch(pull, n=-1)

我得到的错误是:

Error in is(object, Cl) : 
  internal error in RS_DBI_getResultSet: could not find resultSet in connection

是我的语法有问题吗？

谢谢。

Answer 1

我知道这是一个非常老的问题，所以我希望您已经解决了您的问题。正如 @MrFlick 所建议的，paste 构建一个字符串并允许您将其作为单个参数传递。然而，这样做时，您必须执行一些语义以确保正确引用日期。由于我没有您正在讨论的表(或有关其中数据的信息)，因此我将构建一个简单的查询来显示正在发生的情况。

目前，您可能向数据库提交的内容类似于:

date_param <- "2014_02_02"

## currently submitting
paste0("select distinct product_id, group from test_table  where date between  date_sub(", 
  date_param, ", interval 1 year)  and ", date_param, " ;")  

#> [1] "select distinct product_id, group from test_table  where date between  date_sub(2014_02_02, interval 1 year)  and 2014_02_02 ;"


## analogous query
paste0("select  date_sub(", date_param, ", interval 1 year) as start , ", date_param, 
  " as end;")
#> [1] "select  date_sub(2014_02_02, interval 1 year) as start , 2014_02_02 as end;"


## need to quote the dates
paste0("select  date_sub('", date_param, "', interval 1 year) as start , '", 
  date_param, "' as end;")
#> [1] "select  date_sub('2014_02_02', interval 1 year) as start , '2014_02_02' as end;"

如果您将最后一个查询复制并粘贴到 SQL 编辑器中，它应该按照您期望的方式工作(注意日期周围的引号)。

使用paste(或paste0 - 无分隔符)的好处是您可以准确捕获正在发送的查询并在 SQL 编辑器中自行测试。缺点是令人恼火的引用行为和 SQL 注入(inject)风险(以及缺乏类型安全性)。更好的解决方案是使用变量替换，它根据实现的不同而略有不同(RMySQL 和 RMariaDB 使用 ?，RPostgreSQL 和 RPostgres 使用 $1 、$2、$3 等)。请参阅DBI Spec了解更多信息 - 有时您还可以通过提前准备查询来获得性能提升。

另请注意，RMySQL 软件包正在逐步淘汰 - RMariaDB 与 MySQL 和 MariaDB 兼容，并且是 future 开发的重点。 (根据我的探索，RMySQL 包似乎对这样的准备好的查询没有很好的支持)

library(RMariaDB)

date_param <- "2014_02_02"

conn <- dbConnect(MariaDB(), ...)

query <- "select date_sub(?, interval 1 year) as start, date(?) as end;"

rs <- dbSendQuery(conn, query, list(date_param, date_param))
dbFetch(rs)
#>        start        end
#> 1 2013-02-02 2014-02-02
dbClearResult(rs)
#> [1] TRUE