参数化查询不适用于SQL Server 2017
参数化查询不适用于SQL Server 2017
提问于 2019-03-12 01:52:55
问题描述和预期结果
我正在按照这里提供的文档在我的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"))
新的可重现的例子
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 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)回答 1
Stack Overflow用户
发布于 2019-03-13 06:35:15
对于dbGetQuery()和dbExecute()，该参数称为params。这是不幸的，可能会发生变化。
GitHub问题：https://github.com/r-dbi/DBI/issues/235。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/55107679
复制相关文章
相似问题
腾讯云开发者
