Unicode in DBD::Pg

[Dominic Mitchell]

This is following on from a discussion on use.perl.org[1].

I'm trying to get Unicode back from a Postgres 7.3 database under perl 5.8 (DBD::Pg 1.20). I've got this test script:

#!/usr/bin/perl -w
use strict;
use warnings;
use 5.008;
use charnames ':full';
use DBI;
my $dbh =
DBI->connect( 'dbi:Pg:dbname=dom', 'dom', 'dom',
{ AutoCommit => 1, RaiseError => 1 } );
binmode( STDOUT, ':utf8' );

if ($ARGV[0] && $ARGV[0] eq 'setup' ) {
$dbh->do( 'CREATE TABLE test ( name text )' );
my $sth = $dbh->prepare( 'INSERT INTO test (name) VALUES (?)' );
for ('Fred', 'Barney', "\N{LATIN CAPITAL LETTER A WITH MACRON}dam"){
print "Inserting '$_'\n";
$sth->execute( $_ );
}
} elsif ($ARGV[0] && $ARGV[0] eq 'clean' ) {
$dbh->do( 'DROP TABLE test' );
} else {
my $sth = $dbh->prepare( 'SELECT name FROM test' );
$sth->execute;
while ( my ($name) = $sth->fetchrow_array ) {
print "name: $name\n";
}
}
$dbh->disconnect;

And when I run it, I get this output:

% perl fred.pl clean
% perl fred.pl setup
Inserting 'Fred'
Inserting 'Barney'
Inserting 'Ādam'
% perl fred.pl
name: Fred
name: Barney
name: Ādam

The problem is that in the last case, I'm seeing doubly-escaped UTF-8. This is (I think) because I am getting back UTF-8 from the database, but it's not having perl's UTF-8 flag set on it when it comes back from the database. So, perl think's it's Latin-1 and escapes each of the UTF-8 characters again when outputtt=ing it.

David Wheeler wondered whether my postgres was compiled with --enable-multibyte and whether my database was in the UNICODE encoding.

% psql -l
List of databases
Name | Owner | Encoding
-----------+----------+-----------
dom | dom | UNICODE
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(3 rows)

I'm using the -PGDG rpm, and looking at the spec file, it seems to indicate that --enable-multibyte is not specified, but it should be the default anyway. Is there a way that I can verify this from the installed software?

-Dom

[1] http://use.perl.org/~Matts/journal/9470

--
| Semantico: creators of major online resources |
| URL: http://www.semantico.com/ |
| Tel: +44 (1273) 722222 |
| Address: 33 Bond St., Brighton, Sussex, BN1 1RD, UK. |