[R] prepared query with RODBC ?

[David James]

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