Re: Fetchting UTF8 data with DBD::Pg, adding more and storing it back

[Alexander Farber]

Hi,

On Sun, Mar 15, 2009 at 10:05 PM, Ron Savage <r...@savage.net.au> wrote:
>> When I fetch player names from the database above,
>> they don't seem to be recognized as UTF8:
>
> I set this in httpd.conf, but you may want to set it in BEGIN{...}:
>
> PerlSetEnv   PGCLIENTENCODING UTF8

unfortunately

BEGIN {
        $ENV{PGCLIENTENCODING} = 'UTF8';
}

doesn't fix the mangling, but the pg_enable_utf8 => 1 does help:

$ ./dbi-utf.pl
10 ABCDE: UTF8 off, ASCII, 5 characters 5 bytes
Wide character in print at ./dbi-utf.pl line 32.
20 АБВГД: UTF8 on, non-ASCII, 5 characters 10 bytes
Wide character in print at ./dbi-utf.pl line 44.
30 russian АБВГД russian: UTF8 on, non-ASCII, 21 characters 26 bytes
Wide character in print at ./dbi-utf.pl line 44.
40 ♥russian АБВГД russian♥: UTF8 on, non-ASCII, 23 characters 32 bytes

Thank you all
Alex

PS: Here is my test case again

#!/usr/bin/perl -w

#BEGIN {
#       $ENV{PGCLIENTENCODING} = 'UTF8';
#}

use strict;
use utf8;
use DBI qw(:utils);
use Encode qw(encode_utf8 decode_utf8);

use constant HEARTS_HTML => "\x{2665}";
use constant X => 'phpbb';

my ($dbh, $ins1, $ins2, $sel1, $sel2, $href, $str1, $str2);

$dbh = DBI->connect('dbi:Pg:dbname=' . X, X, X,
    { RaiseError => 1, pg_enable_utf8 => 1 });

$dbh->do('create table test1 (col1 integer, col2 varchar(50))');
$dbh->do('create table test2 (col3 integer, col4 text)');
$ins1 = $dbh->prepare('insert into test1 values (?, ?)');
$ins2 = $dbh->prepare('insert into test2 values (?, ?)');
$sel1 = $dbh->prepare('select * from test1 order by col1');
$sel2 = $dbh->prepare('select * from test2');

$ins1->execute(10, 'ABCDE');
$ins1->execute(20, 'АБВГД'); # the 1st 5 russian letters
$sel1->execute();

while ($href = $sel1->fetchrow_hashref()) {
        print "$href->{col1} $href->{col2}: " .
            data_string_desc($href->{col2}) . "\n";

        $str1 = "russian $href->{col2} russian";
        $str2 = HEARTS_HTML . "russian $href->{col2} russian" . HEARTS_HTML;
}

$ins2->execute(30, $str1);
$ins2->execute(40, $str2);
$sel2->execute();

while ($href = $sel2->fetchrow_hashref()) {
        print "$href->{col3} $href->{col4}: " .
            data_string_desc($href->{col4}) . "\n";
}

$dbh->do('drop table test1');
$dbh->do('drop table test2');