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@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.