[R] MySQL query and character set

[Prof Brian Ripley]

We need more information from you to be able to fully understand this:

- I guess this is package RMySQL, but you did not say.
- What OS and what locale?

The posting guide asks for the output of sessionInfo(), and it would be 
very useful here.

On Sat, 14 Apr 2007, Martin Mundschenk wrote:

> Hi!
>
> I retrieve data from a MySQL Database, containing special characters (german 
> umlaute - äöü).
>
> rs<-dbSendQuery(con,statement=paste("SELECT ...")
>
> The query itself works without problem, but displaying the data generates an 
> error at the first line containing such characters.
>
> data<-fetch(rs,n=-1)
> data[x]

What was the error message, and what version of R was this?  (It may well 
be that a later version of R would solve this for you.)

> Whereas the following command works fine:
>
>> print("öäü")
> [1] "öäü"
>
> I assume that the problems are due to the database's character set, which is 
> latin1. Unfortunately I could not find any hint how to set the right encoding 
> for the database query.

I assume then that you are not in a Latin-1 locale, maybe in UTF-8?

It looks like the issue is setting the encoding for the results, not for 
the query itself (although the latter could also be an issue).

You can use iconv() to convert the results returned, and in R 2.5.0 you 
can use Encoding() to avoid having to do so.

> Is anyone familiar with that problem?

In general terms, yes.  As far as I am aware all the DBMS interfaces in R 
currently assume that the DBMS is either running in the same encoding as 
its client or will automatically convert to and from the locale of the 
client.  A version of RODBC to be released after 2.5.0 (and depending on R 
2.5.0) will allow the DBMS encoding to be set.

-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595