[R-sig-DB] RMySQL frustrations

[MacQueen, Don]

I'm no expert, but I believe they are designed to pass one SQL expression
at a time to the database.
(this is not the same as "simple, one line queries"; I frequently use
multi-line moderately complex queries with sub-selects and such).

If you are talking about multiple queries of the form

 mydat <-  dbGetQuery(con, "select ... ; select ... ; select ...")

then I would argue this makes no sense to even try. Any R function returns
a only single object, but this would be asking it to return several
objects. Sure, we could ask the people who wrote the package to recognize
such a case, run each query separately, and return a list object
containing their individual results -- but I think that would be asking
too much since it is simple to split such a set of queries into multiple
calls.

If you are talking about multiple "queries" of the form

  dbGetQuery(con, "create table as ... ; create table as ... ; select ...")

that might be a little more reasonable to expect, but even so, it's easy
enough to break this into a sequence of uses of dbSendQuery() followed by
a final dbGetQuery(). I have done this kind of thing. [or dbSendUpdate()
in the case of JDBC]

I'm trying to imagine a use of multiple statements separated by ";" where
it's truly essential that they all be done in a single R command.

-Don

-- 
Don MacQueen

Lawrence Livermore National Laboratory
7000 East Ave., L-627
Livermore, CA 94550
925-423-1062





On 5/17/12 7:59 AM, "Mark Moon" <mmoon using tensegritycapital.com> wrote:

>Getting frustrated using R with MySQL (on Debian Squeeze).  I can
>connect to mySQL database and I can execute simple queries.  But, having
>various difficulties executing queries with multiple statements
>separated by ";".  Set the client.flag to CLIENT_MULTI_STATEMENTS but
>still having difficulties.
>
>Questions: are the RMySQL and RODBC packages really meant just for
>simple, one line queries?  Can anyone point me to where I might learn
>how to execute more complicated queries with in R?
>
>Thanks!
>
>Mark
>
>_______________________________________________
>R-sig-DB mailing list -- R Special Interest Group
>R-sig-DB using r-project.org
>https://stat.ethz.ch/mailman/listinfo/r-sig-db