rmysql query help
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.