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 RESTRecent 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.