[R-sig-DB] [R] prepared query with RODBC ?

[Laurent Gautier]

[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


On 3/3/06, David James <dj using research.bell-labs.com> wrote:
> 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 using 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
>