clarify 'immediate=' verbiage

Hey everybody, long time no chat! Is anyone going to an in-person R or INFORMS conference any time soon? I think I owe many of you a beer or something :-)

(This might be somewhat of a dupe of #127, but I think I'm geared more towards documentation here.)

Issue Description and Expected Result

The verbiage for immediate= suggests that the driver auto-reattempts with the other API if the first attempt fails. I'm having a hard time understanding when to specific immediate=, and consequences of using it incorrectly.

Namely, using a simple query on a local table,

qry <- "select top 1 * from tablename t where t.Id=?"
prms <- list("203A8526-2992-EB11-A2DD-D094661AB20C")
DBI::dbGetQuery(con, qry, params=prms)
## WORKS
DBI::dbGetQuery(con, qry, params=prms, immediate=FALSE)
## WORKS
DBI::dbGetQuery(con, qry, params=prms, immediate=TRUE)
# Error in new_result(connection@ptr, statement, immediate) : 
#   nanodbc/nanodbc.cpp:1594: 07002: [Microsoft][ODBC Driver 17 for SQL Server]COUNT field incorrect or syntax error

Okay, now a slightly different query:

qry <- "select top 1 * into #sometemp from tablename"

DBI::dbExecute(con, qry)
# [1] 1
DBI::dbGetQuery(con, "select * from #sometemp")
# Error: nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name '#sometemp'.  [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. 
# <SQL> 'select * from #sometemp'

DBI::dbExecute(con, qry, immediate=FALSE)
# [1] 1
DBI::dbGetQuery(con, "select * from #sometemp")
# Error: nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name '#sometemp'.  [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Statement(s) could not be prepared. 
# <SQL> 'select * from #sometemp'

DBI::dbExecute(con, qry, immediate=TRUE)
# [1] 1
DBI::dbGetQuery(con, "select * from #sometemp")
## WORKS
DBI::dbExecute(con, "drop table #sometemp")
# [1] 0

From the verbiage in the ?DBI::dbGetQuery section on "Specification for the 'immediate' argument", I would have thought that I could trust that the backend would handle this automatically for me.

I know I don't fully grok the nuances of immediate=, so I don't think that immediate= is misbehaving. Perhaps it's just my interpretation of the documentation.

Database

DBI::dbGetQuery(con, "select @@version")[[1]]
# [1] "Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) \n\tMar 18 2018 09:11:49 \n\tCopyright (c) Microsoft Corporation\n\tStandard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )\n"

Reproducible Example

Session Info

```r sessioninfo::session_info() # - Session info -------------------------------------------------------------- # hash: grinning cat with smiling eyes, flag: Panama, locked with pen # setting value # version R version 4.1.2 (2021-11-01) # os Windows 10 x64 (build 22000) # system x86_64, mingw32 # ui RTerm # language (EN) # collate English_United States.1252 # ctype English_United States.1252 # tz America/New_York # date 2022-02-19 # pandoc 2.17.1.1 @ C:/Users/r2/AppData/Local/Pandoc/ (via rmarkdown) # - Packages ------------------------------------------------------------------- # package * version date (UTC) lib source # bit 4.0.4 2020-08-04 [1] CRAN (R 4.1.2) # bit64 4.0.5 2020-08-30 [1] CRAN (R 4.1.2) # blob 1.2.2 2021-07-23 [1] CRAN (R 4.1.2) # cli 3.1.0 2021-10-27 [1] CRAN (R 4.1.2) # DBI * 1.1.2 2021-12-20 [1] CRAN (R 4.1.2) # digest 0.6.28 2021-09-23 [1] CRAN (R 4.1.2) # ellipsis 0.3.2 2021-04-29 [1] CRAN (R 4.1.2) # evaluate 0.14 2019-05-28 [1] CRAN (R 4.1.2) # fastmap 1.1.0 2021-01-25 [1] CRAN (R 4.1.2) # hms 1.1.1 2021-09-26 [1] CRAN (R 4.1.2) # htmltools 0.5.2 2021-08-25 [1] CRAN (R 4.1.2) # knitr 1.36 2021-09-29 [1] CRAN (R 4.1.2) # lifecycle 1.0.1 2021-09-24 [1] CRAN (R 4.1.2) # odbc * 1.3.3 2021-11-30 [1] CRAN (R 4.1.2) # pkgconfig 2.0.3 2019-09-22 [1] CRAN (R 4.1.2) # r2 * 0.9.13 2022-02-03 [1] local # Rcpp 1.0.7 2021-07-07 [1] CRAN (R 4.1.2) # rlang 1.0.1 2022-02-03 [1] CRAN (R 4.1.2) # rmarkdown 2.11 2021-09-14 [1] CRAN (R 4.1.2) # sessioninfo 1.2.1 2021-11-02 [1] CRAN (R 4.1.2) # vctrs 0.3.8 2021-04-29 [1] CRAN (R 4.1.2) # xfun 0.29 2021-12-14 [1] CRAN (R 4.1.2) # [1] C:/Users/r2/R/win-library/4.1 # [2] C:/R/R-4.1.2/library # ------------------------------------------------------------------------------ ```

r-dbi / odbc