Dynamic cursor not working in external stored procedure

[h***@dartcontainer.com]

I have a stored procedure calling an external program. This cursor it
creates is dynamic in that the files and libraries are changed. The
first time I call this sp, it works fine. After that, I cannot change
the files or libraries. It gives me an error saying it cannot find a
field from the original run. It is like it is keeping the original
creation of the cursor.

Can anyone point me in the right direction?

Here is my code.

Stored Proc:
CREATE PROCEDURE HMPGMR/DCTP001
(IN Library CHARACTER(10),
IN Member CHARACTER(10),
IN Report CHARACTER(10),
IN User CHARACTER(10),
IN RunNumber DECIMAL(6,0))

DYNAMIC RESULT SETS 1
EXTERNAL NAME HMPGMR/DCRP001
LANGUAGE RPGLE

RPGLE (just the embedded sql):

C/EXEC SQL
C+ PREPARE DYNSQLSTMT
C+ FROM :SQLSTMTSTR (sqlstmtstr is a valid sql string)

C/END-EXEC

* Declare the Cursor
C/EXEC SQL
C+ DECLARE DataCursor1 DYNAMIC SCROLL CURSOR WITH RETURN FOR
C+ DYNSQLSTMT
C/END-EXEC

C/EXEC SQL
C+ OPEN DataCursor1
C/END-EXEC

C/EXEC SQL
C+ Set Result Sets Cursor DataCursor1
C/END-EXEC

[James Perkins]

Post by h***@dartcontainer.com
I have a stored procedure calling an external program. This cursor it
creates is dynamic in that the files and libraries are changed. The
first time I call this sp, it works fine. After that, I cannot change
the files or libraries. It gives me an error saying it cannot find a
field from the original run. It is like it is keeping the original
creation of the cursor.
Can anyone point me in the right direction?
Here is my code.
CREATE PROCEDURE HMPGMR/DCTP001
(IN Library CHARACTER(10),
IN Member CHARACTER(10),
IN Report CHARACTER(10),
IN User CHARACTER(10),
IN RunNumber DECIMAL(6,0))
DYNAMIC RESULT SETS 1
EXTERNAL NAME HMPGMR/DCRP001
LANGUAGE RPGLE
C/EXEC SQL
C+ PREPARE DYNSQLSTMT
C+ FROM :SQLSTMTSTR (sqlstmtstr is a valid sql string)
C/END-EXEC
* Declare the Cursor
C/EXEC SQL
C+ DECLARE DataCursor1 DYNAMIC SCROLL CURSOR WITH RETURN FOR
C+ DYNSQLSTMT
C/END-EXEC
C/EXEC SQL
C+ OPEN DataCursor1
C/END-EXEC
C/EXEC SQL
C+ Set Result Sets Cursor DataCursor1
C/END-EXEC

Well the first thing I see is that you do not close the cursor. Maybe you
just forgot to post that code though.

The second thing you could try is when you compile your code set the "Close
SQL cursor" to *ENDMOD, CLOSQLCSR(*ENDMOD).

Regards,
James R. Perkins

[h***@dartcontainer.com]

Actually, we do not have the close cursor statement. We were having
problems getting any results back to the report when that was included.
I have created Crystal reports that use the same thinking and they did
not have a close cursor statement. We have migrated to Microsoft's RDL
for reports and are trying to create a stored procedure as a data
source.

I will try putting it back in with the *endmod and see what happens.
We have tried everything. We might have missed putting the two
together.

Thanks for you input.

[B***@lp-gmbh.com]

Hi,

you have to close the cursor!

If you cannot do this at the end of your program, do it immediatedly
before you either execute a new PREPARE statement or OPEN the cursor
for the same prepared statement. If the SQL cursor is not yet opened
you'll get the SQLCODE -501 (Cursor not opened), that can be ignored.

It's the OPEN statement that executes your SQL-Statment and returns the
data that can be fetched after. If you try to execute an OPEN-Statement
for an open cursor it will neither execute the SQL-Statement nor
position at the beginning of the result data. Instead you'll get the
SQLCODE -502 (Cursor already opened).

If closing the cursor will be a problem, the option CLOSQLCSR *ENDMOD
will not be an solution! This option will hard close your cursor as
soon as your module/program gets finished.

Not closing the cursor makes sense, if you are using a SCROLL cursor,
that is created once and never changes. But in your case, you change
the statements because you'll access data from different tables and
schemas.

Birgitta

[h***@dartcontainer.com]

We moved our SQL into the stored procedure and cleaned up our sql a
bit. We decided not to dynamically build our sql and just write a
different stored procedure for each report. All we did is define the
result set and declare and open the cursor.

FYI: We did not include a close cursor statement. I am thinking that
the cursor is closed when the activation group is done, therefore we do
not need to explicitly close the cursor.

Thanks for your input.

[Kent Millligan]

For cursors used to return result sets, you do not need to close the
cursor - DB2 will take care of it in this situation. The cursor has to
be left open in order for the result set to be returned.

Post by h***@dartcontainer.com
We moved our SQL into the stored procedure and cleaned up our sql a
bit. We decided not to dynamically build our sql and just write a
different stored procedure for each report. All we did is define the
result set and declare and open the cursor.
FYI: We did not include a close cursor statement. I am thinking that
the cursor is closed when the activation group is done, therefore we do
not need to explicitly close the cursor.
Thanks for your input.