Re: AW: Creating an SQL function

[Alan Shore <AlanShore@xxxxxxxx>]

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..