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

[Buck Calabro <Buck.Calabro@xxxxxxxxxxxx>]

>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