[RESOLVED] insert special chars into MySQL
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 15 of 15

Thread: [RESOLVED] insert special chars into MySQL

  1. #1
    Join Date
    Sep 2008
    Location
    Netherlands
    Posts
    865

    [RESOLVED] insert special chars into MySQL

    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?

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    11,178

    Re: insert special chars into MySQL

    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.

  3. #3
    Join Date
    Sep 2008
    Location
    Netherlands
    Posts
    865

    Re: insert special chars into MySQL

    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.

  4. #4
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    11,178

    Re: insert special chars into MySQL

    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?

  5. #5
    Join Date
    Sep 2008
    Location
    Netherlands
    Posts
    865

    Re: insert special chars into MySQL

    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.

  6. #6
    Join Date
    Oct 2008
    Location
    Cologne, Germany
    Posts
    756

    Re: insert special chars into MySQL

    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.
    win7 x86, VS 2008 & 2010, C++/CLI, C#, .NET 3.5 & 4.0, VB.NET, VBA... WPF is comming

    remeber to give feedback you think my response deserves recognition? perhaps you may want to click the Rate this post link/button and add to my reputation

    private lessons are not an option so please don't ask for help in private, I won't replay

    if you use Opera and you'd like to have the tab-button functionality for the texteditor take a look at my Opera Tab-UserScirpt; and if you know how to stop firefox from jumping to the next control when you hit tab let me know

  7. #7
    Join Date
    Sep 2008
    Location
    Netherlands
    Posts
    865

    Re: insert special chars into MySQL


  8. #8
    Join Date
    Sep 2008
    Location
    Netherlands
    Posts
    865

    Re: insert special chars into MySQL

    Quote Originally Posted by dannystommen View Post
    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.

  9. #9
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    11,178

    Re: insert special chars into MySQL

    Quote Originally Posted by dannystommen View Post
    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?

  10. #10
    Join Date
    Sep 2008
    Location
    Netherlands
    Posts
    865

    Re: insert special chars into MySQL

    Finally solved it.

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

    Code:
    private const string ConnectionString = "data source=localhost;database=temp_db;Uid=temp;password=pass;character set=utf8;";
    Thanks for help

  11. #11
    Join Date
    Sep 2008
    Location
    Netherlands
    Posts
    865

    Re: [RESOLVED] insert special chars into MySQL

    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

  12. #12
    Join Date
    Sep 2008
    Location
    Netherlands
    Posts
    865

    Re: [RESOLVED] insert special chars into MySQL

    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?!?

  13. #13
    Join Date
    Oct 2008
    Location
    Cologne, Germany
    Posts
    756

    Re: [RESOLVED] insert special chars into MySQL

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

    Code:
    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:
    Code:
    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 ;
    Last edited by memeloo; December 29th, 2009 at 06:45 AM.
    win7 x86, VS 2008 & 2010, C++/CLI, C#, .NET 3.5 & 4.0, VB.NET, VBA... WPF is comming

    remeber to give feedback you think my response deserves recognition? perhaps you may want to click the Rate this post link/button and add to my reputation

    private lessons are not an option so please don't ask for help in private, I won't replay

    if you use Opera and you'd like to have the tab-button functionality for the texteditor take a look at my Opera Tab-UserScirpt; and if you know how to stop firefox from jumping to the next control when you hit tab let me know

  14. #14
    Join Date
    Sep 2008
    Location
    Netherlands
    Posts
    865

    Re: [RESOLVED] insert special chars into MySQL

    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
    Code:
    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.

  15. #15
    Join Date
    Oct 2008
    Location
    Cologne, Germany
    Posts
    756

    Re: [RESOLVED] insert special chars into MySQL

    Quote Originally Posted by dannystommen View Post
    @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
    Last edited by memeloo; December 29th, 2009 at 08:23 AM.
    win7 x86, VS 2008 & 2010, C++/CLI, C#, .NET 3.5 & 4.0, VB.NET, VBA... WPF is comming

    remeber to give feedback you think my response deserves recognition? perhaps you may want to click the Rate this post link/button and add to my reputation

    private lessons are not an option so please don't ask for help in private, I won't replay

    if you use Opera and you'd like to have the tab-button functionality for the texteditor take a look at my Opera Tab-UserScirpt; and if you know how to stop firefox from jumping to the next control when you hit tab let me know

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Azure Activities Information Page

Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center