[R] rmysql query help

[Duncan Murdoch]

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.