binding parameters fails with input from dbQuoteLiteral

[bhogan-mitre]

There appears to be something incompatible between dbQuoteLiteral and the params that get passed through from dbGetQuery or dbExecute to dbBind.

I would like to both provide parameter values at execution time and have those values be quoted and escaped. If there is a supported way to do this please share an example, but it seems like the following snippet should work.

library(RPostgres)

con <- dbConnect(RPostgres::Postgres())
dbWriteTable(con, "mtcars", head(mtcars), temporary = TRUE)

# works
dbGetQuery(con, "select * from mtcars where disp > $1;", 
           params = list(200))
#>    mpg cyl disp  hp drat    wt  qsec vs am gear carb
#> 1 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
#> 2 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
#> 3 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1

# fails
dbGetQuery(con, "select * from mtcars where disp > $1;", 
           params = list(dbQuoteLiteral(con, 200)))
#> Error: Failed to fetch row: ERROR:  invalid input syntax for type double precision: "200::float8"
#> CONTEXT:  unnamed portal parameter $1 = '...'

Created on 2022-09-13 with [reprex v2.0.2](https://reprex.tidyverse.org/)

sessionInfo()
#> R version 4.0.4 (2021-02-15)
#> Platform: x86_64-apple-darwin17.0 (64-bit)
#> Running under: macOS Big Sur 10.16
#> 
#> Matrix products: default
#> BLAS:   /Library/Frameworks/R.framework/Versions/4.0/Resources/lib/libRblas.dylib
#> LAPACK: /Library/Frameworks/R.framework/Versions/4.0/Resources/lib/libRlapack.dylib
#> 
#> locale:
#> [1] en_US.UTF-8/en_US.UTF-8/en_US.UTF-8/C/en_US.UTF-8/en_US.UTF-8
#> 
#> attached base packages:
#> [1] stats     graphics  grDevices utils     datasets  methods   base     
#> 
#> other attached packages:
#> [1] RPostgres_1.4.4
#> 
#> loaded via a namespace (and not attached):
#>  [1] Rcpp_1.0.9        pillar_1.7.0      compiler_4.0.4    highr_0.9        
#>  [5] R.methodsS3_1.8.1 R.utils_2.11.0    tools_4.0.4       bit_4.0.4        
#>  [9] digest_0.6.29     lubridate_1.8.0   evaluate_0.15     lifecycle_1.0.1  
#> [13] tibble_3.1.6      R.cache_0.15.0    pkgconfig_2.0.3   rlang_1.0.2      
#> [17] reprex_2.0.2      cli_3.4.0         DBI_1.1.3         rstudioapi_0.13  
#> [21] yaml_2.3.5        xfun_0.30         fastmap_1.1.0     withr_2.5.0      
#> [25] styler_1.6.2      stringr_1.4.0     knitr_1.37        generics_0.1.2   
#> [29] hms_1.1.1         fs_1.5.2          vctrs_0.4.1       bit64_4.0.5      
#> [33] glue_1.6.2        fansi_1.0.3       rmarkdown_2.11    blob_1.2.2       
#> [37] purrr_0.3.4       magrittr_2.0.3    backports_1.4.0   ellipsis_0.3.2   
#> [41] htmltools_0.5.2   utf8_1.2.2        stringi_1.7.6     crayon_1.5.1     
#> [45] R.oo_1.24.0

Note that the same statement works fine when swapping in a connection from RSQLite, so I think the issue is here with RPostgres rather than with DBI. Please let me know if the real issue is elsewhere.

library(RSQLite)

con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(con, "mtcars", head(mtcars), temporary = TRUE)

# works
dbGetQuery(con, "select * from mtcars where disp > $1;", 
           params = list(dbQuoteLiteral(con, 200)))
#>    mpg cyl disp  hp drat    wt  qsec vs am gear carb
#> 1 21.4   6  258 110 3.08 3.215 19.44  1  0    3    1
#> 2 18.7   8  360 175 3.15 3.440 17.02  0  0    3    2
#> 3 18.1   6  225 105 2.76 3.460 20.22  1  0    3    1
Created on 2022-09-13 with [reprex v2.0.2](https://reprex.tidyverse.org/)

[krlmlr]

Thanks. dbQuoteLiteral() is for composing SQL with paste0() and friends, dbBind() expects R objects. Does that help?

[bhogan-mitre]

Thank you! That does help. Feel free to close this, or keep open if you think that's worth clarifying in documentation.