iSeries: SQLRPGLE How to call a procedure for each record in a SELECT statement

Question

Within an SQLRPGLE program I need to run an insert into statement to copy some records to another file having copied records assigned a new record ID. To do so I followed these steps:

1) Prototype a procedure to get a new record ID each time is called

    D ri_box12        PR            13S 0
    D   wkFileName                  10A   Value
    

2) Execute a dynamic SQL statement

    C                   EVAL      SQL_STM='insert into PGMR46/STD05GR0F'
    C                                    +' (select CART, CARV, '
    C                                    +%char(ri_box12('STD05GR0F'))
    C                                    +'  from arasso0f)'
    C/EXEC SQL
    C+ execute immediate :SQL_STM
    C/END-EXEC
    

3) Define ri_box12 procedure

    P ri_box12        B
    D ri_box12        PI            13S 0
    D   wkFileName                  10A   Value
    D   wkID          S             13S 0
    C                   DO

    ** ...some instructions to give wkID a value
    C                   ENDDO
    C                   Return    wkID
    P                 E
   

The point is that ri_box12 is called only once, so all copied records have the same ID; multiple runs of the program again copy all records with a new record ID.

What did I do wrong or what am I missing?

Than you very much

Answer 1

First off, you don't need to use dynamic SQL here. Static SQL would work and static SQL is prefered for security and performance reasons.

/free    
 exec SQL
   insert into PGMR46/STD05GR0F
     select CART, CARV, ri_box12('STD05GR0F')
     from arasso0f
 end-exec;

However, in order for the CALL of the RPGLE function to work, you are missing one piece.

You have to defined what's known as an external User Defined Function (UDF). This is a one time step. You're basically informing the SQL query engine that the RPGLE function exists and what it's interface looks like.

You usually wouldn't want the INSERT statement above in the same program as the ri_box12() function. Normally you'd have a process like so

1. create a *SRVPGM (or *PGM) containing ri_box12()
2. create UDF pointing to ri_box12()
3. create program/stored procedure that uses the UDF

Step 3 is the program containing your INSERT SQL statement.

Step 2 is simply running a SQL statement that will look like so:

create function ri_box12(
  filename char(10)
  ) returns numeric(13,0)
  language RPGLE
  parameter style general
  returns null on null input
  program type sub
  external name 'MYSRVPGM(RIBOX_12)';

There's lots of other settings you can/should specify for the function. Take a look at the CREATE FUNCTION (External Scalar) section of the SQL reference manual.

I'd also highly recommend reading the External Procedures, Triggers, and User-Defined Functions on IBM DB2 for i Redbook