Passing DataFrame column into WHERE clause in SQL query embedded in R via parametrized queries

Question

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

r2evans · Accepted Answer · 2022-01-05 14:44:51Z

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)))
}

Collectives™ on Stack Overflow

Passing DataFrame column into WHERE clause in SQL query embedded in R via parametrized queries

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related