Re: AW: Creating an SQL function

How many time must I repeat myself?

YOU CAN"T USE PARAMETERS PASSED BY VALUE!!!!

You need to pass by reference of CONST reference:

D SUBPROC pr 9 0
D ItemSuffix 4 CONST
D Sku# 6 CONST

Charles



On Mon, Sep 29, 2008 at 4:19 PM, Alan Shore <AlanShore@xxxxxxxx> wrote:

This is wierd
By using STRSRVJOB in another session
and then
STRDBG SRVPGM(*LIBL/SERVPGM)
I am able to go into debug
However, the 2 parameters that I am passing through

D SUBPROC pr 9 0
D ItemSuffix 4 value
D Sku# 6 value

are NOT the values that I am passing in via the SQL
What I am passing in is
SUFFIX '0011'
SKUNO '000070'
What I see in DEBUG

ITEMSUFFIX = ' '
SKU# = 'clÉ Î '

Anyone have any thoughts



Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxxxxx
P:(631) 244-2000 ext. 5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill





Alan Shore
<AlanShore@xxxxxx
om> To
Sent by: Midrange Systems Technical
midrange-l-bounce Discussion
s@xxxxxxxxxxxx <midrange-l@xxxxxxxxxxxx>
cc

09/29/2008 04:01 Subject
PM Re: AW: Creating an SQL function


Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>







AH-HA
I'm making some headway
using
CREATE FUNCTION AVAILQTY (VARCHAR(4), VARCHAR(6))
RETURNS DECIMAL(9,0)
LANGUAGE RPGLE
PARAMETER STYLE GENERAL
EXTERNAL NAME 'PRODLIB/SERVPGM(SUBPROC)'
DETERMINISTIC
NO SQL
NOT FENCED
RETURNS NULL ON NULL INPUT

I do get some results
for example
ITEM SKUNO AVAIL
NO.
SUFFIX
0011 000070 999,999,999-

where the answer 999,999,999- is a valid answer when the item is NOT
found on the item master
Even though I can see its there.
Trying to use debug does not get me into the service program

BUT - I'm making headway

Thanks to everyone who came up with suggestions



Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxxxxx
P:(631) 244-2000 ext. 5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

midrange-l-bounces@xxxxxxxxxxxx wrote on 09/29/2008 03:04:55 PM:

Maybe create your function using VARCHAR instead of CHAR - or
explicitly CAST the values in your SELECT to be CHAR values.

-------------- Original message ----------------------
From: Alan Shore <AlanShore@xxxxxxxx>

Thanks for your reply Birgitta
Unfortunately I still got the same response

ITEM SKUNO AVAIL
NO.
SUFFIX
0011 000070 ++++++++++++



Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxxxxx
P:(631) 244-2000 ext. 5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

midrange-l-bounces@xxxxxxxxxxxx wrote on 09/29/2008 01:39:32 PM:

Hi,

I assume it's the parameter style.
Parameter style expects some additional (optional) parameters that

must

be

defined in the procedure interface or *ENTRY PLIST.
Try to recreate your function with parameter style general. With

parameter

style general only the defined parameters are passed to the RPG

function.

Overloading cannot be the problem, because the function gets called.
If a function is overloaded and the parameters are not passed with

the

same

data types (BTW length must not be an exact match, because it's not
checked), the function gets not called at all. Be careful CHAR and

VARCHAR

are different data types. SMALLINT, INT and BIGINT are also 3

different

data

types.

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 loosing them? Not

training

them

and keeping them!"

-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Alan Shore
Gesendet: Monday, 29. September 2008 17:41
An: Midrange Systems Technical Discussion
Betreff: Creating an SQL function

Good morning all
I am trying to create an SQL function that uses a sub procedure

within a

service program
Here is what I attempted
Within STRSQL

create function MYLIB/AVAILQTY (CHAR, CHAR)
returns DECIMAL(9,0)
External Name 'PRODLIB/SERVPGM(SUBPROC)'
Language RPGLE
Disallow Parallel
No SQL
Parameter Style DB2SQL
Deterministic

When I attempt to use this, for example
SELECT SUFFIX, SKUNO, AVAILQTY(SUFFIX, SKUNO)
FROM ITEMMSTR WHERE SUFFIX = '0011' AND SKUNO = '000070'

What I get is
ITEM SKUNO AVAILQTY
NO.
SUFFIX
0011 000070 ++++++++++++
The input to the sub procedure is 2 fields 4 characters, 6 characters
The output from the sub procedure is decimal(9,0) as noted above
I'll be honest
I don't know where to start on debugging this
If anyone can help, it would be much appreciated)


Thanks in advance



Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxxxxx
P:(631) 244-2000 ext. 5019
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@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)

mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing

list

To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l..


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.