Are there any examples of writing a User Defined Table Function in RPG using a linear-main program?

Question

I have been writing User Defined Table Functions as RPG cycle programs based on Brigitta Houser's informative article "The Power of UDTF". The nature of table functions, where they are called multiple times for open, and on each fetch (at minimum), seems to be a decent fit for a cycle-main program because you can exit using ‘RETURN‘ without setting indicator ‘*LR‘ on and your program will not be unloaded and any module-level variables will still have their values.

I am curious if it is advisable to write these as a linear-main program instead, which should be possible based on the C language examples. This would presumably be a smaller program without the cycle logic and could maybe even be stored in a service program as a procedure rather than a standalone program, but I am not clear how you would exit the procedure without the program unloading (I think ‘RETURN‘ would also unload it in this context, not merely exit). I am also not clear how you could hold data as static variables between calls, yet cause an actual unloading of variables at the end.

An example would probably do more to clarify the details of how this works than any explanation, but I can't find one. Is it perhaps ill-advised?

Answer 1

Sure, you can use a linear main or a service program procedure...

The point you are missing is that ILE code isn't unloaded until the activation group ends.

So even with a cycle main, that RETURNS with LR *ON, the program is still loaded. It seems as if it's unloaded, since the cycle code resets stuff if you call it again.

Note that an RPG IV program running in "OPM compatibility mode" does actually get unloaded. "OPM compatibility mode" means compiled with (poorly named IMHO) DFTACTGRP(*YES) that thus can't use any procedure calls; include any local procedures. Think RPG III source run through CVTRPGSRC with some RPG IV op-codes, syntax, or even free format code added. But still using subroutines instead of sub-procedures.

Answer 2

The essence of the problem is not very clear. When calling UDTF, it has a parameter

CallType int(10) const; 

which can take the values

/////////////////////////////////////////////////////////////////////////
// UDTF call parameter constants
/////////////////////////////////////////////////////////////////////////
dcl-c CALL_OPEN           const(-1);
dcl-c CALL_FETCH          const(0);
dcl-c CALL_CLOSE          const(1);

The first time the function is called with CallType = CALL_OPEN - at the same time you do the necessary preparatory operations (for example, form an array of strings that need to be “sent outside”). Next, it will be called with CallType = CALL_FETCH - and in each call you “give out” the next line

In addition, the function has a parameter

State char(5);

which you set every time you exit. It can take values

/////////////////////////////////////////////////////////////////////////
// SQL States
/////////////////////////////////////////////////////////////////////////
dcl-c SQLSTATEOK          const('00000');
dcl-c ENDOFTABLE          const('02000');
dcl-c UDTF_ERROR          const('38999');

If you filled all the fields with the next values, then at the output you set NULL - field indicators to the value

dcl-c NOTNULL             const(0);

and set State = SQLSTATEOK In the case when there is no more data, it is necessary to set all NULL indicators to the value

dcl-c ISNULL              const(-1);

and assign the State variable the value ENDOFTABLE After this, the above function will be called again with the value CallType = CALL_CLOSE. At this moment, you can carry out the necessary operations (for example, clear the memory in which the data prepared for sampling was located)