Re: hebrew in mysql
I am posting this everywhere in the hope of solving this quickly
First let me say: Thank you for your time and I will appreciate any help as I am quite desperate
This problem relates specifically to using hebrew in MySQL 4.1
I developed a pure Access application (i.e. both front and back end in Access 2002) and implemented it across the clinics in our organization. The Access system has been in place for about a year and is thankfully working very well.
I decided to convert the backend over to MySQL (4.1.7, on Windows 2000 Server, MySQL ODBC Driver 3.51) as I was worried that some of the clinics were getting to the point of overloading Access's capabilities (file size was around 500 meg in one of the clinics and there were the rare corruptions).
Using DBManager Professional (www.dbtools.com.br) to convert over the data structures of the tables and my own software to do the actual data transfer from Access to the new MySQL tables (basically justa series of INSERT statements), I created a test version of my database. The front end continued to be in Access 2002 and used linked tables through the ODBC driver to access the data.
The first hebrew related issue that came up was a collation sequence problem when I tried to UNION two tables. The problem was with the following query:
SELECT Patient_Name, Patient_Type FROM tbl_Regular_Patients
UNION
SELECT Patient_Name, "[a string in hebrew appears here]" FROM tbl_Special_Patients
This example would crash out with an error message related to incompatible collation sequences. I fixed it by wrapping the field in a CSTR function call (in the Access frontend):
SELECT Patient_Name, Cstr(Patient_Type) FROM tbl_Regular_Patients
UNION
SELECT Patient_Name, "[a string in hebrew appears here]" FROM tbl_Special_Patients
So, I realized that the hebrew strings as stored in MySQL were as such that Access could not work with them. But once I used the CSTR function, Access was able to convert the strings and fields being UNIONed into compatible formats.
I have checked the regional settings on my server and they all appear to be appropriate. I am able to use Notepad and Microsoft Office in hebrew without a problem.
The character set for the MySQL database is utf8. Actually though, this is also strange and perhaps part of the problem. Even after making changes in the my.ini file to get the default characer set and collation to be utf8 (and utf8_collation_gi), if I then run a "show variables" query, a number of the character set variables are still in latin (as in one of the character collations). I assume that this is all inter-related
Anyways, I thought this "trick" with the Cstr call solved everything and after further tests, I finally installed the first MySQL based system at one of our clinics. It has been in use a couple of days now and today I discovered a new problem. I had failed to check during the testing phase some of the lookup tables I use. I have one table that is very simple. It is a conversion table of the hebrew alphabet to the numerical equivalent of each letter. So,
-------------------------------------------
|HLetter | HNumValue | ts (timestamp field)|
-------------------------------------------
HLetter is a unique indexed field and HNumValue is the primary key.
When I start entering data (using DB Manager Professional, NOT Access), I get a problem when I enter the second line.
The first line is the hebrew letter aleph for HLetter and then 1 for HNumValue
The second line is the hebrew letter beit and then 2. But when I try to move to the next line after this 2nd line, I get the following error message: "Duplicate entry [some unintelligible characters] for key 3."
I played with this and realized that many of the hebrew letters that I tried entering in the HLetter field were being read as identical (in terms of the primary index refusing to accept them).
I then tried a simple SELECT statement for doing a sort (again through DB Manager Professional):
SELECT Last_Name FROM tbl_Patients ORDER By Last_Name.
The result of this was a confused sort order. When I went into Access and ran the same query, I also got the confused sort until I changed the SQL to:
SELECT Last_Name FROM tbl_Patients ORDER By CSTR(Last_Name) - my old friend CSTR worked again!. SO I know that my data in the MySQL is not corrupted but that there is a fundamental problem with the indexing and sort order (which are the same problem?)
I am quite desperate as the MySQL version of the system is running live. Fortunately, these lookup table issues are NOT crashing the system but they do make certain functions impossible (or unreliable).