Inserting UTF-8 on Mysql using DBI

Fox has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks.

I'm trying to insert utf8 strings on a mysql database using DBI, I think the problem is on DBI/Perl because the equivalent code worked fine on PHP on this same machine and same database.

use strict;
use warnings;
use DBI;

binmode(STDOUT, ":utf8");

my $dbo = DBI->connect('dbi:mysql:testdb:127.0.0.1:3306','','') or die
+ $!;
$dbo->do('SET NAMES utf8');
$dbo->{'mysql_enable_utf8'} = 1;

my $str = "\x{72d0}\x{306e}\x{5de5}\x{4f5c}\x{5ba4}";

print "$str\n";
my $s = $dbo->prepare("INSERT INTO test VALUES(?);");
$s->execute($str);
[download]

As you can see, I tried some solutions I found out there already, but none of that worked. The code prints:
狐の工作室
but on the test table:

+-----------------+
| testcol         |
+-----------------+
| ?????           |
+-----------------+
[download]

the mysql server version is 5.1.41, DBI is 1.615 and perl is 5.12.1
I am out of ideas already..what could be the problem ?

Comment on Inserting UTF-8 on Mysql using DBI Select or Download Code

Replies are listed 'Best First'.
Re: Inserting UTF-8 on Mysql using DBI by Corion (Patriarch) on Oct 15, 2010 at 06:58 UTC
This looks good, assuming that the MySQL SQL program does not (properly) display utf8-encoded characters. Maybe you need to tell the MySQL program that you want the output displayed as utf8, like you did from within Perl? Maybe try retrieving the data back into Perl and displaying it from there or retrieving it from PHP and displaying it there?	[reply]
Re^2: Inserting UTF-8 on Mysql using DBI by Fox (Pilgrim) on Oct 15, 2010 at 10:53 UTC
no, the mysql client( from which I got the ????) is fine, as when I execute this insert from PHP I see the japanese characters instead. And retrieving the contents from the mysql to the PHP give me the same results as in the client.	[reply]
Re^3: Inserting UTF-8 on Mysql using DBI by Corion (Patriarch) on Oct 15, 2010 at 14:10 UTC
So what did you do to cross check that you're actually receiving and sending utf8 to/from MySQL? What data do you get in Perl when you insert a row using PHP? What data do you get in PHP when you insert a row using Perl? Try to eliminate the mysql client as a potential source of confusion - it might not output utf8 properly (I say without knowing the mysql client well) - the question marks seem to me ignored/escaped unicode characters.	[reply]
Re^4: Inserting UTF-8 on Mysql using DBI by Fox (Pilgrim) on Oct 16, 2010 at 12:48 UTC
Re^5: Inserting UTF-8 on Mysql using DBI by Corion (Patriarch) on Oct 16, 2010 at 17:10 UTC
Re^5: Inserting UTF-8 on Mysql using DBI by afoken (Chancellor) on Oct 18, 2010 at 14:43 UTC


Perl Monk, Perl Meditation
	PerlMonks