Re: CPF5035 on 2nd call of SP from RPGLE

[Paul Bailey <PabloMotte+Midrange@xxxxxxxxx>]

Hi Birgitta,

The cursor is created by the ALLOCATE in the RPGLE (instead of an OPEN) and
then closed by the RPGLE after use. I think my initial worries that RPGLE
was part of the problem were incorrect and the issue is actually with the
FINAL TABLE implementation in "DB2 for i". RPGLE handles result sets from
stored procedures using SELECT statements with the same ALLOCATE routine
fine, and the stored procedure I was using for the INSERT also failed with
the same problem when called through a JDBC connection.


-Paul..

On 20 April 2017 at 06:45, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx> wrote:

> Paul,
>
> so you return a Result Set, that is consumed within your RPG Program (if
> you say ALLOCATE)?
> Where is the cursor closed?
> Did you close the cursor after having read the result in your RPG program?
> Or handles the stored procedure closing the cursor before the next call?
>
> It the cursor is not closed the second call will fail.
>
> 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!"
> „Train people well enough so they can leave, treat them well enough so
> they don't want to.“ (Richard Branson)
>
>
> -----Original Message-----
> From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Paul
> Bailey
> Sent: Mittwoch, 19. April 2017 20:46
> To: RPG programming on the IBM i (AS/400 and iSeries) <
> rpg400-l@xxxxxxxxxxxx>
> Subject: Re: CPF5035 on 2nd call of SP from RPGLE
>
>  Thanks Chuck,
>
> The "FINAL TABLE" seemed like a neater way to do it (in a single line,
> too) so I went that way. In the end I couldn't get it to work so I replaced
> that section with a cursor to return the new record after using the
> IDENTITY_VAL_LOCAL function to get the required key field. That worked as
> it has so many times before.
>
> In an off-list conversation with Mark Waterbury I came to the conclusion
> that there was nothing wrong with RPGLE calling the stored procedure
> because calling it via JT400 (through Java, ACS' Run SQL Scripts and IBM
> Data Studio) had the same problem. Calling the stored procedure from IBM's
> Data Studio using the IBM Data Server driver for "DB2 for i" did not have
> the problem.
>
> I suspect there might be something wrong with the implementation of the
> FINAL TABLE option at v7.1 but I don't have time to raise a PMR and work
> through different combinations of code and OS levels with the IBM guys.
> Sorry. Maybe next time.
>
>
> -Paul.
>
> On 19/04/2017 17:50, CRPence wrote:
>
> On 04-Apr-2017 05:00 -0500, Paul Bailey wrote:
>
> I have a stored procedure to create a record in a SQL table. That SQL
> table has an auto-identity column which I need to pass back to the RPG so I
> used a *data-change-table-reference* (i.e. SELECT <fields> FROM FINAL TABLE
> (INSERT ...)) and then used ALLOCATE CURSOR in RPGLE to retrieve the result
> set. […]
>
>
>   Seems a lot more work than should be required solely to return an
> integer value; i.e. why not just pass back the value as an OUT [or INOUT]
> parameter rather than via a cursor?  Or, although not the most appropriate
> [possibly for a variety of reasons], obtain the value from the
> IDENTITY_VAL_LOCAL function in the caller.  Perhaps just something to
> consider, to avoid\circumvent the msg CPF5035 RC14 [followed by
> sqlcode:-138 msg SQL0138 per sqlstate:22011] error, perhaps just until the
> issue with the current coding can be resolved.?
>
> The o/s version is 7.1. All source, programs and files are on the same
> partition […]
>
>
>   FWiW, with a pub400 account the current code could be tested on v7r3.
> --
> 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: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives at
> http://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
>
> --
> 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: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://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