× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.