UTF8/Unicode database access query

  • Thread starter Thread starter Bob Sillett
  • Start date Start date
B

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
 
Bob Sillett said:
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.
Click to expand...

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?
Click to expand...

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?
 
Bob said:
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?
Click to expand...

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
 
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
 
Bob Sillett said:
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.
Click to expand...

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.
Click to expand...

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...
 
Back
Top