5 Replies Latest reply on Feb 25, 2004 2:43 PM by Joachim Van der Auwera

    please help me out!!! connection error

    pbk97030 Newbie

      I am using MSSQL Server and Jboss 3.2.3.
      The problem is when I try to connect to the database, I get the following error message.

      This is part of the error message on the console.
      ==================================
      10:03:42,702 ERROR [NascoReporting] SQL Exception in com.nascopgh.reporting.ejb.ReportingSpreadSheetBean
      java.sql.SQLException: Connection handle has been closed and is unusable at org.jboss.resource.adapter.jdbc.WrappedConnection.checkStatus(Wrapped Connection.java:772) at org.jboss.resource.adapter.jdbc.WrappedConnection.createStatement(WrappedConnection.java:137)
      ==================================

      [mssql-ds.xml]
      ----------------------------

      <local-tx-datasource>
      <jndi-name>genesisDS</jndi-name>
      <connection-url>jdbc:microsoft:sqlserver://dbdev1:1433;Development=Development</connection-url>
      <driver-class>com.microsoft.jdbc.sqlserver.SQLServerDriver</driver-class>
      <user-name>DevUser</user-name>
      devuser
      </local-tx-datasource>


      [jboss.xml]
      ----------------

      <ejb-name>ReportingSpreadSheetBean</ejb-name>
      <jndi-name>reporting</jndi-name>
      <resource-ref>
      <res-ref-name>genesisDS</res-ref-name>
      <jndi-name>java:/genesisDS</jndi-name>
      </resource-ref>
      <method-attributes>
      </method-attributes>


      [ejb-jar.xml]
      <resource-ref><res-ref-name>genesisDS</res-ref-name>
      <res-type>javax.sql.DataSource</res-type>
      <res-auth>Container</res-auth>
      <res-sharing-scope>Unshareable</res-sharing-scope>
      </resource-ref>


      [source code]
      ctx = new InitialContext ( );
      ds = ( DataSource ) ctx.lookup( "java:/genesisDS" );
      conn = ds.getConnection ( );


      please help me out.
      Byoung

        • 1. Re: please help me out!!! connection error
          Joachim Van der Auwera Expert

          Is this a stateful session bean with the connection staying alive across invocations?

          In that case, check your logs. The connecton was probably closed by JBoss already. This can be modified in transaction-service.xml

          Joachim

          • 2. Re: please help me out!!! connection error
            pbk97030 Newbie

            Than you for your explanation.

            This program is stateless sesstion bean.
            Could you tell me what I shoud do?

            Thank you in advance.

            • 3. Re: please help me out!!! connection error
              Joachim Van der Auwera Expert

               

              "pbk97030" wrote:
              This program is stateless sesstion bean.


              Are you keeping your connection open? If that is the case, you cannot be using a SLSB, as the connection is "state"...

              In the other case, better to post your code if you can. You might be doing something wrong in the JDBC stuff.

              Joachim

              • 4. Re: please help me out!!! connection error
                pbk97030 Newbie

                This is part of my source code.
                Please check my code out and reply to me.

                Especially, the error is occurred in the following code.
                pstmt = conn.prepareStatement ( SQL_SELECT_STATUS_DESC );

                Thank you in advance
                -------------------------------------------------------------------------
                try
                {
                conn = getConnection ( );
                log.info("DB Connection:" + conn);
                //log.info(conn.isClosed());

                for(int i = 0; i< listOfLoanNumber.size();i++){
                log.info("inside the loop");
                pstmt = conn.prepareStatement ( SQL_SELECT_STATUS_DESC );

                pstmt.setString ( 1, (String)listOfLoanNumber.elementAt(i) );
                rs = pstmt.executeQuery();
                if(rs.next()){
                OrderBean orbean = new OrderBean();
                orbean.setOrderNumber(rs.getString(1));
                orbean.setLoanNumber((String)listOfLoanNumber.elementAt(i));
                orbean.setOrderStatus(rs.getString(2));
                orbean.setComment(rs.getString(3));
                listOfStatus.add(orbean);
                } else {
                OrderBean orbean = new OrderBean();
                orbean.setLoanNumber((String)listOfLoanNumber.elementAt(i));
                orbean.setOrderStatus("");
                orbean.setComment("");
                listOfStatus.add(orbean);
                }
                rs.close();
                pstmt.close();
                }
                conn.close ( );
                }
                catch ( NamingException e )
                {
                throw new EJBException ( e.toString() );
                }
                catch ( SQLException e )
                {
                throw new SQLException ( e.toString() );
                }
                finally
                {
                try{
                if(pstmt != null) pstmt.close();
                if(rs != null) rs.close();
                if(conn != null) conn.close();
                } catch(SQLException e){
                log.error("SQL Exception in " + this.getClass().getName(), e);
                }

                }

                • 5. Re: please help me out!!! connection error
                  Joachim Van der Auwera Expert

                  Unfortunately, this code does not give me much clues about what is going wrong. However, the code can definately be improved a lot.

                  - The pstmt should be prepared outside the loop. The ide about prepared statements is to prepare them once, then reuse them. Your code is a prefect example, but doesn't use the trick. You should see a speed improvement because of this.

                  - you have a finally block which closes the pstmt and the connection. Good. But then you should not close them beforehand. You are goind to close them twice.

                  Next question is how you get your connection? You call a helper method, but that code is not shown.

                  Joachim