MySQL :: Connector/Net euro sign

Question

My MySQL database can store the euro symbol just fine (as I have tested with a native MySQL client (HeidiSQL)). But with the MySQL .NET connector my ASP.NET application is using I can't insert nor read it back from the database: I only get a ? character back. What could be the possible cause?

Answer 1

The following .Net command can encode these for your database, and then decode them into the original symbol...

System.Web.HttpUtility.HtmlEncode()

System.Web.HttpUtility.HtmlDecode()

Answer 2

I would suggest explicitly specifying the encoding in your connection string:

Server=localhost;Database=schema;Uid=foo;Pwd=bar;CharSet=utf8;

It usually resolves most encoding-related issues with MySQL's Connector/NET.

Answer 3

Have you correctly set the charset in your output page ? i.e. something like <META http-equiv="Content-Type" content="text/html; charset=UTF-8"> or charset=iso-8859-15 but not charset=iso-8859-1

Answer 4

I'd say that MySQL .NET connector sets some collation-related environment variables on connection; compare output of these queries both on HeidiSQL and .NET:

SHOW VARIABLES LIKE "character_set_%"; 
SHOW VARIABLES LIKE "collation_%";

They all should contain utf8-something. If not, you can alter them at runtime as in the manual.

Answer 5

Presumably you are inserting into the euro sign into a field that is typed as NVARCHAR or you wouldn't be able to insert and retrieve the euro sign correctly with other clients?

Are you using the correct syntax in your SQL statements for inserting and retrieving Unicode data?

To do this you must use the N specifier character before any string which is unicode or it will get silently converted to a single byte encoding and become an unreadable character e.g.

//Inserting Unicode data
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = "INSERT INTO SOME_TABLE (someField) VALUES (N'@text')";
cmd.Parameters.Add("text", "Some Unicode Text");
cmd.Connection = conn;
cmd.ExecuteNonQuery();

//Selecting Unicode data
MySqlCommand select = new MySqlCommand();
select.CommandText = "SELECT * FROM SOME_TABLE WHERE someField=N'@text'";
cmd.Parameters.Add("text", "Some Unicode Text");
cmd.Connection = conn;
//Execute your query and process your Results...

Answer 6

I had a similar problem, trying to import data from Sybase 12.5 to MS SQL Server 2005.

Basically MSSQL stores £ and euro signs fine in varchar if they're encoded right, which is great, but what I didn't realise is that Sybase was outputting the data as windows-1252 (western european) and not UTF-8 and this caused data like £ and the euro symbol to get translated by the import programme as '?'. Telling the import programme that the incoming data is encoded as 1252 fixed the issue.

If you encode the output from the DB as 1252 as you're reading it, you should get the right symbols out.

For example:

System.Text.Encoding enc = System.Text.Encoing.GetEncoding(1252);
MySqlDataReader msdr = command.ExecuteReader();
while(msdr.Read())
{
    ...
    string val = enc.GetString(enc.GetBytes(msddr.GetString(mssdr.GetOrdinal("varhcar_field"))));
    ...
}