4

How can I pass both a scalar and a set of values in the bind.data parameter of dbGetQuery() for an SQL statement like

select * from tst where x = ? and y in (?)

Here's what I tried:

> library("RSQLite")
> c <- dbConnect (SQLite())
> dbGetQuery(c, "create table tst (x int, y int)")
> dbGetQuery(c, "insert into tst values (?, ?)", data.frame(x=c (1,2,1,2), y=c(3, 4, 5, 6)))
> dbReadTable(c, "tst")
  x y
1 1 3
2 2 4
3 1 5
4 2 6
> dbGetQuery (c, "select * from tst where x = ? and y not in (?)", data.frame(x=2, y=I (list(7,6))))
Error in sqliteFetch(rs, n = -1, ...) : 
  RAW() can only be applied to a 'raw', not a 'double'

From reading the source, any non-data.frame bind.data parameter gets forced through as.data.frame(), so I guess there's little point in trying anything other than data frames.

NOTE: heck, it seems even binding a single set is problematic:

> dbGetQuery(c, "select * from tst where y not in (?)", c(7,6))
  x y
1 1 3
2 2 4
3 1 5
4 2 6
5 1 3
6 2 4
7 1 5

This makes it clear that 2 separate queries (one of which returns 4 and one of which returns 3 results) are send from R; SQLite never sees a set parameter.

Earlier note: I want the database engine to filter the appropriate row, i don't want R to compute the cartesian product. In the above example, simply getting rid of I() creates a 2-row dataframe (thanks to R's recycling), one of which is the solution. R sends each of these 2 rows to sqlite, and of course the second one matches. But the following shows the SQLite engine doesn't actually receive set parameters with regular data.frames:

> dbGetQuery(c, "select * from tst where x in (?) and y in (?)", data.frame(x=c(3,2), y=c(6,7)))
[1] x y
<0 rows> (or 0-length row.names)
> dbGetQuery(c, "select * from tst where x in (?) and y in (?)", data.frame(x=c(3,2), y=c(7,6)))
  x y
1 2 6
dan3
  • 2,528
  • 22
  • 20

2 Answers2

3

Why do you specify y = I(list(7,6)) instead of y=c(6,7)? This seems to work: 

dbGetQuery (c, 
            "select * from tst where x = ? and y in (?)", 
            data.frame(x=1, y=c(7,6)))

You might be looking for expand.grid.

dbGetQuery (c, 
            "select * from tst where x = ? and y in (?)", 
            expand.grid(x=c(2,3), y=c(7,6)))

EDIT: Another option (and it's not pretty) is to substitute the ? in R. Something like the following: 

dbGetQuerySet <- function(con, statement, ...){
  if (length(list(...)) > 0){
    bind.data <- list(...)[[1]]
    for (set in as.data.frame(bind.data)){  
      statement <- sub('\\?', paste(set, collapse=","), statement)
    }
  }
  sqliteQuickSQL(con, statement, ...)
}
shadow
  • 21,823
  • 4
  • 63
  • 77
  • because data.frame recycles short columns; that's equivalent to sending separate queries for (x=1, y=7) and (x=1, y=6). Note that I've changed the question to x=2 -- obviously there are no rows for x=1 – dan3 Aug 12 '14 at 12:45
  • Sorry, I don't understand. What is your desired result? – shadow Aug 12 '14 at 12:46
  • I guess I'm trying to learn how to bind sets to parameters, but the example doesn't discriminate... For example, how do you bind 2 sets? This is more complex than I thought – dan3 Aug 12 '14 at 12:54
  • 1
    This computes the result in R! I want the database engine to solve the problem, I don't want R to expand the cartesian product – dan3 Aug 12 '14 at 13:00
  • OK, I've clarified the question and I'll upvote your answer -- because it does satisfy the original question. But I'm looking for something else. – dan3 Aug 12 '14 at 13:08
  • Seems this is the best possible. – dan3 Aug 13 '14 at 05:10
0

How about converting variables to strings:

#Variables
myVal_x <- 2
myVal_y <- c(7,6)
#Convert to string
myVal_y <- paste0("(",paste(myVal_y,collapse=","),")")
#Query
dbGetQuery(c, 
            paste("select * from tst where x =",myVal_x,"and y in",myVal_y) 
            )
zx8754
  • 52,746
  • 12
  • 114
  • 209
  • Sure... but the question was about SQL parameter binding. Though it does seem that binding sets is impossible... – dan3 Aug 12 '14 at 14:11