[R] RODBC : using and passing queries that use " in some arguments

Don MacQueen macq at llnl.gov
Tue Sep 22 00:55:43 CEST 2009


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




More information about the R-help mailing list