Parameterized Queries Not Working with SQL Server 2017

Question

Issue description and expected result

I am trying out parameterized queries on my SQL Server following the documentation provided here: https://cran.r-project.org/web/packages/DBI/DBI.pdf. However, it is not working for me.

Specifically when I write a query to insert some rows using parameters, the value returned is 0 indicating no rows were affected. In the example I show below, the value should be 2 because 2 rows are going to be affected.

Furthermore, when I go to query the entire table, the rows I was supposed to have inserted using parameters do not show up.

Looking in the documentation I mention above in the section on parameterizing queries, I noticed SQL Server isn't mentioned. Are parameterized queries available for SQL Server?

Database: SQL Server 2017

EDIT

I got it somewhat working, but there are still underlying issues. dbExecute() and dbGetQuery() do not work. One needs to use dbSendStatement() or dbSendQuery, then dbBind(), dbFetch(), dbClearStatement(). Example provided below.

Specifically, dbExecute() returns 0 rows as being affected and when the table is queried the rows which were supposed to be inserted were not inserted.

dbGetQuery() returns Error in result_fetch(res@ptr, n, ...) : unused argument (param = list("Joe"))

NEW Reproducible Example

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)

OLD Reproducible example

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

- 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)

Going through the document, I would think the paired values to be inserted should look like this... `list((1, "Mike"), c(2, "Hannah")`. And shouldn't the `ID` values be `4 & 5` respectively for `Mike` and `Hannah`? If you create a primary key for ID, then you would get a duplicate key error. — SS_DBA, Mar 11 '19 at 18:24
Thanks for the response. I am adding edits to my original post to reflect what you found. To respond to you here: 1) Changed the ID values. 2) The way I presented parameterized queries is correct, and hopefully that is reflected in my edited original post. — drizzle123, Mar 12 '19 at 15:40
This is still relevant 16/6/2020. `dbExecute()` does not work with SQL server. — vahvero, Jun 16 '20 at 14:30

score 0 · Answer 1 · answered Mar 12 '19 at 22:35

0

For dbGetQuery() and dbExecute(), the argument is called params . This is unfortunate, and subject to change.

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

answered Mar 12 '19 at 22:35

krlmlr

25,056
14
120
217

Unfortunately, I get the same issue in both cases. For dbGetQuery i get the error `Error in result_fetch(res@ptr, n, ...) : unused argument (param = list("Joe"))` if I use the argument "param", or I get the error `Error in result_fetch(res@ptr, n, ...) : unused argument (params = list("Joe"))` if I use the argument "params". For dbExecute(), whether I use param or params, I still get 0 returned and then subsequent queries to the table show the rows were not added. – drizzle123 Mar 15 '19 at 17:54
1

Yes, the _odbc_ package seems to override `dbGetQuery()` . Can you please use `dbBind()` for now and file an issue with the odbc GitHub repo at https://github.com/r-dbi/odbc/issues? Please do check for duplicates before opening a new issue. – krlmlr Mar 16 '19 at 13:03
1

Yeah, I'll be using dbBind() for now, and I submitted the issue on github. https://github.com/r-dbi/odbc/issues/261 – drizzle123 Mar 18 '19 at 21:00
1

@drizzle123: Thanks, very much appreciated! – krlmlr Mar 18 '19 at 21:09