View previous topic :: View next topic
|
Author |
Message |
Rusikesh
New User
Joined: 04 Feb 2010 Posts: 25 Location: United States
|
|
|
|
Hello Team,
I need your inputs on following scenario. i tried multiple things but no luck.
Here is challenge,
I am working on DB2 Native procedure. I created procedure (SP1), and after performing some logic it is returning result into one cursor say CURSOR1. This CURSOR1 is extracting data from GLOBAL TEMPORARY TABLE which is declared in SP1. CURSOR1 is defined WITH RETURN TO CLIENT FOR clause.
Second Stored procedure SP2 is calling SP1, in order to read CURSOR1 (output of SP1) and process it. I tried using ASSOCIATE RESULT SET LOCATOR, however after executing SP2, i see in no result. It is giving error as,
"CURSOR NAME CURSOR1 IS NOT DECLARED".
Here is code which i did in SP2 to process CURSOR1:
ASSOCIATE RESULT SET LOCATOR(result1)
WITH PROCEDURE SCHEMA.SP1
ALLOCATE CURSOR1 CURSOR FOR RESULT SET result1
-- after this I am fetching CURSOR1 and processing it.
========
Note: In SP2 i defined result1 as,
DECLARE result1 RESULT_SET_LOCATOR VARYING;
======== |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Please use the code tags when presenting code and data. After so many years as a member you should be familiar with this. |
|
Back to top |
|
|
Rusikesh
New User
Joined: 04 Feb 2010 Posts: 25 Location: United States
|
|
|
|
Here is code snippet, |
|
Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007 Posts: 8797 Location: Welsh Wales
|
|
|
|
Nic Clouston wrote: |
Please use the code tags when presenting code and data. After so many years as a member you should be familiar with this. |
Rusikesh wrote: |
Here is code snippet,
[attachment here]
|
Spot the difference ............. REQUESTED NE SUPPLIED
Please DO NOT post attachments as
a) Not everyone is permitted to open them
b) Not everyone will trust you that your attachment is safe, I FOR ONE DON'T
c) If you cannot be bothered to post in an easy and open fashion some people may not have enough "bothered" to open your attachment
So tip of the day, help yourself to help yourself. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Quote: |
ALLOCATE CURSOR1 CURSOR FOR RESULT SET result1 |
You have got the same cursor name in both sps.
However, why don't you INSERT into temp table in SP1 from CURSOR1 and then reference that into SP2 to do further processing? |
|
Back to top |
|
|
Rusikesh
New User
Joined: 04 Feb 2010 Posts: 25 Location: United States
|
|
|
|
Rohit, I result from SP1 is named as CURSOR1 and for locator i am using it as result1. i do not think so. i am using same name in both procedures.
@Expat, i understand your concern but i do not know how to place image in message body. so i have to go with attachment. |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Quote: |
Rohit, I result from SP1 is named as CURSOR1 and for locator i am using it as result1. i do not think so. i am using same name in both procedures. |
You are not paying attention here. See if wither one is the problem,
1.ALLOCATE CURSOR1 CURSOR FOR RESULT SET result1
CURSOR1 name in sp2 is same as sp1 try with below declaration,
ALLOCATE CURSOR2 CURSOR FOR RESULT SET result1
ALLOCATE CURSOR
2. You are not calling the SP1 before executing the above code. |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
Quote: |
but i do not know how to place image in message body |
Cut and paste. Simple. |
|
Back to top |
|
|
|