Inserting Unicode characters into MySql using Stored Procedures

August 1, 2006 at 10:00 am 15 comments

Inseting data using stored procedure are very easy, but consider a situation where you have table that contain fields with utf8 character set.

Table something like this

CREATE TABLE `tblperson` (
`Id` int(11) NOT NULL auto_increment, `pname` varchar(255) default NULL,
PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

If you use your simple insert procedure like the one below

CREATE PROCEDURE `P_updateperson`(
IN ppName VARCHAR(255))
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ”
BEGIN

insert into `tblperson`(
pName
)
values (ppName);

END;

This won’t work, it will insert the data but not in correct format. So in order to work this, you have to rewrite the stored procedure like this.

CREATE PROCEDURE `P_updateperson`(
IN ppName VARCHAR(255) charset utf8)
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ”
BEGIN

insert into `tblperson`(
pName
)
values (convert(ppName using utf8));

END;

Hope this tip will save a day for you

Advertisements

Entry filed under: mysql.

Error : A potentially dangerous Request.Form value was detected from the client Loading Gif Animations

15 Comments Add your own

  • 1. miklos  |  February 24, 2008 at 1:39 am

    This doesn’t work… If I do what you suggested, it just inserts a blank record for any values that contain extended characters.

    Reply
  • 2. vdoni  |  November 5, 2008 at 5:01 pm

    Thanks, is working fine for me.

    Reply
  • 3. Dimitar  |  June 24, 2009 at 9:54 am

    Thank you, thats help me a lot !

    Reply
  • 4. Nali  |  August 17, 2009 at 10:38 pm

    Very Very THX !! Thats help my very much 🙂
    You are my MASTER !

    Reply
  • 5. trungnguyen  |  August 28, 2009 at 2:31 am

    Excellent solution

    Reply
  • 6. Udomsak Thailand  |  September 30, 2009 at 10:42 am

    Thank you. Thank you.
    and thank you very mouch.

    Reply
  • 7. Gregor  |  January 21, 2010 at 7:11 pm

    This is useful if you have data in latin1 encoding and want to insert it into utf8 tables.

    The easy way is to connect do database usign SET names utf8 COLLATE utf8_general_ci and then you don’t have to convert input data in stored procedures.

    Reply
  • 8. Veselin M  |  February 1, 2010 at 3:21 pm

    Thanks a lot 🙂

    Reply
  • 9. tran phuong hoang  |  June 27, 2010 at 5:08 pm

    Excellent solution. Thanks a lot.

    Reply
  • 10. Tuan Le  |  October 14, 2010 at 4:55 am

    Thank so much

    Reply
  • 11. Arne Phaedrow  |  December 4, 2010 at 10:57 am

    Thanks, ist works fine. Even 4 years later!

    Reply
  • 12. Akhilesh Sharma  |  February 25, 2011 at 7:31 pm

    Great Tip. I was fighting with a related problem for last several hours and my first test shows that it works. I’m going to test it further.

    Thanks

    Reply
  • 13. kriss  |  April 25, 2011 at 1:38 pm

    Thank yoU!!!

    Reply
  • 14. Amarapala  |  July 18, 2011 at 1:35 am

    Thanks a lot!

    Reply
  • 15. Vikramraj Patil  |  March 31, 2015 at 11:35 am

    thank you very mush .nice tip.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Top Clicks

  • None

Calendar

August 2006
M T W T F S S
« Jul   Oct »
 123456
78910111213
14151617181920
21222324252627
28293031  

Recent Posts

Feeds

Blog Stats

  • 93,195 hits

%d bloggers like this: