Encoding

Hi,
I wonder if anyone can help me.

I am using
- Debian Linux
- Perl 5.8.8
- DBI (I do not know how to figure out its version)
- MySQL 5.0.22-Debian_3-log

I want to store and handle data in UTF-8 but so far I have not been able 
to force Perl/DBI to do so.

I have created a table using

     my $sql = "CREATE TABLE $tbl (".join(", ", @columns).") CHARACTER 
SET utf8 COLLATE utf8_czech_ci;";
     $dbh->do($sql);

I feeded the table with data using

     my $list_of_columns = join(", ", @names);
     my $list_of_values = join(", ", map{"_utf8'$record->{$_}'"}(@names));
     my $sql = "INSERT INTO $tbl ($list_of_columns) VALUES 
($list_of_values);";
     $dbh->do($sql);

I have looked into the database using phpMyAdmin 2.8.2-Debian-0.1 and it 
really looked like the data were stored in correct UTF-8.

However, when I retrieve the data from Perl/DBI, something in the chain 
(MySQL? the driver? DBI?) decides that another encoding (probably, 
Latin1) would be better for me. It "converts" the strings from UTF-8 to 
that encoding, which means, at the time the data arrives in my Perl 
code, all the non-Latin1 characters have already been irrecoverably 
converted to question marks. I would be happy to decode the data myself 
but there is nothing I can do with the question marks.

I am using the following code to retrieve the data:

     my $sql = "SELECT kod, hry.nazev FROM hry INNER JOIN prodej ON 
hry.kod = prodej.kod_hry GROUP BY kod, hry.nazev";
     my $sqlobj = $dbh->prepare($sql);
     $sqlobj->execute();
     while(my ($kod, $nazev) = $sqlobj->fetchrow_array())
     {
         ...
     }

So far, the only workaround I have, is not to tell the DBI the data is 
UTF-8 when I am inserting it (i.e., drop the "_utf8" part before the 
single quote), and use Encode; decode("utf8", ...) on anything I fetch 
from the database. This way, the database never knows the data was a 
UTF-8 text, treats the bytes as Latin1 characters and returns them 
undisturbed. However, I cannot access the data using phpMyAdmin (unless 
I en/decode UTF in my brain), the string lengths do not reflect the 
reality etc.

Is there a better way to do it? I think there must be some small stupid 
locale-like setting telling the machine that I am a UTF guy. But the 
settings I was able to come up with did not help and I actually have no 
idea which part of the MySQL-driver-DBI-Perl chain is responsible.

Any hints are welcome.
Thanks

Dan

• Encoding by Daniel Zeman
• RE: Encoding by h-taguchi
• RE: Encoding by Martin J. Evans
• Re: Encoding by Daniel Zeman
• Re: Encoding by Martin J. Evans