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

[Jeff Newmiller]

I am not quite sure what you are complaining about. The ODBC interface definition is not vectorized, and that has nothing to do with R... that applies across all platforms I have seen. The DBI API is consistent with that. There are some proprietary APIs that implement bulk data transfers, but then you are stuck with that API.
It might be appropriate to discuss this on R-sig-db if you have better information than I do.
---------------------------------------------------------------------------
Jeff Newmiller                        The     .....       .....  Go Live...
DCN:<jdnewmil at dcn.davis.ca.us>        Basics: ##.#.       ##.#.  Live Go...
                                      Live:   OO#.. Dead: OO#..  Playing
Research Engineer (Solar/Batteries            O.O#.       #.O#.  with
/Software/Embedded Controllers)               .OO#.       .OO#.  rocks...1k
--------------------------------------------------------------------------- 
Sent from my phone. Please excuse my brevity.

On August 12, 2014 12:46:30 PM PDT, Dan Muresan <danmbox at gmail.com> wrote:
>Yes, of course, that's an obvious work-around, thanks. Another one is
>to use temporary tables.
>
>But I'd like to know if binding a vector to an SQL parameter is
>possible in rsqlite (or even in the DBI API or with other drivers --
>it seems to me it isn't). This seems like a nasty shortcoming
>(especially in light of SQL injection, but there are other
>considerations).
>
>On 8/12/14, John McKown <john.archie.mckown at gmail.com> wrote:
>> 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
>>
>
>______________________________________________
>R-help at r-project.org mailing list
>https://stat.ethz.ch/mailman/listinfo/r-help
>PLEASE do read the posting guide
>http://www.R-project.org/posting-guide.html
>and provide commented, minimal, self-contained, reproducible code.