Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Exception ResultSet using eclipseLink @NamedStoredProcedureQuery
Exception ResultSet using eclipseLink @NamedStoredProcedureQuery [message #1803733] Thu, 07 March 2019 19:41 Go to next message
Daniel Araujo is currently offline Daniel AraujoFriend
Messages: 4
Registered: September 2018
Location: Brazil
Junior Member
I need help with a exception below when implements a class AttributeConverter:

java.sql.SQLException: Result Set Closed: next
at oracle.jdbc.driver.InsensitiveScrollableResultSet.next(InsensitiveScrollableResultSet.java:565)
at org.eclipse.persistence.mappings.converters.ConverterClass.convertDataValueToObjectValue(ConverterClass.java:163)
at org.eclipse.persistence.mappings.foundation.AbstractDirectMapping.getObjectValue(AbstractDirectMapping.java:619)
at org.eclipse.persistence.mappings.foundation.AbstractDirectMapping.valueFromRow(AbstractDirectMapping.java:1223)
at org.eclipse.persistence.mappings.foundation.AbstractDirectMapping.buildCloneFromRow(AbstractDirectMapping.java:208)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildAttributesIntoWorkingCopyClone(ObjectBuilder.java:2007)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildWorkingCopyCloneFromRow(ObjectBuilder.java:2260)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObjectInUnitOfWork(ObjectBuilder.java:858)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:745)
at org.eclipse.persistence.internal.descriptors.ObjectBuilder.buildObject(ObjectBuilder.java:699)
at org.eclipse.persistence.queries.EntityResult.getValueFromRecord(EntityResult.java:201)
at org.eclipse.persistence.queries.ResultSetMappingQuery.buildObjectsFromRecords(ResultSetMappingQuery.java:270)
at org.eclipse.persistence.queries.ResultSetMappingQuery.buildObjectsFromRecords(ResultSetMappingQuery.java:239)
at org.eclipse.persistence.internal.jpa.StoredProcedureQueryImpl.getOutputParameterValue(StoredProcedureQueryImpl.java:485)
at org.eclipse.persistence.internal.jpa.StoredProcedureQueryImpl.getOutputParameterValue(StoredProcedureQueryImpl.java:519)

My converter class
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.persistence.AttributeConverter;
import javax.persistence.Converter;

@Converter
public class CursorConverter implements AttributeConverter<List<String>, ResultSet>{

    @Override
    public ResultSet convertToDatabaseColumn(List<String> attribute) {
        return null;
    }

    @Override
    public List<String> convertToEntityAttribute(ResultSet rs) {
        List<String> companyCodes= new ArrayList<>() ;
        try {
            while(rs.next()) {
                companyCodes.add(rs.getString("CODE_COMPANY"));
            }
            //rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return companyCodes;
    }
}

My repository class:
import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.query.Procedure;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Transactional(readOnly=true)
@Repository
public interface EmployeeRepository extends JpaRepository<Employee,String>{

    @Procedure(name="Employee.procFind",outputParameterName="EMPL_INFO")
    List<Employee> find(@Param("KEY") String key);

}

My Object class:
import java.io.Serializable;
import java.util.Date;
import java.util.List;

import javax.persistence.Column;
import javax.persistence.Convert;
import javax.persistence.Entity;
import javax.persistence.NamedStoredProcedureQuery;
import javax.persistence.ParameterMode;
import javax.persistence.QueryHint;
import javax.persistence.StoredProcedureParameter;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

import org.apache.commons.lang.builder.ToStringBuilder;
import org.eclipse.persistence.annotations.ConversionValue;
import org.eclipse.persistence.annotations.IdValidation;
import org.eclipse.persistence.annotations.ObjectTypeConverter;
import org.eclipse.persistence.annotations.PrimaryKey;
import org.eclipse.persistence.config.QueryHints;

@Entity
@PrimaryKey(validation=IdValidation.NONE,columns={@Column(name="KEY")})
@NamedStoredProcedureQuery(name = "Employee.procFind", procedureName = "ORACLE.PROC_FIND_EMPLOYEE",hints = {
        @QueryHint(value = "FALSE", name = QueryHints.QUERY_RESULTS_CACHE) },resultClasses = {Employee.class},parameters = {
                @StoredProcedureParameter(name= "KEY" , mode = ParameterMode.IN, type = String.class),
                @StoredProcedureParameter(name = "EMPL_INFO", mode = ParameterMode.REF_CURSOR, type = void.class)})
public class Employee implements Serializable{

    /**
     * 
     */
    private static final long serialVersionUID = 1L;

    @Column(name = "KEY_EMPLOYEE")
    private String key;

    @Convert(converter=CursorConverter.class)
    @Column(name = "CURSOR_CODES")
    private List<String> companyCodes;
...
}

This is my procFind procedure 
PROCEDURE PROC_FIND_EMPLOYEE (KEY IN  VARCHAR2,EMPL_INFO OUT SYS_REFCURSOR)
BEGIN

