|
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
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
