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 ?

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]
      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]

        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]

Back to Seekers of Perl Wisdom