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