Result Set is closed error when referencing table via JPA (Websphere and DB2)

Question

I'm using the IBM implentation of Open JPA on WebSphere 7 and I'm having an issue when I'm trying to reference an object that is @ManyToOne and keep getting the following error from DB2:

com.ibm.db2.jcc.b.SqlException: [jcc][t4][10120][10898][3.50.152] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null

I'm pulling my hair out as to why this doesn't work and hope that somebody can help.

Here is a simplified view the database schemas:

Table Report

record_id - integer - (primary key - generated by DB2)

agency - integer not null (foreign key to Dropdown table)

Table Dropdown

record_id - integer - (primary key - generated by DB2)

Here is the JPA entity for the Report which references the agency

@ManyToOne(fetch=FetchType.EAGER)
@JoinColumn(name="AGENCY")
private Dropdown agency;

Here is the code where I'm running a named query to get the data and then just iterating over the result set to print out the report id and the agnency. Whenever report.getAgency() is called, I get the "result set is closed" error from DB2:

@SuppressWarnings("unchecked")
public List<Report> getOpenIncidentsForUser(String aceId) throws Exception
{
    List<Report> results = null;
    EntityManager em = getEntityManager();

    try
    {
        Query query = em.createNamedQuery("getOpenIncidentsForUser");
        query.setParameter(1, aceId);
        results = (List<Report>) query.getResultList();

        Iterator<Report> it = results.iterator();
        while(it.hasNext())
        {
            Report report = it.next();
            System.out.println("Report [" + report.getRecordId() + "] Agency: [" + report.getAgency() + "]");
        }
    }
    catch (Exception e)
    {
        log.fatal("Fatal error getting incidents for user", e);
        throw e;
    }
    finally
    {
        em.close();
    }

    return (List<Report>) results;
}

if I don't ever refer to the getAgency method, I can print out anything else about the report with no problems. It only seems to be with the reference to the 2nd table. Any ideas?

Answer 1

I had answered this in responses to my original comment, but realized that I never marked the question as answered, so I wanted to do that officially.

The fix is documented here: https://www.ibm.com/support/knowledgecenter/SSEQTP_8.5.5/com.ibm.websphere.base.doc/ae/tejb_jpatroubleshoot.html

The fix ended up being the resulSetHoldability setting needed to be 1 instead of 2

For XA data sources you have to set downgradeHoldCursorsUnderXa to true, otherwise you could get a persistence exception with this message:

An SQL OPEN for a held cursor was issued on a XA connection

Answer 2

Setting DB2 resultSetHoldability=1 will only work if you are using a non-XA datasource. If you need to keep 2PC, then this is not a solution.

I had this exact problem and finally solved it by hard-coding a transaction around the offending code. This is what I have:

public class RequeueRuleList_back {

/*
 * Injected resources ...
 */
@Resource UserTransaction txn;
@PersistenceUnit EntityManagerFactory emf;

:

public List<RequeueRuleBean> getRequeueRules() {

    /* 
     * We need a hard transaction around this code even though it is just a query
     * otherwise we cannot use a DB2 XA datasource to do this:
     * 
     * com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][3.63.75] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
     */

    try {
        txn.begin();
    } catch (Exception e) {
        FacesContext.getCurrentInstance().addMessage(null, 
                new FacesMessage("Error starting transaction: " + e.getMessage()));
        return null;
    }
    EntityManager em = emf.createEntityManager();

    :

    Query q = em.createQuery("SELECT rr FROM RequeueRule rr");

    // Do useful things ...

    em.close();
    try {
        txn.commit();
    } catch (Exception e) {
        FacesContext.getCurrentInstance().addMessage(null, 
                new FacesMessage("Error committing transaction: " + e.getMessage()));
    }
    :
}

}

Answer 3

If you just use jdbc to connect DB2 and didn't use Hibernate etc, you also have got this error. Because in the new JDBC version with DB2 9.7, many functions you shouldn't support in new vesion,althought there are no error running on old version jdbc.

These function include. 1: PreparedStatement old version

pt.executeUpdate(sql);

new version

pt.executeUpdate();

2: Connection Iteration

old version：

  try{      
    conn = ConnectionFactory.getConnection(ApplicationConstants.LOCAL_DATASOURCE_JNDI_NAME);
    sql="select role_id,role_sname,role_sdesc from db2admin.mng_roles "+sql_condition+" order by role_id asc";
    pt = conn.prepareStatement(sql.toString());
    System.out.println("sql ="+sql);
    rs = pt.executeQuery();
    while(rs.next()){
                i++;
                role_id=rs.getInt(1);
                role_sname=PubFunction.DoNull(rs.getString(2)).trim();
                role_sdesc=PubFunction.DoNull(rs.getString(3)).trim();
                role_right=PubFunction.DoNull(newright.getRightsbyRole(conn,role_id)).trim();}

new version

  try{      
    conn = ConnectionFactory.getConnection(ApplicationConstants.LOCAL_DATASOURCE_JNDI_NAME);
    sql="select role_id,role_sname,role_sdesc from db2admin.mng_roles "+sql_condition+" order by role_id asc";
    pt = conn.prepareStatement(sql.toString());
    System.out.println("sql ="+sql);
    rs = pt.executeQuery();
    while(rs.next()){
                i++;
                role_id=rs.getInt(1);
                role_sname=PubFunction.DoNull(rs.getString(2)).trim();
                role_sdesc=PubFunction.DoNull(rs.getString(3)).trim();
                role_right=PubFunction.DoNull(newright.getRightsbyRole(null,role_id)).trim();}