invalid descriptor index fails with param-binding

[r2evans]

Issue Description and Expected Result

This is a continuation of #358, #309, and several others.

The recent fixes seem to work well with "regular" text, but when we introduce bound parameters, it fails.

Database

SQL Server 2017, running on docker. Versions:

DBI::dbGetQuery(conss, "select @@version")
#                                                                                                                                                                                                      
# 1 Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64) \n\tNov 30 2018 12:57:58 \n\tCopyright (C) 2017 Microsoft Corporation\n\tDeveloper Edition (64-bit) on Linux (Ubuntu 16.04.5 LTS)

str(DBI::dbGetInfo(conss))
# List of 15
#  $ dbname                       : chr "master"
#  $ dbms.name                    : chr "Microsoft SQL Server"
#  $ db.version                   : chr "14.00.3048"
#  $ username                     : chr "dbo"
#  $ host                         : chr ""
#  $ port                         : chr ""
#  $ sourcename                   : chr ""
#  $ servername                   : chr "c755900f6200"
#  $ drivername                   : chr "msodbcsql17.dll"
#  $ odbc.version                 : chr "03.80.0000"
#  $ driver.version               : chr "17.06.0001"
#  $ odbcdriver.version           : chr "03.80"
#  $ supports.transactions        : logi TRUE
#  $ getdata.extensions.any_column: logi FALSE
#  $ getdata.extensions.any_order : logi FALSE
#  - attr(*, "class")= chr [1:3] "Microsoft SQL Server" "driver_info" "list"

Reproducible Example

# docker run -d -p "31433:1433" --name testss -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=Mysecretpassword1" -e "MSSQL_PID=Developer" --cap-add SYS_PTRACE microsoft/mssql-server-linux
library(DBI)
conss <- DBI::dbConnect(drv = odbc::odbc(), driver = "ODBC Driver 17 for SQL Server", 
                        server = "127.0.0.1,31433", database = "master", include_password = TRUE, 
                        uid = "SA", pwd = "Mysecretpassword1")

