Re: need help with utf-8
Shaomei Liu <[email protected]> Tue, 17 Dec 2024 23:09:11 -0500
| Newsgroups | gmane.comp.lang.perl.modules.dbi.general |
|---|---|
| Message-ID | <CAK70g6OLYw6M0bkaWVnYD6bApR9qabVsKQw3n7KFhoa0iXFfXg@mail.gmail.com> |
--00000000000017d18806298394d2
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
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.
then I tried decode as suggested by Dan and it worked for both test program
and project. so issue solved for me!!!
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.
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";
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 s=
hows =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 b=
oth EL8 and EL7, so
decode fixed the issue
$query->execute(@values);
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
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
Thank you!!
Shirley
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 al=
so
> work with other DBDs. They should tell you if your problem is between Per=
l
> 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=9Cus=
e utf8=E2=80=9D to
> existing code without a clear understanding of where your program=E2=80=
=99s decode
> & encode points are.
>
> 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 docume=
nted or
> should be reported upstream.
>
> On Dec 16, 2024, at 17:13, Shaomei Liu <[email protected]>
> <[email protected]> wrote:
>
> =EF=BB=BF
> Hello,
> very happy to find this mailing list as it is my last resort!!
> 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=3Dc=
har
>
> 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 a=
nd
> EL7.
>
> =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";
>
> 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
>
> =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
>
> #from psql
> debugutf=3D# select * from table1;
> title
> ---------------
> =C3=A2\u0080\u009C =3D=3D=3D=3D=3D=3D=3D=3D=3D>unexpected
> (1 row)
>
>
> =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
>
> #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)
>
> Any feedback is appreciated.
> thank you
> Shirley
>
> --
> Alexander Fokenmailto:[email protected] <[email protected]>
>
>
--00000000000017d18806298394d2
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
<div dir=3D"ltr">Dear Dan, Mark, Felipe, Alexander,<div>Thank you all for y=
our valuable feedback!</div><div>as I=C2=A0replied Dan yesterday, this is m=
y first time to ask for support from a mailing list. I was very surprised a=
nd happy to get answers so quickly!</div><div>I added "use utf8;"=
as suggested by Dan and it worked for my test program shown in the email, =
but not for project.=C2=A0</div><div>then I tried decode as suggested by Da=
n and it worked for both test program and project. so issue solved for me!!=
!=C2=A0</div><div>perl version is 5.26.3 and 5.16.3 on EL8 and EL7 respecti=
vely.</div><div>DBI version is 1.641 and 1.627 on EL8 and EL7 respectively.=
</div><div><br></div><div>here is the test program with decode. I also prin=
ted 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.</div><div=
><div style=3D"color:rgb(204,204,204);background-color:rgb(31,31,31);font-f=
amily:Consolas,"Courier New",monospace;font-size:14px;line-height=
:19px;white-space:pre"><div><span style=3D"color:rgb(156,220,254)">xxx</spa=
n>.<span style=3D"color:rgb(156,220,254)">com</span><span style=3D"color:rg=
b(212,212,212)">></span> cat <span style=3D"color:rgb(156,220,254)">test=
utf_decode</span>.<span style=3D"color:rgb(156,220,254)">pl</span></div><di=
v>#<span style=3D"color:rgb(212,212,212)">!/</span>usr<span style=3D"color:=
rgb(212,212,212)">/</span>bin<span style=3D"color:rgb(212,212,212)">/</span=
>perl</div><div>use strict;</div><div>use <span style=3D"color:rgb(156,220,=
254)">warnings</span>;</div><div>use <span style=3D"color:rgb(156,220,254)"=
>DBI</span>;</div><div>use <span style=3D"color:rgb(156,220,254)">Encode</s=
pan> <span style=3D"color:rgb(206,145,120)">'decode'</span>;</div><=
div><span style=3D"color:rgb(156,220,254)">print</span> <span style=3D"colo=
r:rgb(206,145,120)">"DBI version: $DBI::VERSION</span><span style=3D"c=
olor:rgb(215,186,125)">\n</span><span style=3D"color:rgb(206,145,120)">&quo=
t;</span>;</div><br><div><span style=3D"color:rgb(156,220,254)">my</span> <=
span style=3D"color:rgb(156,220,254)">$db</span> <span style=3D"color:rgb(2=
12,212,212)">=3D</span> <span style=3D"color:rgb(206,145,120)">"debugu=
tf"</span>;</div><div><span style=3D"color:rgb(156,220,254)">my</span>=
<span style=3D"color:rgb(156,220,254)">$host</span> <span style=3D"color:r=
gb(212,212,212)">=3D</span> <span style=3D"color:rgb(206,145,120)">"db=
"</span>;</div><div><span style=3D"color:rgb(156,220,254)">my</span> <=
span style=3D"color:rgb(156,220,254)">$user</span> <span style=3D"color:rgb=
(212,212,212)">=3D</span> <span style=3D"color:rgb(206,145,120)">"post=
gres"</span>;</div><div><span style=3D"color:rgb(156,220,254)">my</spa=
n> <span style=3D"color:rgb(156,220,254)">$pass</span> <span style=3D"color=
:rgb(212,212,212)">=3D</span> <span style=3D"color:rgb(206,145,120)">"=
"</span>;</div><div><span style=3D"color:rgb(156,220,254)">my</span> <=
span style=3D"color:rgb(156,220,254)">$dbh</span> <span style=3D"color:rgb(=
212,212,212)">=3D</span> <span style=3D"color:rgb(156,220,254)">DBI</span>-=
><span style=3D"color:rgb(220,220,170)">connect</span>(<span style=3D"co=
lor:rgb(206,145,120)">"DBI:Pg:dbname=3D$db;host=3D$host"</span>,<=
span style=3D"color:rgb(156,220,254)">$user</span>,<span style=3D"color:rgb=
(156,220,254)">$pass</span>);</div><div><span style=3D"color:rgb(156,220,25=
4)">my</span> <span style=3D"color:rgb(156,220,254)">$sql</span> <span styl=
e=3D"color:rgb(212,212,212)">=3D</span> <span style=3D"color:rgb(206,145,12=
0)">'INSERT INTO table1 (title) VALUES (?)'</span>;</div><div><span=
style=3D"color:rgb(156,220,254)">my</span> <span style=3D"color:rgb(156,22=
0,254)">$query</span> <span style=3D"color:rgb(212,212,212)">=3D</span> <sp=
an style=3D"color:rgb(156,220,254)">$</span><span style=3D"color:rgb(156,22=
0,254)">dbh</span>-><span style=3D"color:rgb(220,220,170)">prepare</span=
>(<span style=3D"color:rgb(156,220,254)">$sql</span>);</div><div><span styl=
e=3D"color:rgb(156,220,254)">my</span> <span style=3D"color:rgb(156,220,254=
)">$bytes</span> <span style=3D"color:rgb(212,212,212)">=3D</span> <span st=
yle=3D"color:rgb(206,145,120)">'=E2=80=9C'</span>;</div><div><span =
style=3D"color:rgb(156,220,254)">my</span> <span style=3D"color:rgb(156,220=
,254)">$chars</span> <span style=3D"color:rgb(212,212,212)">=3D</span> <spa=
n style=3D"color:rgb(220,220,170)">decode</span>(<span style=3D"color:rgb(2=
06,145,120)">'UTF-8'</span>, <span style=3D"color:rgb(156,220,254)"=
>$bytes</span>);</div><div><span style=3D"color:rgb(156,220,254)">print</sp=
an> <span style=3D"color:rgb(206,145,120)">"$bytes contains "</sp=
an>.<span style=3D"color:rgb(220,220,170)">length</span>($bytes).<span styl=
e=3D"color:rgb(206,145,120)">" characters</span><span style=3D"color:r=
gb(215,186,125)">\n</span><span style=3D"color:rgb(206,145,120)">"</sp=
an>;</div><div><span style=3D"color:rgb(156,220,254)">print</span> <span st=
yle=3D"color:rgb(206,145,120)">"after decode $bytes contains "</s=
pan>.<span style=3D"color:rgb(220,220,170)">length</span>($chars).<span sty=
le=3D"color:rgb(206,145,120)">" characters</span><span style=3D"color:=
rgb(215,186,125)">\n</span><span style=3D"color:rgb(206,145,120)">"</s=
pan>;</div><div><span style=3D"color:rgb(156,220,254)">#my</span> @values <=
span style=3D"color:rgb(212,212,212)">=3D</span> ($bytes); #<span style=3D"=
color:rgb(212,212,212)">=3D=3D=3D=3D=3D=3D=3D>with this line, </span>Dat=
abase shows =E2=80=9C on EL7 but =C3=A2\u0080\u009C on EL8</div><div><span =
style=3D"color:rgb(156,220,254)">my</span> @values <span style=3D"color:rgb=
(212,212,212)">=3D</span> ($chars); =C2=A0#<span style=3D"color:rgb(212,212=
,212)">=3D=3D=3D=3D=3D=3D></span>Database shows =E2=80=9C on both EL8 <s=
pan style=3D"color:rgb(212,212,212)">and</span> EL7, so decode fixed the is=
sue</div><div>$<span style=3D"color:rgb(156,220,254)">query</span>-><spa=
n style=3D"color:rgb(220,220,170)">execute</span>(@values); </div><br><div>=
<span style=3D"color:rgb(156,220,254)">xxx</span>.<span style=3D"color:rgb(=
156,220,254)">com</span><span style=3D"color:rgb(212,212,212)">></span> =
.<span style=3D"color:rgb(212,212,212)">/</span><span style=3D"color:rgb(15=
6,220,254)">testutf_decode</span>.<span style=3D"color:rgb(156,220,254)">pl=
</span> =C2=A0<span style=3D"color:rgb(156,220,254)">#running</span> on EL8=
</div><div>DBI version: <span style=3D"color:rgb(181,206,168)">1.641</span>=
</div><div>=E2=80=9C contains <span style=3D"color:rgb(181,206,168)">3</spa=
n> characters</div><div>after decode =E2=80=9C contains <span style=3D"colo=
r:rgb(181,206,168)">1</span> characters</div><br><div><span style=3D"color:=
rgb(156,220,254)">xxx</span>.<span style=3D"color:rgb(156,220,254)">com</sp=
an><span style=3D"color:rgb(212,212,212)">></span> .<span style=3D"color=
:rgb(212,212,212)">/</span><span style=3D"color:rgb(156,220,254)">testutf_d=
ecode</span>.<span style=3D"color:rgb(156,220,254)">pl</span> <span style=
=3D"color:rgb(156,220,254)">#running</span> on EL7</div><div>DBI version: <=
span style=3D"color:rgb(181,206,168)">1.627</span></div><div>=E2=80=9C cont=
ains <span style=3D"color:rgb(181,206,168)">3</span> characters</div><div>a=
fter decode =E2=80=9C contains <span style=3D"color:rgb(181,206,168)">1</sp=
an> characters</div></div></div><div><br></div><div>Thank you!!</div><div>S=
hirley</div></div><br><div class=3D"gmail_quote gmail_quote_container"><div=
dir=3D"ltr" class=3D"gmail_attr">On Tue, Dec 17, 2024 at 3:30=E2=80=AFPM A=
lexander Foken via dbi-users <<a href=3D"mailto:[email protected]">dbi-=
[email protected]</a>> wrote:<br></div><blockquote class=3D"gmail_quote" st=
yle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padd=
ing-left:1ex"><u></u>
=20
=20
=20
<div>
<p>Hi,</p>
<p>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.<br>
</p>
<p>Alexander<br>
</p>
<div>On 17.12.2024 13:31, Felipe Gasper via
dbi-users wrote:<br>
</div>
<blockquote type=3D"cite">
=20
<div dir=3D"ltr">Respectfully to Dan & others, I don=E2=80=99t ad=
vocate
adding =E2=80=9Cuse utf8=E2=80=9D to existing code without a clear =
understanding
of where your program=E2=80=99s decode & encode points are.</di=
v>
<div dir=3D"ltr"><br>
</div>
<div dir=3D"ltr">Check to see what DBD::Pg actually writes to the
database. If it suddenly started encoding, that=E2=80=99s a breakin=
g
change that either was documented or should be reported
upstream.</div>
<div dir=3D"ltr"><br>
<blockquote type=3D"cite">On Dec 16, 2024, at 17:13, Shaomei Liu
<a href=3D"mailto:[email protected]" target=3D"_blank"><=
;[email protected]></a> wrote:<br>
<br>
</blockquote>
</div>
<blockquote type=3D"cite">
<div dir=3D"ltr">=EF=BB=BF
<div dir=3D"ltr">Hello,<br>
very happy to find this mailing list as it is my last
resort!!
<div>I have a project which uses DBI to write to postgres
DB.</div>
<div>after upgrading from RHEL7 to RHEL8, the utf-8
character is not displayed properly in the DB. DB has
correct utf-8 encoding set.</div>
<div>for example, left double quotation mark=C2=A0 =C2=A0=E2=80=
=9C=C2=A0 is
displayed as=C2=A0<span style=3D"background-color:rgb(31,31,3=
1);color:rgb(204,204,204);font-family:Consolas,"Courier New",mono=
space;font-size:14px;white-space:pre-wrap">=C3=A2\u0080\u009C</span>.</div>
<div>You can use this link to check hex utf-8 bytes</div>
<div><a href=3D"https://www.cogsci.ed.ac.uk/~richard/utf-8.cgi?=
input=3D%E2%80%9C&mode=3Dchar" target=3D"_blank">https://www.cogsci.ed.=
ac.uk/~richard/utf-8.cgi?input=3D%E2%80%9C&mode=3Dchar</a></div>
<div><br>
</div>
<div>below is the file <a href=3D"http://testutf.pl" target=3D"=
_blank">testutf.pl</a> which writes left
double quotation mark=C2=A0
=E2=80=9C to the database. it also shows the query results fr=
om
psql for both EL8 and EL7.<br>
<br>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3Dfile <a href=3D"http://testutf.=
pl" target=3D"_blank">testutf.pl</a>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D<br>
#!/usr/bin/perl<br>
use strict;<br>
use warnings;<br>
use DBI;<br>
print "DBI version: $DBI::VERSION\n";<br>
<br>
my $db =3D "debugutf";<br>
my $host =3D "db";<br>
my $user =3D "postgres";<br>
my $pass =3D "";<br>
my $dbh =3D
DBI->connect("DBI:Pg:dbname=3D$db;host=3D$host",=
$user,$pass);<br>
my $sql =3D 'INSERT INTO table1 (title) VALUES (?)';<=
br>
my $query =3D $dbh->prepare($sql);<br>
my @values =3D ('=E2=80=9C');<br>
$query->execute(@values);<br>
=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<br>
<br>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3Don RHEL8<br>
#execute <a href=3D"http://testutf.pl" target=3D"_blank">test=
utf.pl</a> which wrote =E2=80=9C to
database on RHEL8<br>
<a href=3D"http://text.tac1.dev.bia-boeing.com" target=3D"_bl=
ank">text.tac1.dev.bia-boeing.com</a>>
./<a href=3D"http://testutf.pl" target=3D"_blank">testutf.pl<=
/a><br>
DBI version: 1.641<br>
<br>
#from psql<br>
debugutf=3D# select * from table1;<br>
=C2=A0 =C2=A0 =C2=A0title<br>
---------------<br>
=C2=A0<font color=3D"#ff0000">=C3=A2\u0080\u009C=C2=A0
=3D=3D=3D=3D=3D=3D=3D=3D=3D>unexpected</font><br>
(1 row)<br>
<br>
<br>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3Don RHEL7<br>
#execute <a href=3D"http://testutf.pl" target=3D"_blank">test=
utf.pl</a> which wrote =E2=80=9C to
database on RHEL8<br>
<a href=3D"http://text.tac1.dev.bia-boeing.com" target=3D"_bl=
ank">text.tac1.dev.bia-boeing.com</a>>
./<a href=3D"http://testutf.pl" target=3D"_blank">testutf.pl<=
/a><br>
DBI version: 1.627<br>
<br>
#from psql<br>
debugutf=3D# select * from table1;<br>
=C2=A0 =C2=A0 =C2=A0title<br>
---------------<br>
<font color=3D"#0000ff">=C2=A0=E2=80=9C=C2=A0 =C2=A0 =C2=A0 =
=C2=A0=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D>expected</font><br>
(1 row)</div>
<div><br>
</div>
<div>Any feedback is appreciated.</div>
<div>thank you</div>
<div>Shirley</div>
</div>
</div>
</blockquote>
</blockquote>
<pre cols=3D"72">--=20
Alexander Foken
<a href=3D"mailto:[email protected]" target=3D"_blank">mailto:alexander@fo=
ken.de</a></pre>
</div>
</blockquote></div>
--00000000000017d18806298394d2--