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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Algorithms

From novice to tech pro — start learning today.