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.