Question 89661 in PostgreSQL Question posted in
The official documentation can be found here.

Error while calling stored procedure in Postgres using Spring boot JPA – Postgresql

I have created a stored procedure in Postgres


    CREATE OR REPLACE PROCEDURE reset_disabled()  
    LANGUAGE plpgsql  
    AS $$
    DECLARE
    BEGIN  
    UPDATE regional_price_info
           SET disabled = NULL;
    END;  
    $$;

I tried to call procedure from the repository class in two ways however both not worked

  1. Use @Query annotation

    @Query(value = "CALL reset_disabled();", nativeQuery = true)
void priceActivator();

    I got error

    org.springframework.orm.jpa.JpaSystemException: could not extract ResultSet; nested exception is org.hibernate.exception.GenericJDBCException: could not extract ResultSet

  2. I tried with @Procedure annotation

    @Procedure(value = "reset_disabled")
void priceActivator2();

    I got error

    2023-02-22T07:43:00,013 [scheduling-1] [INFO ] com.zageno.pricing.activator.PriceActivator priceActivatorByValidity [28] – Triggering price activator procedure
    Hibernate: {call reset_disabled()}
    2023-02-22T07:43:00,016 [scheduling-1] [WARN ] org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions [137] – SQL Error: 0, SQLState: 42809
    2023-02-22T07:43:00,017 [scheduling-1] [ERROR] org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions [142] – ERROR: reset_disabled() is a procedure
    Hint: To call a procedure, use CALL.
    Position: 15

    In above error log (highlighted in bold) , Hibernate query using "CALL" and error is suggesting use "CALL" this seems contradictory.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Well I figured out solution

    If SP performs a DML operation like (Insert,Update) add following annotations along with @Query;

    @Transactional 
@Modifying(clearAutomatically = true)
@Query(value = "CALL reset_disabled();", nativeQuery = true)
void priceActivator();

  2. 0

    you need to add @Transactional
    @Modifying tags above of the @Query because without them spring thinks you return resultset within your query.

    @Transactional
@Modifying
@Query(value = "CALL reset_disabled();", nativeQuery = true)
void priceActivator();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search