[R-sig-DB] dbSendPreparedQuery in RSQLite
Seth Falcon
@eth @end|ng |rom u@erpr|m@ry@net
Wed Apr 16 17:24:18 CEST 2008
* On 2008-04-15 at 20:45 -0700 James Bullard 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. I believe it is not supported on select statements as well
> and wonder if this would be a hard addition,
You are correct, currently prepared queries are not supported for
SELECT (I have no memory of why this is the case). I think that
supporting prepared queries for SELECT should be possible, though not
trivial.
Are you sure you can't avoid your loop by sending a large query and
batching through it using fetch on the result set returned?
> also, I wonder if this is the correct syntax below. From other
> settings it seems a little off (see the link in the original email).
By below I think you mean:
> >> ps = dbSendPreparedQuery(conn, "SELECT * FROM table1 WHERE chr = ?",
> >> data.frame("integer"))
> >> for( i in 1:2)
> >> rs = dbGetPreparedQuery(ps, i)
Almost. As above, this is only supported with non-SELECT, so you
might do (untested):
df = data.frame(x=1:10, y=letters[1:10])
sql = "INSERT INTO foo (a, b) VALUES (?, ?)"
rs = dbSendPreparedQuery(db, sql, df)
So you pass a data.frame and the loop is internal.
You can also name the parameters so you could have:
sql = "INSERT INTO foo (a, b) VALUES (:y, :x)"
rs = dbSendPreparedQuery(db, sql, df)
And y, x are matched based on names(df).
+ seth
--
Seth Falcon | http://userprimary.net/user/
More information about the R-sig-DB
mailing list