Get a REFCURSOR with JPA 2.1

0

I'm running a Stored Procedure in Postgre using JPA 2.1 I need to return REF_CURSOR only that an error occurs and I've been trying to resolve it without success.

I already use procedures being called by JPA but not to return a cursor I thought I would not have problems anymore I see that for this case the thing complicates and for this I'm looking for a help the code is as follows (easy to find in web examples )

           StoredProcedureQuery storedProcedure = 
                  entityManager
                    .createStoredProcedureQuery("spu_gerasequencia", Sequencia.class)
                    .registerStoredProcedureParameter(1, void.class, ParameterMode.REF_CURSOR)
                    .registerStoredProcedureParameter(2, String.class, ParameterMode.IN)
                    .setParameter(2, nome);                            

       List<Sequencia> seq = storedProcedure.getResultList();

       return seq;

On the console we see the error

2018-07-10 16:29:07.161 DEBUG 2720 --- [nio-8090-exec-1] org.hibernate.SQL                        : 
{? = call spu_gerasequencia(?)}
Hibernate: 
   {? = call spu_gerasequencia(?)}
2018-07-10 16:29:07.192  WARN 2720 --- [nio-8090-exec-1] 
o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 34000
2018-07-10 16:29:07.192 ERROR 2720 --- [nio-8090-exec-1] 
o.h.engine.jdbc.spi.SqlExceptionHelper   : ERRO: cursor "<unnamed portal 1>" 
não existe
javax.persistence.PersistenceException: 
org.hibernate.exception.GenericJDBCException: Error calling 
CallableStatement.getMoreResults
    org.hibernate.procedure.internal.ProcedureCallImpl.getResultList(ProcedureCallImpl.java:765)
br.com.dominio.repository.interfaces.SequenciasRepositoryImpl.getSequenceProcedure

The code has already been tampered with several times trying to fix, it was the most unfortunate error afterwards.

I'm doing a very simple application with Spring Boot and has the purpose of doing a REST service, the procedure makes an increment in a column, in fact the goal is to get a new ID

Follow the function in Postgre

CREATE OR REPLACE FUNCTION spu_gerasequencia(cnome character varying)
  RETURNS refcursor AS
$BODY$
DECLARE
   -- declaração de variáveis.
   ref REFCURSOR;
   p_SEQ integer;
   REGISTRO_id INT4;
   REGISTRO_nome Varchar(50);
   BEGIN
       LOCK TABLE SEQUENCIA; -- trava a tabela para nenhuma outra instancia 
              da função acessar a tabela e gerar a mesma Sequencia.       

       IF NOT EXISTS(SELECT VALOR FROM SEQUENCIA WHERE NOME = cnome) THEN
          INSERT INTO SEQUENCIA ( NOME, VALOR ) VALUES (cnome, 1);
       END IF;

       OPEN ref FOR
        SELECT id, nome, valor
        FROM SEQUENCIA WHERE NOME = cnome;

   FETCH ref
   INTO REGISTRO_id, REGISTRO_nome, p_seq;

   -- Atualiza a Sequencia    
   p_SEQ = p_SEQ + 1;

   UPDATE SEQUENCIA 
    SET VALOR = p_SEQ
   WHERE NOME = cnome;         

  RETURN ref; -- retorna o cursor

Obs. the function does what it proposes or it finds the record and increments in the column value when I call the same one by pgAdmin and an important information I believe, because I see "unnamed portal 1" this information when I call by pgAdmin example: select spu_gerasequencia ('MATERIAL' );

o.h.engine.jdbc.spi.SqlExceptionHelper: ERROR: cursor "unnamed portal 1" does not exist when I try to consume REST

Recent code update I tried again and changed the code

     .registerStoredProcedureParameter(1, void.class, 
        ParameterMode.REF_CURSOR)
     .registerStoredProcedureParameter(2, String.class, ParameterMode.IN)

There are two parameters removed first and did not work, I returned it only that I left it as second then I get the message that the postgre can only have only one parameter of this type, then put it again and return the error SqlExceptionHelper ERROR: cursor "unnamed portal 1" does not exist, back to square one.

    
asked by anonymous 10.07.2018 / 21:52

0 answers