RE: [EXTERNAL] RE: Problem with an SQL UDF created from a procedure within a service program

Hi Birgitta
I tried the following
select
NBTYGPL.NEXTNUMBER(cast('ALAN SHORE TEST' as VARCHAR(50)))
from SYSIBM.SYSDUMMY1
In STRDBG,the input was garbage
I then tried
select
NBTYGPL.NEXTNUMBER(cast('ALAN SHORE TEST' as CHAR(50)))
from SYSIBM.SYSDUMMY1

In STRDBG, the input again was garbage
Am I still missing something?

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill


-----Original Message-----
From: Alan Shore
Sent: Wednesday, December 9, 2020 8:28 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: [EXTERNAL] RE: Problem with an SQL UDF created from a procedure within a service program

Thanks Birgitta
Let me make that change

Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Wednesday, December 9, 2020 8:22 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] RE: Problem with an SQL UDF created from a procedure within a service program

If you pass a literal 'xxxxx' it is interpreted as VARCHAR Data Type, but your function expects a CHAR Data Type.
Try to cast your parameter as CHAR and then retest your function.

BTW if you define your parameters as CHAR and pass VARCHAR it works correctly.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"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)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Alan Shore via MIDRANGE-L
Sent: Mittwoch, 9. Dezember 2020 13:37
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: Problem with an SQL UDF created from a procedure within a service program

Before I forget, we are on V7r3
I have a procedure (NEXTNUMBER) within a service program (VALUES) This is working in a number of programs I even created a test program that uses this procedure, just so that I could STRDBG and look at the logic flow It works It accepts 2 parameters, the second one being optional First parameter is a
char(50) parameter Second parameter is a logical parameter

Below is how I created the SQL function
When I run the function in STRSQL

select NBTYGPL.NEXTNUMBER('ALAN SHORE TEST') from SYSIBM.SYSDUMMY1

Using STRDBG in another session (after using STRSRVJOB), I can see that the first parameter is garbage Anyone any idea what I did wrong?

As always - all answers gratefully accepted

Here is how I created the SQL function

CREATE FUNCTION NBTYGPL.NEXTNUMBER (
INORD CHAR(50) )
RETURNS NUMERIC(12, 0)
LANGUAGE RPGLE
SPECIFIC NBTYGPL.NEXTNUMBER
NOT DETERMINISTIC
NO SQL
CALLED ON NULL INPUT
DISALLOW PARALLEL
EXTERNAL NAME 'NBTYGPL/VALUE(NEXTNUMBER)'
PARAMETER STYLE GENERAL



Alan Shore
E-mail : ASHORE@xxxxxxxx<mailto:ASHORE@xxxxxxxx>
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill

--
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: 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 https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com

--
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: 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 https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com