Re: need help with utf-8

Do we know, in fact, why this changed?

The new behaviour may be =E2=80=9Cmore correct=E2=80=9D, but it=E2=80=99ll=
 still subtly break a bunch of stuff that worked fine before.

Encoding bugs in Perl are notoriously hard to track down. DBD::Pg is =
popular; it would be good to know exactly why this happened so that =
others could proactively adjust their code accordingly.

Also, I recommend my Unicode/UTF-8 talk on this topic, particularly the =
=E2=80=9Cuse utf8=E2=80=9D section starting at about 9m30s and again =
around 20m20s: https://www.youtube.com/watch?v=3DyH5IyYyvWHU

-FG


> On Dec 18, 2024, at 12:12=E2=80=AFAM, Dan Book <[email protected]> =
wrote:
>=20
> Indeed, how strings work has not changed, but DBD::Pg's interpretation =
of your strings probably did; the new behavior is more "correct" and now =
that you are sending it decoded Unicode characters you may avoid other =
mysterious issues. (Note that DBI itself does not handle strings, it =
just provides the interface, DBD::Pg defines how strings are send to and =
from the database)
>=20
> -Dan
>=20
> On Tue, Dec 17, 2024 at 11:09=E2=80=AFPM Shaomei Liu =
<[email protected]> wrote:
> Dear Dan, Mark, Felipe, Alexander,
> Thank you all for your valuable feedback!
> as I replied Dan yesterday, this is my first time to ask for support =
from a mailing list. I was very surprised and happy to get answers so =
quickly!
> I added "use utf8;" as suggested by Dan and it worked for my test =
program shown in the email, but not for project.=20
> then I tried decode as suggested by Dan and it worked for both test =
program and project. so issue solved for me!!!=20
> perl version is 5.26.3 and 5.16.3 on EL8 and EL7 respectively.
> DBI version is 1.641 and 1.627 on EL8 and EL7 respectively.
>=20
> here is the test program with decode. I also printed length. I thought =
it is a perl thing. but the length is the same on EL8 and EL7. so not =
sure it is perl or DBI change causing the issue.
> xxx.com> cat testutf_decode.pl
> #!/usr/bin/perl
> use strict;
> use warnings;
> use DBI;
> use Encode 'decode';
> print "DBI version: $DBI::VERSION\n";
>=20
> my $db =3D "debugutf";
> my $host =3D "db";
> my $user =3D "postgres";
> my $pass =3D "";
> my $dbh =3D DBI->connect("DBI:Pg:dbname=3D$db;host=3D$host",$user,$pass)=
;
> my $sql =3D 'INSERT INTO table1 (title) VALUES (?)';
> my $query =3D $dbh->prepare($sql);
> my $bytes =3D '=E2=80=9C';
> my $chars =3D decode('UTF-8', $bytes);
> print "$bytes contains ".length($bytes)." characters\n";
> print "after decode $bytes contains ".length($chars)." characters\n";
> #my @values =3D ($bytes); #=3D=3D=3D=3D=3D=3D=3D>with this line, =
Database shows =E2=80=9C on EL7 but =C3=A2\u0080\u009C on EL8
> my @values =3D ($chars);  #=3D=3D=3D=3D=3D=3D>Database shows =E2=80=9C =
on both EL8 and EL7, so decode fixed the issue
> $query->execute(@values);=20
>=20
> xxx.com> ./testutf_decode.pl  #running on EL8
> DBI version: 1.641
> =E2=80=9C contains 3 characters
> after decode =E2=80=9C contains 1 characters
>=20
> xxx.com> ./testutf_decode.pl #running on EL7
> DBI version: 1.627
> =E2=80=9C contains 3 characters
> after decode =E2=80=9C contains 1 characters
>=20
> Thank you!!
> Shirley
>=20
> On Tue, Dec 17, 2024 at 3:30=E2=80=AFPM Alexander Foken via dbi-users =
<[email protected]> wrote:
> Hi,
> DBD::ODBC has several tests related to Unicode handling =
(40UnicodeRoundTrip.t, 41Unicode.t, 45_unicode_varchar.t), they should =
also work with other DBDs. They should tell you if your problem is =
between Perl and Postgres or if it is simply in the encoding of your =
terminal.
> Alexander
> On 17.12.2024 13:31, Felipe Gasper via dbi-users wrote:
>> Respectfully to Dan & others, I don=E2=80=99t advocate adding =E2=80=9C=
use utf8=E2=80=9D to existing code without a clear understanding of =
where your program=E2=80=99s decode & encode points are.
>>=20
>> Check to see what DBD::Pg actually writes to the database. If it =
suddenly started encoding, that=E2=80=99s a breaking change that either =
was documented or should be reported upstream.
>>=20
>>> On Dec 16, 2024, at 17:13, Shaomei Liu <[email protected]> =
wrote:
>>>=20
>>> =EF=BB=BF Hello,
>>> very happy to find this mailing list as it is my last resort!!=20
>>> I have a project which uses DBI to write to postgres DB.
>>> after upgrading from RHEL7 to RHEL8, the utf-8 character is not =
displayed properly in the DB. DB has correct utf-8 encoding set.
>>> for example, left double quotation mark   =E2=80=9C  is displayed as =
=C3=A2\u0080\u009C.
>>> You can use this link to check hex utf-8 bytes
>>> =
https://www.cogsci.ed.ac.uk/~richard/utf-8.cgi?input=3D%E2%80%9C&mode=3Dch=
ar
>>>=20
>>> below is the file testutf.pl which writes left double quotation mark =
 =E2=80=9C to the database. it also shows the query results from psql =
for both EL8 and EL7.
>>>=20
>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3Dfile testutf.pl=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D
>>> #!/usr/bin/perl
>>> use strict;
>>> use warnings;
>>> use DBI;
>>> print "DBI version: $DBI::VERSION\n";
>>>=20
>>> my $db =3D "debugutf";
>>> my $host =3D "db";
>>> my $user =3D "postgres";
>>> my $pass =3D "";
>>> my $dbh =3D =
DBI->connect("DBI:Pg:dbname=3D$db;host=3D$host",$user,$pass);
>>> my $sql =3D 'INSERT INTO table1 (title) VALUES (?)';
>>> my $query =3D $dbh->prepare($sql);
>>> my @values =3D ('=E2=80=9C');
>>> $query->execute(@values);
>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
>>>=20
>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3Don RHEL8
>>> #execute testutf.pl which wrote =E2=80=9C to database on RHEL8
>>> text.tac1.dev.bia-boeing.com> ./testutf.pl
>>> DBI version: 1.641
>>>=20
>>> #from psql
>>> debugutf=3D# select * from table1;
>>>      title
>>> ---------------
>>>  =C3=A2\u0080\u009C  =3D=3D=3D=3D=3D=3D=3D=3D=3D>unexpected
>>> (1 row)
>>>=20
>>>=20
>>> =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3Don RHEL7
>>> #execute testutf.pl which wrote =E2=80=9C to database on RHEL8
>>> text.tac1.dev.bia-boeing.com> ./testutf.pl
>>> DBI version: 1.627
>>>=20
>>> #from psql
>>> debugutf=3D# select * from table1;
>>>      title
>>> ---------------
>>>  =E2=80=9C       =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D>expected
>>> (1 row)
>>>=20
>>> Any feedback is appreciated.
>>> thank you
>>> Shirley
> --=20
> Alexander Foken
> mailto:[email protected]