[RESOLVED] insert special chars into MySQL

[dannystommen]

I'm having some problems storing special character into my MySQL database. Now I resolved this using encoding like described here. This solution works, but I'm not really happy about. The space character is replaced by ' '. For example, 'My varchar field' becomes 'My varchar field'. As you can see, a lot of extra data to save.

When I don't do this, special chars (like € sign) are replace by '?'.

When I use MySQL Administrator (and MySQL Query browser), I'm able to insert these special chars. Also, my colleague that uses PHP, is also able to store these chars. Thus MySQL is able to store these chars (using charset:latin1).

So the problem looks like it has something to do with .NET or the MySQL Connector for .NET. My guess is that it has something to do with the character set.

Does anyone has expierence insertin special chars into a MySQL DB? Or any one an idea how to solve this?

[Arjay]

You need to change the MySql database to store UNICODE characters. You are running into issues because your special characters are outside the latin char set boundries.

[dannystommen]

I'll try it tomorrow at work.

But why does the special char saved correctly when I use MySQL Administrator? The char goes into the same table.

[Arjay]

Don't know, but doesn't it makes sense that if you are trying to store characters outside the latin range it won't work if the db is set to ANSI?

[dannystommen]

I've tried charset UTF8 and ASCI, but both result in the same: a question mark.

The database has still charset Latin1, so that could be a problem. I'll create a new database and create this with another charset. I'll update it here when I'm finished.

But, as mentioned in the first post, my colleague that uses PHP has the exact same properties to its database as I do. The charset of both database and table are latin1. Why is he able to save these special chars then? I even tried to insert a special char into his table, also results in a question mark.

It looks like the problem has something to do with C# .NET, and not the MySQL database's charset.

[memeloo]

can you tell me please what MySQL connector for .Net are you using? I'd like to test it too. and some string with special characters that you are trying to save.

[dannystommen]

Connector-Net 6.2.2

[dannystommen]

I'll create a new database and create this with another charset. I'll update it here when I'm finished.

Hereby the update:

I created a new database and table with both charset UTF8. Still the same result.

[Arjay]

It looks like the problem has something to do with C# .NET, and not the MySQL database's charset.

The issue isn't with .Net because it uses UNICODE internally. Does the MySql connector support unicode?

[dannystommen]

Finally solved it.

When both database and table are set to UTF8, finally the connectionstring also needs this character set

private const string ConnectionString = "data source=localhost;database=temp_db;Uid=temp;password=pass;character set=utf8;";

Thanks for help

[dannystommen]

To make it even more interesting, I just found out that editing the connectionstring only can do the job. I now even store the € char into a database and table with charset latin1

[dannystommen]

Hmz, looks like I cheered to early. When I insert a special char into the table, and I use MySQL administrator to see the content of the database, I can see the chat.

But when I select the data via C#, the char suddenly disappeared?!?

[memeloo]

I tested it on my dummy-table that uses utf8_bin and with your connectionString I can both save and read the "€" sign.

MySqlConnection conn = new MySqlConnection("server=localhost;uid=csharp;pwd=csharp;database=csharptest;charset=utf8");
conn.Open();

MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = "INSERT INTO specialchars VALUES(NULL, \"4€\")";
cmd.Prepare();
cmd.ExecuteNonQuery();

conn.Close();
conn.Open();
cmd = new MySqlCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT * FROM specialchars";
MySqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())
{
	Debug.WriteLine(rdr[0] + " -- " + rdr[1]);
}

rdr.Close();
conn.Close();

and this is my table:

CREATE TABLE IF NOT EXISTS `specialchars` (
  `id` int(11) NOT NULL auto_increment,
  `specialtext` varchar(255) character set utf8 collate utf8_bin NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_bin AUTO_INCREMENT=4 ;

[dannystommen]

Then finally: the solution (this time it really is)

When creating a new database, set the database's and table's charset to UTF8. In the connectionstring also append 'character set=utf8'. Inserting and reading works fine.

But in mine case, I need to read/write data to the database of my colleague, which is already created with charset latin1 (both DB and tables). First, append the charset UTF8 to the connectionstring. Next, when creating your command, be sure to execute 'SET NAMES UTF8; SET CHARACTER SET UTF8;' first.

Thus

using (MySqlConnection connection = new MySqlConnection("data source=localhost;database=test_db;Uid=user;password=pass;character set=utf8;")) {
    connection.Open();
    using (MySqlCommand command = new MySqlCommand("SET NAMES UTF8; SET CHARACTER SET UTF8; SELECT * FROM tbl_test", connection)) {
      using( MySqlDataReader reader = command.ExecuteReader()) {
          //..
      }
    }					
  }
}

@Memeloo
Probably you'll know, but the MySqlConnection, MySqlCommand and MySqlDataReader should be disposed.

[memeloo]

@Memeloo
Probably you'll know, but the MySqlConnection, MySqlCommand and MySqlDataReader should be disposed.

sure it was only a quick&dirty code to test the special chars.

here a short supplement for casual readers:

SET NAMES indicates what character set the client will use to send SQL statements to the server.

SET CHARACTER SET is similar to SET NAMES but sets character_set_connection and collation_connection to character_set_database and collation_database.

more info on: dev.mysql.com