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@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.
______________________________________________
R-help@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.
