Hi all,

can somebody tell me why the very last operation in a stored procedure (Z/0S, DB2 V7.1, Cobol LE370 stored procedure) has to be the DECLARE CURSOR?

As a conscientious (sp?) programmer, I always check the SQL code after any SQL operation. However, if I do this AFTER the declare cursor with return, the result set is not available to the calling program. WHY? I once lost a couple of hours trying to figure out where my result set went. And I haven't found anything particularly enlightening on this subject anywhere.

This is purely for my curiosity - if someone happens to know the why, I'd be interested.

Thanx in advance

L8r, eh?
Ralph

Just to clarify, I think you mean that the OPEN cursor must be in the last SQL statement, not the DECLARE cursor. But I don't know the reason for it.

(I too presume you mean OPEN) I've written SPs in this environment where I've checked the sqlcode after the open. And, since checking the sqlcode does not involve any interaction with DB2, I would be surprised if it did.

The only thing that comes to mind is that you are somehow closing the cursor - either a commit/rollback without WITH HOLD or an explicit close.

James Campbell

Hi again,

by 'last operation' I meant any operation, be it exec sql or otherwise. It seems there must be a goback immediately after the open cursor.

yup, after the OPEN CURSOR I can't even check the sqlcode or my result set seems to disappear.

In the calling program, after the exec sql call xxxx, I check the sqlcode to see if it is 466, when it is, then I do an associate locators followed by an allocate cursor using the locator.

l8r, eh,
Ralph