CODEWITHSUNDEEP
rpostgresqlrshiny
Helene
Helene

Reputation: 959

In R, how to pass PostgreSQL column contents to dbGetQuery using param

I am trying to query my PostgreSQL DB in R, using dbGetQuery(). Below are what I have done so far:

This command ran through without issue I have it here just to show how the database was queried

test_db = dbPool(drv = dbDriver("PostgreSQL", max.con = 100),
           dbname = "TEST",
           host = "localhost",
           user = "postgres",
           password = "password",
           idleTimeout = 3600000
        )

This command ran with error

dbGetQuery(test_db, 
       "select * from public.table1 where tag = ?", 
       params = 'tag_col_content1') 

Error in postgresqlExecStatement(conn, statement, ...) : 
RS-DBI driver: (could not Retrieve the result : ERROR:  syntax error at end of input
LINE 1: ...lect * from public.table1 where tag = ?
                                                  ^
)
NULL
Warning message:
In postgresqlQuickSQL(conn, statement, ...) :
  Could not create execute: select * from public.table1 where tag = ?

I have to suspect it has something to do with the double/single quotes, but could not figure out what exactly. I also tried the following which works:

dbGetQuery(test_db, 
       "select * from public.table1 where tag = 'tag_col_content1'")

Please note that it is important for me to pass value to the SQL statement because eventually this line will be used interactively in Rshiny. Thank you very much

Upvotes: 0

Views: 95

Answers (2)

jjanes
jjanes

Reputation: 44383

In PostgreSQL, the official way to spell place holders is $1, $2, ... $9,... not ?. Some drivers will automatically parse and transform queries for you, but evidently not this one.

When I used $1 in place of ?, it works.

Upvotes: 1

Connor Krenzer
Connor Krenzer

Reputation: 489

If I understand your question, you want to supply user input to the where clause of your query. You could probably do some mental gymnastics with tidy eval, but paste0() seems like an easy fix:

param <- "'tag_col_content1'"

dbGetQuery(test_db, 
           paste0("select * from public.table1 where tag = ", param))

Upvotes: 1

Related Questions