Error Code: 1366. Incorrect string value - MySql stored procedure

Hello friends,

In continuation to the character encoding issues that I have been facing during development of my website AssameseBooks.com, here comes another blog.

The situation goes as follows,
I had the requirement to pull data that were saved in different tables to a single table. The data in the source tables were saved properly in UTF-8 character encoding.
I went through the most simplest approach to do it i.e. write a stored procedure and have the job one.

Well, the moment I executed the stored procedure, I encountered following error i.e.

Error Code: 1366. Incorrect string value: '\xE0\xA6\x85\xE0\xA6\xB8...' for column 'CUSTOM1' at row 1

where custom1 is a column in my database table having the text in assamese font.

Well, the solution was to alter my table and set the character set to UTF8 and collate to utf8_unicode_ci

so the query to be executed to achieve is,

ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Hope this comes handy to you guys too.

Cheers!! and Happy coding :-)

How to insert Indian font characters in MySql database using JAVA

Hello friends,

This blog is regarding my latest finding while working on my website Assamesebooks.com
As the name suggests, the website is all about Assamese literature and hence the books names had to be displayed in assamese as well.

I have created a dataload utility to load the product data from excel file into the mySql database.
The data in the excel file is in assamese font. Now the problem was that after the dataload, the assamese font  were not getting saved properly in the DB and were visible as ???? (question marks)

First I thought it might be due to the CHARSET or CHARACTER ENCODING that I had selected for my database schema and the table. But there were set to utf8_general_ci

This kept me puzzled for quite some time. While googling I came across the real solution which was related to how I was creating my JDBC connection URL.
In order to have the UNICODE characters saved properly to the mysql database we need to specify to use unicode and character encoding as UTF-8 while making the JDBC connection URL for databse.
This can be done by simply adding following parameters to the connection URL i.e. useUnicode=true&characterEncoding=UTF-8

So, your JDBC connection URL my look like,

String url = "jdbc:mysql://localhost/TestDB?useUnicode=true&characterEncoding=UTF-8";

Hope this information comes handy to you people as well.

Cheers! and happy coding :-)

PS: The google page where I found this helpful tip is :
http://uwudamith.wordpress.com/2011/09/02/how-to-insert-unicode-values-to-mysql-using-java/