RE: AW: Creating an SQL function

[Alan Shore <AlanShore@xxxxxxxx>]

Morning Dan
Thanks for your reply
I will certainly give it a go
MUCH appreciated



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 05:36:27 PM:

> Yes. SQL is putting pointers on the stack and your RPG subprocedure
> is trying to read the pointers as values. By changing to CONST or
> REFERENCE(the default) RPG will pick the pointers up off the stack
> and dereference the pointers to get to the value, all as if by magic.
>
> Kimmel
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-
> bounces@xxxxxxxxxxxx] On Behalf Of Alan Shore
> Sent: Monday, September 29, 2008 3:44 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: AW: Creating an SQL function
>
>
> Hi Charles
> Thanks for your reply
> Are you then saying that I should shange subprocedure to accept the
> parameters For example The prototype is presently
> D Subproc        pr             9  0
> D  ItemSuffix                    4    value
> D  Sku#                          6    value
>
> should in fact be
> D Subproc        pr             9  0
> D  ItemSuffix                    4    CONST
> D  Sku#                          6    CONST
>
> 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

>              "Charles Wilt"

>              <charles.wilt@gma

>              il.com>
To
>              Sent by:                  "Midrange Systems Technical

>              midrange-l-bounce         Discussion"

>              s@xxxxxxxxxxxx            <midrange-l@xxxxxxxxxxxx>

>
cc
>

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

>

>

>              Please respond to

>              Midrange Systems

>                  Technical

>                 Discussion

>              <midrange-l@midra

>                  nge.com>

>

>

> Alan,
>
> Have you changed the procedure paramters?
>
> You can't use RPG's (or C/C++ for that matter) ability to pass
> parameters by VALUE.
>
> The parameters for your external function must be defined as by
> reference or by CONST reference.
>
> Charles
>
> On Mon, Sep 29, 2008 at 3:50 PM, Alan Shore <AlanShore@xxxxxxxx> wrote:
> > 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..
>
>
> --
> 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.