R，dbGetQuery()将不会为在params中传递的所有值选择记录

Question

我试图让dbGetQuery从一个SybaseIQ表(test_in)中检索char (test_ID2)中引用的所有行。结果是只检索test_ID2中的第一个条目。

dbGetQuery()如下所示：

test_con <- DBI::dbConnect(odbc::odbc(),
                           driver = 'SybaseIQ',
                           host = 'xxx.xx.xxx',
                           port = 'xxxx',
                           uid = 'xxxx',
                           pwd = 'xxxx')

test_out <- dbGetQuery(test_con,"SELECT * FROM test_in WHERE ID2 = ?", 
                           params = list(test_ID2))

test_in看起来是这样的：

> test_in
          ID1       DATE       ID2  QTY
115 18383M472 2017-02-01 93964W108  594
116 18383M472 2017-02-01 939653101  254
117 18383M472 2017-02-01 948741103  437
118 18383M472 2017-02-01 95040Q104 1236
119 25459W458 2017-02-01 G5876H105 4542
120 25459W458 2017-02-01 N07059210  557
121 25459W458 2017-02-01 N6596X109 1205
122 25459W458 2017-02-01 Y09827109 1401
123 25459W458 2017-02-01 007903107 8223
124 25459W458 2017-02-01 032654105 1609
125 25459W458 2017-02-01 038222105 3709

test_ID2看起来是这样的：

> test_ID2
[1] "939653101" "N6596X109" "N99999999"

以上dbGetQuery()的结果仅包含一个条目

> test_out
          ID1       DATE       ID2 QTY
116 18383M472 2017-02-01 939653101 254

它漏掉了ID2 = "N6596X109“的条目。

我做错什么了？

谢谢你的想法！

更新

似乎起作用的是使用glue_sql()以及dbSendQuery()和dbFetch()。

test_ID2 <- as.character(test_ID2)

detail_qry <- glue_sql("select * from test_in where ID2 IN ({ID2*})",
                       ID2 = test_ID2, .con = test_con)

details <- dbSendQuery(test_con,detail_qry)

test_out <- dbFetch(details)

我仍然很好奇为什么前面的代码不能工作。这似乎更直接。

Answer 1

在SQL中，可以更有效地重用准备好的语句。这样，使用绑定参数的准备语句就会受益，所以当您需要重用查询时，使用数据准备不足的语句可以利用这一点。(无可否认，这里有个小小的挥手.现在，我将提供这个作为glue_sql的替代方案。)

从普通的SQL查询开始，演示如何提供简单的集合(使用params=)。

# con <- DBI::dbConnect(...)
DBI::dbGetQuery(con, "select 1 where 1 in (1,2)")
#    
# 1 1
DBI::dbGetQuery(con, "select 1 where 1 in (?,?)", params=list(1,2))
#    
# 1 1
DBI::dbGetQuery(con, "select 1 where 1 in (?,?)", params=list(2,3))
# [1] 
# <0 rows> (or 0-length row.names)

让我们将其正式化一点：

DBI::dbWriteTable(con, "testcars", mtcars)
cyls <- c(4, 6)
qry <- sprintf("select * from testcars where cyl in (%s)", paste(rep("?", length(cyls)), collapse = ","))
qry
# [1] "select * from testcars where cyl in (?,?)"

通常，R用户只会使用DBI::dbGetQuery进行一次性查询，可以选择绑定参数.但是，如果您有详细的查询并且/或需要提高查询速度，则可以执行多步操作。(目前我无法直接量化这一差异。)这些步骤通常如下：

向query/statement;

• Optionally绑定parameters;

• Fetch发送数据；向结果集发送

1. .

res <- DBI::dbSendQuery(con, qry)
res
# <OdbcResult>
#   SQL  select * from testcars where cyl in (?,?)
#   ROWS Fetched: 0 [complete]
#        Changed: 0
res2 <- DBI::dbBind(res, list(2, 4))
DBI::dbFetch(res2)
#         row_names  mpg cyl  disp  hp drat    wt  qsec vs am gear carb
# 1      Datsun 710 22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1
# 2       Merc 240D 24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
# 3        Merc 230 22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2
# 4        Fiat 128 32.4   4  78.7  66 4.08 2.200 19.47  1  1    4    1
# 5     Honda Civic 30.4   4  75.7  52 4.93 1.615 18.52  1  1    4    2
# 6  Toyota Corolla 33.9   4  71.1  65 4.22 1.835 19.90  1  1    4    1
# 7   Toyota Corona 21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
# 8       Fiat X1-9 27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
# 9   Porsche 914-2 26.0   4 120.3  91 4.43 2.140 16.70  0  1    5    2
# 10   Lotus Europa 30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
# 11     Volvo 142E 21.4   4 121.0 109 4.11 2.780 18.60  1  1    4    2

而且我们可以通过绑定新参数(没有新的查询)重用该查询(特别是res值集)：

res2 <- DBI::dbBind(res, list(6, 7))
DBI::dbFetch(res2)
#        row_names  mpg cyl  disp  hp drat    wt  qsec vs am gear carb
# 1      Mazda RX4 21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4
# 2  Mazda RX4 Wag 21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
# 3 Hornet 4 Drive 21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
# 4        Valiant 18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1
# 5       Merc 280 19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4
# 6      Merc 280C 17.8   6 167.6 123 3.92 3.440 18.90  1  0    4    4
# 7   Ferrari Dino 19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6
DBI::dbClearResult(res2)