MySQL Bugs: #36729: Hebrew displayed as question marks with ODBC 5.1

Bug #36729	Hebrew displayed as question marks with ODBC 5.1
Submitted:	15 May 2008 2:38	Modified:	16 May 2008 7:29
Reporter:	alon gfsfd	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.01.04	OS:	Windows (server 2003)
Assigned to:		CPU Architecture:	Any
Tags:	hebrew, odbc 5.1

Description:
When connecting via asp and ODBC connector 5.01.04 to my DB (mysql 5.0.x),
i am seeing hebrew displayed as question marks.

When connecting using ODBC connector 3.51.25 i am able to see hebrew properly
(but only after selecting the following: Control panel => regional and language settings => Advanced => Hebrew (under "Language for non-unicode programs") )

What might be the problem with connector 5.1?

my database is defined as utf8, and the fields are varbinary.

thank you.

How to repeat:
reproduce enviroment explained in description

Hi Alon and thanks for your report.

Ummm, where to start? First of all, I'm almost sure this is *not* MyODBC bug and here's why. First you should start with knowing which charset data is really in. The most important in charsets is field encoding and session encoding, only if they mismatch, any conversions are done and, in most cases, the double encoding happens.
In your case, I think, utf8 bytes are seen as hebrew (latin1...) chars and encoded into utf8 yet again. This is most common corruption and you eventually see A?A?A?A?A?, for example. Why are they seen as such? It's because you are treating data as UTF8 without marking it as such.

So, now you need to check all of your charset settings in MySQL server, your session encoding and stuff making sure they match. There is a lot of reading on this matter in BugsDB as well as in manual. I am confident that you'll find the answer and overcome your problem.

Just to get you started: Bug#36675, Bug#3250 (and especially http://bugs.mysql.com/file.php?id=8197). Try also searching bugsdb under Server/charsets (look for comments from Bar especially).

Waiting on your results.

you were currect.

i believe my data was not stored as utf8 while my db was defined as utf8.

once i called SET NAMES HEBREW before all of my queries (instead of the default which is utf8), it solved the problem.

however now i am more confused.
why when i inserted my data using asp it was not stored as true utf8?

do i need to switch to connector 5.1?
does my fields need to be varchar or varbinary?

thank you

Alon, your questions depend on what you want to do... If you only need Hebrew characters you might as well stay with 3.51 and "SET NAMES" workaround. If not, then you need UTF8 and 5.1 (no "SET NAMES"! and, of course, you'll need a trick or two re-code your data properly so that it can be fetched as UTF8).

Now, your data can not be retrieved as UTF8 as it's not encoded that way! The proof is that "SET NAMES HEBREW" works. This is also related to your questions of VARCHAR/VARBINARY filed types. as your data is not binary, VARCHAR would be the way to go.

Double conversion happens because your session charset is different from what's defined on table level, please see documentation regarding this (show variables like %char%).

As this is not a bug in our product I am not at liberty to discuss this further, for that you'll need support contract. And it's not fair to others having problems I haven't got time to check yet.
Anyway, you can find many reports on this in BugDB, just choose c/ODBC category and search for word "hebrew".

Thans it's work great fore me!