Getting ERROR: cursor "<unnamed portal 1>" does not exist

Question

Im trying to use SimpleJdbcCall from spring.jdbc calling function that return a cursor and im getting following error:

org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback; uncategorized SQLException for SQL [{? = call dbo.api_config_select(?)}]; SQL state [34000]; error code [0]; ERROR: cursor "<unnamed portal 1>" does not exist; nested exception is org.postgresql.util.PSQLException: ERROR: cursor "<unnamed portal 1>" does not exist

This is PostGreSQL function code:

CREATE OR REPLACE FUNCTION "dbo"."api_config_select" (in "_id" integer) RETURNS refcursor AS
$$
    DECLARE
      ref refcursor;                                                     
    BEGIN
      OPEN ref FOR 

        SELECT
            1;

      RETURN ref;                                                       
    END;
    $$
LANGUAGE 'plpgsql' COST 100

and this is Java code

        simpleJdbcCall = new SimpleJdbcCall(jdbcTemplate).withFunctionName("api_config_select").withSchemaName("dbo")
              .declareParameters(

              new SqlOutParameter("_cursor",  Types.OTHER),
              new SqlParameter("_id", Types.INTEGER));

        HashMap<String, Object> params = new HashMap<String, Object>();
        params.put("_id", id);

        try {
            Map<String, Object> result = simpleJdbcCall.execute(10);
            for (String s : result.keySet()) {
                System.out.println("6.0  " + result.get(s));
            }
        }

        catch(UncategorizedSQLException e) {
            e.printStackTrace();
        }
        catch(Exception e) {
                e.printStackTrace();
        }

As soon as app call simpleJdbcCall.execute() im getting error. I tried to pass refcursor name, but getting same error.

Anyone have code sample code of using PostgreSql, Spring JDBC and cursor?

Answer 1

use this code block in your method :

Connection conn = jdbcTemplate.getDataSource().getConnection();
conn.setAutoCommit(false);
CallableStatement proc = conn.prepareCall("{? = call dbo.api_config_select() }");
proc.registerOutParameter(1, Types.OTHER);
proc.execute();
ResultSet results = (ResultSet) proc.getObject(1);
while (results.next())
{
    // do something with the results.
}
results.close();
proc.close();

Answer 2

Make sure that you must have use connection.setAutoCommit(false); just after the connection check as below: if (connection != null) { connection.setAutoCommit(false);

Reason is if you will not use setAutoCommit(false), cursor will be close and while retrieving the data it will fail.