dbSendPreparedQuery in RSQLite
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 at 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 at 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 at 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