how to use parameterized query using R and Rpostgres so as to accept values contained in a variable as input to the psql

Question

I want to pass values contained in variables as inputs to a postgresql query using R.

Currently I'm following where it gives as example:
https://db.rstudio.com/best-practices/run-queries-safely/

Using a parameterised query with DBI requires three steps.

You create a query containing a ? placeholder and send it to the database with dbSendQuery():

airport <- dbSendQuery(con, "SELECT * FROM airports WHERE faa = ?")
Use dbBind() to execute the query with specific values, then dbFetch() to get the results:

dbBind(airport, list("GPT"))
dbFetch(airport)
##   faa            name      lat       lon alt tz dst
## 1 GPT Gulfport-Biloxi 30.40728 -89.07011  28 -6   A
Once you’re done using the parameterised query, clean it up by calling dbClearResult()

dbClearResult(airport)

Here is my current setup.


install.packages("RPostgres")
#https://github.com/r-dbi/RPostgres
require(RPostgres)
require(DBI)
require(tidyr)
# RPostgreSQL::PostgreSQL()

# make connection
con <- dbConnect(RPostgres::Postgres(), dbname = 'test', 
                      host = 'mydbtest.com',
                      port = 1234, # or any other port specified by your DBA
                      user = 'test',
                      password = 'test')

rs = dbGetQuery(con, "select count(*),state from sales where created > ? and created < ? group by state")

What I want to do: Pass two dates as inputs to the query.

Error I get:

> rs = dbGetQuery(prod_con, "select count(*),state from sales where created > ? and created < ? group by state")
Error in result_create(conn@ptr, statement) : 
  Failed to prepare query: ERROR:  syntax error at or near "and"
LINE 1: ...count(*),state from sales where created > ? and create...

Question1 How do I get around this error, and what is causing it? I'm using the ? placeholder as given in the example.

Question 2 How do I pass multiple values to the two ?s like this

dbBind(con, list("2019-06-21","2019-06-22")) ?

References:
how to pass value stored in r variable to a column in where clause of postgresql query in R

RPostgreSQL - Passing Parameter in R to a Query in RPostgreSQL

Jake · Answer 1 · 2020-12-15T22:26:34.937

Answer to question 1

Two reasons why it doesn't work. The syntax error you get is due to a known issue with RPostgresql. You can resolve by replacing ? with $.

And after fixing the syntax, you must bind the parameters. Refer to fixed version of example in this answer.

Answer to question 2

You can pass multiple parameters in a query by appending sequential integers to the $ signs. ie: SELECT * FROM table where integercolumn > $1 and stringcolumn = $2.

Remember to bind those parameters after!

Fixed version of example posted in question

install.packages("RPostgres")
require(RPostgres)
require(DBI)
require(tidyr)
# RPostgreSQL::PostgreSQL()

# MAKE CONNECTION
con <- dbConnect(RPostgres::Postgres(),
    dbname = 'test', 
    host = 'mydbtest.com',
    port = 1234, # or any other port specified by your DBA
    user = 'test',
    password = 'test')

# SEND QUERY
rs = dbSendQuery(con, "SELECT count(*), state FROM sales WHERE created > $1 and created < $2 GROUP BY state")

# BIND PARAMETERS
dbBind(rs, list(date1, date2)) # where date1 and date2 are your defined variables or raw date values

# FETCH FROM DB USING QUERY
rows <- dbFetch(rs)

# CLEAR RESULT
dbClearResult(rs)

Note that when binding parameters $1 corresponds to date1.

this is my first stack overflow answer so an upvote would help me a lot if this helps you! -Jake

Croote · Answer 2 · 2019-06-24T00:48:19.320

Parametrized queries are best done using bind -> send -> fetch.

So Where you have used the ? expression in your dbGetQuery, this would be done in the

rs = dbSendQuery(con, "select count(*), state 
                       from sales 
                       where created > ? and created < ? group by state")

Then you would Bind your parameters to the Query dbBindQuery(rs, list(blah, blah)) Finally you dbFetchQuery(rs).

So your frame work would look like;

rs <- dbSendQuery(...) 

dbBindQuery(rs, ...)

dbFetchQuery(rs)

dbClearResult(rs)

This can all be found on the Rstudio website (You are using already) Here Which you have already noted.

So in response to

Question 1: You bind your parameters in dbBindQuery on an object returned from dbSendQuery not dbGetQuery.

Question 2: I am not as sure on this but your parameters are passed in a list. There are many examples online of people showing how to speed up your Queries with parametrisation. Here is one such example

how to use parameterized query using R and Rpostgres so as to accept values contained in a variable as input to the psql

2 Answers2