[pushramesh]

Hi,

I have a client program (Java) calling SP1 calls SP2 calls SP3. SP1, SP2, SP3 are Cobol stored procedures all defined with NO COMMIT ON RETURN.
SP1 calls SP2 successfully, then SP2 calls SP3 successfully. Then SP3 returns values to SP2 successfully. SP3 passes values to SP2 successfully.
In SP2 there is a join of tables and the resultant set is put in a cursor. I am opening the cursor in SP2. Till this point all the calls are successful.
Now the cursor opened on SP2 will be fetched from SP1.
The first step in SP1 is ASSOCIATE LOCATOR to the SP2. ..this is successful. Next step is ALLOCATE the cursor which is opened in SP2 to the LOCATOR...this is also successful.
Then I am trying to fetch the cursor in SP1 which is already opened from SP2. At that time I am getting -501 (trying to fetch a cursor which is not open).
I am checking the sqlcode after the open cursor in SP2 and it is successful.
But when I try to fetch the same cursor from SP1, its returning back -501.
Can anyone help me out ?
Thanks much in advance.

[msujit]

Have you used CURSOR WITH HOLD and WITH RETURN ?

[pushramesh]

WITH RETURN AND WITH HOLD CANNN'T BE USED IN THE SAME DECLARED CLAUSE.