首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >参数化查询不适用于SQL Server 2017

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

Stack Overflow用户
提问于 2019-03-12 01:52:55
回答 1查看 683关注 0票数 3

问题描述和预期结果

我正在按照这里提供的文档在我的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()返回Error in result_fetch(res@ptr, n, ...) : unused argument (param = list("Joe"))

新的可重现的例子

代码语言:javascript
复制
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)

旧的可重现的例子

代码语言:javascript
复制
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")

会话信息

代码语言:javascript
复制
- 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)
EN

回答 1

Stack Overflow用户

发布于 2019-03-13 06:35:15

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

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

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/55107679

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档