0 Replies Latest reply on Jul 6, 2005 2:29 PM by Shaji Kakkodi

    SQLException during stress test

    Shaji Kakkodi Newbie



      During stress tests with large number of concurrent users (50-100)

      I get SQLException below after user count reach a certain number. This happens intermittently.

      Could this be because connections could not be opened at the database end in quick intervals? or is there a configuration that need to looked at?


      2005-07-06 14:02:45,506 WARN [org.jboss.resource.connectionmanager.JBossManagedConnectionPool] Throwable while attempting to get a new connection: null
      org.jboss.resource.JBossResourceException: Could not create connection; - nested throwable: (java.sql.SQLException: Io exception: The Network Adapter could not establish the connection)


      The application is a simple struts application querying a table in oracle 9i database. Jboss 4.0.1 is running on Windows 2000

      Application code that make database query is listed below



      public List getAllCDES(String longName) throws Exception
      {
      Connection conn = null;
      Statement st =null;
      ResultSet rs = null;
      List list = new ArrayList();
      try
      {
      conn = SimpleDAOImpl.getConnectionFromContainer("java:comp/env/jdbc/CDEBrowserDS");
      //Statement st = conn.
      String sqlStmt = "SELECT * FROM data_elements_view "
      +"WHERE long_name like '"+longName+"'";

      st = conn.createStatement();
      rs = st.executeQuery(sqlStmt);

      while(rs.next())
      {
      DataElement de = new DataElement();
      de.setLongName(rs.getString("long_name"));
      de.setPublicId(rs.getString("cde_id"));
      list.add(de);
      }

      }

      catch (Exception e)
      {
      System.out.println("Exception while getting CDEs");
      throw e;
      }
      finally
      {
      if(st!=null)
      try
      {
      st.close();
      }
      catch (Exception ex)
      {
      System.out.println("Exception while Closing st");
      }
      if(rs!=null)
      try
      {
      rs.close();
      }
      catch (Exception exp)
      {
      System.out.println("Exception while Closing rs");
      }
      if(conn!=null)
      try
      {
      conn.close();
      System.out.println("Connection Closed");
      }
      catch (Exception ecexp)
      {
      System.out.println("Exception while Closing Connection");
      }
      }
      return list;

      }

      public static Connection getConnectionFromContainer(String dataSourceName)
      throws NamingException,Exception {

      Connection conn = null;
      try {
      InitialContext ic = new InitialContext();
      DataSource ds = (DataSource)ic.lookup(dataSourceName);
      conn = ds.getConnection();

      System.out.println("Connected to the database successfully using datasource "+dataSourceName);
      }
      catch (NamingException ne) {
      System.out.println("Exception occurred in getConnectionFromContainer for DataSource name="+dataSourceName);
      throw new NamingException
      ("Failed to lookup JDBC datasource. Check the datasource name");
      }
      catch (Exception e) {
      System.out.println("Exception occurred in getConnectionFromContainer");
      throw new Exception("Exception in getConnectionFromContainer() ");
      }
      return conn;
      }

      Below is the oracle-ds.xml entry


      <local-tx-datasource>
      <jndi-name>jdbc/CDEBrowserDS</jndi-name>
      <connection-url>jdbc:oracle:thin:@myhost:1521:dev</connection-url>
      <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
      <user-name>user</user-name>
      mypassword
      <exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
      <blocking-timeout-millis>0</blocking-timeout-millis>
      <idle-timeout-minutes>1</idle-timeout-minutes>
      <max-pool-size>100</max-pool-size>
      <min-pool-size>0</min-pool-size>
      </local-tx-datasource>

      Thanks
      -Shaji