Solved

Java call DB2 Stored Procedure with OUT as ResultSet

Posted on 2014-10-14
10
330 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

There is an easy way, in .NET, to centralize the treatment of all unexpected errors. First of all, instead of launching the application directly in a Form, you need first to write a Sub called Main, in a module. Then, set the Startup Object to th…
Windows Script Host (WSH) has been part of Windows since Windows NT4. Windows Script Host provides architecture for building dynamic scripts that consist of a core object model, scripting hosts, and scripting engines. The key components of Window…
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
The viewer will learn additional member functions of the vector class. Specifically, the capacity and swap member functions will be introduced.

685 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