RE: Problem with an SQL function created from a procedure

Hi everyone
I located what the problem was
Sorry - someone else located what the problem was - credit where credits due

It is in the create function
I had created the function using
PARAMETER STYLE SQL
It should have been
PARAMETER STYLE general

I dropped the function and created it again (the correct way)
It is now working


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

From: Alan Shore
Sent: Monday, September 12, 2016 7:39 AM
To: 'midrange-l@xxxxxxxxxxxx' <midrange-l@xxxxxxxxxxxx>
Subject: Problem with an SQL function created from a procedure

Hi everyone
Before I forget - we have JUST (this past weekend) converted to V7r1 (from V5r4)
We have a function that was created from a procedure in a service program that was working on V5r4
Here is how the function was created

CREATE FUNCTION PRODPA/GETTRACKINGNUM (
INYY NUMERIC(2, 0) ,
INMMDD NUMERIC(4, 0) ,
INXXXX NUMERIC(3, 0) ,
INORD VARCHAR(2) ,
INSP NUMERIC(3, 0) )
RETURNS CHAR(50)
LANGUAGE RPGLE
SPECIFIC PRODPA/GETTRACKINGNUM
NOT DETERMINISTIC
NO SQL
CALLED ON NULL INPUT
DISALLOW PARALLEL
EXTERNAL NAME 'PRODPA/ORDERSP(GETTRACKNO)'
PARAMETER STYLE SQL

Here is the prototype for said procedure
D GetTrackNo PR 50
D in_yy 2S 0 CONST
D in_mmdd 4S 0 CONST
D in_xxx 3S 0 CONST
D in_ordnum 2A VARYING
D in_spnum 3S 0 CONST

When the function is now used, we get the following response
An array index is out of range (C G D F).
When I take an F1 on this message
Additional Message Information

Message ID . . . . . . : RNQ0121 Severity . . . . . . . : 99
Message type . . . . . : Inquiry
Date sent . . . . . . : 09/12/16 Time sent . . . . . . : 07:35:49

Message . . . . : An array index is out of range (C G D F).
Cause . . . . . : RPG procedure DBU0061 in program DBU/DBU0061 tried to use
an array index at statement 855 which was less than one or greater than the
number of elements in the array.
Recovery . . . : Contact the person responsible for program maintenance to
determine the cause of the problem.
Possible choices for replying to message . . . . . . . . . . . . . . . :
D -- Obtain RPG formatted dump.
S -- Obtain system dump.
G -- Continue processing at *GETIN.
C -- Cancel.
F -- Obtain full formatted dump.
Bottom

I wrote a small program to use the procedure and that is working fine
I have dropped the function and recreated it under V7r1 and the response is still the same
As always, any response is MUCH appreciated

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