Inserting Unicode characters into MySql using Stored Procedures

August 1, 2006

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

Entry Filed under: mysql. .

6 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.

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

    Thanks, is working fine for me.

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

    Thank you, thats help me a lot !

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

    Very Very THX !! Thats help my very much :)
    You are my MASTER !

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

    Excellent solution

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

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

Leave a Comment

Required

Required, hidden

Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <pre> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Trackback this post  |  Subscribe to the comments via RSS Feed


Top Posts

Top Clicks

Calendar

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

Tags

ASP.NET ASP.NET Tips WPF WPF Blog WPF Controls WPF thirdparty controls

a

Recent Posts

2

Fav links

Feeds

Blog Stats