Skip to content

rmysql query help

2 messages · Aaron Sims, Duncan Murdoch

Aaron Sims
# 
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'"))
Duncan Murdoch
#
On 5/6/2009 3:47 PM, Aaron Sims wrote:
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