0

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

Improve this question
4
  • and what should jpa do with a refcursor? why not return the row it self or the uniue id? Commented Jun 20, 2023 at 20:57
  • its just an example, but I want to do it for more than one row where REF CURSOR is required Commented Jun 21, 2023 at 4:29
  • When I am printing Hibernate Show SQL logs {? = call test_sub_procedure(?)} Which looks like function call and not procedure call Commented Jun 21, 2023 at 5: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 Commented Jun 21, 2023 at 6:18
Related questions
0
How can I fix this Java with PostgreSQL error (table does not exist)?
0
Syntax error at or near "order" (Scala with Quill, Doobie and PostgreSQL)
0
How can I send a statement to a PostgreSQL database directed to a character varying attribute?
Load 5 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.