R help - Dec 2007 - RMySQL and free text variable

Hi all,

    I have a quick question, I want to send a select statement to the 
mysql server. With the code below I connect and ask my select statement.
As you can see from the code I want to have a mode = "+". The
'+' needs
to be free text and hence the "\"" around it. But it doesn't
work. If i
put just plus in instead of the 'mode' I get a table. How can I give the
'select' statement a variable and put it in free text ?

cheers,

Paul

library(RMySQL)
 >con<- dbConnect(MySQL(), user, password, host, dbname)
 > mode<-"+"
 > query <- paste("SELECT * FROM data WHERE molNUM =", 165 ,
" AND mode
=", "\",mode,\"" ) ## my statement
 > scans <- dbGetQuery(con, query)
 > scans
NULL data frame with 0 row

 > query <- paste("Select * FROM metabolite_msms WHERE molid =",
165 , "
AND mode =", "\"+\"" )##only difference is that mode is
replace by +
 > scans <- dbGetQuery(con, query)
 > scans
   molNUM       mz  intensity    fwhm     mode adduct
1  165    385.5790   0.492911 0.0975147    +
2  165    111.1130   0.497857 0.3894250    +
3  165    229.2050   0.499505 0.4097280    +
4  165    302.8850   0.514342 0.0521955    +
5  165    186.9430   0.524233 0.1347370    +
6  165    125.1010   0.530828 0.0571061    +
7  165    207.2150   0.535773 0.0586751    +
8  165    217.2020   0.552258 0.0612605    +
9  165    171.1100   0.560501 0.0823234    +

 > mode<-'+' ## ok double quote didn't work SO...
 > scans <- dbGetQuery(con, query)
 > scans
NULL data frame with 0 rows

Yea that worked !!
Did
paste(SELECT * FROM MET WHERE molid=", molid, "AND mode=",
"\'" mode,
"\'" , sep="")
I guess all I needed was the sep="" which I totally forgot about.

Cheers,

Paul

Eric wrote:
> Does this work for you?
>
> "SELECT * FROM data WHERE molNUM = 165 AND mode = '+'"
>
> Possibly related: when using the paste function, remember that you 
> might need explicitly set sep = "" and add your own padding where
needed.
>
> Please re-post to the list if it works.
>
>
>
>