2

I am using RPostgresql and DBI in RStudio. 

library(RPostgreSQL)
library(DBI)
#save password
prod_pw <- {
  "my_pass"
}

 # make db connection
 con <- dbConnect(RPostgreSQL::PostgreSQL(), dbname = 'my_dbname', 
                        host = 'my_host',
                        port = 5432, # or any other port
                        user = 'user_name',
                        password = prod_pw)


# save query
myquery<- 'select count(*), state from results where date=\'2018-11-10\';'


#run query
my_query_stats<-dbGetQuery(con,myquery)

However I want to automate this, such a way that

the date can be either input from the user, or at minimum use the system date at the time of running the script.

What I tried: ex:

 this_date<-Sys.Date()
#or accept from user
this_date<- readline("Please Enter Date\n")  
# Please Enter Date2018-11-30
# this_date
# [1] "2018-11-30"

    myquery<- 'select count(*), state from results where date=this_date;'
    dbGetQuery(con,myquery) # didn't work, null value returned.

myquery<- 'select count(*), state from results where date=\'this_date\';'
    dbGetQuery(con,myquery) # didn't work, null value returned.

 myquery<- 'select count(*), state from results where date=\"this_date\";'
dbGetQuery(con,myquery) # didn't work, returned null value.

Please advise on how to accept value from user and send that to the psql query's date field.

kRazzy R
  • 1,561
  • 1
  • 16
  • 44
  • 3
    See [here](https://stackoverflow.com/questions/51648611/rpostgresql-passing-parameter-in-r-to-a-query-in-rpostgresql) and pay special attention to the alternative suggestion in the comments in reference to sql injection concerns. – joran Dec 04 '18 at 04:55
  • 2
    https://xkcd.com/327/ (*"Oh yes, Little Bobby Tables, we call him"*). – r2evans Dec 04 '18 at 04:58
  • Does `RPostgreSQL` not take `DBI::dbBind`? – r2evans Dec 04 '18 at 05:06
  • (If it doesn't, `RPostgres` does ... I don't know for certain what other significant differences might exist.) – r2evans Dec 04 '18 at 05:22

1 Answers1

2

try this 

this_date = "2018-11-30"


 string = paste("select count(*), state from results where date= 
 TO_DATE(this_date,'YYYYMMDD')")

 rs = dbGetQuery(connection,string)
Zeeshan
  • 1,208
  • 1
  • 14
  • 26
  • have you tried this? I get the following error `Error in postgresqlExecStatement(conn, statement, ...) : RS-DBI driver: (could not Retrieve the result : ERROR: column "this_date" does not exist`. I think the paste is not working so the value of this date is not going into the `string` variable. – kRazzy R Jun 23 '19 at 23:22
  • Yes, and it runs successfully. – Zeeshan Jun 24 '19 at 06:14