[R-sig-DB] RMySQL frustrations

[MacQueen, Don]

I would 't be optimistic about the @variable persisting from one dbSendQuery call to the next. But maybe it will.

However, I think it likely you can achieve the same result on the R side, i.e., by defining an R variable to take the place of the @variable, and then using paste() to construct the subsequent queries.

Other than that, the rest of your steps look just like what I have done from time to time. My guess would be like Sean's, a permissions issue.

I use
  dbExistsTable()
and
  dbDropTable()
instead of dbSendQuery() when I need to drop a table if it exists.

-Don

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


From: Mark Moon <mmoon using tensegritycapital.com<mailto:mmoon using tensegritycapital.com>>
To: Donald MacQueen <macqueen1 using llnl.gov<mailto:macqueen1 using llnl.gov>>
Cc: "r-sig-db using r-project.org<mailto:r-sig-db using r-project.org>" <r-sig-db using r-project.org<mailto:r-sig-db using r-project.org>>
Subject: Re: [R-sig-DB] RMySQL frustrations

Thanks, all.  I am trying to do the following:

- set an @variable,
- drop a table if exists
- create a temp table from a select
- update the temp table
- do a select on the updated, temp table

I'm now trying to run each as separate calls to dbSendQuery().  But, I'm getting allkinds of errors, now of the flavor,

Error in mysqlExecStatement(conn, statement, ...): RS-DBI driver (could not run statement ....

DROP command denied to user ..

Access denied for user ..

UPDATE command denied to user ..

Not sure why I don't have access to DROP, UPDATE, nor can create a temporary table.

Any help will be GREATLY appreciated!

Mark




On 05/17/2012 11:23 AM, MacQueen, Don wrote:

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




--
--
Mark Moon
Managing Director and Principal
Ross Institutional Investors
33 Whitney Avenue
New Haven, CT 06510
cell (805) 657-9504
home (805) 491-2826

	[[alternative HTML version deleted]]