r - 参数化查询不适用于 SQL Server 2017

Question

问题描述和预期结果

我正在按照此处提供的文档在我的 SQL Server 上尝试参数化查询:https://cran.r-project.org/web/packages/DBI/DBI.pdf .但是，它对我不起作用。

特别是当我编写查询以使用参数插入一些行时，返回的值为 0，表示没有行受到影响。在下面显示的示例中，该值应为 2，因为将影响 2 行。

此外，当我去查询整个表时，我应该查询的行已插入使用参数不显示。

查看我在上面关于参数化查询的部分中提到的文档，我注意到没有提到 SQL Server。参数化查询是否可用于 SQL Server？

数据库:SQL Server 2017

编辑

我觉得它有点工作了，但仍然存在潜在的问题。 dbExecute() 和 dbGetQuery() 不起作用。需要使用 dbSendStatement() 或 dbSendQuery，然后是 dbBind()、dbFetch()、dbClearStatement()。下面提供了示例。

具体来说，dbExecute() 返回 0 行受影响，而当查询表时，应该插入的行没有插入。

dbGetQuery() 返回 result_fetch(res@ptr, n, ...) 中的错误:未使用的参数 (param = list("Joe"))

新的可重现示例

library(DBI)
library(odbc)
library(dplyr)
library(dbplyr)

con <- dbConnect(odbc(),
                 Driver = "SQL SERVER",
                 Server = SERVER_NAME,
                 Database = DB_NAME,
                 UID = USERNAME,
                 PWD = PASSWORD)


# Create a table and insert some entries
createTable <- dbExecute(con, "CREATE TABLE People (id int NOT NULL, name char(10) NOT NULL)")
insertRows <- dbExecute(con, "INSERT INTO People (id, name) VALUES (1, 'Joe'), (2, 'Mark'), (3, 'Laura')")

# Query the table we just created
query <- dbGetQuery(con, "SELECT * FROM People")

# Parameterized queries do not work using dbExecute and dbSendQuery
# insertRowsParams <- dbExecute(con, "INSERT INTO People (id, name) VALUES (?, ?)", param = list(c(4, 5), c("Mike", "Hannah"))
# query <- dbGetQuery(con, "SELECT * FROM People WHERE name = ?", param = list("Joe"))

# Parameterized queries do work using more primitive, labor-intensive functions
insertRowsParams <- dbSendStatement(con, "INSERT INTO People (id, name) VALUES (?, ?)")
dbBind(insertRowsParams, list(c(4, 5), c("Mike", "Hannah")))
resultStatement <- dbFetch(insertRowsParams)
dbClearResult(insertRowsParams)
query <- dbGetQuery(con, "SELECT * FROM People")

selectRowsParams <- dbSendQuery(con, "SELECT * FROM People WHERE name = ?")
dbBind(selectRowsParams, list("Joe"))
resultQuery <- dbFetch(selectRowsParams)
dbClearResult(selectRowsParams)

旧的可复制示例

library(DBI)
library(odbc)
library(dplyr)
library(dbplyr)

con <- dbConnect(odbc(),
                 Driver = "SQL SERVER",
                 Server = SERVER_NAME,
                 Database = DB_NAME,
                 UID = USERNAME,
                 PWD = PASSWORD)

# Create a table and insert some entries
createTable <- dbExecute(con, "CREATE TABLE People (id int NOT NULL, name char(10) NOT NULL)")
insertRows <- dbExecute(con, "INSERT INTO People (id, name) VALUES (1, 'Joe'), (2, 'Mark'), (3, 'Laura')")

# The value for insertRowsParams is 0 indicating 0 rows were affected
insertRowsParams <- dbExecute(con, "INSERT INTO People (id, name) VALUES (?, ?)", param = list(c(4, 5), c("Mike", "Hannah")))

# Query the table we just created, and there are only 3 rows
query <- dbGetQuery(con, "SELECT * FROM People")

session 信息

- Session info --------------------------------------------------------------------------------------------------------------------------------
 setting  value                       
 version  R version 3.5.2 (2018-12-20)
 os       Windows Server >= 2012 x64  
 system   x86_64, mingw32             
 ui       RStudio                     
 language (EN)                        
 collate  English_United States.1252  
 ctype    English_United States.1252  
 tz       America/New_York            
 date     2019-03-11                  

