Re: Generic stored procedure (was: Is AS400JDBCDriver available outside jt400.zip?)

["Bruce Jin" <brucej@xxxxxxxxxxxxxxxxxxxx>]

 Thanks Buck. It looks like this will work if we group programs by
parameters and define a SP for each group. Bruce
----- Original Message -----
From: "Buck Calabro" <Buck.Calabro@commsoft.net>
To: <java400-l@midrange.com>
Sent: Wednesday, October 10, 2001 10:07 AM
Subject: Generic stored procedure (was: Is AS400JDBCDriver available outside
jt400.zip?)


> >I try to figure out what you mean by "a
> >generic stored procedure" but I can't
> >come up with a strategy. Could you explain
> >this a little bit?
>
> Sure, Bruce!
>   You write a stored procedure in an HLL like RPG:
>
> h debug dftactgrp(*no) actgrp(*caller)
>  * dbgview(*list)
>
>  * Generic driver program
>  * to register:
>
>  * CREATE PROCEDURE BUCK/SQPSP(IN pgmName CHAR (10 ),
>  *                             IN pgmType INT ,
>  *                             INOUT returnValue CHAR (50 ))
>  *                  LANGUAGE RPGLE NOT DETERMINISTIC
>  *                  NO SQL CALLED ON NULL INPUT
>  *                  EXTERNAL NAME BUCK/SQLSP PARAMETER STYLE
>  *                  PARAMETER STYLE GENERAL
>
> d pgmName         s             10
> d pgmType         s             10i 0
> d returnValue     s             50
>
>  * parameters for called programs
> d char8           s              8
>
> c     *entry        plist
> c                   parm                    pgmName
> c                   parm                    returnValue
>
> c                   select
> c                   when      pgmType = 1
> c                   call      pgmName
> c                   parm                    char8
> c                   eval      returnValue = char8
> c                   other
> c                   eval      returnValue = 'Unknown pgmType'
> c                   endsl
>
> c                   eval      *inlr = *on
>
> All this program does is direct traffic - it calls the program passed to
it
> in parameter one.  The second parameter is there to help with various
> parameter lists that the called program might have.  While this is a
> simplistic case, you probably get the idea.  The third parameter is for
> returning data back to the original caller of the procedure.
>
> This is all messy, but if you have SPs that don't take variables
> (PrintCreditReport) or take a single variable (relieveInventory 12345)
then
> this technique could reduce your need to have one SP created for each HLL
> program.
>
> As for the parameter type, you could easily build a table of program
> name/parameter type that will provide that to the caller of the SP.
>
> I hope I made some sense.  Reading it back makes me wonder about my
sanity.
>   --buck
> _______________________________________________
> This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
> To post a message email: JAVA400-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/java400-l
> or email: JAVA400-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.
>