[R-sig-DB] RMySQL frustrations

[Sean Davis]

On Thu, May 17, 2012 at 2:43 PM, Mark Moon <mmoon using tensegritycapital.com>wrote:

> 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.
>
>
Hi, Mark.

Access to those features is a function of the mysql server.  You will need
to explicitly GRANT those privileges to your user (or have your DBA/admin
do it) before you can perform those functions.  This isn't an RMySQL issue,
I don't think.

Sean


> 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]]
>
> _______________________________________________
> 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
>

	[[alternative HTML version deleted]]