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
RMySQL frustrations
6 messages · Mark Moon, Sean Davis, MacQueen, Don
On Thu, May 17, 2012 at 10:59 AM, Mark Moon <mmoon at 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?
What kinds of queries are you trying to do that require multiple statements separated by ";"? It seems these could be simply separated into individual queries without loss of generality? Sean
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 at r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-db
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 at 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 at r-project.org >https://stat.ethz.ch/mailman/listinfo/r-sig-db
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]]
On Thu, May 17, 2012 at 2:43 PM, Mark Moon <mmoon at 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 at r-project.org https://stat.ethz.ch/mailman/listinfo/r-sig-db
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 at tensegritycapital.com<mailto:mmoon at tensegritycapital.com>> To: Donald MacQueen <macqueen1 at llnl.gov<mailto:macqueen1 at llnl.gov>> Cc: "r-sig-db at r-project.org<mailto:r-sig-db at r-project.org>" <r-sig-db at r-project.org<mailto:r-sig-db at 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