ROracle dbGetQuery按名称绑定参数

Question

我正在尝试使用ROracle处理大量带有R中参数的查询。我知道哪些参数出现在每个查询中，但我不知道它们是以什么顺序出现的。因此，我正在寻找在每个查询中按名称提交参数的方法。样本代码：

library(ROracle)

# establish connection to DB
drv <- dbDriver("Oracle")
con <- dbConnect(drv, "User", "password", dbname = "DB")

# create table
createTab <- "create table RORACLE_TEST(num1 number, num2 number)"
dbGetQuery(con, createTab)

# insert String
insStr <- "insert into RORACLE_TEST values(:row1, :row2)"
dbGetQuery(con, insStr, data.frame(row2 = 0, row1 = 1))

# check output
dbGetQuery(con, "SELECT * FROM RORACLE_TEST")

# Output is:
#  NUM1 NUM2
#1    0    1

# Desired output should be:
#  NUM1 NUM2
#1    1    0

除同类解决方案外，任何解决办法都将不胜感激。

dbGetQuery(con,gsub(":row2", "0", gsub(":row1", "1", insStr)))

因为这不会对sql注入进行净化(参数将来自用户输入)。

Answer 1

我最近花了一些时间在同一问题上，没有找到完美的解决办法。在我看来，从语法的角度来看，使用命名占位符是非常错误的，因为它给人的印象是参数顺序是微不足道的。

我认为这不是ROracle库的问题，也不是它的责任，因为这也会导致一个结果，对于那些不太了解PL/SQL的人来说，这是不可能的：

DEFINE
  row1 number;
  row2 number;
BEGIN
  row1 := 1;
  row2 := 0;
  EXECUTE IMMEDIATE 'insert into RORACLE_TEST values(:row1, :row2)' USING row2, row1;
END;
/

当我们用RODBC将应用程序从RODBCext迁移到ROracle时，我们继续使用?作为绑定变量占位符，并在数据库连接性API中使用冒号样式替换它们。至少这不会引起任何人的注意：

# placeholders '?' are replaced with :1 and :2 in custom_dbGetQuery()
insStr <- "insert into RORACLE_TEST values(?, ?)"
custom_dbGetQuery(con, insStr, data.frame(row2 = 0, row1 = 1))

编辑:重新排序数据帧的建议

通过检查查询字符串中出现的占位符，您还可以自己重新排序数据帧：

custom_dbGetQuery <- function(con, insStr, data) {
    names <- names(data)

    name.pos <- sort(sapply(names, function(ph) { 
        regexp <- paste0(":", ph, "[^\\w]")
        matches <- gregexpr(regexp, insStr, perl = TRUE)
        matches <- unlist(matches)
        stopifnot(length(matches) == 1, all(matches != -1))
        matches 
    }))

    data <- data[, names(name.pos)]

    print("running query:")
    print(insStr)
    print("using data:")
    print(data)

    dbGetQuery(con, insStr, data)
}

insStr <- "insert into RORACLE_TEST values(:row1, :row2)"
custom_dbGetQuery(con, insStr, data.frame(row2 = 0, row1 = 1))

# Output:
# [1] "running query:"
# [1] "insert into RORACLE_TEST values(:row1, :row2)"
# [1] "using data:"
#   row1 row2
# 1    1    0

Answer 2

受sqlInterpolate启发的tidyverse解决方案(谢谢@Scarabee!)：

readr::read_file('query.sql') %>%
stringr::str_replace_all(., ':','?') %>%
DBI::sqlInterpolate(con, ., row2 = 0, row1 = 1) %>%
DBI::dbGetQuery(con, .)