DBI::dbExecute(conss, "
  create table quux1 (
    id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() PRIMARY KEY NOT NULL,
    txt1 nvarchar(max),
    num1 float
  )")
DBI::dbWriteTable(conss, "quux1", data.frame(txt1 = "hello", num1 = pi), append = TRUE)
DBI::dbGetQuery(conss, "select * from quux1")
#                                     id  txt1     num1
# 1 4DFE423E-F36B-1410-8E36-00576F6E978E hello 3.141593
DBI::dbGetQuery(conss, "select * from quux1 where num1 > 3")
#                                     id  txt1     num1
# 1 4DFE423E-F36B-1410-8E36-00576F6E978E hello 3.141593

SFSG. If we use bind-params without column-order being a factor, it works:

DBI::dbGetQuery(conss, "select id from quux1 where num1 > ?", params = list(3))
#                                     id
# 1 4DFE423E-F36B-1410-8E36-00576F6E978E

But the same query with the column-order issue fails:

DBI::dbGetQuery(conss, "select * from quux1 where num1 > ?", params = list(3))
# Error in result_fetch(res@ptr, n) : 
#   nanodbc/nanodbc.cpp:3069: 07009: [Microsoft][ODBC Driver 17 for SQL Server]Invalid Descriptor Index 
#     x
#  1. +-DBI::dbGetQuery(...)
#  2. +-DBI::dbGetQuery(...)
#  3. | \-odbc:::.local(conn, statement, ...)
#  4. |   +-DBI::dbFetch(rs, n = n, ...)
#  5. |   \-odbc::dbFetch(rs, n = n, ...)
#  6. |     \-odbc:::result_fetch(res@ptr, n)
#  7. \-base::stop(...)
# Warning in dbClearResult(rs) : Result already cleared

Session Info

devtools::session_info()
# - Session info ---------------------------------------------------------------
#  setting  value                       
#  version  R version 4.0.3 (2020-10-10)
#  os       Windows 10 x64              
#  system   x86_64, mingw32             
#  ui       RTerm                       
#  language (EN)                        
#  collate  English_United States.1252  
#  ctype    English_United States.1252  
#  tz       America/New_York            
#  date     2020-12-18                  
# - Packages -------------------------------------------------------------------
#  package     * version  date       lib source        
#  assertthat    0.2.1    2019-03-21 [1] CRAN (R 4.0.0)
#  backports     1.1.6    2020-04-05 [1] CRAN (R 4.0.0)
#  bit           1.1-15.2 2020-02-10 [1] CRAN (R 4.0.0)
#  bit64         0.9-7    2017-05-08 [1] CRAN (R 4.0.0)
#  blob          1.2.1    2020-01-20 [1] CRAN (R 4.0.0)
#  callr         3.4.3    2020-03-28 [1] CRAN (R 4.0.0)
#  cli           2.0.2    2020-02-28 [1] CRAN (R 4.0.0)
#  crayon        1.3.4    2017-09-16 [1] CRAN (R 4.0.0)
#  DBI           1.1.0    2019-12-15 [1] CRAN (R 4.0.0)
#  desc          1.2.0    2018-05-01 [1] CRAN (R 4.0.0)
#  devtools      2.3.0    2020-04-10 [1] CRAN (R 4.0.0)
#  digest        0.6.25   2020-02-23 [1] CRAN (R 4.0.0)
#  dplyr         1.0.2    2020-08-18 [1] CRAN (R 4.0.2)
#  ellipsis      0.3.0    2019-09-20 [1] CRAN (R 4.0.0)
#  evaluate      0.14     2019-05-28 [1] CRAN (R 4.0.0)
#  fansi         0.4.1    2020-01-08 [1] CRAN (R 4.0.0)
#  fs            1.4.1    2020-04-04 [1] CRAN (R 4.0.0)
#  generics      0.0.2    2018-11-29 [1] CRAN (R 4.0.0)
#  glue          1.4.0    2020-04-03 [1] CRAN (R 4.0.0)
#  hms           0.5.3    2020-01-08 [1] CRAN (R 4.0.0)
#  htmltools     0.5.0    2020-06-16 [1] CRAN (R 4.0.2)
#  knitr         1.28     2020-02-06 [1] CRAN (R 4.0.0)
#  lifecycle     0.2.0    2020-03-06 [1] CRAN (R 4.0.0)
#  magrittr      2.0.1    2020-11-17 [1] CRAN (R 4.0.3)
#  memoise       1.1.0    2017-04-21 [1] CRAN (R 4.0.0)
#  odbc          1.3.0    2020-10-27 [1] CRAN (R 4.0.3)
#  pillar        1.4.4    2020-05-05 [1] CRAN (R 4.0.0)
#  pkgbuild      1.0.8    2020-05-07 [1] CRAN (R 4.0.0)
#  pkgconfig     2.0.3    2019-09-22 [1] CRAN (R 4.0.0)
#  pkgload       1.0.2    2018-10-29 [1] CRAN (R 4.0.0)
#  prettyunits   1.1.1    2020-01-24 [1] CRAN (R 4.0.0)
#  processx      3.4.2    2020-02-09 [1] CRAN (R 4.0.0)
#  ps            1.3.2    2020-02-13 [1] CRAN (R 4.0.0)
#  purrr         0.3.4    2020-04-17 [1] CRAN (R 4.0.0)
#  r2          * 0.9.10   2020-12-10 [1] local         
#  R6            2.4.1    2019-11-12 [1] CRAN (R 4.0.0)
#  Rcpp          1.0.4.6  2020-04-09 [1] CRAN (R 4.0.0)
#  remotes       2.1.1    2020-02-15 [1] CRAN (R 4.0.0)
#  rlang         0.4.7    2020-07-09 [1] CRAN (R 4.0.2)
#  rmarkdown     2.4      2020-09-30 [1] CRAN (R 4.0.2)
#  rprojroot     1.3-2    2018-01-03 [1] CRAN (R 4.0.0)
#  sessioninfo   1.1.1    2018-11-05 [1] CRAN (R 4.0.0)
#  testthat      2.3.2    2020-03-02 [1] CRAN (R 4.0.0)
#  tibble        3.0.1    2020-04-20 [1] CRAN (R 4.0.0)
#  tidyselect    1.1.0    2020-05-11 [1] CRAN (R 4.0.0)
#  usethis       1.6.1    2020-04-29 [1] CRAN (R 4.0.0)
#  vctrs         0.3.4    2020-08-29 [1] CRAN (R 4.0.2)
#  withr         2.2.0    2020-04-20 [1] CRAN (R 4.0.0)
#  xfun          0.13     2020-04-13 [1] CRAN (R 4.0.0)
# [1] C:/Users/r2/R/win-library/4.0
# [2] C:/R/R-4.0.3/library

This is with the CRAN release of odbc, but I also just installed the github version 1.3.0.9000 and get the same results.

[detule]

Thanks @r2evans !

Haven't had a chance to check/test but detule@e833f18 should, in theory address this. It moves the the "fix" to a location where it should hopefully get picked up for SELECTs that are prepared (in addition to those that get executed immediately).

Will take a closer look next week unless someone beats me to it.

[r2evans]

Thank you thank you thank you thank you thank you thank you thank you thank you!

Celebratory music, confetti cannons, crowds screaming, gaiety abound. Christmas and Kwanzaa come early with this commit, and though it may be a few days late for Chanukah, I think celebration will still be had by many.

(If it isn't obvious by now, I'm very grateful for your efforts, individually and collectively. Thanks @detule, @jimhester, @krlmlr, and all others who are a part of this. Happy Holidays, everybody!)