Returning result set from COBOL/DB2 Stored Procedure to java

srj1957 · #1 (**permalink**) 08-08-08, 15:04

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

This is the scenario.
Java client is calling my COBOL DB2 stored Procedure. I have to send back result set.

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.TROUNRP    
      (                                             
       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 some file then insert data into GTT...

Code:

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

Now I waant to get the data and send it back to Java app...

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.TROUNRP              
END-EXEC.

I know there is always more that one row to return
My question is how do I return the data

I have seen SET RESULT SET, but I don't understand how this works or how to code it properly...or could I just create an array and send the whole array aas one big string

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

Thanks in advance
Steve

Internet Services

Web Development

Digital Marketing

Consumer Tech