|
View previous topic :: View next topic
|
| Author |
Message |
srj1957
New User
Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA
|
|
|
|
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 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 |
|
 |
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
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 |
|
|
srj1957
New User
Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA
|
|
|
|
Suresh,
Thank you very much for the information..
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 |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
| Sure.. You are Welcome |
|
| Back to top |
|
|
srj1957
New User
Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA
|
|
|
|
Thanks Suresh for the info...the Java proggy finally got the results.
Apparently the fetch in Java is done automatically..
Just curious... 
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 |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
| Back to top |
|
|
Suresh Ponnusamy
Active User
Joined: 22 Feb 2008
Posts: 107
Location: New York
|
|
|
|
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 |
|
|
srj1957
New User
Joined: 15 Dec 2005
Posts: 72
Location: RALEIGH NC, USA
|
|
|
|
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 |
|
|
|
|
