Re: CPF5035 on 2nd call of SP from RPGLE

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