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@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
______________________________________________
R-help@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
