RE: [OJB-developers] Fixes for ORA-01000 (open cursor problem)

[Matthew B. <ma...@so...>]

Thanks for the input.

Upon analysis, your patch is 1/2 correct. We can shut the resultset, but we
have to keep the statement around as it is re-used. I put in the resultset
closure code, and ran all tests and it works fine. Haven't tested it with
Oracle, but it *should* solve the problem. Let me know if you find
otherwise.

Regards,
Matthew

-----Original Message-----
From: Galvin Hsiu [mailto:gk...@ya...] 
Sent: Friday, June 07, 2002 2:24 PM
To: obj...@li...
Subject: [OJB-developers] Fixes for ORA-01000 (open cursor problem)

In 0.9, I encountered an open cursor problem with
Oracle, which is caused by prepared statements and
results not getting closed properly (happened using
the PersistentBrokerImpl in singleVM mode).

The problem:

Resultsets and PreparedStatements were not getting
closed when issuing any kind of JdbcAccess calls.

The cause of this is because there is nothing defined
in StatementManager.closeResources()!

Furthermore, the materializeObject is incorrect - in
the older CVS version, it attempts to close just the
resultset but not the prepared statement (this is for
references and collections within the materialized
object).  The version in CVS attempts to close both,
which would raise an exception.

I unit tested this codefix while using SELECT * FROM
V_$OPEN_CURSOR (internal user) - So far tests indicate
everything is ok.

In 0.8375 prior to this, this problem did not occur.

The 2 fixes:

1)  StatementManager.java

public void closeResources(Statement stmt, ResultSet
rs)
    {
            /* START NEW CODE */

		// if the resultset is valid, close it now
		try
		{
			if (rs != null)
			{
				rs.close();
			}
		}
		catch (SQLException ignored)
		{
			LoggerFactory.getDefaultLogger().warn("closing
resultset had error: "+ ignored.getMessage());
		}

		// if the stmt is valid get the connection from it,
		// then close both
		if (stmt != null)
		{
			try
			{
				stmt.close();
			}
			catch (SQLException ignored)
			{
	
LoggerFactory.getDefaultLogger().warn("closing
statement had error: "+ ignored.getMessage());
			}
		}
		// now set them all to null
		rs = null;
		stmt = null;
            
            /* END NEW CODE */
    }

2)  JdbcAccess.java

    public Object materializeObject(ClassDescriptor
mif, Identity oid)
        throws PersistenceBrokerException
    {
        Object obj = null;
        ResultSet rs = null;
        PreparedStatement stmt = null;
        try
        {
            stmt =
broker.getStatementManager().getSelectByPKStatement(mif);
            synchronized (stmt)
            {
               
broker.getStatementManager().bindSelect(stmt, oid);
                rs = stmt.executeQuery();
                // data available read object, else
return null
                if (rs.next())
                {
                    RowReader reader =
mif.getRowReader();
                    Object[] row = new
Object[mif.getFieldDescriptions().length];
                    reader.readObjectArrayFrom(rs,
mif, row);
                    obj =
mif.getRowReader().readObjectFrom(row, mif);
                }
                else
                {
                    obj = null;
                }
            }
        }
        catch (PersistenceBrokerException e)
        {
			logger.error("PersistenceBrokerException during the
execution of materializeObject: " + e.getMessage(),
e);
            throw e;
        }
        catch (SQLException e)
        {
			logger.error("SQLException during the execution of
materializeObject: " + e.getMessage(), e);
            throw new
PersistenceBrokerSQLException(e);
        }
        finally
        {
                  /* START MODIFIED CODE */
			broker.getStatementManager().closeResources(null,
rs);
                  /* END MODIFIED CODE */
        }

        return obj;
    }

__________________________________________________
Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com

_______________________________________________________________

Don't miss the 2002 Sprint PCS Application Developer's Conference
August 25-28 in Las Vegas -
http://devcon.sprintpcs.com/adp/index.cfm?source=osdntextlink

_______________________________________________
Objectbridge-developers mailing list
Obj...@li...
https://lists.sourceforge.net/lists/listinfo/objectbridge-developers