Quarkus JPA Postgres Stored Procedure call - use CALL Error

Question

I implemented a PostgresSQL stored procedure call from Quarkus JPA in the way that Davide suggested here:

Quarkus REST API with Hibernate/Panache - Endpoints querying Postgres Stored Procedure

However calling the stored procedure always fails with the following error: org.postgresql.util.PSQLException: ERROR: generate_sums(bigint, bigint, bigint) is a procedure Hint: To call a procedure, use CALL.

After doing some research I think that this is my problem:

https://github.com/pgjdbc/pgjdbc/issues/1413

However according to this post this issue has been fixed with the Postgres JDBC driver Version higher than 42.2.16:

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

Only difference: they are talking about Spring here, not Quarkus. But looking at the problem this should not make a difference.

I checked the Maven dependency tree of my Quarkus application and noticed that it uses 42.2.22. So a version which includes this fix.

However, I still get this error when trying to call the PostgresSQL Stored Procedure from Quarkus.

The only difference to Davides example is that I don't provide a ResultClass since my procedure is a INSERT SELECT statement which fills a table based on other tables and does not return anything.

I am using Quarkus 2.0.

Answer 1

I had some problems to execute Stored Procedures from Oracle with Quarkus some time ago, I'm not sure, but I think it was one error similar to yours.

I was setting the wrong type on the "registerStoredProcedureParameter" method.

I'll put here my example codes executing two different Stored Procedures, I hope it can help you.

First example

Stored Procedure

create or replace package proc_cliecoh_v2 is procedure execconh (v_param char);

SQL command do execute this Stored Procedure

CALL PROC_CLIECOH_V2.EXECCONH( 'S' );

Java class

import javax.enterprise.context.ApplicationScoped;
import javax.inject.Inject;
import javax.persistence.EntityManager;
import javax.persistence.ParameterMode;
import javax.persistence.PersistenceException;
import javax.persistence.Query;
import javax.persistence.StoredProcedureQuery;
import javax.persistence.TypedQuery;
import javax.transaction.Transactional;

@ApplicationScoped
public class MyExampleClass {

    @Inject
    EntityManager em;

    @Transactional
    public boolean firstExample() throws PersistenceException {

        StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("PROC_CLIECOH_V2.EXECCONH");
        storedProcedure.registerStoredProcedureParameter("v_param", String.class, ParameterMode.IN);
        storedProcedure.setParameter("v_param", "S");
        return storedProcedure.execute();

    }
}

Second example

Stored Procedure

create or replace package proc_impclih_v2 is
 procedure execimph ( cd_empresa number, cd_usuario number, tp_ocorrencia char, cd_cliente_padrao number); 
end proc_impclih;

SQL command do execute this Stored Procedure

CALL PROC_IMPCLIH_V2.EXECIMPH( 123 , 1 , 'I' , 456 );

Java class

import javax.enterprise.context.ApplicationScoped;
import javax.inject.Inject;
import javax.persistence.EntityManager;
import javax.persistence.ParameterMode;
import javax.persistence.PersistenceException;
import javax.persistence.Query;
import javax.persistence.StoredProcedureQuery;
import javax.persistence.TypedQuery;
import javax.transaction.Transactional;

@ApplicationScoped
public class MyExampleClass {

    @Inject
    EntityManager em;


    @Transactional
    public boolean secondExample() throws PersistenceException {

        StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("PROC_IMPCLIH_V2.EXECIMPH");

        storedProcedure.registerStoredProcedureParameter("cd_empresa", Integer.class, ParameterMode.IN);
        storedProcedure.registerStoredProcedureParameter("cd_usuario", Integer.class, ParameterMode.IN);
        storedProcedure.registerStoredProcedureParameter("tp_ocorrencia", String.class, ParameterMode.IN);
        storedProcedure.registerStoredProcedureParameter("cd_cliente_padrao", Integer.class, ParameterMode.IN);

        storedProcedure.setParameter("cd_empresa", 123);
        storedProcedure.setParameter("cd_usuario", 1);
        storedProcedure.setParameter("tp_ocorrencia", "I");
        storedProcedure.setParameter("cd_cliente_padrao", 456);

        return storedProcedure.execute();

    }
}