R help - Sep 2009 - RODBC : using and passing queries that use " in some arguments

Dear R users,

I am trying to connect R to data that is in a Access Database but I have problem
with the construction of queries using special characters.

I am using RODBC package.

The following is working :
> MyQuery<-paste("SELECT first( (DateHeure) ) ,
avg(NNO3_AT322_OUT_moy) AS Cond FROM Colonne_3 ")
> Col3<-sqlQuery(con, query=MyQuery)
> Col3
             Expr1000     Cond
1 2009-06-23 10:15:02 579.9562
> 
> MyQuery<-paste("SELECT first( format (DateHeure) ) ,
avg(NNO3_AT322_OUT_moy) AS Cond FROM Colonne_3 ")
> Col3<-sqlQuery(con, query=MyQuery)
> Col3
             Expr1000     Cond
1 23/06/2009 10:15:02 579.9562

But I have problems as soon as I want to use SQL functions in my query that use
a double quote (eg FORMAT() function). I have no idea on how to build my sql
instruction containing "" since the escape code \" does not seem
to work/be sufficient in this case. (Nota : the argument I would like to use in
the following call to format is "00" )

#Obvious error :
> MyQuery<-paste("SELECT first( format(DateHeure, "00")) )
, avg(NNO3_AT322_OUT_moy) AS Cond FROM Colonne_3 ")
Erreur : constante num?rique inattendu(e) dans
"MyQuery<-paste("SELECT first( format(DateHeure, "00"
             Expr1000     Cond
1 23/06/2009 10:15:02 492.0594
> 
#Trying to solve the error using the escape code \"
:
> MyQuery<-paste("SELECT first( format(DateHeure, \"00\"))
) , avg(NNO3_AT322_OUT_moy) AS Cond FROM Colonne_3 ")
> Col3<-sqlQuery(con, query=cat(MyQuery))
SELECT first( format(DateHeure, "00")) ) , avg(NNO3_AT322_OUT_moy) AS
Cond FROM Colonne_3 Erreur dans odbcQuery(channel, query, rows_at_time) :
  'getEncChar' doit ?tre appel? sur un CHARSXP

Do you have any idea on how to proceed?

With Best regards
Pascal Boisson

___________________________________________________________________________________

Protegeons ensemble l'environnement : avez-vous besoin d'imprimer ce
courrier electronique ?
___________________________________________________________________________________

Les informations figurant sur cet e-mail ont un caractere strictement
confidentiel et sont exclusivement adressees au destinataire mentionne
ci-dessus.Tout usage, reproduction ou divulgation de cet e-mail est strictement
interdit si vous n'en etes pas le destinataire. Dans ce cas, veuillez nous
en avertir immediatement par la meme voie et detruire l'original. Merci.

This e-mail is intended only for use of the individual or entity to which it is
addressed and may contain information that is privileged, confidential and
exempt from disclosure under applicable law.
Any use, distribution or copying of this e-mail communication is strictly
prohibited if you are not the addressee. If so, please notify us immediately by
e-mail, and destroy the original. Thank you.

Start by using single quotes in your paste() command.

dumb example:
   sql <- paste(' select x = "3" ')

(the query is nonsense, I just wrote it to show 
how to get double quotes into the query)

-Don

At 6:45 PM +0200 9/21/09, BOISSON, Pascal wrote:
>Dear R users,
>
>I am trying to connect R to data that is in a 
>Access Database but I have problem with the 
>construction of queries using special characters.
>
>I am using RODBC package.
>
>The following is working :
>
>>  MyQuery<-paste("SELECT first( (DateHeure) ) , 
>>avg(NNO3_AT322_OUT_moy) AS Cond FROM Colonne_3 
>>")               
>>  Col3<-sqlQuery(con, query=MyQuery)
>>  Col3
>              Expr1000     Cond
>1 2009-06-23 10:15:02 579.9562
>>
>>  MyQuery<-paste("SELECT first( format 
>>(DateHeure) ) , avg(NNO3_AT322_OUT_moy) AS Cond 
>>FROM Colonne_3 ")               
>>  Col3<-sqlQuery(con, query=MyQuery)
>>  Col3
>              Expr1000     Cond
>1 23/06/2009 10:15:02 579.9562
>
>But I have problems as soon as I want to use SQL 
>functions in my query that use a double quote 
>(eg FORMAT() function). I have no idea on how to 
>build my sql instruction containing "" since the 
>escape code \" does not seem to work/be 
>sufficient in this case. (Nota : the argument I 
>would like to use in the following call to 
>format is "00" )
>
>#Obvious error :
>>  MyQuery<-paste("SELECT first( 
>>format(DateHeure, "00")) ) , 
>>avg(NNO3_AT322_OUT_moy) AS Cond FROM Colonne_3 
>>")               
>Erreur : constante num?rique inattendu(e) dans 
>"MyQuery<-paste("SELECT first( format(DateHeure, 
>"00"
>              Expr1000     Cond
>1 23/06/2009 10:15:02 492.0594
>>
>
>#Trying to solve the error using the escape code \" :
>>  MyQuery<-paste("SELECT first( 
>>format(DateHeure, \"00\")) ) , 
>>avg(NNO3_AT322_OUT_moy) AS Cond FROM Colonne_3 
>>")               
>>  Col3<-sqlQuery(con, query=cat(MyQuery))
>SELECT first( format(DateHeure, "00")) ) , 
>avg(NNO3_AT322_OUT_moy) AS Cond FROM Colonne_3 
>Erreur dans odbcQuery(channel, query, 
>rows_at_time) :
>   'getEncChar' doit ?tre appel? sur un CHARSXP
>
>Do you have any idea on how to proceed?
>
>With Best regards
>Pascal Boisson
>
>___________________________________________________________________________________
>
>Protegeons ensemble l'environnement : avez-vous 
>besoin d'imprimer ce courrier electronique ?
>___________________________________________________________________________________
>
>Les informations figurant sur cet e-mail ont un 
>caractere strictement confidentiel et sont 
>exclusivement adressees au destinataire 
>mentionne ci-dessus.Tout usage, reproduction ou 
>divulgation de cet e-mail est strictement 
>interdit si vous n'en etes pas le destinataire. 
>Dans ce cas, veuillez nous en avertir 
>immediatement par la meme voie et detruire 
>l'original. Merci.
>
>This e-mail is intended only for use of the 
>individual or entity to which it is addressed 
>and may contain information that is privileged, 
>confidential and exempt from disclosure under 
>applicable law.
>Any use, distribution or copying of this e-mail 
>communication is strictly prohibited if you are 
>not the addressee. If so, please notify us 
>immediately by e-mail, and destroy the original. 
>Thank you.
>
>______________________________________________
>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.

-- 
--------------------------------------
Don MacQueen
Environmental Protection Department
Lawrence Livermore National Laboratory
Livermore, CA, USA
925-423-1062