[R-sig-DB] RMySQL frustrations

[Mark Moon]

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