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

294 Views Asked by At

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

0

There are 0 best solutions below