Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
:?:Returning rowss from COBOL/DB2 Stored Procedure to Client

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
srj1957

New User


Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA

PostPosted: Mon Aug 11, 2008 10:34 pm    Post subject: :?:Returning rowss from COBOL/DB2 Stored Procedure to Client
Reply with quote

This is my first exercise in using DB2 stored procedures. I'm using good old COBOL... icon_biggrin.gif

This is the scenario.
Java client is calling my COBOL DB2 stored Procedure. I will sending back multiple rows.

This what I have so far...
Stored procedure is in COBOL pgm
Code:

...
WORKING STORAGE
01 SPO-MED-NAME           PIC X(30).           
01 SPO-MED-STRENGTH       PIC X(10).           
01 SPO-MED-NDC            PIC X(11).           
01 SPO-DIET               PIC X(60).           
01 SPO-DNR                PIC X(03).           
01 SPO-SQLCODE            PIC S9(09) USAGE COMP.
...
PROCEDURE DIVISION USING
                          SPI-ACCT-NO         
                        , SPO-MED-NAME       
                        , SPO-MED-STRENGTH   
                        , SPO-MED-NDC         
                        , SPO-DIET           
                        , SPO-DNR             
                        , SPO-SQLCODE.     
 

I create temp table,

Code:
 
EXEC SQL                                           
   CREATE GLOBAL TEMPORARY TABLE SESSION.TABLE1   
      (                                             
       C_PT_ACCT_NO     CHAR(12) NOT NULL,         
       C_MED_NAME       CHAR(30) ,                 
       C_MED_STRENGTH   CHAR(10) ,                 
       C_MED_NDC        CHAR(11) ,                 
       C_DIET           CHAR(60) ,                 
       C_DNR            CHAR(03)                   
       )                                           
END-EXEC.     


I get data from file and then insert data into GTT...

Code:
EXEC SQL                       
INSERT INTO SESSION.TABLE1   
   VALUES (:WS-ACCT-NO         
          ,:WS-MED-NAME       
          ,:WS-MED-STRENGTH   
          ,:WS-MED-NDC         
          ,:WS-DIET           
          ,:WS-DNR             
          )                   
END-EXEC.   


Now I want to get the data and send it back to Client
So I Declare Cursor

Code:
EXEC SQL DECLARE MEDS_CURSOR CURSOR     
            WITH RETURN FOR             
     SELECT C_MED_NAME                   
          , C_MED_STRENGTH               
          , C_MED_NDC                   
          , C_DIET                       
          , C_DNR                       
       FROM SESSION.TABLE1           
END-EXEC.   


Then I fetch each row
Code:

EXEC SQL                     
   FETCH MEDS_CURSOR         
    INTO :SPO-MED-NAME       
        ,:SPO-MED-STRENGTH   
        ,:SPO-MED-NDC         
        ,:SPO-DIET           
        ,:SPO-DNR             
END-EXEC                     
 


This is where I can't figure out how to code it so it send multiple rows back
...I know there is always more that one row to return

My question is how do I return more that one row

I have seen SET RESULT SET, but I don't understand how this works or how to code it properly...

Would someone please help on the best way to do this and help on the syntax

Thanks in advance
Steve
Back to top
View user's profile Send private message

Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Mon Aug 11, 2008 10:49 pm    Post subject:
Reply with quote

Hi Steve,

In DB2 SP, the result set's records will be fetched by JAVA directly. We do not need fetch the records in MF. What you need to do is Declare Cursor and Open the Cursor. That is enough. Fetch, your Java program will take care.
Back to top
View user's profile Send private message
srj1957

New User


Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA

PostPosted: Mon Aug 11, 2008 11:00 pm    Post subject: Java does the the Fetch ??
Reply with quote

Suresh,

Thank you very much for the information.. icon_biggrin.gif

So the Java client does the the Fetch ?

OK ...I'll tell the Java programmer to do the FETCH and see what he gets.

I maybe back to ask more ...

Steve
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Mon Aug 11, 2008 11:17 pm    Post subject:
Reply with quote

Sure.. You are Welcome
Back to top
View user's profile Send private message
srj1957

New User


Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA

PostPosted: Tue Aug 12, 2008 7:55 pm    Post subject:
Reply with quote

Thanks Suresh for the info...the Java proggy finally got the results.
Apparently the fetch in Java is done automatically..


Just curious... icon_confused.gif

How would this work if I were using a COBOL DB2 prog calling the stored procedure...I mean, how would it send many rows back, would the calling program have to do the fetch ?
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Tue Aug 12, 2008 8:58 pm    Post subject:
Reply with quote

Please refer to prev post

http://ibmmainframes.com/viewtopic.php?p=144197&highlight=#144197
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Tue Aug 12, 2008 8:58 pm    Post subject:
Reply with quote

Hi Steve,

When JAVA issues, SQL CONNECT command a new DB2 thread is established between JAVA and Mainframe. After CONNECT statement, JAVA issues CALL statement to the DB2 Stored procedure with the Input Parameter Details.

When DB2 receives SQL CALL statement, it searches in SYSIBM.SYSPROCEDURES catalog table for a row associated with the Stored Procedure name. DB2 will obtain SP information and PARAMETER Details from SYSIBM.SYSROUTINES and SYSIBM.PARMS table.

After getting the SP details, it will get executed in WLM Established Address Space. This entire operation will be performed using Single thread which got established, when the CALL statement issued.

In our SP, we open only the cursor and since it is same thread JAVA can directly fetch the result set from the cursor.

Cursor should be declared with "WITH HOLD RETURN FOR" to retain the cursor ever after issing commit (in SP definition we can state COMMIT_ON_RETURN ON or COMMIT_ON_RETURN OFF).

Once JAVA fetched the result set, it will issue a command to Close the Connection. Thus the entire cycle is being performed from JAVA to MAINFRAME..

Hope this helps.
Back to top
View user's profile Send private message
srj1957

New User


Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA

PostPosted: Wed Aug 13, 2008 8:13 pm    Post subject:
Reply with quote

ashimer wrote:
Please refer to prev post

http://ibmmainframes.com/viewtopic.php?p=144197&highlight=#144197


Thanks ashimer for this link.. I got the answers I needed to code and get the data back into my COBOL DB2 pgm calling a Stored Procedure.

Also thank you Suresh...

This is a very helpful forum, it save hours of pawing over books to find a solution to our challenges...

Thanks
Steve
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
This topic is locked: you cannot edit posts or make replies. Extract all "IF" Statements... Adarsh Damodaran CLIST & REXX 1 Wed Sep 06, 2017 9:28 am
No new posts ALPHABETIC check in COBOL vidyaa COBOL Programming 8 Thu Aug 17, 2017 7:13 pm
No new posts XML Parse for COBOL 5.2 Bhanu Praveen COBOL Programming 1 Sat Jul 08, 2017 8:58 pm
No new posts Dynamic output file creation in cobol... smileheal COBOL Programming 7 Thu Jun 15, 2017 10:53 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us