Java call DB2 Stored Procedure with OUT as ResultSet

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 ?
Chong Chee LeongProgrammerAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Chong Chee LeongProgrammerAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Chong Chee LeongProgrammerAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
Chong Chee LeongProgrammerAuthor Commented:
understood and thanks
0
 
Tomas Helgi JohannssonCommented:
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
 
Chong Chee LeongProgrammerAuthor Commented:
Thanks Tomas for the sample stored pro.
0
All Courses

From novice to tech pro — start learning today.