Amazon Redshift JDBC driver to support returning a Resultsets using standard JDBC API approach

[dqmdev]

Redshift JDBC 2.1.0.9

Subject: Amazon Redshift driver to automatically handle accessing ref_cursors to return Resultsets using standard JDBC approach.

Similar to many other databases, allow an application to receive Resultsets from a procedure without having to write code to handle ref_cursor types etc.

Engines with PostgreSQL heritage also support returning a result as shown vs using a ref_cursor. This approach is not claimed to be supported by Redshift.

https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_PROCEDURE.html

CREATE FUNCTION pres( ) RETURNS SETOF tset1 LANGUAGE plpgsql AS $$ begin return query select RNUM, C1, C2 from TSET1; end; $$;

Instead, you must declare it thus.

create procedure PRES ( PRES1 INOUT refcursor ) as $$ begin open PINOUT for select RNUM, C1, C2 from TSET1; end; $$
LANGUAGE 'plpgsql' //

You cannot use standard JDBC Callable statements to access the Resultset returned by the procedure.

        String callString = "{ CALL \"dqm\".\"cert\".\"pres\"(  ) }";
    CallableStatement cstmt = dbConnection.prepareCall(callString);
    if (cstmt.execute()) {
        System.out.println("no result set");
    } else {
        System.out.println("has result set");
        ResultSet rs = cstmt.getResultSet();
        ResultSetMetaData rsMd = rs.getMetaData();
        describeRs(rsMd);
        printRs(rs, rsMd.getColumnCount());
        rs.close();
    }

Exception in thread "main" com.amazon.redshift.util.RedshiftException: ERROR: procedure pres() does not exist Hint: No procedure matches the given name and argument types. You may need to add explicit type casts. at com.amazon.redshift.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2607) at com.amazon.redshift.core.v3.QueryExecutorImpl.processResultsOnThread(QueryExecutorImpl.java:2275) at com.amazon.redshift.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1880) at com.amazon.redshift.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1872) at com.amazon.redshift.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368) at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeInternal(RedshiftStatementImpl.java:514) at com.amazon.redshift.jdbc.RedshiftStatementImpl.execute(RedshiftStatementImpl.java:435) at com.amazon.redshift.jdbc.RedshiftPreparedStatement.executeWithFlags(RedshiftPreparedStatement.java:200) at com.amazon.redshift.jdbc.RedshiftCallableStatement.executeWithFlags(RedshiftCallableStatement.java:82) at com.amazon.redshift.jdbc.RedshiftPreparedStatement.execute(RedshiftPreparedStatement.java:184) ....

This fails as well

System.out.println("Calling sp");
    String callString = "{ CALL \"dqm\".\"cert\".\"pres\"( ? ) }";

    CallableStatement cstmt = dbConnection.prepareCall(callString);
    cstmt.registerOutParameter(1,java.sql.Types.REF_CURSOR);

    if (cstmt.execute()) {
        System.out.println("no result set");
    } else {
        System.out.println("has result set");
        ResultSet rs = (ResultSet)cstmt.getObject(1);
        ResultSetMetaData rsMd = rs.getMetaData();
        describeRs(rsMd);
        printRs(rs, rsMd.getColumnCount());
        rs.close();
    }

Exception in thread "main" com.amazon.redshift.util.RedshiftException: ERROR: procedure dqm.cert.pres() does not exist Hint: No procedure matches the given name and argument types. You may need to add explicit type casts. at com.amazon.redshift.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2607) at com.amazon.redshift.core.v3.QueryExecutorImpl.processResultsOnThread(QueryExecutorImpl.java:2275) at com.amazon.redshift.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1880) at com.amazon.redshift.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1872) at com.amazon.redshift.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368) at com.amazon.redshift.jdbc.RedshiftStatementImpl.executeInternal(RedshiftStatementImpl.java:514) at com.amazon.redshift.jdbc.RedshiftStatementImpl.execute(RedshiftStatementImpl.java:435) at com.amazon.redshift.jdbc.RedshiftPreparedStatement.executeWithFlags(RedshiftPreparedStatement.java:200) at com.amazon.redshift.jdbc.RedshiftCallableStatement.executeWithFlags(RedshiftCallableStatement.java:82) at com.amazon.redshift.jdbc.RedshiftPreparedStatement.execute(RedshiftPreparedStatement.java:184)

[bhvkshah]

@dqmdev Sorry for the delay! I will look into this issue and get back to you once i have an update. Thanks for submitting it!

[bhvkshah]

Upon reproducing, error seems to be coming from the server. I have opened a ticket with the server team.

[bhvkshah]

Closing this as it is not a driver issue, and ticket has been opened internally with server team.