MySQL Bugs: #13465: ASP shows question marks instead of Hebrew Chars when conn to MySQL 4.1

Bug #13465	ASP shows question marks instead of Hebrew Chars when conn to MySQL 4.1
Submitted:	25 Sep 2005 8:36	Modified:	10 Jan 2008 18:46
Reporter:	Alon Solell	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.11, 3.51.19	OS:	Windows (Windows 2003 Server Standard)
Assigned to:		CPU Architecture:	Any
Tags:	ADO

Description:
We have a mysql 4.0.26 loaded with data, all in hebrew.
We have another instance of mysql 4.1.14 loaded with the same data all in hebrew.
The database is working fine and the data is pulling up correctly when using 
mysql and PHP.
However, the same data shows question marks instead of Hebrewe chars when we use ASP pages with ODBC connection.
Since the ASP pages pull up the hebrew chars fine in version 4.0 of mysql, I'm left with the only thing that is not working, and that is the ODBC driver itself.
It has a problem connecting correctly to mysql version 4.1 and translating the Hebrew chars correctly.

How to repeat:
my.ini

[client]
port=3309
default-character-set=hebrew

[mysqld]

port=3309

basedir="C:/hostingapps/mysql.4.1.14/"
datadir="C:/hostingapps/mysql.4.1.14/Data/"
default-character-set=hebrew
default-storage-engine=INNODB

In my database I have the following for my tables:
Collation hebrew_general_ci 

Data is pulling up fine in phpMyAdmin version 
phpMyAdmin 2.6.4-pl1
MySQL 4.1.14-nt running on localhost:3309 as root@localhost

Suggested fix:
I don't know how to fix this.
This has been an open issue for several months and we need to get this resolved.
There is a problem with mysql version 4.0 that can't handle hebrew sorting very good and that is resolved in mysql version 4.1.
Please advise on what to do.

Thanks,

-Alon
alon@wsco.com

Could you please create and attach some ASP test case ? Do you use IIS?

My ASP sample code to extract data from MySQL

Attachment: alon.asp (application/octet-stream, text), 1.50 KiB.

Sample data to be used. The purpose is to see Hebrew and not question marks

Attachment: alon-TableSample-1.txt (text/plain), 20.43 KiB.

Yes. I use IIS6

Could you please insert the follow commands after your opened connection:

 SET character_set_client=hebrew
 SET character_set_results=hebrew

and test you asp file ?

Hi Vasily,

Where should I enter those entries?
In what file? the ASP file?
in the Database Creation?
In the my.ini file?

If I understand you correctly you want me to enter that after the DSN connnection? or as part of the DSN connection?

This is my ASP connect strings.
I have the choice of doing it via DSN, or as a connect string.
Both work (I just need to comment one out).

 'MySQL ODBC Connection String
 datasourcename = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost;DATABASE=databasename; USER=username; PASSWORD=password; port=3309 OPTION=3;"
 datasourcename = "samba1"

OR,. did you mean somewhere else?

Thanks,

-Alon.

datasourcename = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=localhost;DATABASE=databasename; USER=username; PASSWORD=password; port=3309 OPTION=3;SET character_set_client=hebrew; SET character_set_results=hebrew"

This just brings the same results with the question marks.
Any other suggetionts?

-Alon

I have the same problem with MySQL 5.0.18 usin MSVC and ODBC to get the data.
In fact I tried every possible character set for the connection without results.

To be honest with this problem I forced to remove MySQL, it is useless to me.

I was able to reproduce this bug in Win2003 server SP1

Hello Bogdan Degtyariov,

I am happy you were able to replicate the problem.
Till now, I've dropped the support for 4.1 and 5.x and left users who I wanted to port from MS-ACCESS on MS-ACCESS as it was a not a viable option to have question marks instead of chars.
Now that you finally got to replicate the problem, do you think this can be escallated and be resolved?
Thanks,

-Alon.

Bogdan, what is the status of this problem?

Bug#29367 has been marked as duplicate of this one.

MyODBC 5.1.1-beta displays Hebrew characters correctly.

Strange test case. Run it and see hebrew characters not displayed correctly through Response.Write

Attachment: alon2.asp (application/octet-stream, text), 259 bytes.

Unicode is not supported in ADO applications (including ASP) with the Connector/ODBC 3.51 driver. Please consider testing MySQL Connector/ODBC 5.1 which includes Unicode support for ASP.