Re: DBD::Pg && handling of UTF-8 char columns

Sent from my iPhone

> On Oct 12, 2019, at 3:32 AM, Matthias Apitz <[email protected]> wrote:
>=20
> =EF=BB=BFEl d=C3=ADa viernes, octubre 11, 2019 a las 04:03:31p. m. -0600, J=
on Jensen escribi=C3=B3:
>=20
>> Perl's internal storage of string data is a little odd. \xe4 is the=20
>> correct Unicode code point as per:
>>=20
>> https://en.wikipedia.org/wiki/Latin-1_Supplement_%28Unicode_block%29
>>=20
>> It is not UTF-8 encoded, true, but there's no reason Perl internally need=
s=20
>> to use UTF-8 specifically, and I believe for Latin-1 it does not by=20
>> default. It's a question of in-memory storage and processing (some kind o=
f=20
>> Unicode) vs. input/output (where you want UTF-8).
>>=20
>> If your script is configured to send UTF-8 to STDOUT, then I would expect=
=20
>> that \xe4 will show up as the UTF-8 \xc3\xa4 instead.
>=20
> I inserted another row into this table, encoded in UTF-8:
>=20
> pos71=3D# select d02name from d02ben where d02bnr =3D '08.05.1945' ;
> =D0=BE=D1=81=D0=B2=D0=BE=D0=B1=D0=BE=D0=B6=D0=B4=D0=B5=D0=BD=D0=B8=D0=B5
>=20
> pos71=3D# select d02name::bytea from d02ben where d02bnr =3D '08.05.1945' ;=

> \xd0bed181d0b2d0bed0b1d0bed0b6d0b4d0b5d0bdd0b8d0b520202020202020 ...
>=20
> If I run this through Perl DBD::Pg:
>=20
>   @row =3D $sth->fetchrow_array;
>   $HexStr =3D unpack("H*", $row[0]);
>   print "HexStr: " . $HexStr . "\n";
>   print "$row[0]\n";
>=20
>   binmode(STDOUT, ':encoding(utf8)');
>   print "after binmode: $row[0]\n";
>=20
>=20
> it gives:
>=20
> DBI is version 1.642, DBD::Pg is version 3.10.0
> client_encoding=3DUTF8, server_encoding=3DUTF8
> HexStr: 3e41323e313e3634353d38352020202020202020 ...
> Wide character in print at ./utf8-01.pl line 66.
> =D0=BE=D1=81=D0=B2=D0=BE=D0=B1=D0=BE=D0=B6=D0=B4=D0=B5=D0=BD=D0=B8=D0=B5
> after binmode: =D0=BE=D1=81=D0=B2=D0=BE=D0=B1=D0=BE=D0=B6=D0=B4=D0=B5=D0=BD=
=D0=B8=D0=B5
>=20
> and if I add an utf8::encode($row[0]) after the fetch, like:
>=20
>   @row =3D $sth->fetchrow_array;
>   utf8::encode($row[0]);
>=20
> it gives the correkt UTF-8 encoding:
>=20
> DBI is version 1.642, DBD::Pg is version 3.10.0
> client_encoding=3DUTF8, server_encoding=3DUTF8
> HexStr: d0bed181d0b2d0bed0b1d0bed0b6d0b4d0b5d0bdd0b8d0b520202020202020 ...=

> =D0=BE=D1=81=D0=B2=D0=BE=D0=B1=D0=BE=D0=B6=D0=B4=D0=B5=D0=BD=D0=B8=D0=B5
> after binmode: =C3=90=C2=BE=C3=91=C3=90=C2=B2=C3=90=C2=BE=C3=90=C2=B1=C3=90=
=C2=BE=C3=90=C2=B6=C3=90=C2=B4=C3=90=C2=B5=C3=90=C2=BD=C3=90=C2=B8=C3=90=C2=B5=

>=20
> i.e. the array returned by $sth->fetchrow_array does not contain an UTF-8 s=
tring.
>=20
> Why it has to be passed through utf8::encode($row[0]) ?

This seems related to the behavior of pg_encode_utf8. If you want/need to de=
al with octets only then set it to 0 on your $dbh.=20

$dbh =3D DBI->connect(...);
$dbh->{pg_encode_utf8} =3D 0;
...

HTH,

David=20