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