Re: [OJB-developers] ORA-01000

[Jason v. Z. <ja...@ze...>]

On Wed, 2002-06-19 at 13:27, Martin Fuzzey wrote:
> Matthew and all,

The mailing lists and code are now @ jakarta, please repost there.

http://jakarta.apache.org/ojb/
 
> Following the thread at :
> 
> http://sourceforge.net/mailarchive/forum.php?thread_id=796595&forum_id=4880
> 
> In cvs r1.8 of StatementManager.java the ORA-01000 problem still exists.
> I can reproduce it easilly simply by repeating "SELECT *" type calls using :
>          Query query = new QueryByCriteria(IFdp.class, null);
>          try {
>              return m_broker.getCollectionByQuery(query);
>          } catch(PersistenceBrokerException ex) {
>              throw new IOException(ex.toString());
>          }
> 
> (for me the problem occurs after about 6 loops but that will depend on the 
> Oracle configuration and the object model).
> 
> I too tried to fix this by adding a stmt.close() to 
> StatementManager.closeResources() and, as was pointed out in the above 
> thread, this causes errors elesewhere due to cached statements.
> 
> On Oracle it is definitely necessary to close unused statements. Closing 
> the statement will close any associated result set but NOT the reverse.
> 
> Here is my analysis and suggested correction for the problem (I'm new to 
> OJB so maybe I've missed something).
> 
> StatementsForClass caches several statements for each class (for uodate, 
> insert, delete and primary key lookup). It provides accessor methods which 
> return the cached statement or create it if necessary. These statements 
> must not be closed when an iterator is discarded. However there is also a 
> getGenericStmt() method which does NOT do caching. In particular this 
> method is used for select * type queries (JdbcAccess.executeQuery()). Hence 
> statements obtained in this way (encapsulated in a ResultStateAndStatement 
> object) DO need to be closed.
> 
> What I did was to add a method to StatementsForClassIF :
> 
> 	public boolean isCached(Statement stmt);
> 
> This is implemented in StatementsForClass :
>      public boolean isCached(Statement stmt) {
>          if (stmt == selectByPKStmt ||
>              stmt == insertStmt ||
>              stmt == updateStmt ||
>              stmt == deleteStmt) {
>              return true;
>          }
>          return false;
>      }
> 
> Then in StatementManager during closeResources() we need to see if the 
> statement to be closed has been cached by any of the StatementForClass 
> objects (fortunately StatementManager already knows them) and close it if 
> it is NOT cached :
> 
>      public void closeResources(Statement stmt, ResultSet rs)
>      {
>          /**
>           * MBAIRD
>           * We need to close the resultset and statement in order to 
> reclaim resources, and help Oracle
>           * which will eventually throw an ORA-01000 (open cursor problem).
>           */
>          try
>          {
>              if (stmt != null) // MF
>              {
>                  closeIfNotCached(stmt);
>              }
> 
>              if (rs != null)
>              {
>                  rs.close();
>              }
>          }
>          catch (SQLException ignored)
>          {
>              LoggerFactory.getDefaultLogger().warn("closing resultset had 
> error: "+ ignored.getMessage());
>          }
>          rs = null;
>          stmt = null;
>      }
> 
>      private void closeIfNotCached(Statement stmt) {
>          boolean doClose = true;
>          for (Iterator it = statementTable.values().iterator(); 
> it.hasNext();) {
>              StatementsForClassIF sfc = (StatementsForClassIF) it.next();
>              if (sfc != null) {
>                  if (sfc.isCached(stmt)) {
>                      doClose = false;
>                      LoggerFactory.getDefaultLogger().debug("Not closing 
> stmt as cached");
>                      break;
>                  }
>              }
>          }
>          if (doClose) {
>              try {
>                  stmt.close();
>              } catch(SQLException ignored) {
>                  LoggerFactory.getDefaultLogger().warn("closing statement 
> had error: "+ ignored.getMessage());
>              }
>          }
>      }
> 
> I'm not sure this is the most elegant solution but it works for me.
> 
> Martin
> 
> 
> ----------------------------------------------------------------------------
>                    Bringing you mounds of caffeinated joy
>                    >>>     http://thinkgeek.com/sf    <<<
> 
> _______________________________________________
> Objectbridge-developers mailing list
> Obj...@li...
> https://lists.sourceforge.net/lists/listinfo/objectbridge-developers
-- 
jvz.

Jason van Zyl
ja...@ap...
http://tambora.zenplex.org

In short, man creates for himself a new religion of a rational
and technical order to justify his work and to be justified in it.
  
  -- Jacques Ellul, The Technological Society