Extended ASCII characters show up as junk in MySQL db is inserted through perl

Question

I have a MySQL 'articles' table and I am trying to make the following insert using SQLyog.

insert into articles (id,title) values (2356606,'Jérôme_Lejeune');

This works fine and the data shows fine when I do a select query.

The problem is that when I do the same insert query through my perl script, the name shows up with some junk characters in place of é and ô in the database. I need to know how to properly store the name through my script. The part of code that does the insert is like this.

$sql_insert = "insert into articles (id,title) values (?,?)";
$sth_insert = $dbh->prepare($sql_insert);
$sth_insert->execute($id,$title);

$id and $title have the correct required data which I have checked by print before I am inserting them. Please assist.

Answer 1

There are few things to follow.

First you have to make sure, that Perl understands that data which is moving between your program and DB is encoded as UTF-8 (i expect your databases and tables are set properly). For this you need to say it loud out on connecting to database, like this:

 my($dbh) = DBI->connect(
     'dbi:mysql:test',
     'user',
     'password',
     {
         mysql_enable_utf8 => 1,
     }      
);

Next, you need send data to output and you must set it to decaode data as UTF-8. For this i like pretty good module:

use utf8::all;

But this module is not in core, so you may want to set it with binmode yourself too:

binmode STDIN, ":utf8"; 
binmode STDOUT, ":utf8"; 

And if you deal with webpages, you have to make sure, that browser understoods that you are sending your data encoded as UTF-8. For that you should make sure your HTTP-headers include encoding:

Content-Type: text/html; charset=utf-8;

and set it with HTML META-tag too:

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

Now you should get your road covered.