Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Java call DB2 Stored Procedure with OUT as ResultSet

Posted on 2014-10-14
10
317 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 143

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 143

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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

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
 
LVL 143

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 25

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Nadia
Suppose you use Uber application as a rider and you request a ride to go from one place to another. Your driver just arrived at the parking lot of your place. The only thing you know about the ride is the license plate number. How do you find your U…
This article will show, step by step, how to integrate R code into a R Sweave document
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.

839 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