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

[Peter Dow <petercdow@xxxxxxxxx>]

Hi Birgitta,

Chuck Pence pointed out that the link to my code was incomplete -- it 
should end in .html, not .htm.  The perils of copy/paste!

And yes, when COUNTER > 5, I set SQLSTT = '02000' and RETURN.  As Sam 
pointed out, I displayed SQLSTT before setting it, so when the function 
is called and increments COUNTER to 7, it then displays SQLSTT = '02000'.

Chuck Pence had some suggestions about better ways to determine what's 
going on; I'll be trying those tomorrow (I'm at a different job today).

--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx>/


On 11/11/2013 10:08 PM, Birgitta Hauser wrote:
> Did you set and return SQLSTATE 02000 after having read and returned the
> last row?
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
>
>
> -----Ursprüngliche Nachricht-----
> Von: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Peter Dow
> Gesendet: Monday, 11.11 2013 20:04
> An: MIDRANGE-L@xxxxxxxxxxxx
> Betreff: Simple UDTF only gets called with OPEN, FETCH events
>
> I'm trying out user defined table functions based on Scott Klement's example
> at
> http://www.scottklement.com/presentations/RPG%20User%20Defined%20Functions%2
> 0%26%20Table%20Functions.pdf
>
> My code is at http://code.midrange.com/399002eaaa.htm.
>
> The interesting thing is that my UDTF only gets called with a call type of
> -1 (open) and 0 (fetch), but never 1 (close).
>
> I tried creating the function without the NO FINAL CALL, which theoretically
> means my UDTF should have been called with a call type of
> -2 (startup) and 2 (final) as well as the open/fetch/close, but still only
> got called on open and fetch.  I have a counter that's incremented each time
> the function is called, and put in DSPLYs like this
>
> 0045.00        dsply ('STARTUP: ' + %editc(CallType:'L') + ' ' + SQLSTT
> + ' '
> 0046.00                           + %editc(Counter:'L') + ' '
> 0047.00                           + %editc(parmIn:'L'));
>
> to see what was happening and ran it from the green screen STRSQL just to
> keep it simple and this is what I got:
>
> STRSQL
>
> select * from table(UDTF1(1)) as t
> SELECT statement run complete.
>
> DSPLY  OPEN...:     1- 00000          1            1
> DSPLY  FETCH..:     0  00000          2            1
> DSPLY  FETCH..:     0  00000          3            1
> DSPLY  FETCH..:     0  00000          4            1
> DSPLY  FETCH..:     0  00000          5            1
> DSPLY  FETCH..:     0  00000          6            1
> DSPLY  FETCH..:     0  02000          7            1
>
> ....+...
> PARMOUT
>         2
>         3
>         4
>         5
> ********  End of data  ********
>
>
> STRSQL
>
> select * from table(UDTF1(2)) as t
> SELECT statement run complete.
>
>    DSPLY  OPEN...:     1- 00000          8            2
>    DSPLY  FETCH..:     0  00000          9            2
>    DSPLY  FETCH..:     0  02000         10            2
>
> ....+...
> PARMOUT
> ********  End of data  ********
>
>
> STRSQL
>
> select * from table(UDTF1(3)) as t
> SELECT statement run complete.
>
> DSPLY  OPEN...:     1- 00000         11            3
> DSPLY  FETCH..:     0  00000         12            3
> DSPLY  FETCH..:     0  02000         13            3
>
> ....+...
> PARMOUT
> ********  End of data  ********
>
> DSPLY  OPEN...:     1- 00000         14            4
> DSPLY  FETCH..:     0  00000         15            4
> DSPLY  FETCH..:     0  02000         16            4
>
> ....+...
> PARMOUT
> ********  End of data  ********
>
> Anyone have an idea what's going on? Is it because I'm calling it from
> STRSQL? It occurred to me that maybe the close and final calls happen when I
> sign off, so I did a SIGNOFF *LIST, but the resulting job log did not show
> anything after the last FETCH.
>
> --
> *Peter Dow* /
> Dow Software Services, Inc.
> 909 793-9050
> petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
> pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx>/