Re: SQL Cursors and recursive procedures

[Mike Jones <mike.jones.sysdev@xxxxxxxxx>]

This has relevance if you use SQL functions and recursion, but not manually
coded SQL cursors embedded in RPG.

If you make an SQL function that uses recursive SQL, in my experience, the
SQL engine uses (generates) an SQL cursor to accomplish the recursion,
which makes sense.  Last I checked, the name of the generated cursor is
effectively hard-coded (matches the function name IIRC).

If you try to stack two copies of that function in the same call stack, the
second instance of the function will get an error, because it is reusing
the same cursor name, which is still open from the first instance still on
the call stack.

However, if you add the FENCED option to the function source code and
recompile it, you now can stack two, or more, copies of the SQL function in
the call stack.  I have confirmed this multiple times at the V7R1 level

I don't use SQL procedures that much, so I've not had the need or tried to
see if the same FENCED concepts apply, but I suspect they will.

This doesn't help for SQL cursors embedded in recursive RPG procedures, but
FENCED certainly handles what you describe for SQL functions involving
generated cursors for recursive SQL that are being called recursively.

HTH,

Mike



On Sat, Feb 17, 2018 at 11:49 PM, D*B <dieter.bender@xxxxxxxxxxxx> wrote:

> <Bradley>
> I seem to recall having a chat with Jon Paris (probably on this list)
> about SQL Cursors and not being able to use them recursively. I've done
> some searching and just can't find that conversation.
>
> Is that still the case on V7R2/3? Is there any way to set a cursor as
> unique to the recursion level of the call to the function instead of at the
> program level?
>
> If not, besides RLA what other options are there?
>
>
> Bradley V. Stone
> </Bradley>
>
> This is a Bugfeature of the SQL Precompilers: the precompiler is using
> static storage (just global vars) for it's variables.
> The RPG/SQL solution would be to use SQL CLI and you could have multiple
> instances of the same cursor, each of it having a diffrent statement handle.
>
> D*B
> --
> This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
> mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: http://amzn.to/2dEadiD