How to store Hindi characters in MYSQL database

Hi,

I'm using Unicode Character Set in MFC application.

Database and table created manually using MySQL via command prompt

Code:

---

CREATE DATABASE hindi_test CHARACTER SET utf8  COLLATE utf8_unicode_ci;
USE hindi_test;
CREATE TABLE `hindi` ( `data` varchar(200) COLLATE utf8_unicode_ci NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `hindi` (`data`) VALUES ('कंप्यूटर');

---

But showing via command prompt like bellow

Code:

---

mysql> select * from hindi;
+-----------+
| data      |
+-----------+
| ???? ?? |
+-----------+

---

The same table read using vc++ code

Code:

---

        TRY
        {
                CString QueryStr;
                CRecordset recset( &DB);
                QueryStr = "SELECT data FROM hindi";
                recset.Open(CRecordset::forwardOnly,QueryStr,CRecordset::readOnly);               
                while( !recset.IsEOF() )
                {       
                        recset.GetFieldValue(L"data",sLangname);
                        ::AfxMessageBox( sLangname);                       
                        recset.MoveNext();
                }       
                recset.Close();
        }
        CATCH(CDBException, e)
        {
                AfxMessageBox("Database error: "+e->m_strError);
        }
        END_CATCH;

---

Reading output "???? ??".

How can i insert/read hindi language input via both mysql & VC++?

also refereed
MySQL - Supported Character Sets and Collations

Code:

---

CREATE TABLE `hindi` ( `data` varchar(200) COLLATE ucs2_general_ci NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=ucs2 COLLATE=ucs2_general_ci;

---

can not read properly.

Use nvarchar, not varchar

Quote:

---

Originally Posted by Arjay

Use nvarchar, not varchar

---

that also tired sir

Code:

---

mysql> CREATE TABLE tbl_hindi (data nvarchar(1000));
Query OK, 0 rows affected (0.36 sec)

mysql> select * from tbl_hindi;
+-----------+
| data      |
+-----------+
| ?????? ?? |
+-----------+
1 row in set (0.00 sec)

---

And what result do you get with vc++ code? Note that code must be built as UNICODE!

Quote:

---

Originally Posted by VictorN

And what result do you get with vc++ code? Note that code must be built as UNICODE!

---

Code:

---

        TRY
        {
                CString QueryStr;
                CRecordset recset( &DB);
                QueryStr = "SELECT data FROM hindi";
                recset.Open(CRecordset::forwardOnly,QueryStr,CRecordset::readOnly);               
                while( !recset.IsEOF() )
                {       
                        recset.GetFieldValue(L"data",sLangname);
                        ::AfxMessageBox( sLangname);                       
                        recset.MoveNext();
                }       
                recset.Close();
        }
        CATCH(CDBException, e)
        {
                AfxMessageBox("Database error: "+e->m_strError);
        }
        END_CATCH;

---

Getting Output using VC++ code: ?????? ??

Note: using properties sets Unicode Character Set

Additionally done below procedures
1. Open the project Property Pages dialog box.
2. Select the Configuration Properties > C/C++ > Command Line property page.
3. In Additional Options, add the /utf-8 option to specify your preferred encoding.
4. Choose OK to save your changes.

What output do you get when you do select * from a mysql query browser?

Quote:

---

Originally Posted by Arjay

What output do you get when you do select * from a mysql query browser?

---

Getting Output : ?????? ??

Did you try

Code:

---

...
CREATE TABLE `hindi` ( `data` nvarchar(200) COLLATE utf8_unicode_ci NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `hindi` (`data`) VALUES (N'कंप्यूटर');

---

Quote:

---

Originally Posted by VictorN

Did you try

Code:

---

...
CREATE TABLE `hindi` ( `data` nvarchar(200) COLLATE utf8_unicode_ci NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO `hindi` (`data`) VALUES (N'कंप्यूटर');

---

---

table created using command prompt

Code:

---


C:\Windows\System32>mysql -u root -p
Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.14 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> drop table hindi;
Query OK, 0 rows affected (0.33 sec)

mysql> CREATE TABLE `hindi` ( `data` nvarchar(200) COLLATE utf8_unicode_ci NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Query OK, 0 rows affected (0.50 sec)

---

INSERT ata using vc++ code. reference image attached.
Output:

Code:

---

mysql> select * from hindi;
+-----------+
| data      |
+-----------+
| ????????  |
| ?????? ?? |
| ?????? ?? |
+-----------+
3 rows in set (0.00 sec)

---

Quote:

---

Originally Posted by saraswathisrinath

Getting Output : ?????? ??

---

If the data doesn't get returned correctly when you do a select query from a my sql browser, then the problem is in how you are setting up the database, not in the C++ code.

Try to not specify utf8 when you create the database, and store the data in a nvarchar type.

You'll want to be able to insert hindi into the table and read it back out with a query in a mysql browser.

Once you can do this successfully, then compile the C++ app as UNICODE and use CString or TCHAR string types amd this will probably just work.

Quote:

---

Originally Posted by Arjay

Try to not specify utf8 when you create the database, and store the data in a nvarchar type.

---

Tried all possibilities, i think problem is not table definition part. its from INSERT part.

Just noticed, getting some issues while using insert command from MySQL command prompt.
Hindi characters not wrote properly in the command prompt.

Code:

---

mysql> INSERT INTO `hindi` (`data`) VALUES (N'????????');
Query OK, 1 row affected (0.03 sec)

---

Image attached.

Is any setup/config missed?
Note: Server version: 5.6.14 MySQL
default chcp = 437

To change the chcp done below work,
opened Registry key HKEY_LOCAL_MACHINE\Software\Microsoft\Command Processor
added a String = Autorun & value = @chcp 65001>nul
Now checked chcp = 65001 from command line

Now changed the command line for Unicode character.

Tried again table creation and insert query. hindi letters not copy in to my command prompt

Code:

---

INSERT INTO `hindi` (`data`) VALUES (N'');

---