UNICODE & .NET w/Enterprise Library
Posted: Mon 26 Feb 2007 22:27
I'm having trouble getting my application to read & write UNICODE text to the MySQL database. I'm using the MySQLDirect .NET2 Data Provider (v3.55.18.0) in conjunction with Microsoft Enterprise Library Data Access Application Block in C# with .NET Framework 2.0.
The same code reads/writes UNICODE characters just fine with the Microsoft SQL Server built-in provider for SQLEXPRESS, so I'm pretty sure the problem is with the MySQL Direct .NET provider, or in my MySQL database definitions or server configuration. I'm going to give a description of my environment and the problems below, and maybe somebody has an idea what I'm not doing or doing wrong.
MySQL database table definition (abbreviated):
CREATE TABLE `testtable` (
`id` int NOT NULL,
`Slug` varchar(100) character set utf8 NOT NULL default '',
`Text` mediumtext character set utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
MySQL Direct connect string entry in .config file:
My sample text: "Hello Привет"
MySQL server is configured to use 'latin1' as the default character set. But as I understand it, the UNICODE=true setting in the MySQL Direct connection string and the table definition should override that.
I am using stored procedures in MySQL to do the actual insert/update operations. I am using the CoreLab.MySql.MySqlAdapter (obtained via the Enterprise Library factory functions) to do an ADO.NET Update() call to change an existing record with the sample text.
When I make the update call, I get the CoreLab.MySql.MySqlException:
Data too long for column`Slug`at row 1`.
Obviously, however, my text is not anywhere near too long (100 is the length), so the error must mean something else. No difference if I try to update the mediumtext column instead (yes, it actually tells me the string is too long for a mediumtext!).
If I don't have the Russian characters in the update string, the update succeeds. If I don't use the UNICODE=true in the connection string, then the update call also succeeds, but the Russian characters change to ?????? (obviously the data isn't being sent right to the server). No difference if I choose to use UCS2 instead of UTF8 as the character set.
Any ideas why I'm having such difficulty doing such a trivial operation? Again, this works just fine with SQL Server into a nvarchar() and ntext field, but with MySQL and the unicode character sets, just bad results.
Appreciate any advice or assistance you can provide to get MySQL Direct .NET2 to do what I need.
CP
The same code reads/writes UNICODE characters just fine with the Microsoft SQL Server built-in provider for SQLEXPRESS, so I'm pretty sure the problem is with the MySQL Direct .NET provider, or in my MySQL database definitions or server configuration. I'm going to give a description of my environment and the problems below, and maybe somebody has an idea what I'm not doing or doing wrong.
MySQL database table definition (abbreviated):
CREATE TABLE `testtable` (
`id` int NOT NULL,
`Slug` varchar(100) character set utf8 NOT NULL default '',
`Text` mediumtext character set utf8 NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
MySQL Direct connect string entry in .config file:
My sample text: "Hello Привет"
MySQL server is configured to use 'latin1' as the default character set. But as I understand it, the UNICODE=true setting in the MySQL Direct connection string and the table definition should override that.
I am using stored procedures in MySQL to do the actual insert/update operations. I am using the CoreLab.MySql.MySqlAdapter (obtained via the Enterprise Library factory functions) to do an ADO.NET Update() call to change an existing record with the sample text.
When I make the update call, I get the CoreLab.MySql.MySqlException:
Data too long for column`Slug`at row 1`.
Obviously, however, my text is not anywhere near too long (100 is the length), so the error must mean something else. No difference if I try to update the mediumtext column instead (yes, it actually tells me the string is too long for a mediumtext!).
If I don't have the Russian characters in the update string, the update succeeds. If I don't use the UNICODE=true in the connection string, then the update call also succeeds, but the Russian characters change to ?????? (obviously the data isn't being sent right to the server). No difference if I choose to use UCS2 instead of UTF8 as the character set.
Any ideas why I'm having such difficulty doing such a trivial operation? Again, this works just fine with SQL Server into a nvarchar() and ntext field, but with MySQL and the unicode character sets, just bad results.
Appreciate any advice or assistance you can provide to get MySQL Direct .NET2 to do what I need.
CP