- Packages ------------------------------------------------------------------------------------------------------------------------------------
 package     * version date       lib source        
 assertthat    0.2.0   2017-04-11 [1] CRAN (R 3.5.2)
 backports     1.1.3   2018-12-14 [1] CRAN (R 3.5.2)
 base64enc     0.1-3   2015-07-28 [1] CRAN (R 3.5.2)
 bit           1.1-14  2018-05-29 [1] CRAN (R 3.5.2)
 bit64         0.9-7   2017-05-08 [1] CRAN (R 3.5.2)
 blob          1.1.1   2018-03-25 [1] CRAN (R 3.5.2)
 callr         3.1.1   2018-12-21 [1] CRAN (R 3.5.2)
 cli           1.0.1   2018-09-25 [1] CRAN (R 3.5.2)
 crayon        1.3.4   2017-09-16 [1] CRAN (R 3.5.2)
 DBI         * 1.0.0   2018-05-02 [1] CRAN (R 3.5.2)
 dbplyr      * 1.3.0   2019-01-09 [1] CRAN (R 3.5.2)
 desc          1.2.0   2018-05-01 [1] CRAN (R 3.5.2)
 devtools      2.0.1   2018-10-26 [1] CRAN (R 3.5.2)
 digest        0.6.18  2018-10-10 [1] CRAN (R 3.5.2)
 dplyr       * 0.8.0.1 2019-02-15 [1] CRAN (R 3.5.2)
 evaluate      0.13    2019-02-12 [1] CRAN (R 3.5.2)
 fansi         0.4.0   2018-10-05 [1] CRAN (R 3.5.2)
 fs            1.2.6   2018-08-23 [1] CRAN (R 3.5.2)
 glue          1.3.0   2018-07-17 [1] CRAN (R 3.5.2)
 hms           0.4.2   2018-03-10 [1] CRAN (R 3.5.2)
 htmltools     0.3.6   2017-04-28 [1] CRAN (R 3.5.2)
 httpuv        1.4.5.1 2018-12-18 [1] CRAN (R 3.5.2)
 jsonlite      1.6     2018-12-07 [1] CRAN (R 3.5.2)
 knitr         1.21    2018-12-10 [1] CRAN (R 3.5.2)
 later         0.8.0   2019-02-11 [1] CRAN (R 3.5.2)
 magrittr      1.5     2014-11-22 [1] CRAN (R 3.5.2)
 memoise       1.1.0   2017-04-21 [1] CRAN (R 3.5.2)
 mime          0.6     2018-10-05 [1] CRAN (R 3.5.2)
 odbc        * 1.1.6   2018-06-09 [1] CRAN (R 3.5.2)
 pillar        1.3.1   2018-12-15 [1] CRAN (R 3.5.2)
 pkgbuild      1.0.2   2018-10-16 [1] CRAN (R 3.5.2)
 pkgconfig     2.0.2   2018-08-16 [1] CRAN (R 3.5.2)
 pkgload       1.0.2   2018-10-29 [1] CRAN (R 3.5.2)
 prettyunits   1.0.2   2015-07-13 [1] CRAN (R 3.5.2)
 processx      3.2.1   2018-12-05 [1] CRAN (R 3.5.2)
 promises      1.0.1   2018-04-13 [1] CRAN (R 3.5.2)
 ps            1.3.0   2018-12-21 [1] CRAN (R 3.5.2)
 purrr       * 0.3.0   2019-01-27 [1] CRAN (R 3.5.2)
 R6            2.4.0   2019-02-14 [1] CRAN (R 3.5.2)
 Rcpp          1.0.0   2018-11-07 [1] CRAN (R 3.5.2)
 remotes       2.0.2   2018-10-30 [1] CRAN (R 3.5.2)
 rlang         0.3.1   2019-01-08 [1] CRAN (R 3.5.2)
 rmarkdown     1.11    2018-12-08 [1] CRAN (R 3.5.2)
 rprojroot     1.3-2   2018-01-03 [1] CRAN (R 3.5.2)
 rstudioapi    0.9.0   2019-01-09 [1] CRAN (R 3.5.2)
 sessioninfo   1.1.1   2018-11-05 [1] CRAN (R 3.5.2)
 shiny       * 1.2.0   2018-11-02 [1] CRAN (R 3.5.2)
 stringi       1.3.1   2019-02-13 [1] CRAN (R 3.5.2)
 stringr       1.4.0   2019-02-10 [1] CRAN (R 3.5.2)
 tibble        2.0.1   2019-01-12 [1] CRAN (R 3.5.2)
 tidyselect    0.2.5   2018-10-11 [1] CRAN (R 3.5.2)
 usethis       1.4.0   2018-08-14 [1] CRAN (R 3.5.2)
 utf8          1.1.4   2018-05-24 [1] CRAN (R 3.5.2)
 withr         2.1.2   2018-03-15 [1] CRAN (R 3.5.2)
 xfun          0.5     2019-02-20 [1] CRAN (R 3.5.2)
 xtable        1.8-3   2018-08-29 [1] CRAN (R 3.5.2)
 yaml          2.2.0   2018-07-25 [1] CRAN (R 3.5.2)

Answer 1

对于 dbGetQuery() 和 dbExecute()，参数称为 params 。这是不幸的，并且可能会发生变化。

GitHub 问题:https://github.com/r-dbi/DBI/issues/235 .