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.
______________________________________________
[email protected] 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.
