3

in a database I have strings stored that contain special characters such as "§". Using the command

sqlQuery()

from package RODBC "§" is translated to "?". This is also the case for characters such as " ' " as it can be found in French words.

Of course I can not replace every "?" by one of the special characters after the query. Does anybody have an idea for this problem? I work under windows 7.

As requested the out put of sessionInfo()

R version 2.14.1 (2011-12-22)
Platform: x86_64-pc-mingw32/x64 (64-bit)

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252   
[3] LC_MONETARY=English_United States.1252 LC_NUMERIC=C                          
[5] LC_TIME=English_United States.1252    

attached base packages:
[1] grid      stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
[1] timeDate_2160.97 gridExtra_0.9.1  reshape2_1.2.2   RODBC_1.3-6      ggplot2_0.9.3.1  plyr_1.8        
[7] Runiversal_1.0.2

loaded via a namespace (and not attached):
 [1] colorspace_1.2-1   dichromat_2.0-0    digest_0.6.3       gtable_0.1.2       labeling_0.1       MASS_7.3-21       
 [7] munsell_0.4        proto_0.3-10       RColorBrewer_1.0-5 scales_0.2.3       stringr_0.6.2      tools_2.14.1
Simon O'Hanlon
  • 58,647
  • 14
  • 142
  • 184
Richi W
  • 3,534
  • 4
  • 20
  • 39
  • 4
    I think you need to set the `DBMSencoding` in `odbcDriverConnect()` to whatever the database is encoded in. I guess `"UTF-8"` would be worth a start. – Simon O'Hanlon Sep 05 '13 at 14:23
  • Any joy setting the encoding? – Simon O'Hanlon Sep 05 '13 at 17:13
  • I try soon and tell you - thanks! – Richi W Sep 06 '13 at 11:32
  • @SimonO101 I tried to set the DBMSencoding right in odbcConnect I tried: odbcConnect(mydb, uid=myuid,pwd=mypwd,DBMSencoding="UTF-8") but this did not solve the problem. I don't know how to use odbcDriverConnec but additional arguments to odbcConnect should be passed on. – Richi W Sep 06 '13 at 13:44
  • @SimonO101 I have checked it. The syntax odbcConnect("XX", uid="YY",pwd="ZZ",DBMSencoding="UTF-8"); passes the DBMSencoding to odbcDriverConnect. But still I get "?" instead of "§" ... – Richi W Sep 09 '13 at 15:02
  • @SimonO101 I have checked again: the charset of the database is UTF8 and I can pass it to odbcConnect but still I don't get the "§" ... – Richi W Sep 09 '13 at 15:24
  • is their any solution ? I have the same issue while connecting my access database with R. – Christophe D. Oct 29 '15 at 16:14
  • I did not solve this issue so far. – Richi W Oct 29 '15 at 16:28
  • did any got answer for this. I do got struck here – Praveen DA Apr 12 '17 at 13:01

1 Answers1

0

If you are seeing an issue where you are using sqlSave() in R to send to MYSQL where not all of the data streams through, it's likely because of special characters. The key is to make sure the character collation is set to the same in both..I found latin1 works best.

Rob.C
  • 209
  • 1
  • 2
  • 8