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

Hi everyone
Now that I have the SQL User Defined Function working
The result of this UDF gives me the next identification/batch number
I need to use the result of this in a number of SQL scripts
My first thought was to capture the result of this UDF and create a SQL variable

I read the following web page
https://stackoverflow.com/questions/47662356/create-a-temporary-variable-in-db2-400-v5r4-within-a-runsqlstm-source-file-mem

So I tried this
CREATE or REPLACE VARIABLE MYVARIABLE decimal
default
(select
NBTYGPL.NEXTNUMBERSQL('ALAN SHORE TEST')
from SYSIBM.SYSDUMMY1)
so that I could use MYVARIABLE in a number of different SQL scripts

It doesn't like it
The error is
Use of function NEXTNUMBERSQL in NBTYGPL not valid.

F1 prompt on this message results in
Additional Message Information

Message ID . . . . . . : SQL0583 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic

Message . . . . : Use of function NEXTNUMBERSQL in NBTYGPL not valid.
Cause . . . . . : Function NEXTNUMBERSQL in NBTYGPL cannot be invoked where
specified because it is defined to be not deterministic or contains an
external action. Functions that are not deterministic cannot be specified
in a GROUP BY clause or in a JOIN clause, or in the default clause for a
global variable. Functions that are not deterministic or contain an
external action cannot be specified in a PARTITION BY clause or an ORDER BY
clause for an OLAP function and cannot be specified in the select list of a
query that contains an OFFSET clause. The RAISE_ERROR function cannot be
specified in a GROUP BY or HAVING clause.
Recovery . . . : Remove the function. Try the request again.

Bottom
Press Enter to continue.

Am I reading this right, I cannot use the SQL function in this method?

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: Thursday, December 10, 2020 1:04 PM
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 Charles
I thought that the rectification was the change of CHAR to VARCHAR Learn something new every day

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 Charles Wilt
Sent: Thursday, December 10, 2020 12:52 PM
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

Alan,

Just wanted to make sure you realize that changing to from VALUE to CONST was the actual fix.

CHAR vs VARCHAR isn't the problem...

Years back, having the proc defined with CHAR and trying to call it with a literal from Run SQL Scripts would result in a "not found" error.

IBM has recently enhanced the implicit conversion the DB will do.

Since the RPG proc was invoked, you didn't have any issues passing VARCHAR as a CHAR.

Charles

On Wed, Dec 9, 2020 at 1:02 PM Alan Shore via MIDRANGE-L < midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Okay - I figured out a way around this situation

I created a NEW function NEXTNUMBERSQL and changed the PR/PI so that
Function is now varying Here is the code
P NextNumberSQL B export

D NextNumberSQL PI 12p 0

D Function 50 const

D Varying

D UseCommitmentControl...

D n const options(*nopass)

D HldNextNumber s like(nn_NextNum)

D HldFunction s 50

*- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -

* Open the appropriate next numbers file, as needed

HldFunction = %trim(Function);

select;

when %parms = 1;

HldNextNumber = NextNumber(HldFunction);

other;

HldNextNumber = NextNumber(HldFunction: UseCommitmentControl);

endsl;

return HldNextNumber;

P NextNumberSQL E

Then I created the SQL function in the following manner CREATE or
replace FUNCTION NBTYGPL.NEXTNUMBERSQL ( INORD VARCHAR(50)) RETURNS
DECIMAL(12, 0) LANGUAGE RPGLE SPECIFIC NBTYGPL.NEXTNUMBERSQL NOT
DETERMINISTIC NO SQL CALLED ON NULL INPUT DISALLOW PARALLEL
EXTERNAL NAME 'NBTYGPL/VALUE(NEXTNUMBERSQL)'
PARAMETER STYLE GENERAL

These changes work

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 2:17 PM
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

Apologies Charles
I thought had done that -
D NextNumber PR 12p 0
D Function 50 value
D UseCommitmentControl...
D n const options(*nopass)

D NextNumber PI 12p 0
D Function 50 value
D UseCommitmentControl...
D n const options(*nopass)

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 Charles Wilt
Sent: Wednesday, December 9, 2020 2:00 PM
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

again...post your PR/PI

Charles

On Wed, Dec 9, 2020 at 7:34 AM Alan Shore via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

Hi Charles
Thanks for your reply
Within STRDBG, not specifying the second parameter seems to be
working fine The procedure uses the following code
C Select

C When %parms = 1

C eval CommitmentControlIsActive = *off

C Other

C eval CommitmentControlIsActive =

C
UseCommitmentControl
C EndSL

And this looks to be working fine

According to Birgitta - the problem is how the input parameter is
defined in my function and How I am using the function in STRSQl
Tru=ing to rctify this situation - but having no luck

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 Charles Wilt
Sent: Wednesday, December 9, 2020 9:27 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

You said it accepts two parms, but you only have 1 defined...

CALLED NULL INPUT probably isn't what you want with PARAMETER STYLE
GENERAL since there won't be anyway for your RPG program to
determine if a NULL is passed.

Bet to post the PR/PI of the RPG proc in addition to the SQL Definition.

Charles

On Wed, Dec 9, 2020 at 5:36 AM Alan Shore via MIDRANGE-L <
midrange-l@xxxxxxxxxxxxxxxxxx> wrote:

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

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

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