| Bug #62235 | Any version of ODBC is not working with UTF8 character set | ||
|---|---|---|---|
| Submitted: | 23 Aug 2011 9:27 | Modified: | 24 Jan 2014 12:26 |
| Reporter: | Adrian Chapela Cordeiro | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / ODBC | Severity: | S2 (Serious) |
| Version: | From 3.51 to 5.1.8 | OS: | Windows (Windows 2000, 2003, 2008 + ASP) |
| Assigned to: | Bogdan Degtyariov | CPU Architecture: | Any |
| Tags: | odbc connector asp | ||
[23 Aug 2011 19:49]
Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php You have to specify charset in ODBC DSN. See http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-configuration-connection-parameters.... for details.
[24 Aug 2011 8:30]
Adrian Chapela Cordeiro
To reduce options and to do an use case, I will say to you the options used to reproduce the problem and a workaround found yesterday:
Webserver SO: Windows 2000
Webserver: IIS 5.0
MYODBC Working Version: 5.1.5, 5.1.8. These versions work only if you create a DSN. They don't work if you use a connection string
MyODBC fail version: 3.51.27
Language code: ASP
MySQL Server: 5.1.53 comunity server
Script:
++++ ASP ++++
==========================================================================
<% @ CodePage = 65001 %>
<%
Option Explicit
Response.ContentType="text/html"
'65001
Dim strConn,conn_principal
strConn = "Driver={MySQL ODBC 5.1 Driver}; Server=mysql.server; Port=3306; Option=0;
Socket=; Stmt=; Database=DB; Uid=user; Pwd=pwd; charset=utf8" ' Doesn't work
strConn = "Driver={MySQL ODBC 3.51 Driver}; Server=mysql.server; Port=3306; Option=0;
Socket=; Stmt=; Database=DB; Uid=user; Pwd=pwd; charset=utf8" ' Doesn't work
'strConn = "DSN=dsn_name" 'Works OK with 5.1 but not with 3.51
'strConn = "DSN=dsn_name; charset=utf8" ' Doesn't work with any version
Set conn_principal = Server.CreateObject("ADODB.Connection")
conn_principal.Open strConn
Dim sql, test_rs, objStream
Set test_rs = Server.CreateObject ("ADODB.RecordSet")
sql = "SELECT test_field FROM test_tbl WHERE id = 1"
test_rs.Open sql, conn_principal, 0
Const adTypeBinary = 1
Const adTypeText = 2
Response.write "Test_field: " & test_rs.Fields("test_field").value & vbCrLf
Response.CharSet = "utf-8"
Response.end
%>
If you use any of the next connections:
strConn = "Driver={MySQL ODBC 5.1 Driver}; Server=mysql.server; Port=3306; Option=0;
Socket=; Stmt=; Database=DB; Uid=user; Pwd=pwd; charset=utf8" ' Doesn't work
strConn = "Driver={MySQL ODBC 3.51 Driver}; Server=mysql.server; Port=3306; Option=0;
Socket=; Stmt=; Database=DB; Uid=user; Pwd=pwd; charset=utf8" ' Doesn't work
'strConn = "DSN=dsn_name; charset=utf8" ' Doesn't work with any version
You will see the next:
Test_field: теÑтовыÐ
If you use any of the next connections:
strConn = "Driver={MySQL ODBC 3.51 Driver}; Server=mysql.server; Port=3306; Option=0;
Socket=; Stmt=; Database=DB; Uid=user; Pwd=pwd;"
'strConn = "DSN=dsn_name; "
You will see the next:
Test_field: ???????? ?????
If you use the next connection, and the driver used in the DSN is 5.1:
strConn = "DSN=dsn_name;" ' Doesn't work with any version
You will see the correct text:
Test_field: тестовый текст
The only way to make it works is using a DSN, use version 5.1 and not specifying any charset. Version 3.51 is not working in any way.
[24 Aug 2011 8:31]
Adrian Chapela Cordeiro
I still think that it is a bug...
[25 Aug 2011 6:11]
Bogdan Degtyariov
Adrian, thanks for reporting this bug. Verified with 5.1.8 and 3.51.27. The severity of this bug is not S1 (complete loss of service and no workarounds exist). Removing CHARSET=UTF8 from the connection string helps to resolve the problem. Setting the severity to S2.
[25 Aug 2011 7:34]
Bogdan Degtyariov
By default all results returned by Connector/ODBC driver are in UTF-8. Looks like the conversion function performs double conversion to UTF-8 data, which is already UTF-8
[25 Aug 2011 8:16]
Adrian Chapela Cordeiro
Yes...this is what we thought a double conversion is performed by MyODBC, always in 3.51 and in 5.1.8 when you put utf8 in string (but only is a theory).
[24 Jan 2014 12:26]
Bogdan Degtyariov
This problem can be resolved by using Connector/ODBC 5.2 ANSI version.
Description: When you try to use classic ASP with ODBC + MySQL table in UTF8, you can't show all characters for Russian language. Even if you use PHP directly with ODBC you can't show all characters. It is like ODBC is always sending result in latin1. How to repeat: Russian string to test: тестовый текст CREATE TABLE test_tbl ( id int(11) NOT NULL AUTO_INCREMENT, test_field varchar(15) NOT NULL DEFAULT '', PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO test_tbl (test_field) values ('тестовый текст'); Then you could connect to test server with one of this ASP or PHP to test: ++++ PHP ++++ ========================================================================== <?php header("Content-type: text/html; charset=utf-8"); ini_set ( 'odbc.defaultlrl' , '65001' ); function Conectarse() { if (!($link2 = mysql_connect("mysql.server:3306","user","pwd"))) { echo "ERROR CONNECTING"; exit(); } mysql_set_charset('utf8'); if (!mysql_select_db("DB",$link2)) { echo "ERROR SELECTING DATABASE"; exit(); } return $link2; } function ODBC() { $db_host = "mysql.server"; $db_user = "user"; $db_pass = "pwd"; $db_name = "DB"; $db_port = "3306"; $dsn = "DRIVER={MySQL ODBC 3.51 Driver};Server=$db_host;Port=$db_port;Database=$db_name;uid=$db_user; pwd=$db_pass"; $link = odbc_connect($dsn, $db_user, $db_pass); if (!$link) { echo "ERROR CONNECTING ODBC"; exit(); } return $link; } $link2 = Conectarse(); $link = ODBC(); // mysql_connect $sql = "SELECT test_field FROM test_tbl WHERE id = 1"; $res = mysql_query($sql, $link2) or die("ERROR"); // odbc_connect odbc_exec($link, "SET names utf8"); $sql = "SELECT test_field FROM test_tbl WHERE id = 1"; $result = odbc_prepare ($link, $sql); odbc_execute ($result); ?> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title> Russian Hello World! </title> </head> <body> <p> <?php $string = odbc_result_all($result, "border=1"); ?> </p> <div> <?php while ($row = mysql_fetch_array($res)) { echo "Test_field: <b>".$row['test_field']."</b><br>"; } ?> </div> </body> </html> <?php mysql_close($link2); ?> ++++ ASP ++++ ========================================================================== <% @ CodePage = 65001 Option Explicit Response.ContentType="text/html" '65001 Dim strConn strConn = "Driver={MySQL ODBC 3.51 Driver}; Server=mysql.server; Port=3306; Option=0; Socket=; Stmt=; Database=DB; Uid=user; Pwd=pwd; " Set conn_principal = Server.CreateObject("ADODB.Connection") conn_principal.Open strConn Dim sql, test_rs, objStream Set test_rs = Server.CreateObject ("ADODB.RecordSet") sql = "SELECT test_field FROM test_tbl WHERE id = 1" test_rs.Open sql, conn_principal, 0 Const adTypeBinary = 1 Const adTypeText = 2 Response.write "Test_field: " & test_rs.Fields("test_field").value & vbCrLf Response.CharSet = "utf-8" Response.end %>