Inserting Unicode characters into MySql using Stored Procedures

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