Search code examples
rshinyrpostgresql

RPostgreSQL query not working in Shiny server

I am trying to get data from a PostgreSQL database in a shiny app. The query is not working. Running the following

function(input, output) {
  data.df<- eventReactive(input$fetcher, {
    qry<-paste0("\'SELECT * FROM bhavcopy WHERE \"TIMESTAMP\" BETWEEN \'",input$start,"\' AND \'",input$end,"\' AND \"SYMBOL\"=\'",input$symbol,"\'\'")

    dbGetQuery(con,qry)
  })

returns the following error.

Warning: Error in : Failed to prepare query: ERROR: syntax error at or near "'SELECT * FROM bhavcopy WHERE "TIMESTAMP" BETWEEN '" LINE 1: 'SELECT * FROM bhavcopy WHERE "TIMESTAMP" BETWEEN '2020-06-1... ^

When I use the query (copying the result of cat(qry)) and run the following query

dbGetQuery(con,'SELECT * FROM bhavcopy WHERE "TIMESTAMP" BETWEEN '2020-06-11' AND '2020-07-04' AND "SYMBOL"='HDFCLIFE'')

the error is

Error: unexpected numeric constant in "dbGetQuery(con,'SELECT * FROM bhavcopy WHERE "TIMESTAMP" BETWEEN '2020"

Using this query(SELECT * FROM bhavcopy WHERE "TIMESTAMP" BETWEEN '2020-06-11' AND '2020-07-04' AND "SYMBOL"='HDFCLIFE') in PostgreSQL manager returns proper result.

I'm unable to catch the specific error and rectify it. Can anyone help?

Solution

  • I think you have to leave out the outer quotes. Try this:

    function(input, output) {
  data.df<- eventReactive(input$fetcher, {
    qry<-paste0("SELECT * FROM bhavcopy WHERE \"TIMESTAMP\" BETWEEN '", input$start, "' AND '", input$end, "' AND \"SYMBOL\"= '", input$symbol, "'")

    dbGetQuery(con,qry)
  })

    I cant test it but maybe you also have to leave out the single quotes surrounding input$start and input$end.