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 <mm...@tensegritycapital.com<mailto:mm...@tensegritycapital.com>> To: Donald MacQueen <macque...@llnl.gov<mailto:macque...@llnl.gov>> Cc: "r-sig-db@r-project.org<mailto:r-sig-db@r-project.org>" <r-sig-db@r-project.org<mailto:r-sig-db@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]] _______________________________________________ R-sig-DB mailing list -- R Special Interest Group R-sig-DB@r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-db
