7

PostgreSQL 11 now supports stored procedures and I am trying to call one with Hibernate 5.3.7.Final and Postgresql 42.2.5 JDBC driver. Previous to PostgreSQL 11 we had functions that could be called with JPA's @NamedStoredProcedure. However, the functions were executed with SELECT my_func(); and the new stored procedures have to be executed with CALL my_procedure();

I am trying to execute the following simple stored procedure:

CREATE OR REPLACE PROCEDURE p_raise_wage_employee_older_than(operating_years 
int, raise int)
AS $$
    BEGIN
       UPDATE employees
       SET wage = wage + raise 
       WHERE EXTRACT(year FROM age(entrance_date)) >= operating_years;
    END $$
LANGUAGE plpgsql;

The JPA annotation looks like the following:

@NamedStoredProcedureQuery(name = "raiseWage", 
   procedureName = "p_raise_wage_employee_older_than", 
   parameters = {
        @StoredProcedureParameter(name = "operating_years", type = Integer.class, 
           mode = ParameterMode.IN),
        @StoredProcedureParameter(name = "raise", type = Integer.class, 
            mode = ParameterMode.IN)
})

And I am calling the stored procedure with:

StoredProcedureQuery storedProcedureQuery = this.em.createNamedStoredProcedureQuery("raiseWage"); 
storedProcedureQuery.setParameter("operating_years", 20);
storedProcedureQuery.setParameter("raise", 1000);
storedProcedureQuery.execute();

My logs look like the following:

Hibernate: {call p_raise_wage_employee_older_than(?,?)}
2019-02-17 11:07:41.290  WARN 11168 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 42809
2019-02-17 11:07:41.291 ERROR 11168 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: p_raise_wage_employee_older_than(integer, integer) is a procedure
  Hinweis: To call a procedure, use CALL.
  Position: 15

The first Hibernate log is indicating that Hibernate uses call to execute the stored procedure but I am getting a SQL exception that CALL is not used. Is this a bug in the Postgresql dialect as previous to Postgresql 11 you were able to model the FUNCTIONS as stored procedures within JPA and therefore SELECT was used and not CALL?

rieckpil
  • 10,470
  • 3
  • 32
  • 56
  • Can you use a basic JDBC CallableStatement with one of those? Suggest that you try that since this all a JPA provider is likely using, so it could isolate the problem down to what is different –  Feb 17 '19 at 11:58
  • 1
    As pgJDBC 42.2.5 came out in Aug, 2018 before PostgreSQL 11 (Oct, 2018) I doubt that there is support for the `CALL` syntax in it. The `{call ...}` syntaxt you see in the logs are the JDBC specific one, which sould be translated to the appropriate syntax by the JDBC driver. You probably want to post this [here](https://github.com/pgjdbc/pgjdbc/issues) as I don't see any relevant issue there. -- For the time being, you could use the "old" way (i.e. define `FUNCTION`s with `RETURNS VOID`). – pozs Feb 17 '19 at 20:51

3 Answers3

3

As the pgJDBC 42.2.5 was released prior (Aug, 2018) to the PostgreSQL 11 release (Oct, 2018), I think this is currently a issue within the JDBC driver for PostgreSQL itself. I've created a issue within the GitHub repository.

For a workaround, you could rewrite the STORED PROCEDURE as a FUNCTION and use @NamedStoredProcedureQuery or directly interact with the JDBC CallableStatement e.g.:

Connection conn = DriverManager.getConnection("jdbc:postgresql://localhost:5432/", "postgres", "postgres");

CallableStatement callableStatement = conn.prepareCall("{call f_raise_wage_employee_older_than(?,?)}");
callableStatement.setInt(1, 20);
callableStatement.setInt(2, 500);
callableStatement.executeUpdate();

Or execute a native query with the EntityManager: 

this.em.createNativeQuery("CALL p_raise_wage_employee_older_than(1, 20)");

I'll update this answer as soon as I get an answer from the pgJDBC maintainer.

UPDATE:

This topic is already discussed in the Postgres Mailing list (https://www.postgresql.org/message-id/4285.1537201440%40sss.pgh.pa.us) and there is currently no solution. The only way is to pass native SQL queries to the database or rewrite the STORED PROCEDURE as a FUNCTION

rieckpil
  • 10,470
  • 3
  • 32
  • 56
3

After PostgreSQL 11, PostgreSQL JDBC driver team has introduced a ENUM name EscapeSyntaxCallMode in PostgreSQL driver version 42.2.16. So this enum we can use while creating database connections or creating DataSource object. This Enum has 3 types of Values:

  1. "func" - set this when we always want to call functions.
  2. "call" - set this when we always want to call Procedures.
  3. "callIfNoReturn" - It checks for the return type in calling function/procedure, if return type exists PostgreSQL considers it as a function and calls it as a Function way. Otherwise it calls it as procedure way. So in my project I used this "callIfNoReturn", as I wanted PostgreSQL to auto detect whether I am calling function or procedure.

I already have given the answer in detail with the proper steps to follow:

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

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Abhishek Singh
  • 1,367
  • 1
  • 22
  • 46
0

Same problem. This is my solution.

try {
    Query query = entityManager.createNativeQuery("CALL procedure( :param1, :param2 )");
    query.setParameter("param1", intParam1);
    query.setParameter("param2", intParam2);
    
    log.info("Running...");
    try {
        query.getSingleResult();
    } catch (PersistenceException pe) {
        if (pe.getCause() != null && pe.getCause() instanceof GenericJDBCException) {
            GenericJDBCException gjdbce = (GenericJDBCException) pe.getCause();
            // NO_RESULTS_CODE = "02000" in PostgreSQL
            if (NO_RESULTS_CODE.equals(gjdbce.getSQLState())) {
                log.info("Success!");
                return;
            }
        }
        
        throw pe;
    }
} catch (Exception e) {
    log.error(e.getMessage(), e);
}

Good luck!

Sergio Marsilli
  • 101
  • 5