Re: Strange issue with inserting varchar2()
[email protected] (Marcus Bergner) Fri, 28 May 2021 08:14:51 +0000
| Newsgroups | perl.dbi.users |
|---|---|
| Message-ID | <HE1PR10MB1737492B7271559314F026F288229@HE1PR10MB1737.EURPRD10.PROD.OUTLOOK.COM> |
--_000_HE1PR10MB1737492B7271559314F026F288229HE1PR10MB1737EURP_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
The way I have done it is to ensure that truncation only happens on valid U=
TF-8 boundaries but length calculation is done in "use bytes" mode, and the=
n try to guesstimate where to make the UTF-8 cut, and try that repeatedly u=
ntil you manage to go under the max size you specified. Then you should hav=
e a valid UTF-8 string whos' byte encoded representation is less than the g=
iven number of bytes. There might be circumstances where this truncates sli=
ghtly more but has worked well in practice for me for exactly this purpose =
of storing slightly too long text values in varchar columns.
sub truncByteString
{
my ($str, $bytes) =3D @_;
my ($len,$blen);
return undef unless defined $str;
while(1) {
do { use bytes; $blen =3D length $str };
last unless $blen > $bytes;
$len =3D length $str;
my $scaled_diff =3D int (($blen-$bytes)/($blen/$len))-1;
$scaled_diff =3D 1 if $scaled_diff<1;
my $nlen =3D $len - $scaled_diff;
$str =3D substr $str,0,$nlen;
}
return $str;
}
/ Marcus
--
Marcus Bergner, M.Sc CSE............E-mail: [email protected]
Software Architect, Vizrt.................Mobile: +46 (0)730-808025
________________________________
From: Fennell, Brian <[email protected]>
Sent: Friday, May 28, 2021 03:11
To: Bruce Johnson <[email protected]>; dbi users <dbi-users@perl=
.org>
Subject: RE: Strange issue with inserting varchar2()
One more
https://eur01.safelinks.protection.outlook.com/?url=3Dhttps%3A%2F%2Fstackov=
erflow.com%2Fquestions%2F1454952%2Fdummys-guide-to-unicode&data=3D04%7C=
01%7CMarcus.Bergner%40vizrt.com%7C1767beb561424d97b19f08d92175a617%7Cc63c3b=
a740db460ebdf1e63a02a3ad59%7C0%7C0%7C637577611463282317%7CUnknown%7CTWFpbGZ=
sb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1=
000&sdata=3DwqFX4kaJyubBeA2vuUo4U5bhggujKZcyukf1%2FN5%2BwEg%3D&rese=
rved=3D0
The information contained in this electronic mail transmission is intended =
only for the use of the individual or entity named in this transmission. If=
you are not the intended recipient of this transmission, you are hereby no=
tified that any disclosure, copying or distribution of the contents of this=
transmission is strictly prohibited and that you should delete the content=
s of this transmission from your system immediately. Any comments or statem=
ents contained in this transmission do not necessarily reflect the views or=
position of Radial or its subsidiaries and/or affiliates.
--_000_HE1PR10MB1737492B7271559314F026F288229HE1PR10MB1737EURP_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<html>
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=
>
<style type=3D"text/css" style=3D"display:none;"> P {margin-top:0;margin-bo=
ttom:0;} </style>
</head>
<body dir=3D"ltr">
<div style=3D"font-family: Tahoma, Geneva, sans-serif; font-size: 10pt; col=
or: rgb(0, 0, 0);">
The way I have done it is to ensure that truncation only happens on valid U=
TF-8 boundaries but length calculation is done in "use bytes" mod=
e, and then try to guesstimate where to make the UTF-8 cut, and try that re=
peatedly until you manage to go under the
max size you specified. Then you should have a valid UTF-8 string whos' by=
te encoded representation is less than the given number of bytes. There mig=
ht be circumstances where this truncates slightly more but has worked well =
in practice for me for exactly this
purpose of storing slightly too long text values in varchar columns.<br>
<br>
sub truncByteString
<div>{<br>
</div>
<div> my ($str, $bytes) =3D @_;</div>
<div> my ($len,$blen);</div>
<div> return undef unless defined $str;</div>
<div><br>
</div>
<div> while(1) {</div>
<div> do { use bytes; $blen =3D length $str };</=
div>
<div> last unless $blen > $bytes;</div>
<div> $len =3D length $str;<br>
</div>
<div> my $scaled_diff =3D int (($blen-$bytes)/($=
blen/$len))-1;</div>
<div> $scaled_diff =3D 1 if $scaled_diff<1;</=
div>
<div> my $nlen =3D $len - $scaled_diff;<br>
</div>
<div> $str =3D substr $str,0,$nlen;</div>
<div> }</div>
<div><br>
</div>
<div> return $str;</div>
<span>}</span><br>
</div>
<div>
<div style=3D"font-family: Tahoma, Geneva, sans-serif; font-size: 10pt; col=
or: rgb(0, 0, 0);">
<br>
</div>
<div style=3D"font-family: Tahoma, Geneva, sans-serif; font-size: 10pt; col=
or: rgb(0, 0, 0);">
/ Marcus</div>
<div id=3D"Signature">
<div>
<div style=3D"font-family:Tahoma; font-size:13px">
<div class=3D"BodyFragment"><font size=3D"2">
<div class=3D"PlainText">--<br>
Marcus Bergner, M.Sc CSE............E-mail: [email protected]<br>
Software Architect, Vizrt.................Mobile: +46 (0)730-808025<br>
</div>
</font></div>
</div>
</div>
</div>
</div>
<div id=3D"appendonsend"></div>
<hr style=3D"display:inline-block;width:98%" tabindex=3D"-1">
<div id=3D"divRplyFwdMsg" dir=3D"ltr"><font face=3D"Calibri, sans-serif" st=
yle=3D"font-size:11pt" color=3D"#000000"><b>From:</b> Fennell, Brian <fe=
[email protected]><br>
<b>Sent:</b> Friday, May 28, 2021 03:11<br>
<b>To:</b> Bruce Johnson <[email protected]>; dbi users &l=
t;[email protected]><br>
<b>Subject:</b> RE: Strange issue with inserting varchar2() </font>
<div> </div>
</div>
<div class=3D"BodyFragment"><font size=3D"2"><span style=3D"font-size:11pt;=
">
<div class=3D"PlainText">One more<br>
<br>
<a href=3D"https://eur01.safelinks.protection.outlook.com/?url=3Dhttps%3A%2=
F%2Fstackoverflow.com%2Fquestions%2F1454952%2Fdummys-guide-to-unicode&a=
mp;data=3D04%7C01%7CMarcus.Bergner%40vizrt.com%7C1767beb561424d97b19f08d921=
75a617%7Cc63c3ba740db460ebdf1e63a02a3ad59%7C0%7C0%7C637577611463282317%7CUn=
known%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJ=
XVCI6Mn0%3D%7C1000&amp;sdata=3DwqFX4kaJyubBeA2vuUo4U5bhggujKZcyukf1%2FN=
5%2BwEg%3D&amp;reserved=3D0">https://eur01.safelinks.protection.outlook=
.com/?url=3Dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F1454952%2Fdummys-=
guide-to-unicode&amp;data=3D04%7C01%7CMarcus.Bergner%40vizrt.com%7C1767=
beb561424d97b19f08d92175a617%7Cc63c3ba740db460ebdf1e63a02a3ad59%7C0%7C0%7C6=
37577611463282317%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luM=
zIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=3DwqFX4kaJyubBeA2vuU=
o4U5bhggujKZcyukf1%2FN5%2BwEg%3D&amp;reserved=3D0</a><br>
<br>
<br>
<br>
<br>
The information contained in this electronic mail transmission is intended =
only for the use of the individual or entity named in this transmission. If=
you are not the intended recipient of this transmission, you are hereby no=
tified that any disclosure, copying
or distribution of the contents of this transmission is strictly prohibite=
d and that you should delete the contents of this transmission from your sy=
stem immediately. Any comments or statements contained in this transmission=
do not necessarily reflect the
views or position of Radial or its subsidiaries and/or affiliates.<br>
<br>
<br>
</div>
</span></font></div>
</body>
</html>
--_000_HE1PR10MB1737492B7271559314F026F288229HE1PR10MB1737EURP_--