asked on

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 ?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chong Chee Leong

ASKER

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?

Guy Hengel [angelIII / a3]

>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

Chong Chee Leong

ASKER

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?

Guy Hengel [angelIII / a3]

>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

Chong Chee Leong

ASKER

understood and thanks

Tomas Helgi Johannsson

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

Chong Chee Leong

ASKER

Thanks Tomas for the sample stored pro.