[R-sig-DB] dbSendPreparedQuery in RSQLite

[James Bullard]

Okay, well I thought that what I was doing was not easily done with  
one query but since I am wrong more often than not I'll ask and see if  
anyone can think of a way to combine it into one query.

I have a set of regions:

regions <- cbind(chr = c(1,2,3), start = c(1,230,4950), end = c(293,  
320, 19200))

In the db I have the following table:

CREATE TABLE count_table (chr, location, count);

chr location count
1	10		1
1	290		10
2	245		3
3	10		2

The query that I am using is:

apply(regions, 1, function(region) {
	paste("SELECT * FROM count_table WHERE chr =", region[1], "AND  
location >=", region[2],  "AND location <=", region[3], ";")
})

Which gives me back the following (which is exactly what I want):
[[1]]
   chr location count__1
1   1       10        1
2   1      290       10

[[2]]
   chr location count__1
1   2      245        3

[[3]]
NULL data frame with 0 rows


So I don't see any obvious way to pass in multiple ranges, but maybe I  
missing something with using the IN operator, thanks in advance, sorry  
for the long email.


Jim



On Apr 16, 2008, at 8:47 AM, Sean Davis wrote:
> On Tue, Apr 15, 2008 at 11:45 PM, James Bullard  
> <bullard using berkeley.edu> wrote:
>> Yes, in this example that would certainly work. However, I have a  
>> large
>> number of queries in a tight loop and it would be nice to avoid the  
>> overhead
>> of the query parsing and compiling on each call through the loop.
>
> A couple of typical tricks for avoidance of loops is to either:
>
> 1) Use an "in" query
> 2) Use a temporary table (CREATE TEMPORARY TABLE .... or
> dbWriteTable()) and then do a join
>
> If you have hundreds or thousands of queries, it is quite possible
> that #2 could be much faster.  Benchmarking would be useful here, but
> it is definitely worth a try if you are too slow with looping.
>
> Sean
>
>> thanks again, jim
>>
>>
>>
>>
>> On Apr 15, 2008, at 7:21 PM, Sean Davis wrote:
>>
>>> On Tue, Apr 15, 2008 at 8:31 PM, James Bullard  
>>> <bullard using berkeley.edu>
>> wrote:
>>>
>>>> How do we send prepared queries in RSQLite. We are interested in
>>>> something along the lines of
>>>>
>>>> ps = dbSendPreparedQuery(conn, "SELECT * FROM table1 WHERE chr  
>>>> = ?",
>>>> data.frame("integer"))
>>>> for( i in 1:2)
>>>> rs = dbGetPreparedQuery(ps, i)
>>>>
>>>> In C it would look something along the lines of
>>>> http://www.mail-archive.com/sqlite-users@sqlite.org/msg31854.html
>>>>
>>>
>>> It looks like this is not supported for select statements, though I
>>> could easily be wrong.  In this particular case, you could rewrite
>>> your select as:
>>>
>>> "SELECT * from table1 where chr in (1,2)"
>>>
>>> Sean
>>>
>>
>>