UTF8/Unicode database access query

[Bob Sillett]

This is one of those issues that doesn't clearly fit into a single category
as it's both a C# and a database (mySql) issue.

I want to INSERT and SELECT text in UTF8 from a C# application to mySql.

My database character set is UTF8. I am using the native .NET provider from
mysql.com. Everything is at the current version.

My C# code for INSERT looks like this:

cmdMySql.CommandText = "INSERT INTO tblCommunity (Details) VALUES
(?Details)";
MySqlParameter P = new MySqlParameter();
P.MySqlDbType = MySqlDbType.String;
P.ParameterName = "?Details";
P.Value = "Any Russian text string";
cmdMySql.Parameters.Add(P);
cmdMySql.ExecuteNonQuery();

This works, but my Unicode text (Russian) ends up as ???????????????????? in
the database. When I'm in the C# debugger, I can see the Russian text as I
step through the code. So I know my problem is somewhere in how I'm passing
the data to the database. I have also tried a vanilla INSERT statement with
no parameters. It also gives me ??????????? for the Russian text. And if
my string mixes English and Russian, the English text is fine.

Does anyone have any ideas as to what I'm doing wrong?

Thanks.

Bob

[Jon Skeet [C# MVP]]

Bob Sillett <si***************@ABCREMOVE.yahoo.com> wrote:

This is one of those issues that doesn't clearly fit into a single category
as it's both a C# and a database (mySql) issue.
Actually, I'm not sure that it's C#-specific at all. .NET-specific
maybe, but not C#. Anyway, that goes for a good number of other
questions here, so on we go...
I want to INSERT and SELECT text in UTF8 from a C# application to mySql.

My database character set is UTF8. I am using the native .NET provider from
mysql.com. Everything is at the current version.

My C# code for INSERT looks like this:

cmdMySql.CommandText = "INSERT INTO tblCommunity (Details) VALUES
(?Details)";
MySqlParameter P = new MySqlParameter();
P.MySqlDbType = MySqlDbType.String;
P.ParameterName = "?Details";
P.Value = "Any Russian text string";
cmdMySql.Parameters.Add(P);
cmdMySql.ExecuteNonQuery();

This works, but my Unicode text (Russian) ends up as ???????????????????? in
the database. When I'm in the C# debugger, I can see the Russian text as I
step through the code. So I know my problem is somewhere in how I'm passing
the data to the database. I have also tried a vanilla INSERT statement with
no parameters. It also gives me ??????????? for the Russian text. And if
my string mixes English and Russian, the English text is fine.

Does anyone have any ideas as to what I'm doing wrong?

It sounds like the MySql provider is mucking things up. Is there
anything you can put on the connection string to tell it what character
set to use?

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too

[Robert Jordan]

Bob Sillett wrote:

This is one of those issues that doesn't clearly fit into a single category
as it's both a C# and a database (mySql) issue.

I want to INSERT and SELECT text in UTF8 from a C# application to mySql.

My database character set is UTF8. I am using the native .NET provider from
mysql.com. Everything is at the current version.

My C# code for INSERT looks like this:

cmdMySql.CommandText = "INSERT INTO tblCommunity (Details) VALUES
(?Details)";
MySqlParameter P = new MySqlParameter();
P.MySqlDbType = MySqlDbType.String;
P.ParameterName = "?Details";
P.Value = "Any Russian text string";
cmdMySql.Parameters.Add(P);
cmdMySql.ExecuteNonQuery();

This works, but my Unicode text (Russian) ends up as ???????????????????? in
the database. When I'm in the C# debugger, I can see the Russian text as I
step through the code. So I know my problem is somewhere in how I'm passing
the data to the database. I have also tried a vanilla INSERT statement with
no parameters. It also gives me ??????????? for the Russian text. And if
my string mixes English and Russian, the English text is fine.

Does anyone have any ideas as to what I'm doing wrong?

Are you sure you're using a MySQL server version (>= 4.1)
with UTF-8 support? You also need a quite up-to-date .NET
mysql-connector (ByteFX.Data) >= 0.9.

bye
Rob

[Bob Sillett]

Hi, Jon.

I cannot find any connection string options for character set. But that
doesn't mean that one doesn't exsit. As with many open source programs,
documentation tends to lag behind the actual code. For example, there is a
connection string option to enable ZIP-based compression. It's not listed
in the documentation, but is supported.

My main purpose to posting to this group was to see if I was doing something
fundamentally wrong in C# with the Unicode data. That is, if I needed to
run a text encoder or something.

I'll see what I can come up with in the mySql groups.

Thanks again.

Bob

[Jon Skeet [C# MVP]]

Bob Sillett <si***************@ABCREMOVE.yahoo.com> wrote:

I cannot find any connection string options for character set. But that
doesn't mean that one doesn't exsit. As with many open source programs,
documentation tends to lag behind the actual code. For example, there is a
connection string option to enable ZIP-based compression. It's not listed
in the documentation, but is supported.

My main purpose to posting to this group was to see if I was doing
something fundamentally wrong in C# with the Unicode data. That is,
if I needed to run a text encoder or something.
You really shouldn't need to, no. If you do, it's a definite deficiency
in the provider.
I'll see what I can come up with in the mySql groups.

Righto. If you could post back here to let us know what happens, I'd be
grateful - I've heard about similar problems before, but haven't heard
the answer...

--
Jon Skeet - <sk***@pobox.com>
http://www.pobox.com/~skeet
If replying to the group, please do not mail me too