Re: Characters not getting stored
Posted by: Andrea Croci
Date: April 05, 2013 01:00AM

Thank you Rick, I'm impressed about the humongous volume of your knowledge far beyond MySQL. Here are the answers to your questions and more.

Approach 1: I first prepared the file with Eclipse under Windows 7. Then I tried again with Notepad and TexMakerX because I wanted to try saving the file as utf8 and I didn't find this feature in Eclipse (I'm completely new to it too). With Notepad and TexMakerX I tried to save the file as ANSI and as UTF8. More about this later. I make SQL files with those tools, which all get interpreted correctly with all settings when I do "source c:...", but again, there are no non-english characters in SQL. I'm also entering the commands and viewing the results from cmd (MySQL).

Approach 2:
Using the following setting:
character_set_database utf8
character_set_filesystem binary (again, I'm assuming it's not a good idea to change this, right?)
character_set_server utf8
character_set_system utf8

These settings I didn't change.

2.1: Using NAMES at cp850, the first column outputs 'ß' and the second E1.
2.2: With set NAMES latin1, idem as before ('ß' and E1).
2.3: With set NAMES utf8, idem as before.
2.4: With NAMES utf8, I did SELECT _cp850'ß', hex(_cp850'ß'); and I got a srange character as first (which I can't reproduce here) and E1.
2.5: With NAMES latin1, I did SELECT _cp850'ß', hex(_cp850'ß'); and I got a srange character as first (which I can't reproduce here, but different from that before) and E1.
2.6: With NAMES cp850, I did SELECT _cp850'ß', hex(_cp850'ß'); and I got 'ß' as first and E1.
2.7: With names cp850, I did SELECT _cp850'ß', hex(convert(_cp850'ß' using utf8)); and I got 'ß' and C39F.
2.8: With names cp850, I did SELECT _cp850'ß', hex(convert(_cp850'ß' using latin1)); and I got 'ß' and DF.
2.9: With names cp850, I did SELECT _cp850'ß', hex(convert(_cp850'ß' using cp850)); and I got 'ß' and E1.
2.10: With names latin1, I did SELECT _cp850'ß', hex(convert(_cp850'ß' using utf8)); and I got the strange character as 2.5 above and C39F.
2.11: With names latin1, I did SELECT _cp850'ß', hex(convert(_cp850'ß' using latin1)); and I got the strange character as 2.5 above and DF.
2.12: With names latin1, I did SELECT _cp850'ß', hex(convert(_cp850'ß' using cp850)); and I got the strange character as 2.5 above and E1.
2.13: With names utf8, I did SELECT _cp850'ß', hex(convert(_cp850'ß' using utf8)); and I got the same strange character as point 2.4 and C39F.
2.14: With names utf8, I did SELECT _cp850'ß', hex(convert(_cp850'ß' using latin1)); and I got the same strange character as point 2.4 and DF.
2.15: With names utf8, I did SELECT _cp850'ß', hex(convert(_cp850'ß' using cp850)); and I got the same strange character as point 2.4 and E1.

If this is not confusing enough (it is already hell for me, but I'm sure it takes a lot more to confuse you), I did additional testing on the importing of files of my first post.

The only setting among the many I have tried that seems to store the correct utf8 code of the characters (according to this site: http://www.utf8-zeichentabelle.de/) and to show it right on console, is

character_set_client latin1
character_set_connection latin1
character_set_results cp850

and only with the files saved as ANSI (utf8 also didn't work).

Now I'm all happy because I created a .csv file with the names of all the countries of the world in German, imported it and it is now stored and showing up properly, although damn me if I understand why.

The problem I'm getting now is when I want to query for a country whose german name starts with 'Ä'. If I do a select ... where name_de like 'Ä%' I get also those that starts with regular 'A' and the only one that starts with the A with the circle on top (which is not a german letter and I don't know why it was in the list of the official german names, but I imported it anyway). Well, I believe it would be appropriate to start a new thread with this problem (but obviously, if you have any hint here, it would be appreciated).

Thank you very much.

Options: ReplyQuote


Subject
Views
Written By
Posted
4988
April 02, 2013 01:19PM
1841
April 04, 2013 07:48PM
Re: Characters not getting stored
2081
April 05, 2013 01:00AM
1942
April 05, 2013 11:07PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.