Re: Feed result set into a command

["Buck" <buck.calabro@xxxxxxxxxxxx>]

Vern Hamberg replied:

> I recommend putting the exception join into an RPG program
> (or REXX, if you want some cool fun and something that
> no one else in your shop could maintain).

Knowing Vern, he's being a tad funny here.  ReXX is both fun and easy to
use, and is quite a reasonable fit for this scenario.  Put this source in a
source file (I use the ever-imaginative QREXSRC) and process it with the
STRREXPRC command:

/* REXX Find where column is used */
address EXECSQL

Parse Source . . pgmname .           /* Get the name of the program */
Parse Arg columname .                /* Get the argument string     */

If columname='' Then Do
   Say pgmname 'takes one parameter:'
   Say 'The field name you want to find in the SQL catalogue'
   Say 'Feel free to use wildcards.  % matches all, ? matches one.'
   Return
   End

say "Starting REXX program " pgmname

EXECSQL 'set OPTION COMMIT = *NONE'

select = 'select sys_cname,sys_dname,sys_tname,coltype,',
         'length,coalesce(scale,0),label',
         'from syscolumns where sys_cname like ?'

EXECSQL 'prepare STMTCOL from :select'
EXECSQL 'declare CRSRCOL cursor for STMTCOL'
EXECSQL 'open CRSRCOL using :columname'

say 'Column name' colunname 'found in these tables:'

do forever
  EXECSQL 'fetch CRSRCOL into :column,:library,:table,:type,:length,',
           ':scale,:label'
  if sqlcode <> 0 then leave
  say library '/' table '-' column type length scale label
end

return