sqlrr-dbi

Pass multiple parameters in R SQL query

I am trying to pass same parameters multiple times in R SQL query. The query is:

table_x <- c(‘Rain’, ‘Cloudy’)

df <- dbGetQuery(conn, "select schemaname, tablename, dateupdated, test_result from weather 
Where dateupdated in (select max(dateupdated) from weather where conditions in (?,?))
and dateupdated = sysdate
and conditions in (?,?)",
params= table_x)

The query works if I remove the 2nd conditions clause. Otherwise it complains

Query requires 4 parameters; 2 supplied

I tried creating another vector and pass that through along with 1st vector but still getting same error. Any suggestions.

Thanks

Solution

  • For every ? found in the query, you must have a parameter in params=. Order is important, so params=c(table_x, table_x) should work.

    df <- dbGetQuery(conn, "
  select schemaname, tablename, dateupdated, test_result
  from weather 
  where dateupdated in (select max(dateupdated) from weather where conditions in (?,?))
    and dateupdated = sysdate
    and conditions in (?,?)",
  params = c(table_x, table_x))