      OPEN EMPL_INFO FOR
         SELECT KEY_EMPLOYEE,
                CURSOR_CODES_SOUTH (ROW_ID) CURSOR_CODES,
         FROM EMPLOYEE_NORTH
         WHERE  KEY_EMPLOYEE = KEY
         UNION ALL
         SELECT KEY_EMPLOYEE,
                CURSOR_CODES_NORTH (ROW_ID) CURSOR_CODES,
         FROM EMPLOYEE_SOUTH
         WHERE  KEY_EMPLOYEE = KEY;

END PROC_FIND_EMPLOYEE;

and this is my cursor_codes_south function
FUNCTION CURSOR_CODES_SOUTH(COM_ID IN VARCHAR2) RETURN SYS_REFCURSOR
IS
  CUR_COM  SYS_REFCURSOR;
  V_SQL VARCHAR2(500);
BEGIN

  V_SQL := 'SELECT  CODE_COMPANY
            FROM    COMPANY_SOUTH
            WHERE   ROW_ID = :b1';

  OPEN CUR_COM FOR V_SQL USING COM_ID;

  RETURN CUR_COM;

END;


well, looking at the code of the org.eclipse.persistence.jpa project in the StoredProcedureQueryImpl class I checked that the main ResultSet is always closing. (Line 134) :(

index.php/fa/35022/0/


Can you guys help me, please?

Or another solution to this problem, as I can not modify oracle procedures. :(

Thank you.

Report message to a moderator

Re: Exception ResultSet using eclipseLink @NamedStoredProcedureQuery [message #1803737 is a reply to message #1803733] Thu, 07 March 2019 21:29 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1389
Registered: July 2009
Senior Member
You posted the same issue here https://stackoverflow.com/questions/54953127/exception-resultset-using-eclipselink-namedstoredprocedurequery

I don't know why you wouldn't map out the CURSOR_CODES_NORTH and CURSOR_CODES_SOUTH and then build the list directly, but you might bet better off using straight JDBC for this, as the design doesn't fit with the purpose of JPA. JPA is expected to operate over the results and close the cursor, and is giving your converter a chance to operate on the rows, not the cursor itself

If you are going to use this stored procedure, you have to remember that the cursor it is returning is just a list of strings; it doesn't have the "KEY_EMPLOYEE"/"CURSOR_CODES" fields your JPA entity expects, so there is no way to build entities from it. Get it to return just a List of Strings, not Employee objects:

@NamedStoredProcedureQuery(name = "Employee.procFind", procedureName = "ORACLE.PROC_FIND_EMPLOYEE",hints = {
@QueryHint(value = "FALSE", name = QueryHints.QUERY_RESULTS_CACHE) },resultClasses = {String.class},parameters = {
@StoredProcedureParameter(name= "KEY" , mode = ParameterMode.IN, type = String.class),
@StoredProcedureParameter(name = "EMPL_INFO", mode = ParameterMode.REF_CURSOR, type = void.class)})

When you execute it, it will give you a List of strings that was contained within the ref cursor, without the need for a converter.



Report message to a moderator

Re: Exception ResultSet using eclipseLink @NamedStoredProcedureQuery [message #1803761 is a reply to message #1803737] Fri, 08 March 2019 14:21 Go to previous messageGo to next message
Daniel Araujo is currently offline Daniel AraujoFriend
Messages: 4
Registered: September 2018
Location: Brazil
Junior Member
@Chris the problem is that I can not modify the cursor, I do not have permission to access the oracle procedures, just the cursors. But I see that there is no other way but to use jdbc. the CURSOR_CODES_NORTH and CURSOR_CODES_SOUTH ​​receives a field (ROW_ID) that is not mapped in the cursor EMPL_INFO.
In fact, I'd like something like @Structs.
Thank you and sorry for replicating this message.

[Updated on: Sat, 09 March 2019 13:11]

Report message to a moderator

Re: Exception ResultSet using eclipseLink @NamedStoredProcedureQuery [message #1803928 is a reply to message #1803761] Wed, 13 March 2019 18:34 Go to previous message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1389
Registered: July 2009
Senior Member
My suggestion wasn't to change the stored procure or what it returns, but to change your application's expectations: The JPA query used to execute this query should return a list of strings, not employee instances.

My comment on using JDBC is because I can see no use for this Java class/entity in a JPA model as it stands -- there are many other options but they all depend on factors outside of what you have presented. If you can get a query that returns struct, you can certainly map them to java objects, but again that means changing your stored procedure and what it returns. You could also map out that stored procedure logic, so that your Employee class has two lists representing the different north and south values, that you union in your code, making the Employee entity more representative of your actual database model.

Best Regards,
Chris

stand though.

Report message to a moderator

Previous Topic:executeUpdate failed with EclipseLink-4002 / ORA-00936
Next Topic:Moxy Java11
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jun 04 11:02:17 GMT 2023

Powered by FUDForum. Page generated in 0.01809 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top

Sign up to our Newsletter

A fresh new issue delivered monthly