R HELP,
I am trying to use an R script to connect to a mysql database. I am
having a problem using a variable in the where clause that contains a
"space" in its value.
If I include the variable inside the quotes of the query - i think it is
searching for the name of the variable in the database and not the value
of the variable.
If I put it outside the quotes, then it complains about the space. Are
there special escape characters or something else Im missing? This date
format in a mysql table is pretty standard
Any ideas?
Thanks,
Aaron
require(RMySQL)
startdatetime<-"2009-04-04 01:00:00"
connect <-
dbConnect(MySQL(),user="xxxxx",password="xxxxxx",dbname="xxxxx",host="xxx.xxx.xxx.xxx")
forecast <- dbSendQuery(connect, statement=paste("SELECT ICE FROM table1
WHERE BEGTIME >= 'startdatetime'")) # doesnt read variable
or
forecast <- dbSendQuery(connect, statement=paste("SELECT ICE FROM table1
WHERE BEGTIME >="startdatetime)) # space error
but this seems to work
forecast <- dbSendQuery(connect, statement=paste("SELECT ICE FROM table1
WHERE BEGTIME >='2009-04-04 01:00:00'"))
On 5/6/2009 3:47 PM, Aaron Sims wrote:> R HELP, > > I am trying to use an R script to connect to a mysql database. I am > having a problem using a variable in the where clause that contains a > "space" in its value. > If I include the variable inside the quotes of the query - i think it is > searching for the name of the variable in the database and not the value > of the variable. > If I put it outside the quotes, then it complains about the space. Are > there special escape characters or something else Im missing? This date > format in a mysql table is pretty standard > Any ideas?You need to construct a query like your last one, by pasting the startdatetime value in. MySQL can't see R variables. So it should be something like: dbSendQuery(connect, statement=paste("SELECT ICE FROM table1 WHERE BEGTIME >='", mydatetime, "'", sep="")) Duncan Murdoch> > Thanks, > Aaron > > > require(RMySQL) > startdatetime<-"2009-04-04 01:00:00" > connect <- > dbConnect(MySQL(),user="xxxxx",password="xxxxxx",dbname="xxxxx",host="xxx.xxx.xxx.xxx") > > forecast <- dbSendQuery(connect, statement=paste("SELECT ICE FROM table1 > WHERE BEGTIME >= 'startdatetime'")) # doesnt read variable > or > forecast <- dbSendQuery(connect, statement=paste("SELECT ICE FROM table1 > WHERE BEGTIME >="startdatetime)) # space error > > but this seems to work > forecast <- dbSendQuery(connect, statement=paste("SELECT ICE FROM table1 > WHERE BEGTIME >='2009-04-04 01:00:00'")) > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.
Hello, Okay, this is a help for me too, thanks! However, how do I do it for two variables/entries in the database? Thanks jorgusch Aaron Sims wrote:> > R HELP, > > I am trying to use an R script to connect to a mysql database. I am > having a problem using a variable in the where clause that contains a > "space" in its value. > If I include the variable inside the quotes of the query - i think it is > searching for the name of the variable in the database and not the value > of the variable. > If I put it outside the quotes, then it complains about the space. Are > there special escape characters or something else Im missing? This date > format in a mysql table is pretty standard > Any ideas? > > Thanks, > Aaron > > > require(RMySQL) > startdatetime<-"2009-04-04 01:00:00" > connect <- > dbConnect(MySQL(),user="xxxxx",password="xxxxxx",dbname="xxxxx",host="xxx.xxx.xxx.xxx") > > forecast <- dbSendQuery(connect, statement=paste("SELECT ICE FROM table1 > WHERE BEGTIME >= 'startdatetime'")) # doesnt read variable > or > forecast <- dbSendQuery(connect, statement=paste("SELECT ICE FROM table1 > WHERE BEGTIME >="startdatetime)) # space error > > but this seems to work > forecast <- dbSendQuery(connect, statement=paste("SELECT ICE FROM table1 > WHERE BEGTIME >='2009-04-04 01:00:00'")) > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide > http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. > >-- View this message in context: http://www.nabble.com/rmysql-query-help-tp23414168p24167408.html Sent from the R help mailing list archive at Nabble.com.