How can I handle unicode with Perl's DBI?

It's worth noting that if you're running a version of DBD::mysql new enough (3.0008 on), you can do the following: $dbh->{'mysql_enable_utf8'} = 1; and then everything's decode()ed/encode()ed for you on the way out from/in to DBI.

Enable UTF8, when you connect to database like this:

my $dbh = DBI->connect(
    "dbi:mysql:dbname=db_name", 
    "db_user", "db_pass",
     {RaiseError => 0, PrintError => 0, mysql_enable_utf8 => 1}
 ) or die "Connect to database failed.";

This should get you character mode strings with the UTF8 flag set as needed.

From DBI General Interface Rules & Caveats:

Perl supports two kinds of strings: Unicode (utf8 internally) and non-Unicode (defaults to iso-8859-1 if forced to assume an encoding). Drivers should accept both kinds of strings and, if required, convert them to the character set of the database being used. Similarly, when fetching from the database character data that isn't iso-8859-1 the driver should convert it into utf8.

And the specifics from DBD::mysql for mysql_enable_utf8

Additionally, turning on this flag tells MySQL that incoming data should be treated as UTF-8. This will only take effect if used as part of the call to connect(). If you turn the flag on after connecting, you will need to issue the command SET NAMES utf8 to get the same effect.

The term

$dbh->do(qq{SET NAMES 'utf8';});

definitely saves the day for accessing an utf-8 declared database, but take notice, if you are going to do any perl processing of any data obatined from the db it would be wise to store it in a perl var as an utf8 string with, as this operation is not implicit.

$utfstring = decode('utf8',$string_from_db);

of course, for proper i/o handling of utf8 strings (reading, printing, writing to output) remember to set

use open ':utf8';

and

binmode STDOUT, ":utf8";

the latter being essential for printing out utf8 strings. Hope this helps.

By default, the driver Perl/MySQL handles binary data (at least I concluded this from some experiments with MySQL 5.1 and 5.5).

Without setting mysql_enable_utf8, I encoded/decoded the strings to/from UTF-8 before writing/reading to/from the database.

It should not be relied upon the perl-internal string representation as an array of byte; be aware that the internal 'utf8' is not guaranteed to be standard UTF-8; in converse, the single byte encoding is not guaranteed to be ISO-8859-1; really do encode/decode to/from UTF-8 (and not 'utf8').

There are also some settings of MySQL (like SET NAMES above, as far as I remember there is a client encoding, a connection encoding, and a server encoding, whose interactions are not quite clear to me if they do not all have the same value) regarding to the encodings; setting all of them to UTF-8, and the recipe above, worked for me.

Leave this one out:

binmode STDOUT, ":utf8";

when using:

$dbh->do(qq{SET NAMES 'utf8';});

Otherwise your output will have double utf8 encoding, resulting in unreadable double byte characters!

I have a string with some special characters I am trying to put in a database. Namely chessgames from François-André Danican Philidor. When I try to put this name in my MySQL database using DBI, I get this error...

HAND CHECK: Fran�ois Andr� Philidor||NN||1||0||Fran�ois Andr� Philidor||NN||
DBD::mysql::st execute failed: Incorrect string value: '\xE7ois A...' for column 'white_player' at row 1 at chessgames.pl line 110, <GEN0> line 2360.
SQL Error: Incorrect string value: '\xE7ois A...' for column 'white_player' at row 1
Fran�ois Andr� Philidor||NN||1||0||Fran�ois Andr� Philidor||NN||

MySQL is having trouble understanding the special characters in the name, specifically the ç and the é. The first thing that helped was to add the following to my script...

use utf8; #some names have utf8 characters
binmode(STDOUT, ':utf8');

These commands changed the output so that when Perl printed the name with special characters, it printed the special characters properly. But MySQL still did not understand the special characters. Output changed slightly to this...

HAND CHECK: François André Philidor||NN||1||0||François André Philidor||NN||
DBD::mysql::st execute failed: Incorrect string value: '\xE7ois A...' for column 'white_player' at row 1 at chessgames.pl line 110, <GEN0> line 2360.
SQL Error: Incorrect string value: '\xE7ois A...' for column 'white_player' at row 1
Fran�ois Andr� Philidor||NN||1||0||Fran�ois Andr� Philidor||NN||

My manual check was working, but the MySQL query was still returning an error. I tried some of the other solutions listed above...

$dbh->do('SET NAMES utf8');

This solution did not work for me and produced the same errors.

$dbh->{'mysql_enable_utf8'} = 1;

This solution also did not work. A different question suggested when creating the table in SQL, to use this command. How to store unicode in MySQL?

white_player VARCHAR(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL, 

This did not work as well. I decided to look at the Unicode section in perldoc. I read through

$ perldoc perlunicode

Which pointed to

$ perldoc perlunitut

Which in turn pointed to

$ perldoc perlpacktut

Which is where I found the solution that worked for me. Here is the relevant text from perldoc perlpacktut under the Unicode section...

Please note: in the general case, you're better off using
"Encode::decode('UTF-8', $utf)" to decode a UTF-8 encoded byte string to
a Perl Unicode string, and "Encode::encode('UTF-8', $str)" to encode a
Perl Unicode string to UTF-8 bytes. These functions provide means of
handling invalid byte sequences and generally have a friendlier
interface.

Encoding (as a verb) is the conversion from *text* to *binary*.
Decoding is the conversion from *binary* to *text*.

I added the following command to my code

use Encode qw(encode decode); #suggestion from perldoc perlpacktut
$whiteplayer = decode("UTF-8",$pgn->white); 

However this also didn't work, database still shows Fran�ois Andr� Danican Philidor instead of François André Danican Philidor. I eventually found this answer

execute failed: Incorrect string value: '\xE4rvine...' with mariadb and perl DBD

And saw that this encoding was not UTF8, but a similar encoding iso-8859-1, also known as latin-1. I changed my decode statement to

$whiteplayer = decode("iso-8859-1",$pgn->white);

and finally it was working! The code is long and non-obvious, but I will sum up the solution as the following.

1. include use utf8;
2. include binmode(STDOUT, ':utf8');
3. do a little reading namely perldoc perlunicode, perldoc perlunitut, and perldoc perlpacktut
4. find the correct encoding of the text you want to enter into the database. Usually UTF-8 but check to make sure. Note: Careful not to mix up terms in the decode function. It is UTF-8 and not UTF8.
5. include use Encode qw(encode decode); and decode the string with special characters using something like $newstring=decode("UTF-8",$oldstring);

Here is some code to check if your string contains non ASCII characters...

while ( $string =~ /([^\x00-\x7f])/g ){
  print "string: $string contains non-ASCII character: $1\n"
}
print "\n";

As long as CPAN is configured correctly, you can install the necessary packages by running

$ cpan
$ cpan[1]> install utf8 Encode