I am using a Postgres database. And I have one stored procedure which returns a refcursor as below
INOUT p_refcur refcursor DEFAULT NULL::refcursor
I am calling the SP in pgAdmin tool as below and getting proper result :
BEGIN;
CALL my_spg('some_val', 'another_val', 'refcursorresult');
FETCH ALL IN "refcursorresult";
COMMIT;
Now I am trying to call the above SP from my Java code and trying to obtain the result from the refcursor , but I am not able to fetch. I looked around many forums but I did not find any single way on how to obtain the value from the refcursor in postgres.
My sample minimum relevant java code :
public List<ResponseDTO> callMySP(String someVal, String anotherVal) {
Connection conn = DatabaseConnection.connect();
CallableStatement cstmt = null;
List<ResponseDTO> respList= new ArrayList<>();
try {
if(conn != null) {
conn.setAutoCommit(false);
}
cstmt = conn.prepareCall("call public.my_sp (?,?,'refcursorresult');");
cstmt.setString(1, someVal);
cstmt.setString(2, anotherVal);
cstmt.registerOutParameter(1, Types.OTHER);
Boolean result = cstmt.execute();
if(result) {
ResultSet rs = (ResultSet) cstmt.getObject(1);
if(null != rs) {
// Here I am confused on how to obtain the refcursor value from RS.
while(rs.next()) {
// my logic here
respList.add();
}
}
} else {
LOGGER.info("Result Set is null");
}
conn.commit();
} catch (SQLException sqlEx) {
LOGGER.error("", sqlEx);
} catch (Exception e) {
LOGGER.error("", e);
} finally {
try {
cstmt.close();
conn.close();
} catch (SQLException e) {
LOGGER.error("Error in connection close.", e);
}
}
return respList;
}
Can some one please help me out. I am stuck here.