How to save Hindi character In MySQL database

Question

I am working in a Window Application(C#) and database is MySQL. In Application I am fetching gmail inbox and save it into Database. But my problem is that when I am using Insert Query in code Subject or message column showing message in Hindi but When save in Database it's showing character ???????? like this. I have already changed everything in UTF-8 in MySQL.

And when I Insert same using MySQL editor it saves in Hindi Font. That's a weird situation for me as I am new to MySQL.

  foreach (ImapMessageInfo message in messages)
        {

            InsertMail(message.UniqueId, message.Sender.ToString(), message.To.ToString(), message.Subject.Trim(), message.Date.LocalTime, client.GetMailMessage(message.SequenceNumber).BodyText);

        }



 public void InsertMail(string uid, string from, string to, string subject, DateTime datetime, string message)
    {
        try
        {

            MySqlCommand cmd = new MySqlCommand();

            cmd.Parameters.AddWithValue("@unique_id", uid);
            cmd.Parameters.AddWithValue("@sender", from);
            cmd.Parameters.AddWithValue("@reciever", to);
            cmd.Parameters.AddWithValue("@subject", subject);
            cmd.Parameters.AddWithValue("@date", datetime);
            cmd.Parameters.AddWithValue("@message", message);

            cmd.Parameters.AddWithValue("@mail_status", "unread");
            cmd.Connection = con;
            string query = "Insert Ignore into gmail_inbox(unique_id,sender,reciever,subject,date,message,mail_status) values (@unique_id,@sender,@reciever,@subject,@date,@message,@mail_status) ";
            cmd.CommandText = query;
            cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {

        }

    }

You don't save fonts in a database, you save characters. How are you inserting? Where's the C# code? How are you trying to view the database after inserting? — Wooble, Apr 12 '13 at 13:43

score 1 · Answer 1 · answered Apr 12 '13 at 13:58

1

Check the character set and collation of your database. Using UTF8 should allow you to save these correctly and to display them correctly. Also make sure that when you initialize the connection you set the connection character set as utf8, otherwise other errors can occur.

Basicaly you need to make sure that the column/table and the connection character set is utf8, and after that you should be fine inserting the data you want.

answered Apr 12 '13 at 13:58

Xnoise

502
2
9

Hello Xnoise, I checked all things. – Neetesh Apr 12 '13 at 13:59
character_set_client, utf8 character_set_connection, utf8 character_set_database, utf8 character_set_filesystem, binary character_set_results, utf8 character_set_server, utf8 character_set_system, utf8 character_sets_dir, C:\Program Files\MySQL\MySQL Server 5.5\share\charsets\ – Neetesh Apr 12 '13 at 14:00
Where are you getting the ???? Using the mysql client? Please describe where exactly where the problem appears. – Xnoise Apr 12 '13 at 14:01
When I Insert value through c# its show hindi character like ?????? but when I use same thing in SQL Editor its working fine. – Neetesh Apr 12 '13 at 14:02
Ok. Try issuing this before any query: SET NAMES UTF8; and then run the query. Also, the ??? characters: are they in the mysql client, in the software that you use for looking at the database, in the browser, etc. Depending on where you see these results multiple things can cause this, so let's try to reduce the problem a bit. – Xnoise Apr 12 '13 at 14:03
Character Also show ???? in software after reading datbase. But Manually update in My sql (using editor) its works fine. It means My sql supports Hindi Font and also when we pass value thats also show in Hindi then where is the problem. I use SET NAMES UTF8; but nothing happens. – Neetesh Apr 12 '13 at 14:10
Unfortunatelly at this point seems to be a problem related to C#, but i have no experience there unfortunatelly. The connection and the rest of the settings seem to be fine, and if adding from the database management software also works fine, then it can only be a problem of C# unless i missed something obvious. – Xnoise Apr 12 '13 at 14:13
http://stackoverflow.com/questions/11689129/c-sharp-mysql-utf8-encoding but not sure. – Xnoise Apr 12 '13 at 14:15
I also thinking may be problem in C#. – Neetesh Apr 12 '13 at 14:17
Any Way Thanks for Your valuable Time. – Neetesh Apr 12 '13 at 14:20
No problem. Please put the solution in here if you find it. – Xnoise Apr 12 '13 at 14:21
First thing I'd check if I get question marks is that the font used to display the characters supports the required glyphs. Hindi is certainly not widely supported in average fonts. – Mike Lischke Apr 12 '13 at 15:24

score 0 · Answer 2 · answered Jun 30 '16 at 10:33

1) Set table Collation to utf8_unicode_ci using below query

alter table <table_name> convert to character set utf8 collate utf8_unicode_ci;

2) You need to modify you Connection String and add Character Set=utf8 it should be like this

"User Id=YourUserID;Password=YourPassword;Port=3306;Database=YourDatabaseName;Character Set=utf8;

score 0 · Answer 3 · edited Aug 09 '17 at 09:51

0

Try this:

// mytable=2 fields id(Auto increment),title(nvarchar(max))
string title = "बिलाल";

SqlCommand cmd = new SqlCommand("insert into mytable values (N'" + title + "')", con);

con.Open();
cmd.ExecuteNonQuery();
con.Close();

edited Aug 09 '17 at 09:51

stelioslogothetis

9,371
3
28
53

answered Aug 09 '17 at 09:15

bilal chaudhari

79
4

score 0 · Answer 4 · edited Sep 24 '19 at 18:36

0

Follow the steps:

Change collection "utf8 - default collection" in the table in my sql.

Add "Character Set=utf8;" into connection string.

<appSettings>
    <add key="ConnString" value="Server=xxxxx; Port=3306; Database=books;Uid=xxxx; Pwd=xxxx; Convert Zero Datetime=True; Character Set=utf8;" />
</appSettings>

edited Sep 24 '19 at 18:36

derHugo

83,094
9
75
115

answered Sep 24 '19 at 17:31

Pawan Koshti

1

score 0 · Answer 5 · edited Sep 05 '20 at 13:20

For each database:

ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

For each table:

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

For each column:

ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

#update database properties
datasource.url = jdbc:mysql://localhost:3306/DBName?useUnicode=yes&characterEncoding=UTF-8

How to save Hindi character In MySQL database

5 Answers5

For each database:

For each table:

For each column: