0

I am having a very difficult time passing a column from a DataFrame as well as a Date parameter into a SQL query in R. I have recently stumbled upon the idea of parametrized queries which looks promising for simplifying what I've been doing.

The setup is the following:

#Column of values I wish to use in the query in my where clause
values <- df$'column'
vallist<-list(values)


library(tidyverse)
library(DBI)
library(glue)
library(openxlsx)

#Function I wish to define with parameters the date and the values from above

Data_Function <- function(database,date,vals){



  query <- glue_sql("

                     SELECT column1,
                            column2,
                            dateof
                     FROM database
                     WHERE dateof>=?
                     AND column1 IN ? 
")


df0<-dbSendQuery(database,query)
dbBind(df0,date,vals)
dbFetch(df0)

}
Data_Function(db,c('2021-12-01',EB_List))

When I try to run this I get the following error:

Error in result_bind(res@ptr, params, batch_rows) : 
  nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '@P2'.  [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

It does not seem to like my "AND column1 IN ?" clause. What is the proper way to pass both of these arguments?

Thank you

PDS
  • 61
  • 5

1 Answers1

0

This is very close to your previous question, more formalized now in a function. There are a few things to note:

  • dbBind takes three arguments, but all of the parameters to be bound must be in position 2; position 3 (the ellipses) is any argument(s) for other methods, not the parameters themselves. You can fix this with dbBind(df0, c(date, as.list(vals))) or see my next note.

  • while it is fine to use dbSendQuery, dbBind, and dbFetch, you can wrap all of that into one call (just like I showed in my previous answer) using dbGetQuery(conn, query, params). This one call handles sending the query, binding parameters, and clearing the result set automatically.

  • if you prefer to still use dbSendQuery, then you really should clean up after it: the return value from this call is a "result set" (often using a variable res, by convention), which needs to be cleared. Most DBI-based systems will auto-clear a previous result when you send another query, but it's better practice to do so explicitly. It needs to be cleared after you fetch data, so a common way to effect this is to

    res <- dbSendQuery(database,query)
on.exit(dbClearResult(res), add = TRUE)
dbBind(res, c(date, as.list(vals)))
dbFetch(res)

    as I've shown in the commented-out code below. The expression in on.exit is not evaluated immediately, it is evaluated when Data_Function is complete and returning to the caller (though the value from dbFetch(df0) is still given to the user. I prefer the use of on.exit for other reasons, but if you wanted something a little simpler then this also works:

    res <- dbSendQuery(database,query)
dbBind(res, c(date, as.list(vals)))
out <- dbFetch(res)
dbClearResult(res)
out

Your function, amended/fixed:

Data_Function <- function(database, date, vals){
  qmarks <- paste(rep("?",  length(vals)), collapse = ",")
  query <- paste(
    "
    SELECT column1,
           column2,
           dateof
    FROM database
    WHERE dateof >= ?
      AND column1 IN (", qmarks, ")")
  # res <- dbSendQuery(database,query)
  # on.exit(dbClearResult(res), add = TRUE)
  # dbBind(res, c(date, as.list(vals)))
  # dbFetch(res)
  dbGetQuery(database, query, params = c(date, as.list(vals)))
}
r2evans
  • 141,215
  • 6
  • 77
  • 149