Solved

Java call DB2 Stored Procedure with OUT as ResultSet

Posted on 2014-10-14
10
277 Views
Last Modified: 2014-11-27
Dear Sir,

Here is my DB2 Stored Procedure :

CREATE PROCEDURE SP_Test3 (OUT resultset VARCHAR(2048))
begin

declare mycur cursor for

select Val_CODE, Val_DESC From CODE_TYPE ;

open mycur;
Fetch from mycur into resultset;
close mycur;

end

Open in new window



Would like to know how to define the code in Java to call above stored procedure ?
0
Comment
Question by:Chong Chee Leong
  • 4
  • 3
10 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40381522
please refer to the documentation:
http://publib.boulder.ibm.com/infocenter/idshelp/v111/index.jsp?topic=/com.ibm.jccids.doc/com.ibm.db2.luw.apdv.java.doc/doc/tjvcscsp.htm

in your case, that should go like this:
String resultset ;
Connection con;
CallableStatement cstmt;
ResultSet rs;

cstmt = con.prepareCall("CALL SP_Test3(?)");                
cstmt.registerOutParameter (1, Types.VARCHAR);                         
cstmt.executeUpdate();     
resultset = cstmt.getString(1);
cstmt.close();               

Open in new window

0
 

Author Comment

by:Chong Chee Leong
ID: 40381619
Noted and thanks.

Actually I would to get the output return as Resultset.
But in Stored Procedure I want to close the cursor 1st with include "close mycur;" as you can see...

Is this possible doing in this way?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40382179
>Actually I would to get the output return as Resultset.
as you return only a single string values, this is not possible with the current procedure

in that case, you would want to define the parameters as cursor, and not loop/fetch the cursor (just open it) inside the procedure
0
 

Author Comment

by:Chong Chee Leong
ID: 40383592
Noted.

Would like to know cursor must be remain open during in DB2 Stored Procedure?
Once it's close, it's totally can't get the ResultSet right in coding?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40396345
>Would like to know cursor must be remain open during in DB2 Stored Procedure?
yes

>Once it's close, it's totally can't get the ResultSet right in coding?
exactly

once the session that runs the procedure has finished the read (and closed the reader), the cursor is also closed automatically
0
 

Author Comment

by:Chong Chee Leong
ID: 40398402
understood and thanks
0
 
LVL 24

Expert Comment

by:Tomas Helgi Johannsson
ID: 40404201
Hi!

This is how you code SP to return a resultset.

CREATE OR REPLACE PROCEDURE SEARCH_COUNTRY (IN searchKey varchar(30))
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    BEGIN
        DECLARE result_set_1 CURSOR WITH RETURN TO CLIENT FOR
            SELECT * FROM country WHERE country_name LIKE searchKey;   
        OPEN result_set_1;
    END;

Open in new window


The client (the caller) takes care of the closing of the resultset.
See the manual for your version of DB2 for more info.

Regards,
     Tomas Helgi
0
 

Author Comment

by:Chong Chee Leong
ID: 40406493
Thanks Tomas for the sample stored pro.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Prime numbers are natural numbers greater than 1 that have only two divisors (the number itself and 1). By “divisible” we mean dividend % divisor = 0 (% indicates MODULAR. It gives the reminder of a division operation). We’ll follow multiple approac…
When we want to run, execute or repeat a statement multiple times, a loop is necessary. This article covers the two types of loops in Python: the while loop and the for loop.
The goal of this video is to provide viewers with basic examples to understand and use switch statements in the C programming language.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now