PostgreSql 12 JDBC multiple resultset, can only get the first resultset

Question

Want JDBC to get multiple result-sets from PostgreSql 12 function. JDBC version org.postgresql/postgresql/42.2.9. The PostgreSql function is here:

create or replace function test()
returns setof refcursor as $$
declare
    ret1 refcursor;
    ret2 refcursor;
begin
    open ret1 for
    select 10 as a;
    return next ret1;
    open ret2 for
    select 20 as b;
    return next ret2;
end; $$ language plpgsql;

Java code is like this:

stmt = getConnection().prepareCall("{ ? = call test() }");
stmt.registerOutParameter(1, Types.OTHER);
stmt.execute();
rs = (ResultSet)stmt.getObject(1);
if (rs.next()) {
    int a = rs.getInt("a");
    if (rs.next()) {
        int b = rs.getInt("b");
    }
}

"a" is successfully retrieved, but the second "rs.next()" returns false. Googled a lot but couldn't make it work. How can I get the 2nd result-set?

brewphone · Accepted Answer · 2020-08-16 05:06:38Z

1

This link helped me a lot and the problem was resolved.

    ...
    out ret1 refcursor,
    out ret2 refcursor)
returns record as $$
begin
    open ret1 for select 10;
    open ret2 for select 20;
end; $$ language plpgsql;

Java:

stmt = getConnection().prepareCall("{call test(?,?)}");
stmt.registerOutParameter(1, Types.OTHER);
stmt.registerOutParameter(2, Types.OTHER);
stmt.execute();
ResultSet rs1 = (ResultSet)stmt.getObject(1);
ResultSet rs2 = (ResultSet)stmt.getObject(2);
int a = 0, b = 0;
if (rs1.next()) {
    a = rs1.getInt(1);
}
if (rs2.next()) {
    b = rs2.getInt(1);
}

answered Aug 16, 2020 at 5:06

brewphone

1,3767 gold badges25 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSql 12 JDBC multiple resultset, can only get the first resultset

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related