Message-ID: <4A01F192.6070504@stats.uwo.ca>
Date: 2009-05-06T20:22:42Z
From: Duncan Murdoch
Subject: rmysql query help
In-Reply-To: <4A01E93B.3090401@ncsu.edu>
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.