prepared query with RODBC ?

[A thread started on r-help, now moved to the r-sig-db list.]

I am precisely about about something similar.
From what I read/heard some folks/DBMs make the distinction
between prepared queries (as you give example code for) and
batch queries. Both of them speed up the process when similar
queries are to be performed, with the batch queries being the fastest
(since in the case of the prepared query an answer is returned by
the database for each query...).
From what I could understand, RODBC will make a prepared query
(at the C level) when one wants to write a data.frame and select the
option 'fast=TRUE'...

I shall experiment further...

Thanks,

Laurent
Perhaps this thread should be continued in the r-sig-db list?

Laurent Gautier wrote:
Dear List,

Would anyone know how to perform prepared queries with ROBC ?
I had a shot with some of the internal (non-exported) functions of the package
but ended up with a segfault, so I prefer asking around before
experimenting further...

Thanks,

Laurent

______________________________________________
R-help at stat.math.ethz.ch mailing list
https://stat.ethz.ch/mailman/listinfo/r-help
PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html

R applications that require a tighter interface to DBMSs could
greatly benefit from such a facility, but prepared statements
have not been used much from R, AFAIK.

In a nutshell, a prepared statement is an SQL statement that is
parsed, optimized, cached in the server, and then repeatedly executed
with new data (using what is called "data binding").  Some of its
benefits are significant improved performance, breaking big tasks
into smaller, more manageable tasks, etc.

A trivial example:

  ## prepare an SQL statement for repeated insertions, and bind
  ## output data.frame columns 1, 2, 3, and 4 to the SQL statement

  ps <- dbPrepareStatement(conn,
           "INSERT into SCORING (id, x1, x2, score) VALUES (:1,:2,:3,:4)",
           bind = c("char", "char", "numeric", "numeric"))

  ## compute new scores....
  while(condition){
     ...
     new_scores <- predict(model, newdata)
     dbExecStatement(ps, data = new_scores)

  }
  dbCommit(con)
  dbClearResult(ps)

I believe most DBMSs provide means to do this (PostgreSQL, MySQL,
Sybase, Oracle, SQLite, ODBC 3.0, ...), but I think only the
R-Oracle interface currently implements them (and only in an
experimental basis).

Regards,

--
David