[R] pass vector binding to DBI parameter (rsqlite)

[John McKown]

On Tue, Aug 12, 2014 at 10:55 AM, Dan Muresan <danmbox at gmail.com> wrote:
> Hi, is there a way to bind vectors to DBI query parameters? The
> following tells me that vectors are sent as separate values:
>
>> 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 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 looks like 2 result sets (4 + 3 entries), not one.
>
> Is there to send multiple values to a '?' binding? Is this at all
> possible using the R DBI interface (not necessarily with rsqlite)?

I don't really _know_ much, but what I would try would be something like:

dbGetQuery(c,"select * from tst where y not in (?)",paste(c(7,6),collapse=','));

The paste(c(7,6),collapse=',') results in the string "6,7". You could
always subject yourself to a SQL injection attack by doing:

dbGetQuery(c,paste("select * from tst where y not in
(",c(7,6),")",collapse=','));

If you do this and use a variable instead of the c(7,6), make sure you
"cleanse" the contents of the variable. Just as making sure that there
is no "bare" semi-colon in it. And other things that don't come to
mind off hand.

Hum, perhaps better:

values<-c(7,6);
dbGetQuery(c,paste("select * from tst where y not in (",
                                paste(rep('?',length(values)),collapse=','),
                                ")"),
                        values);

As you can see, this dynamically adjusts the number of ? marks in the
SELECT statement, based on the number of elements in the "values"
variable.

-- 
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown