6 Replies Latest reply on Feb 23, 2007 7:03 AM by adrian.brock

    idle time out question

    arparikh

      If the quey is taking longer than <idle-timeout-minutes> configuration, Will Jboss Connection pool make that connection idle (meaning close the connection) ?

      E.g.

      conn.executeQuery is taking 15 minutes and <idle-timeout-minutes> is 10 mins, Will conn.close() throw null pointer exception because connection was closed by idle timeout setting.

        • 1. Re: idle time out question
          weston.price

          No.

          The idle-timeout is used to evaluate connections in the pool, not connections in use. The element

          <query-timeout>integer value of seconds</query-timeout>
          


          allows you to set a timeout on the query itself.




          • 2. Re: idle time out question
            arparikh

            Thats what i thought

            We had issue where conn.close was throwing null pointer exception. I know getConnection (our method which gets Connection from datasource) is returning the Connection object because before it returns connection object, it does conn.setAutoCommit(true).

            The caller method of getConnection uses the connection where it does conn.createStatement and stmt.executeQuery. In finally block we have conn.close().

            The conn.close was throwing null pointer exception. This was not making sense because we were getting valid connection object since conn.setAutoCommit and conn.createStatement was not throwing null pointer which were done before conn.close.
            This started happening when query was taking long time to execute and got fixed when query started working normally.

            Any insights.

            • 3. Re: idle time out question
              jaikiran

              Post the code where you are doing this connection handling.

              • 4. Re: idle time out question
                arparikh

                Here is the code snippets.


                Below is the getConnection which returns Connection object

                public void init(String resourceId, String resourceName, boolean defaultResource) throws DBException {
                try {
                Context initCtx = new InitialContext();
                Context envCtx = (Context) initCtx.lookup("java:comp/env");
                this.m_sources.put(resourceId, envCtx.lookup(resourceName));
                if (defaultResource) {
                this.m_defaultResourceId = resourceId;
                }
                }
                catch (NamingException ne) {
                throw new DBException(ne);
                }
                }

                public Connection getConn(String resourceId) throws DBException {
                if (resourceId == null) {
                resourceId = this.m_defaultResourceId;
                }

                try {
                DataSource dataSource = (DataSource) this.m_sources.get (resourceId);
                if (dataSource == null) {
                m_logger.error("###### FATAL ERROR -- DATA SOURCE NULL #######");
                }
                Connection conn = dataSource.getConnection();
                if (conn == null) {
                // try again????
                conn = dataSource.getConnection();
                if (conn == null) {
                m_logger.error("###### FATAL ERROR -- CONNECTION NULL #######");
                }
                }
                conn.setAutoCommit(true);
                return conn;
                }
                catch (SQLException se) {
                throw new DBException(se);
                }
                }

                Below is the code where we call getConnection to the connection object. We are getting NullPointer where we do conn.close(). The thing that I am wondering is if we are getting null conn, we should get null pointer at conn.setAutoCommit in getConnection or conn.createStatement. But instead we are getting on conn.close



                public void doSelect(String p_sql, int[] p_dataTypes) throws DBException
                {
                Connection conn = null;
                Statement sqlStmt = null;
                ResultSet resultSet = null;
                RecSet recset = new RecSet(this.m_resourceId);

                try
                {
                conn = DBConnMgr.getInstance().getConn(this.m_resourceId);
                // The above code getConn which is pasted above
                if (conn != null) {
                m_logger.info("@@@@ We got the connection object");
                } else {
                m_logger.info("######## DBSelect.doSelect CONNECTION IS NULL!!! ########");
                }
                sqlStmt = conn.createStatement();

                /*
                if (p_dataTypes != null)
                {
                for (int i = 0; i < p_dataTypes.length; i++)
                {
                //sqlStmt.defineColumnType(i+1, p_dataTypes);
                }
                }
                */

                resultSet = sqlStmt.executeQuery(p_sql);

                //operate on resultSet here
                }
                catch (SQLException se)
                {
                if (DBConnMgr.getInstance().isFatalError(se))
                {
                throw new DBException("Exception: " + se + ": " + se.getErrorCode() +
                "\n" + "Database is down " + "\n", se.getErrorCode());
                }
                else
                {
                throw new DBException("Exception: " + se + ": " + se.getErrorCode() +
                "\n" + "Could not perform " + p_sql + "\n");
                }
                }
                finally
                {
                try
                {
                if (resultSet != null)
                {
                resultSet.close();
                }
                if (sqlStmt != null)
                {
                sqlStmt.close();
                }
                }
                catch (SQLException se)
                {
                throw new DBException("Exception: " + se + "\n" +
                "Could not close sql statement" + "\n");
                }
                finally
                {
                try
                {
                conn.close();
                }
                catch (SQLException se)
                {
                m_logger.info("Failed to close DB connection");
                }
                }
                }

                m_logger.debug("Successful " + p_sql + "\n");


                }

                Thanks for the assistance

                • 5. Re: idle time out question

                   

                  "arparikh" wrote:

                  Below is the code where we call getConnection to the connection object. We are getting NullPointer where we do conn.close(). The thing that I am wondering is if we are getting null conn, we should get null pointer at conn.setAutoCommit in getConnection or conn.createStatement. But instead we are getting on conn.close

                  public void doSelect(String p_sql, int[] p_dataTypes) throws DBException
                  {
                  Connection conn = null;
                  try
                  {
                  conn = DBConnMgr.getInstance().getConn(this.m_resourceId);
                  }
                  finally
                  {
                  try
                  {
                  }
                  finally
                  {
                  try
                  {
                  conn.close();
                  }
                  catch (SQLException se)
                  {
                  m_logger.info("Failed to close DB connection");
                  }
                  }
                  }
                  }

                  Thanks for the assistance


                  This is a basic java question. What do you think happens
                  when getConn() throws an exception? Do you think it still invokes
                  the finally block(s)?

                  Here are the correct patterns:

                  1) Allocate before entering try/finally
                  Connection c = allocateConnection();
                  try
                  {
                  }
                  finally
                  {
                   // c cannot be null
                   c.close();
                  }
                  


                  2) Allocate inside the try block (because you want only one catch block)
                  Therefore you must check in the finally whether you actually got a connection
                  Connection c = null;
                  try
                  {
                   c = allocateConnection();
                  }
                  catch (SQLException e)
                  {
                  }
                  finally
                  {
                   // c could be null if allocateConnection() threw an exception
                   if (c != null)
                   c.close();
                  }
                  



                  • 6. Re: idle time out question

                    Incidently, if the code were:

                    Connection c; // no initalizer
                    try
                    {
                     c = allocateConnection();
                    }
                    catch (SQLException e)
                    {
                    }
                    finally
                    {
                     c.close();
                    }
                    


                    Most good compilers will fail to compile it, because it will tell you that
                    c could be uninitalized in the finally block usage.