Postgres DB - Using JPA StoredProcedureQuery invoke Stored Procedure in Postgres DB which has OUT Parameter as Ref Cursor

Question

Please do not confuse this with Function, its specifically for Stored Procedure.

Need to call a Stored Procedure in Postgres DB using JPA's StoredProcedureQuery. Stored Procedure has OUT parameter as REF Cursor.

CREATE OR REPLACE PROCEDURE test_sub_procedure(
    OUT sub_proc_ref_cursor REFCURSOR,
    IN sub_proc_desc_text varchar
)
    LANGUAGE plpgsql AS
$$
DECLARE
    dummy_test timestamp;
BEGIN
    INSERT INTO TEMP_TABLE_TEST_REF_CURSOR
    VALUES (sub_proc_desc_text);
    OPEN sub_proc_ref_cursor FOR SELECT * FROM TEMP_TABLE_TEST_REF_CURSOR;
END;
$$;

When calling from JPA like this

    StoredProcedureQuery query = session.createStoredProcedureQuery("test_sub_procedure");
    query.registerStoredProcedureParameter(1, void.class, ParameterMode.REF_CURSOR);
    query.registerStoredProcedureParameter(2, String.class, ParameterMode.IN);
    query.setParameter(2, "from java jpa code");
//            List<Object[]> postComments = query.getResultList();
    query.execute();
    ResultSet resultSet = (ResultSet) query.getOutputParameterValue(1);

Getting error

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: Error calling CallableStatement.getMoreResults
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:154)
    at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:181)
    at org.hibernate.procedure.internal.ProcedureCallImpl.execute(ProcedureCallImpl.java:622)
Caused by: org.hibernate.exception.SQLGrammarException: Error calling CallableStatement.getMoreResults
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:103)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
    at org.hibernate.result.internal.OutputsImpl.convert(OutputsImpl.java:83)
    at org.hibernate.result.internal.OutputsImpl.<init>(OutputsImpl.java:60)
    at org.hibernate.procedure.internal.ProcedureOutputsImpl.<init>(ProcedureOutputsImpl.java:34)
    at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:416)
    at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(ProcedureCallImpl.java:352)
    at org.hibernate.procedure.internal.ProcedureCallImpl.outputs(ProcedureCallImpl.java:632)
    at org.hibernate.procedure.internal.ProcedureCallImpl.execute(ProcedureCallImpl.java:615)
    ... 2 more
Caused by: org.postgresql.util.PSQLException: ERROR: test_sub_procedure(character varying) is a procedure
  Hint: To call a procedure, use CALL.
  Position: 15
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2675)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2365)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:355)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:166)
    at org.postgresql.jdbc.PgCallableStatement.executeWithFlags(PgCallableStatement.java:83)
    at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:155)
    at com.mchange.v2.c3p0.impl.NewProxyCallableStatement.execute(NewProxyCallableStatement.java:3214)
    at org.hibernate.result.internal.OutputsImpl.<init>(OutputsImpl.java:56)
    ... 7 more

EDIT : So what is happening is, its trying to call Function Way. When I added ?escapeSyntaxCallMode=call it was forced to call it in Procedure Way and it worked. But when I am using ?escapeSyntaxCallMode=callIfNoReturn it it thinking its a function and failing

Why is it thinking of it as Function and not Procedure?

Reference : Postgresql 11: Stored Procedure call error - To call a procedure, use CALL, Java

Is there any way to specifically set ?escapeSyntaxCallMode=call to StoredProcedureQuery Object

and what should jpa do with a refcursor? why not return the row it self or the uniue id? — nbk, Jun 20 '23 at 20:57
its just an example, but I want to do it for more than one row where REF CURSOR is required — Vishwanath Joshi, Jun 21 '23 at 04:29
When I am printing Hibernate Show SQL logs {? = call test_sub_procedure(?)} Which looks like function call and not procedure call — Vishwanath Joshi, Jun 21 '23 at 05:01
Why do you want a refcursor, as far as I can tell no driver can do that, can you elaborate what you need the refcursor in jpa for — nbk, Jun 21 '23 at 06:18

Postgres DB - Using JPA StoredProcedureQuery invoke Stored Procedure in Postgres DB which has OUT Parameter as Ref Cursor

0 Answers0