MySQL Bugs: #29833: Problem with Japanese Character

Bug #29833	Problem with Japanese Character
Submitted:	16 Jul 2007 20:16	Modified:	28 May 2013 8:39
Reporter:	Kamal Dhanani	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	MySQL ASP(not asp.net) Japanese Character ODBC Connector 5.0

Description:
I have problem with reading/writing the Japanese Character with MySQl 5.0.41 and combination of either ODBC Connector 3.51 and 5.00.11

Please help me out.

How to repeat:
I have problem inserting Japanese Character into MySQL 5.00.41 with ODBC Connector 3.51 and With the RecordSet.

For more information please see the code below:

        Set connUser = Server.CreateObject("ADODB.Connection")
        connUser.ConnectionString = SQLConnectionString
        
        Set rsUser = Server.CreateObject("ADODB.Recordset")
        rsUser.CursorLocation = 3

        connUser.Open
        connUser.Execute "SET NAMES 'UTF8'"
        connUser.Execute "SET CHARACTER SET UTF8"
  
        rsUser.Open "Select * from tableA where 1=2",connUser,3,3
        If rsUser.EOF Then
           rsUser.AddNew
           name = CStr(Request.Form("name"))
           rsUser.Fields("name").Value = name
           rsuser.Update
        End If

It fails with the error
"Microsoft Cursor Engine roor '80040e21'

Multiple-step operation generated errors. Check each status value."

on the line nubmer where the "rsUser.Fields("name").Value = name" is written in the asp page.

Now instead of updating the recordset I used the Conn.Execute

connUser.Execute "Insert into tableA(name) values ('" & Request("name") & "')"

It works.. but inserted "?????" into database
I tried the "Select HEX(name) from tableA" returns 3F3F3F3F3F.

I tried to read the data from database and show on webpage, it appears on the webpage as "?????"

Now I used the ODBC Connector 5.00.11
    
    - I used the update recordset method it fails with the same error
    - I used the conn.execute method works fine and inserting the right data.

Now again if I tried to read the data from database and show on webpage, it appears on the webpage as "?????"

Please Please somebody help me out.

Hi Kamal,

Thanks for sending a bug report.
We will try to reproduce your issue on version 3.51.

Regards,

Susanne

Hi Kamal.

Please, let's focus on MyODBC 3.51 as it is a stable connector. We can deal with problems in v5 later. This will make report much more readable.

>        connUser.Open
>        connUser.Execute "SET NAMES 'UTF8'"
>        connUser.Execute "SET CHARACTER SET UTF8"

This will never work with ODBC as "SET NAMES ..." should be executed right while 
driver manager is loading MyODBC and establishing connection. That's why you have "Initial statement" box in driver configuration (http://dev.mysql.com/doc/refman/5.0/en/myodbc-configuration-dsn-windows.html).

>        If rsUser.EOF Then
>           rsUser.AddNew
>           name = CStr(Request.Form("name"))
>           rsUser.Fields("name").Value = name
>           rsuser.Update
>        End If
> It fails with the error
> "Microsoft Cursor Engine roor '80040e21'

This should work. The only problem I see here is that ADO is confusing byte count with character count for UTF, which is a gray area and even MS and IBM read specs differently.

Now, please download MyODBC 3.51.17 and retest as there have been numerous changes, good deal of them related to problem you have. Please note that besides "Initial statement" box you now have "Character set" box too:
  * Added an option (CHARSET) for specifying the default character
    set for a connection. This must be used instead of a "SET NAMES"
    statement. Also available from the GUI setup dialog. (Related
    to Bug #6667 and Bug #9498.)

Waiting on your feedback.

Kamal, please see discussion in Bug#29576 too.

Dear Tonci Grgin,

Thanks for your valuable time.

I installed the 3.51.17 and did exectly whatever you asked me to try.

means, I have set initial Statement to "SET NAMES UTF8" and select the character set as "UTF8".

Hard Luck, The result is same with the AddNew Method it gives me same error but more description of the error is :

Description: Empty row cannot be inserted. Row must have at least one column value set.
Help context: 0
Help file: 
Native error: 1023
Error number: -2147467259
Error source: Microsoft Cursor Engine
SQL state: 

Now if I use the conn.Execute method, query works fine but enter all the japanese character as "?????". I confirmed it by HEX value. That was all 3F.

I have already set codepage and charset as following:
Response.CodePage = 65001
Response.CharSet = "utf-8"

I have also set the meta tag as follow:
<meta http-equiv="Content-Type" content="text/html;charset=utf-8">

Please guide me what next??

Kamal, you got it wrong:
>means, I have set initial Statement to "SET NAMES UTF8" and select the character set as "UTF8".

"Added an option (CHARSET) for specifying the default character set for a connection. This must be used *instead* of a "SET NAMES" statement."

You should *not* use initial Statement to "SET NAMES UTF8" but just define charset. Please try again and inform me of result.

I did that and the result is same as before.

With addNew method same error and with conn.execute method, it enters "?????"  characters.

Hi Kamal.

We have two problems here:
  1) name = CStr(Request.Form("name"))
  This won't work ever. If you check ODBC trace log from DM you'll see it's plain wrong...

  2) ADO - MyODBC + Unicode
  Until we support both Unicode W interfaces in our ODBC driver and have working UTF-8, this won't work fully either...

So, this is a genuine problem. Thanks for bringing it to our attention.

Hi Kamal,

We did a lot on UTF8 support during the last weeks.

Please, try our new version. MyODBC 5.1.

Also, please make sure, that really all is using utf8 (the table, where you store the data, set names utf8, your script, the web server configuration, ...). 

Regards,

Susanne

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Kamal, I would still like to see some info from you regarding:

  1) name = CStr(Request.Form("name"))
  Not using this syntax

  2) Trying new 5.1 driver (Since we discovered the incompatibility problem in ADO when working with UTF-8 data (bug#29367 and bug#13465) I do not think MyODBC 3.51 will ever support getting UTF-8 data in ADO.)

Also, a correct hex values in UTF-8 encoded database might help (like mysql> select hex(unicode_text) from test_unicode\G).

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Cannot continue without feedback from the reporter.
The bug is closed.