You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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 = '...'Createdon2022-09-13with [reprexv2.0.2](https://reprex.tidyverse.org/)
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/)
The text was updated successfully, but these errors were encountered:
There appears to be something incompatible between
dbQuoteLiteral
and the params that get passed through fromdbGetQuery
ordbExecute
todbBind
.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.
Note that the same statement works fine when swapping in a connection from
RSQLite
, so I think the issue is here withRPostgres
rather than withDBI
. Please let me know if the real issue is elsewhere.The text was updated successfully, but these errors were encountered: