Thanks for the suggestion Brigitta,
However, I am more interested why it works on 7.4 and not on 7.3
I do not know how to debug the sql code when it processes the regular
expression to see what the error is.
Cheers
Don
Don Brown
Senior Consultant
[1]OneTeam IT Pty Ltd
P: 1300 088 400
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Birgitta Hauser
Sent: Tuesday, 25 March 2025 6:04 PM
To: 'Midrange Systems Technical Discussion'
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: camelCase UDTF from Neils Liisberg not working on 7.3
What If you convert the incoming string into UTF-8 and then convert it and
before retuning convert it back into EBCDIC.
... or if you just pass the parameter values and the return values as
UTF-8?
In both cases you have to modify Niels function a little.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization – Education – Consulting on IBM i Database and Software
Architect IBM Champion since 2020
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown) "If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
"Train people well enough so they can leave, treat them well enough so
they don't want to. " (Richard Branson) "Learning is experience …
everything else is only information!" (Albert Einstein)
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Don
Brown via MIDRANGE-L
Sent: Tuesday, 25 March 2025 05:58
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: Don Brown <dbrown@xxxxxxxxxxxxxxxx>
Subject: RE: camelCase UDTF from Neils Liisberg not working on 7.3
Thanks Mark but still having issues.
And I think you meant 5770SS1 (not 5722SS1)
I deleted the library I restored (QICU)
Save license program 5770SS1 option 39 from a V7R3 machine where it had
been installed.
Restored the license program where I am testing.
But it appears the regular expressions are still not working.
I recreated the function and re tested but still not working.
I commented out all the regular expressions
SET TEMP = LOWER(NAME);
-- SET TEMP = REGEXP_REPLACE(TEMP , '[Æ]' , 'AE');
-- SET TEMP = REGEXP_REPLACE(TEMP , '[Ø@]' , 'OE');
-- SET TEMP = REGEXP_REPLACE(TEMP , '[Å]' , 'AA');
-- SET TEMP = REGEXP_REPLACE(TEMP , '[^A-Z0-9]' , ' ');
SET TEMP = TRIM(TEMP); -- DONE HERE IF THE NAME HAS TRAILING INVALID CHARS
-- SET TEMP = REGEXP_REPLACE(TEMP , ' +', '_');
But this breaks the procedure as it needs at least the last one to work.
So why are the regular expressions not working ?
Any suggestions ?
Is there anything I need to do after restoring that license program option
?
Thanks
Don
Don Brown
Senior Consultant
[1]OneTeam IT Pty Ltd
P: 1300 088 400
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Mark
Waterbury
Sent: Tuesday, 25 March 2025 2:16 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: camelCase UDTF from Neils Liisberg not working on 7.3
Hi, Neil,
Or just document that it needs 5722-SS1 Option 39, International
Components for Unicode. This option does not "cost extra" and comes with
the "base" IBM i operating system, and should be on the installation DVD
images. :-) All the best, Mark S. Waterbury
On Tuesday, March 25, 2025 at 12:10:06 AM EDT, Niels Liisberg
<nli@xxxxxxxxxxxxxxxxx> wrote:
It is the regex that uses Unicode conversion,
My solution to you will be- simply remove all the special regex stuff we
have to deal with in Scandinavia
tirs. 25. mar. 2025 kl. 10.17 skrev Don Brown via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx>:
> I created the UDTF from Neils and it worked on a 7.4 box. (I have
> included the procedure below.)
>
> I compiled on a 7.3 partition successfully but received an error when
> I tried to run it about a missing QICU library.
>
> I searched for an installation method for this library and failed to
> find anything.
>
> I saved this library from another 7.3 partition and restored on the
> partition where I was testing the UDTF.
>
> Now the functions runs but the results are not correct. Neils
> provided an example as follows.
>
> -- usecase
> values ( qusrsys.camel_case('Saldo beløb'));
>
> On 7.4 this returns ... saldoBeloeb
>
> On 7.3 this returns ... <Result is blank>
>
> I am not seeing any errors in the log.
>
> Visual explain provided no errors.
>
> Any suggestions greatly appreciated.
>
> Thanks
> Don
>
> -- UDTF to return a camelcase of a string for column naming purposes
>
> -- Simply paste this gist into ACS SQL and run it to create the UDTF.
>
> -- Note: I am using library QUSRSYS. I suggest you put it into your
> own tool library
>
> -- It is a cool example how far you can go with SQL: Have fun -
> -- (C) Niels Liisberg 2021
>
> -- This gist is distributed on an "as is" basis, without warranties
> -- or conditions of any kind, either express or implied.
>
>
>-----------------------------------------------------------------------
>----------------------- create or replace function qusrsys.camel_case
>(
> name varchar(128)
> )
> returns varchar(128)
> no external action
> set option output=*print, commit=*none, dbgview = *list begin
> declare temp varchar(128);
> declare outString varchar(128);
> declare i int;
> declare upperNext int;
> declare c char(1);
>
> -- First snake-case the name to sanitise it AND it will also works
>if the string is already snake-case
> set temp = lower(name);
> set temp = regexp_replace(temp , '[æ]' , 'ae');
> set temp = regexp_replace(temp , '[ø@]' , 'oe');
> set temp = regexp_replace(temp , '[å]' , 'aa');
> set temp = regexp_replace(temp , '[^a-z0-9]' , ' ');
> set temp = trim(temp); -- Done here if the name has trailing
>invalid chars
> set temp = regexp_replace(temp , ' +', '_');
>
> -- Columns names can not begin with a digit
> if substring(temp , 1 , 1) >= '0' then
> set temp = 'x' concat temp;
> end if;
>
> set i = 1;
> set upperNext = 0;
> set outString = '';
> while i <= length(temp) do
> set c = substr(temp , i ,1);
> if c = '_' then
> set upperNext = 1;
> elseif upperNext = 1 then
> set outString = outString || upper(c);
> set upperNext = 0;
> else
> set outString = outString || c;
> end if;
> set i = i +1;
> end while;
> return outString;
> end;
>
> -- usecase
> values ( qusrsys.camel_case('Saldo beløb'));
>
>
> Brisbane - Sydney - Melbourne
>
>
> Don Brown
>
> Senior Consultant
>
>
>
>
> P: 1300 088 400
>
>
>
>
> DISCLAIMER. Before opening any attachments, check them for viruses and
> defects. This email and its attachments may contain confidential
> information. If you are not the intended recipient, please do not
> read, distribute or copy this email or its attachments but notify
> sender and delete it. Any views expressed in this email are those of
> the individual sender
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
> subscribe, unsubscribe, or change list options,
> visit: [2][2]
https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives at
> [3][3]
https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
> related questions.
>
>
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: [4][4]
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
[5][5]
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: [6][6]
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
[7][7]
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
--
Message protected by MailGuard: e-mail anti-virus, anti-spam and content
filtering.
[8][8]
https://www.mailguard.com.au
References
Visible links
1. [9]
https://www.oneteamit.com.au/
2. [10]
https://lists.midrange.com/mailman/listinfo/midrange-l
3. [11]
https://archive.midrange.com/midrange-l.
4. [12]
https://lists.midrange.com/mailman/listinfo/midrange-l
5. [13]
https://archive.midrange.com/midrange-l.
6. [14]
https://lists.midrange.com/mailman/listinfo/midrange-l
7. [15]
https://archive.midrange.com/midrange-l.
8. [16]
https://www.mailguard.com.au/
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: [17]
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
[18]
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: [19]
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
[20]
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
--
Message protected by MailGuard: e-mail anti-virus, anti-spam and content
filtering.
[21]
https://www.mailguard.com.au
References
Visible links
1.
https://www.oneteamit.com.au/
2.
https://lists.midrange.com/mailman/listinfo/midrange-l
3.
https://archive.midrange.com/midrange-l.
4.
https://lists.midrange.com/mailman/listinfo/midrange-l
5.
https://archive.midrange.com/midrange-l.
6.
https://lists.midrange.com/mailman/listinfo/midrange-l
7.
https://archive.midrange.com/midrange-l.
8.
https://www.mailguard.com.au/
9.
https://www.oneteamit.com.au/
10.
https://lists.midrange.com/mailman/listinfo/midrange-l
11.
https://archive.midrange.com/midrange-l.
12.
https://lists.midrange.com/mailman/listinfo/midrange-l
13.
https://archive.midrange.com/midrange-l.
14.
https://lists.midrange.com/mailman/listinfo/midrange-l
15.
https://archive.midrange.com/midrange-l.
16.
https://www.mailguard.com.au/
17.
https://lists.midrange.com/mailman/listinfo/midrange-l
18.
https://archive.midrange.com/midrange-l.
19.
https://lists.midrange.com/mailman/listinfo/midrange-l
20.
https://archive.midrange.com/midrange-l.
21.
https://www.mailguard.com.au/
midrange.com
