1 Reply Latest reply on Dec 21, 2007 3:51 AM by Vicky Kak

    java.sql.SQLException: The statement is closed.

    Paul Park Newbie

      I'm getting a java.sql.SQLException: The statement is closed. error in Jboss. Do you notice any problem in the code or config file?

      server.log:

      2007-12-20 18:00:08,025 TRACE [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Returning connection to pool org.jboss.resource.connectionman
      ager.TxConnectionManager$TxConnectionEventListener@793aac5f[state=NORMAL mc=org.jboss.resource.adapter.jdbc.local.LocalManagedConnection@7b0acf26 handles=0 l
      astUse=1198191608025 permit=false trackByTx=false mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@af917bd context=org.jboss.resou
      rce.connectionmanager.InternalManagedConnectionPool@67857eeb xaResource=org.jboss.resource.connectionmanager.TxConnectionManager$LocalXAResource@2a606e6 txSy
      nc=null] [InUse/Available/Max]: [6/88/100]
      2007-12-20 18:00:08,041 INFO [STDOUT] Exception in processReading java.sql.SQLException: The statement is closed.
      2007-12-20 18:00:08,041 INFO [STDOUT] Test 6

      oracle-ds.xml:

      <local-tx-datasource>
      <jndi-name>OracleDS</jndi-name>
      <use-java-context>false</use-java-context>
      <connection-url>jdbc:oracle:thin:@ldcdevl:1521:xxxx</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>xxxxx</user-name>
      xxxxx
      <min-pool-size>10</min-pool-size>
      <max-pool-size>100</max-pool-size>
      <prepared-statement-cache-size>200</prepared-statement-cache-size>
      <max-idle-timeout>0</max-idle-timeout>
      <blocking-timeout-millis>50000</blocking-timeout-millis>
      <idle-timeout-minutes>30</idle-timeout-minutes>
      <track-statements>true</track-statements>

      <type-mapping>Oracle9i</type-mapping>

      </local-tx-datasource>




      public class BillingResponseDAO {
      private DBConnection dbCon = null;
      private List billingReadList = new ArrayList();
      private BillingRead billingRead = null;
      private com.th.commonUtil.intface.Context ctx = null;
      public String premCode = null;
      public String servNum = null;

      public static final String billingResponseString =
      "INSERT INTO UXBSRRS values (?,?,?,?,?,?,?,SYSDATE,USER)";
      public static final String billingResponseDetailString =
      "INSERT INTO UXRSRRS values (?,?,?,?,?)";

      AppContext appCtxt = null;

      public BillingResponseDAO() {
      }

      public BillingResponseDAO(String test) {
      }

      public

      void init(Context ctx) {
      System.out.println("Inside BillingResponseDAO 1");
      try {
      dbCon = ctx.getDBConMDB();
      dbCon.getConnectionMDB();
      } catch (Exception e) {
      System.out.println("Exception in BillingResponseDAO init" + e);
      }
      }


      public void processReading(BillingResponse bResponse) throws Exception {

      PreparedStatement stmt = null;
      PreparedStatement stmt1 = null;
      String premCodeLocal = null;
      String noData = null;
      String validationStatus = null;
      String status = "N";
      String dateStr = null;
      String dateStr1 = null;
      String dateStr2 = null;
      String dateStr3 = null;

      try {
      if (dbCon == null) {
      dbCon = ctx.getDBConMDB();
      dbCon.getConnectionMDB();
      }
      stmt =
      (PreparedStatement)dbCon.getCachedStatement(billingResponseString);
      stmt1 =
      (PreparedStatement)dbCon.getCachedStatement(billingResponseDetailString);
      if (stmt != null) {
      DBUtil.setString(stmt, 1, bResponse.getTransactionID());

      /** Commented on May 21 JCH *
      parseSDP(bResponse.getSDP());

      premCodeLocal = removeLeadingZeros(premCode);
      DBUtil.setString(stmt, 2, premCodeLocal);

      DBUtil.setLong(stmt, 3, Long.parseLong(servNum));

      DBUtil.setString(stmt, 4, bResponse.getMeterNum());
      */
      /* Added the code Below May 21**/

      Long localServNum=null;
      String tempSDP = null;
      tempSDP = bResponse.getSDP();
      System.out.println("Temp SDP ="+tempSDP);
      if ( (tempSDP == null) ||
      (tempSDP.equals(""))
      )
      {
      DBUtil.setString(stmt, 2, premCode);
      DBUtil.setLong(stmt, 3,localServNum);
      }
      else{
      parseSDP(bResponse.getSDP());
      premCodeLocal = removeLeadingZeros(premCode);

      DBUtil.setString(stmt, 2, premCodeLocal);
      DBUtil.setLong(stmt, 3, Long.parseLong(servNum));
      }
      /* End the code Below May 21**/
      DBUtil.setString(stmt, 4, bResponse.getMeterNum());

      String tempReadDate = bResponse.getReadDate();
      if ( (tempReadDate ==null)||
      (tempReadDate.equals(""))
      )
      {
      DBUtil.setDate(stmt, 5,null);
      }
      else
      {
      dateStr = bResponse.getReadDate();
      dateStr1 = new String(dateStr.substring(0, 10));
      dateStr2 = new String(dateStr.substring(11, 19));
      dateStr3 = dateStr1 + " " + dateStr2;
      System.out.println("dateStr =" + dateStr3);
      String testString = getDateTime(dateStr3);
      SimpleDateFormat df =
      new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

      DBUtil.setDate(stmt, 5, df.parse(testString));
      }

      DBUtil.setString(stmt, 6, bResponse.getReaderID());

      DBUtil.setString(stmt, 7, bResponse.getErrorCode());

      billingReadList = new ArrayList();
      billingReadList = bResponse.getBillingRead();
      System.out.println("Statement After Values are SET -1 = " + stmt);
      if (billingReadList == null) {
      ;
      }
      else
      {
      for (int i = 0; i < billingReadList.size(); i++) {
      billingRead = (BillingRead)billingReadList.get(i);
      DBUtil.setString(stmt1, 1, bResponse.getTransactionID());
      DBUtil.setInt(stmt1, 2, i + 1);
      DBUtil.setString(stmt1, 3, billingRead.getMeausrementType());
      String reading= billingRead.getReading();
      /*Long reading1=null; Defect# */
      Float reading1=null;
      /*Code Added */
      if ( (reading == null) ||
      (reading.equals(""))
      )
      {
      reading1=null;
      }
      else{
      // reading1= Long.parseLong(reading.trim());
      reading1= Float.parseFloat(reading.trim());
      System.out.println("Reading= "+reading1);
      }

      /*DBUtil.setLong(stmt1, 4, reading1); */
      DBUtil.setFloat(stmt1, 4, reading1);

      validationStatus = billingRead.getValidationStatus();
      noData = billingRead.getNoData();


      if (bResponse.getErrorCode() == "0") {
      if (noData == "false") {
      if (validationStatus == "E")
      status = "E";
      else if (validationStatus == "I")
      status = "I";
      else if (validationStatus == "Ext")
      status = "U";
      else
      status = "V";
      } else
      status = "N";

      DBUtil.setString(stmt1, 5, status);
      }
      DBUtil.setString(stmt1, 5, status);
      stmt1.execute();
      }
      }

      stmt.execute();

      }

      } catch (Exception e) {
      System.out.println("Exception in processReading " + e);
      } finally {
      try {
      DBUtil.close(stmt);
      DBUtil.close(stmt1);
      dbCon.close();
      } catch (Exception e) {
      System.out.println("Exception in closing " + e);
      }
      }
      }
      }

        • 1. Re: java.sql.SQLException: The statement is closed.
          Vicky Kak Master

           

          "paulpark" wrote:
          I'm getting a java.sql.SQLException: The statement is closed. error in Jboss. Do you notice any problem in the code or config file?

          Why don't you put more logging to trace the point which is closing the statement which you are trying to use later ?
          You would be in better position to trace the point from where the error is originating.