Tag Archives: Database

Converting a database to Unicode with Perl

Posted on by Sean Redmond
Reply

It’s happened a few times—I’m transfering data from one database to another and the old one has a few accented characters in it, but came from the days before Unicode. So if you’re using Perl to pull data from the first database and you have a string with a a word like “façade”, when you try to insert it into the second database you get an error like DBD::Pg::db selectrow_array failed: ERROR: invalid byte sequence for encoding “UNICODE”: 0xe76164.

Fortunately, Unicode::MapUTF8 makes it easy with its to_utf8 function. For instance, if the source data is in iso-8859-1:

    use Unicode::MapUTF8 qw(to_utf);
    #
    # ... snip
    #
    # query $source for the value, and put a converted version
    # into $dest
    my $value = $source->selectrow_array('get my_val from my_table');
    $dest->do('insert into my_new_table (my_val) values (?)', {},
            to_utf8( { -string => $value, -charset => 'ISO-8859-1' });

Based on information found here: Problem with LATIN1 characters from Perl-DBI