[R-sig-DB] dbSendPreparedQuery in RSQLite

[Seth Falcon]

* 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/