Re: Simple UDTF only gets called with OPEN, FETCH events

[CRPence <CRPbottle@xxxxxxxxx>]

  Sorry about the effectively duplicate reply... I started responding 
to the incorrect message on my first try, and clicked Send instead of 
Cancel; still trying to get the brain-fog cleared, per being off of 
caffeine since two mornings ago.

  The problem is with the declaration of the CallType.  The interface 
requires a 4-byte binary integer.  However declared as a two-byte binary 
integer, the call to CLOSE with x'00000001' *looks like* the call to 
FETCH with x'0000'; i.e. the first two-bytes of x'00000001'.  Of course 
the reason the call to OPEN works, is because x'FFFFFFFF' looks like a 
decimal-negative-one irrespective of 2-byte vs 4-byte integer.

  The corrected [similarly in the PI] code:

   D UDTF1           pr                  extpgm('UDTF1')
    ...
   D  CallType                     10i 0 const

  The original\incorrect [similarly in the PI] code:

   D UDTF1           pr                  extpgm('UDTF1')
    ...
   D  CallType                      5i 0 const

  The first line of code that performs "n_parmIn=PARM_NULL;" is also 
suspect, but not for the described issue with the output.  That 
information, declared as variable n_parmIn, is /input/ to the program 
from the SQL, about whether the dec(11) value passed as an argument was 
the NULL value versus having a value.  For example, test the following:

    select a.* from table(udtf1(cast(null as dec(11)))) as a

Regards, Chuck

On 12-Nov-2013 08:37 -0800, Peter Dow wrote:
> Thanks for pointing that out. But as you also note, it drops into
> CALL_FETCH when COUNTER = 7, which means it was called with
> CALLTYPE=0, which is the puzzling part. I was expecting a CALLTYPE of
> 1, and no subsequent calls.
>
> On 11/11/2013 2:05 PM, Sam_L wrote:
> > This probably isn't the complete answer...
> >
> > In CALL_FETCH you are setting SQLSTT after you DSPLY it. The
> > display of SQLSTT when COUNTER = 6 is 00000. When COUNTER = 7
> > SQLSTT is 02000, presumably from it being set in the previous
> > CALL_FETCH.
> >
> > That said, I don't see why when COUNTER = 7 you are falling into
> > CALL_FETCH.
> >
> > I believe you get the CALL_CLOSE after you send back SQLSTT =
> > 02000.