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

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.