Allow the user to override parameter metadata

[erikvona]

Currently, the implementation for SQL server does not allow for unicode support when writing tables, quoting literals, or using parameters.

This leads to tables being created without unicode support or special unicode characters, and queries being created with literals without special unicode characters, leading to a comparison with a non-ascii unicode character returning the wrong results.

There have been some issues, like https://github.com/r-dbi/DBI/issues/215, where it's suggested to encode strings as bytes, but that results in fragile code that relies on specific SQL server settings and locales, and only solves specific issues like writing tables. I'd like to write code that "just works" if at all possible.

library(DBI)
#> Warning: package 'DBI' was built under R version 4.1.3
library(odbc)
conn <- dbConnect(odbc(), driver = "ODBC Driver 17 for SQL Server", server = "localhost", database = "example", trusted_connection = "yes", encoding = "utf-8")
unicode_string <- "\u2915"
odbc::dbQuoteString(conn, unicode_string) # Expected: <SQL> N'⤕'
#> <SQL> '<U+2915>'
odbc::odbcDataType(conn, unicode_string) # Expected: nvarchar(255)
#> [1] "varchar(255)"
dbGetQuery(conn, "SELECT ?", params = list(unicode_string)) # Expected: 1  '⤕'
#>    
#> 1 ?

# Note that the connection does support it and properly returns the expected character
dbGetQuery(conn, "SELECT N'\u2915'")
#>           
#> 1 <U+2915>

^{Created on 2022-11-04 with reprex v2.0.2}

Session info

sessioninfo::session_info()
#> - Session info ---------------------------------------------------------------
#>  setting  value
#>  version  R version 4.1.2 (2021-11-01)
#>  os       Windows 10 x64 (build 19044)
#>  system   x86_64, mingw32
#>  ui       RTerm
#>  language (EN)
#>  collate  Dutch_Netherlands.1252
#>  ctype    Dutch_Netherlands.1252
#>  tz       Europe/Berlin
#>  date     2022-11-04
#>  pandoc   2.19.2 @ H:/Programs/RStudio/bin/quarto/bin/tools/ (via rmarkdown)
#> 
#> - Packages -------------------------------------------------------------------
#>  package     * version date (UTC) lib source
#>  bit           4.0.4   2020-08-04 [1] CRAN (R 4.0.2)
#>  bit64         4.0.5   2020-08-30 [1] CRAN (R 4.0.2)
#>  blob          1.2.3   2022-04-10 [1] CRAN (R 4.1.3)
#>  cli           3.4.1   2022-09-23 [1] CRAN (R 4.1.3)
#>  DBI         * 1.1.3   2022-06-18 [1] CRAN (R 4.1.3)
#>  digest        0.6.29  2021-12-01 [1] CRAN (R 4.1.2)
#>  ellipsis      0.3.2   2021-04-29 [1] CRAN (R 4.1.0)
#>  evaluate      0.17    2022-10-07 [1] CRAN (R 4.1.3)
#>  fansi         1.0.3   2022-03-24 [1] CRAN (R 4.1.3)
#>  fastmap       1.1.0   2021-01-25 [1] CRAN (R 4.0.3)
#>  fs            1.5.2   2021-12-08 [1] CRAN (R 4.1.2)
#>  glue          1.6.2   2022-02-24 [1] CRAN (R 4.1.3)
#>  highr         0.9     2021-04-16 [1] CRAN (R 4.1.0)
#>  hms           1.1.2   2022-08-19 [1] CRAN (R 4.1.3)
#>  htmltools     0.5.3   2022-07-18 [1] CRAN (R 4.1.3)
#>  knitr         1.40    2022-08-24 [1] CRAN (R 4.1.3)
#>  lifecycle     1.0.3   2022-10-07 [1] CRAN (R 4.1.3)
#>  magrittr      2.0.3   2022-03-30 [1] CRAN (R 4.1.3)
#>  odbc        * 1.3.3   2021-11-30 [1] CRAN (R 4.1.2)
#>  pillar        1.8.1   2022-08-19 [1] CRAN (R 4.1.3)
#>  pkgconfig     2.0.3   2019-09-22 [1] CRAN (R 4.0.3)
#>  purrr         0.3.5   2022-10-06 [1] CRAN (R 4.1.3)
#>  R.cache       0.16.0  2022-07-21 [1] CRAN (R 4.1.3)
#>  R.methodsS3   1.8.2   2022-06-13 [1] CRAN (R 4.1.3)
#>  R.oo          1.25.0  2022-06-12 [1] CRAN (R 4.1.3)
#>  R.utils       2.12.0  2022-06-28 [1] CRAN (R 4.1.3)
#>  Rcpp          1.0.9   2022-07-08 [1] CRAN (R 4.1.3)
#>  reprex        2.0.2   2022-08-17 [1] CRAN (R 4.1.3)
#>  rlang         1.0.6   2022-09-24 [1] CRAN (R 4.1.3)
#>  rmarkdown     2.17    2022-10-07 [1] CRAN (R 4.1.3)
#>  rstudioapi    0.14    2022-08-22 [1] CRAN (R 4.1.3)
#>  sessioninfo   1.2.2   2021-12-06 [1] CRAN (R 4.1.2)
#>  stringi       1.7.8   2022-07-11 [1] CRAN (R 4.1.2)
#>  stringr       1.4.1   2022-08-20 [1] CRAN (R 4.1.3)
#>  styler        1.7.0   2022-03-13 [1] CRAN (R 4.1.3)
#>  tibble        3.1.8   2022-07-22 [1] CRAN (R 4.1.3)
#>  utf8          1.2.2   2021-07-24 [1] CRAN (R 4.1.0)
#>  vctrs         0.4.2   2022-09-29 [1] CRAN (R 4.1.3)
#>  withr         2.5.0   2022-03-03 [1] CRAN (R 4.1.3)
#>  xfun          0.33    2022-09-12 [1] CRAN (R 4.1.3)
#>  yaml          2.3.5   2022-02-21 [1] CRAN (R 4.1.3)
#> 
#>  [1] C:/r-checkpoint/no-checkpoint
#>  [2] \\vf-d3-home/d3home$/egjvonasmuth/Programs/R/R-4.1.2/library
#> 
#> ------------------------------------------------------------------------------