4 Replies Latest reply on Nov 22, 2009 12:23 PM by Ronald van Kuijk

    SQL Exception throws in JBPM 3.1.4 while changing the Hibern

    Darshan Gohel Newbie

      Hi All,

      We are developing a work flow based system using Struts and Hibernate 3. All is working good until we changed some configuration to implement the Threading and C3P0 for concurrent users. The configuration for this in hibernate.cfg.xml is as below:

      <property name="hibernate.connection.url">jdbc:oracle:thin:@<hostname>:1521:orcl</property>
      <property name="hibernate.connection.username">username</property>
      <property name="hibernate.connection.password">pass</property>
      <property name="hibernate.current_session_context_class">thread</property>
      <property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
      
      <property name="hibernate.cache.provider_class">org.hibernate.cache.EhCacheProvider</property>
      <property name="hibernate.cache.use_second_level_cache">true</property>
      <property name="show_sql">false</property>
      
      <property name="hibernate.c3p0.max_size">4</property>
      <property name="hibernate.c3p0.min_size">4</property>
      <property name="hibernate.c3p0.timeout">5000</property>
      <property name="hibernate.c3p0.max_statements">0</property>
      <property name="hibernate.c3p0.idle_test_period">300</property>
      <property name="hibernate.c3p0.acquire_increment">1</property>
      


      There are two types of errors throwing after implementing the same,
      1) Closed ResultSet: Error Console for the error is as below:

      java.sql.SQLException: Closed Resultset: next
       at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
       at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:222)
       at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:916)
       at oracle.jdbc.driver.OracleResultSetImpl.next(OracleResultSetImpl.java:181)
       at com.mchange.v2.c3p0.impl.NewProxyResultSet.next(NewProxyResultSet.java:2859)
       at com.elitecore.workorder.subscriberapplication.dao.SubscriberApplicationDao.getSearchedGroupCustomerByCustomerId(SubscriberApplicationDao.java:739)
       at com.elitecore.workorder.subscriberapplication.manager.BulkNewProvisionManager.getSearchedGroupCustomerByCustomerId(BulkNewProvisionManager.java:321)
       at com.elitecore.workorder.subscriberapplication.action.SearchCustomerAction.searchGroupCustomer(SearchCustomerAction.java:121)
       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
       at java.lang.reflect.Method.invoke(Method.java:585)
       at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:269)
       at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:170)
       at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:425)
       at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:228)
       at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
       at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462)
       at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
       at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
       at com.elitecore.workorder.jbpm.session.JbpmSessionConversationFilter.doFilter(JbpmSessionConversationFilter.java:78)
       at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
       at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
       at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
       at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
       at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
       at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
       at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
       at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
       at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
       at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
       at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
       at java.lang.Thread.run(Thread.java:595)
      


      The code snap for the same is:
      JbpmContext jbpmContext = JbpmContextManager.getJbpmContext();
      PreparedStatement pst = null;
      StringBuffer sbQuery = new StringBuffer();
      
      sbQuery.append("select DISTINCT c.id,c.clientName,c.clientTitle, c.PANGIRNO,c.headquarteraddressid, ");
      sbQuery.append("c.customerid,c.PANGIRNO from tblmclient c ");
      
      if(accessLevel !=null && accessLevel.equalsIgnoreCase("LDCA")){
       sbQuery.append("join tbltassignedlocation al on al.locationtypeid=C.ldcabcaid ");
      }else if(accessLevel !=null && accessLevel.equalsIgnoreCase("Region")){
       sbQuery.append("join tbltassignedlocation al on al.locationtypeid=C.regionid ");
      
      }else if(accessLevel !=null && accessLevel.equalsIgnoreCase("Circle")){
       sbQuery.append("join tbltassignedlocation al on al.locationtypeid=c.circleid ");
      }
      sbQuery.append("where upper(c.customerid) like (?) ");
      sbQuery.append("and c.isdeleted like 'No' ");
      sbQuery.append("and al.staffid='"+staffId.trim()+"' order by c.CLIENTNAME ");
      
      pst = jbpmContext.getConnection().prepareStatement(sbQuery.toString());
      pst.setString(1,"%"+ customerId.toUpperCase()+"%");
      rs = pst.executeQuery();
      
      while(rs.next())// Error comes here.
      {
       searchGroupCustomer=new SearchGroupCustomer();
       searchGroupCustomer.setClientId(rs.getString("id"));
       searchGroupCustomer.setSubscriberName(rs.getString("CLIENTNAME"));
       searchGroupCustomer.setSubscriberTitle(rs.getString("clientTitle"));
       searchGroupCustomer.setCustomerId(rs.getString("customerId"));
       searchGroupCustomer.setPanGIRNo(rs.getString("PANGIRNO"));
       Address address1=(Address)abstractDao.initliaze(Address.class,rs.getString("headquarteraddressid"));
       searchGroupCustomer.setAddress(address1);
       lstCustomerName.add(searchGroupCustomer);
      }
      


      2) Invalid Cursor:Error Console for the error is as below:
      java.sql.SQLException: ORA-01001: invalid cursor
      at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:180)
      at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:208)
      at oracle.jdbc.ttc7.v8Odscrarr.receive(v8Odscrarr.java:192)
      at oracle.jdbc.ttc7.TTC7Protocol.describe(TTC7Protocol.java:592)
      at oracle.jdbc.driver.OracleStatement.describe(OracleStatement.java:5371)
      at oracle.jdbc.driver.OracleStatement.get_column_index(OracleStatement.java:5141)
      at oracle.jdbc.driver.OracleResultSetImpl.findColumn(OracleResultSetImpl.java:698)
      at oracle.jdbc.driver.OracleResultSet.getString(OracleResultSet.java:1505)
      at com.mchange.v2.c3p0.impl.NewProxyResultSet.getString(NewProxyResultSet.java:3342)
      at com.elitecore.workorder.subscriberapplication.dao.SearchSubscriberApplicationDao.getSAStatusReport(SearchSubscriberApplicationDao.java:445)
      at com.elitecore.workorder.subscriberapplication.manager.SubscriberApplicationManager.getSAStatusReport(SubscriberApplicationManager.java:114)
      at com.elitecore.workorder.subscriberapplication.action.UpgradePostConnectionAction.listApplications(UpgradePostConnectionAction.java:259)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
      at java.lang.reflect.Method.invoke(Method.java:585)
      at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:269)
      at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:170)
      at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:425)
      at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:228)
      at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
      at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:637)
      at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
      at com.elitecore.workorder.jbpm.session.JbpmSessionConversationFilter.doFilter(JbpmSessionConversationFilter.java:78)
      at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
      at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
      at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
      at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
      at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:128)
      at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
      at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
      at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:286)
      at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:845)
      at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:583)
      at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:447)
      at java.lang.Thread.run(Thread.java:595)

      The code snap for the same is:
      PreparedStatement pst=null;
      JbpmContext jbpmContext = JbpmContextManager.getJbpmContext();
      
      pst = jbpmContext.getConnection().prepareStatement(getSAStatusReport);
      pst.setString(1, saId);
      rs = pst.executeQuery();
      
      while (rs.next())
      {
       saStatusInfo = new SaStatusInfo();
       AbstractDao abstractDao=new AbstractDao();
       Address endAAddress=(Address)abstractDao.initliaze(Address.class,rs.getString("endAAddressId"));
       Address endBAddress=(Address)abstractDao.initliaze(Address.class,rs.getString("endBAddressId"));//Error Comes here.
       log.debug(" endAAddress.getAddressAsString() "+endAAddress.getAddressAsString());
       log.debug(" endBAddress.getAddressAsString() "+endBAddress.getAddressAsString());
       saStatusInfo.setEndAInstallAddress(endAAddress.getAddressAsString());
       saStatusInfo.setEndBInstallAddress(endBAddress.getAddressAsString());
      }
      


      As from basic debugging we identify that the problem arises when the
      abstractDao.initliaze()
      is used. This class basically fetch the data of a table and prepare an object for the same. The code snap for the same is as below:
      public Object initliaze(Class clazz, String id) throws Exception
      {
       JbpmContext jbpmContext = JbpmContextManager.getJbpmContext();
      
       return jbpmContext.getSession().get(clazz,id);
      }
      


      We are at a very critical stage in the project and this Errors are started.
      Kindly suggest us a way by which we can be out with this problems.

      Note: All this errors removed if we commented the hibernate.cfg.xml configuration for Thread and C3P0.

      Thanks in Advance.
      Darshan Gohel.