Workaround solution for inout refcursor parameters for stored procedure in postgresql 11 JDBC

December 10, 2019 - 1 minutes read - 82 words

The workaround solution mentioned at Issue doesn’t work when inout refcursor parameter is used.

Here is my workaround solution for that.

CREATE OR REPLACE PROCEDURE sp_test(INOUT results refcursor)
 LANGUAGE plpgsql
 SECURITY DEFINER
AS $procedure$

begin
    results := 'cur' ;

	OPEN results for select generate_series dt
from generate_series(date'2019-01-01' , date'2019-01-31', interval '1 day');

end;$procedure$
;

conn.setAutoCommit(false);
Statement st = conn.createStatement();
ResultSet rs0 = st.executeQuery("call sp_test('')");
ResultSet rs = st.executeQuery("fetch all in cur");
DatabaseMetaData  rsmd = rs.getMetaData();
log.info("ColumnCount:" + rsmd.getColumnCount().toString());
rs.last();
log.info("RowNo:" + rs.getRow().toString());