Re: Strange issue with inserting varchar2()
[email protected] Fri, 28 May 2021 06:55:51 -0400
| Newsgroups | gmane.comp.lang.perl.modules.dbi.general |
|---|---|
| Message-ID | <[email protected]> |
--Apple-Mail-CD313E42-5064-4E35-A7A7-2458CE65D034 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable What about truncating the data in PLSQL? Eg an insert function? It would h= ave to input a CLOB I would guess=E2=80=A6 Sent from my iPhone > On May 28, 2021, at 4:15 AM, Marcus Bergner <[email protected]> wro= te: >=20 > =EF=BB=BF > 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 then= try to guesstimate where to make the UTF-8 cut, and try that repeatedly unt= il you manage to go under the max size you specified. Then you should have a= valid UTF-8 string whos' byte encoded representation is less than the given= number of bytes. There might be circumstances where this truncates slightly= more but has worked well in practice for me for exactly this purpose of sto= ring slightly too long text values in varchar columns. >=20 > sub truncByteString > { > my ($str, $bytes) =3D @_; > my ($len,$blen); > return undef unless defined $str; >=20 > 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; > } >=20 > return $str; > } >=20 > / 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@per= l.org> > Subject: RE: Strange issue with inserting varchar2() > =20 > One more >=20 > https://eur01.safelinks.protection.outlook.com/?url=3Dhttps%3A%2F%2Fstacko= verflow.com%2Fquestions%2F1454952%2Fdummys-guide-to-unicode&data=3D04%7C= 01%7CMarcus.Bergner%40vizrt.com%7C1767beb561424d97b19f08d92175a617%7Cc63c3ba= 740db460ebdf1e63a02a3ad59%7C0%7C0%7C637577611463282317%7CUnknown%7CTWFpbGZsb= 3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000= &sdata=3DwqFX4kaJyubBeA2vuUo4U5bhggujKZcyukf1%2FN5%2BwEg%3D&reserved= =3D0 >=20 >=20 >=20 >=20 > 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 not= ified that any disclosure, copying or distribution of the contents of this t= ransmission is strictly prohibited and that you should delete the contents o= f this transmission from your system immediately. Any comments or statements= contained in this transmission do not necessarily reflect the views or posi= tion of Radial or its subsidiaries and/or affiliates. >=20 >=20 --Apple-Mail-CD313E42-5064-4E35-A7A7-2458CE65D034 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable <html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D= utf-8"></head><body dir=3D"auto">What about truncating the data in PLSQL? &n= bsp;Eg an insert function? It would have to input a CLOB I would guess= =E2=80=A6<br><br><div dir=3D"ltr">Sent from my iPhone</div><div dir=3D"ltr">= <br><blockquote type=3D"cite">On May 28, 2021, at 4:15 AM, Marcus Bergner &l= t;[email protected]> wrote:<br><br></blockquote></div><blockquote t= ype=3D"cite"><div dir=3D"ltr">=EF=BB=BF <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii">= <div style=3D"font-family: Tahoma, Geneva, sans-serif; font-size: 10pt; colo= r: rgb(0, 0, 0);"> The way I have done it is to ensure that truncation only happens on valid UT= F-8 boundaries but length calculation is done in "use bytes" mode, and then t= ry to guesstimate where to make the UTF-8 cut, and try that repeatedly until= you manage to go under the max size you specified. Then you should have a valid UTF-8 string whos' byt= e encoded representation is less than the given number of bytes. There might= be circumstances where this truncates slightly more but has worked well in p= ractice 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 };</d= iv> <div> last unless $blen > $bytes;</div> <div> $len =3D length $str;<br> </div> <div> my $scaled_diff =3D int (($blen-$bytes)/($b= len/$len))-1;</div> <div> $scaled_diff =3D 1 if $scaled_diff<1;</d= iv> <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; colo= r: rgb(0, 0, 0);"> <br> </div> <div style=3D"font-family: Tahoma, Geneva, sans-serif; font-size: 10pt; colo= r: 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" sty= le=3D"font-size:11pt" color=3D"#000000"><b>From:</b> Fennell, Brian <fenn= [email protected]><br> <b>Sent:</b> Friday, May 28, 2021 03:11<br> <b>To:</b> Bruce Johnson <[email protected]>; dbi users <= ;[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%2F%= 2Fstackoverflow.com%2Fquestions%2F1454952%2Fdummys-guide-to-unicode&amp;= data=3D04%7C01%7CMarcus.Bergner%40vizrt.com%7C1767beb561424d97b19f08d92175a6= 17%7Cc63c3ba740db460ebdf1e63a02a3ad59%7C0%7C0%7C637577611463282317%7CUnknown= %7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6M= n0%3D%7C1000&amp;sdata=3DwqFX4kaJyubBeA2vuUo4U5bhggujKZcyukf1%2FN5%2BwEg= %3D&amp;reserved=3D0">https://eur01.safelinks.protection.outlook.com/?ur= l=3Dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F1454952%2Fdummys-guide-to-= unicode&amp;data=3D04%7C01%7CMarcus.Bergner%40vizrt.com%7C1767beb561424d= 97b19f08d92175a617%7Cc63c3ba740db460ebdf1e63a02a3ad59%7C0%7C0%7C637577611463= 282317%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6I= k1haWwiLCJXVCI6Mn0%3D%7C1000&amp;sdata=3DwqFX4kaJyubBeA2vuUo4U5bhggujKZc= yukf1%2FN5%2BwEg%3D&amp;reserved=3D0</a><br> <br> <br> <br> <br> The information contained in this electronic mail transmission is intended o= nly for the use of the individual or entity named in this transmission. If y= ou are not the intended recipient of this transmission, you are hereby notif= ied that any disclosure, copying or distribution of the contents of this transmission is strictly prohibited= and that you should delete the contents of this transmission from your syst= em 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> </div></blockquote></body></html>= --Apple-Mail-CD313E42-5064-4E35-A7A7-2458CE65D034--