Re: hebrew in mysql
Posted by: Nahum Kovalski
Date: January 21, 2005 12:54AM

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).

Options: ReplyQuote


Subject
Views
Written By
Posted
17076
November 22, 2004 10:27AM
7606
November 25, 2004 02:48AM
6912
November 29, 2004 04:43PM
7048
November 29, 2004 04:45PM
5752
December 03, 2004 05:26AM
Re: hebrew in mysql
9102
January 21, 2005 12:54AM
4564
January 21, 2005 01:30AM
4229
January 22, 2005 02:55PM
4119
January 23, 2006 06:50AM
3880
January 29, 2006 05:38AM
8046
February 09, 2006 11:04AM
4481
January 24, 2005 02:38AM
4623
May 26, 2005 03:03PM
4969
August 20, 2005 02:03PM
9394
January 23, 2006 04:54AM
4954
January 23, 2006 05:26AM
3850
December 27, 2006 04:06AM
4421
February 07, 2007 12:56PM
3969
June 21, 2009 10:18AM
3657
June 21, 2009 11:43AM


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.