IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Handling result sets in the calling Native procedure


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Rusikesh

New User


Joined: 04 Feb 2010
Posts: 25
Location: United States

PostPosted: Fri Apr 13, 2018 12:25 am
Reply with quote

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
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Fri Apr 13, 2018 1:27 am
Reply with quote

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
View user's profile Send private message
Rusikesh

New User


Joined: 04 Feb 2010
Posts: 25
Location: United States

PostPosted: Fri Apr 13, 2018 4:35 am
Reply with quote

Here is code snippet,
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales

PostPosted: Fri Apr 13, 2018 11:41 am
Reply with quote

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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Fri Apr 13, 2018 8:23 pm
Reply with quote

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
View user's profile Send private message
Rusikesh

New User


Joined: 04 Feb 2010
Posts: 25
Location: United States

PostPosted: Fri Apr 13, 2018 9:40 pm
Reply with 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.


@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
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 3048
Location: NYC,USA

PostPosted: Sat Apr 14, 2018 12:10 am
Reply with quote

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
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK

PostPosted: Sat Apr 14, 2018 1:11 pm
Reply with quote

Quote:
but i do not know how to place image in message body

Cut and paste. Simple.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Calling DFSORT from Cobol, using OUTF... DFSORT/ICETOOL 5
No new posts Calling Java method from batch COBOL ... COBOL Programming 5
No new posts Calling an Open C library function in... CICS 1
No new posts first column truncated in search result IBM Tools 13
No new posts SDSF like solution in EJES (store com... All Other Mainframe Topics 4
Search our Forums:

Back to Top