Thanks Steve for quick reply. I will try as you mentioned. The releaseConnection() method basically what it does is, it will close the connection.
if (connection_ != null) connection_.close(); Kumar -----Original Message----- From: Steve Kirk [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 26, 2005 12:09 PM To: 'Tomcat Users List' Subject: RE: Error: Cannot get a connection, pool exhausted IMHO I would say that you just need to rearrange your code a little - I would move some code that you have in the try{} block to a finally{} block, so that it gets executed whether or not there is an error. At the moment, the section > rs.close(); > > pstmt.close(); > > // can relese connection now > releaseConnection(); only executes if the main try{} block executed OK. Because there is no finally{} block to match the main try{}...catch{} construct, the rs and pstmt objects do not get cleaned up. I include a suggested alternative version below. I haven't compiled and run it but you'll see what I've rearranged. Your idea of nesting try/catch/finally inside the catch block, and testing for null before calling close(), is a sound one. I've shown below a slightly more long-winded version of that below. The idea of it is that if cleanup of any one resource object fails when the cleanup method (close or releaseConnection) is called, the code still continues, and attempts to clean up the other resources, to make sure that as far as possible, no resources are ever left hanging. Using 4 separate t/c/f constructs like this is a but repetitive, but if you only have one t/c/f wrapped around the 4 cleanup calls, an exception from the first, second or third call will prevent the calls that follow it from running. Some might argue that this is overkill. All I can say is that I always code like this and it works :) I have left in the call to releaseConnection() in the main finally{} block, although I'm not sure what that does. I'll leave that to you to decide. public java.util.Vector getYears(String teamId) throws DAOException { java.sql.ResultSet rs = null; java.sql.Connection conn = null; java.sql.PreparedStatement pstmt = null; try { // code in this block will complete as // long as there are no errors // therefore the only code to go here // is that which *ONLY* needs to run when all goes OK // vector to hold result data set java.util.Vector yearList = new java.util.Vector(); // get connection conn = getConnection(); pstmt = conn.prepareStatement(yearQueryCommand()); pstmt.setString(1, teamId); // execute query rs = pstmt.executeQuery(); // There can be multiple records. while (rs.next()) { yearList.addElement(rs.getString(1)); } return yearList; } catch(Exception e) { // code in this block only executes if there was a problem // in the main try block throw new DAOException("getYears(): Failed to access report year ! Error: " + e.getMessage(), e); } finally { // code in this block always executes, // whether or not there was an error try { releaseConnection(); } catch(Exception finEx1) {/*ignore*/} if (null != rs) { try { rs.close(); } catch(Exception finEx2) {/*ignore*/} } rs = null; if (null != pstmt) { try { pstmt.close(); } catch (Exception finEx3) {/*ignore*/} } pstmt = null; if (null != conn) { try { conn.close(); } catch (Exception finEx4) {/*ignore*/} } conn = null; } } > -----Original Message----- > From: KUMAR, NANDA [AG-Contractor/1000] > [mailto:[EMAIL PROTECTED] > Sent: Wednesday 26 October 2005 16:56 > To: 'Tomcat Users List' > Subject: RE: Error: Cannot get a connection, pool exhausted > > > Thanks Steve.. I am confident that, we don't have 100 > concurrent conns. Our > DAO handles the connection, here is the DAO example, please > let me know what > we are doing wrong here? > > public java.util.Vector getYears(String teamId) throws DAOException{ > > java.sql.ResultSet rs = null; > java.sql.Connection conn = null; > java.sql.PreparedStatement pstmt = null; > > try > { > // vector to hold result data set > java.util.Vector yearList = new java.util.Vector(); > > // get connection > conn = getConnection(); > > pstmt = conn.prepareStatement(yearQueryCommand()); > pstmt.setString(1, teamId); > > // execute query > rs = pstmt.executeQuery(); > > // There can be multiple records. > while (rs.next()) > { > yearList.addElement(rs.getString(1)); > > } > > rs.close(); > > pstmt.close(); > > // can relese connection now > releaseConnection(); > > return yearList; > } > catch(Exception e) > { > finally > { > try > { > if(rs != null) > rs.close(); > if (pstmt != null) > pstmt.close(); > if (conn != null) > conn.close(); > } > catch(SQLException se) > { > /** Ignore */ > } > } > > throw new DAOException("getYears(): Failed to > access report > year ! Error: " + e.getMessage()); > } > > } > > > Thanks > Kumar > > > -----Original Message----- > From: Steve Kirk [mailto:[EMAIL PROTECTED] > Sent: Wednesday, October 26, 2005 10:35 AM > To: 'Tomcat Users List' > Subject: RE: Error: Cannot get a connection, pool exhausted > > > > how many concurrent requests are you handling? you have configured > > <name>maxActive</name> > > <value>100</value> > so as soon as 100 concurrent conns are reached, you will > exhaust the pool. > > If you do not have as many as 100 concurrent conns, then it > is likely that > your servlet calls are not returning conns to the pool after they have > finished using them. Make sure that your servlets' doPost() > and doGet() > methods include a call to the Connection#close() method > before they return, > whether or not an error occurred. This returns connections > to the pool, for > use by other threads using the pool. The best place to call > the close() > method is in a finally() block. > > > -----Original Message----- > > From: KUMAR, NANDA [AG-Contractor/1000] > > [mailto:[EMAIL PROTECTED] > > Sent: Wednesday 26 October 2005 16:13 > > To: users@tomcat.apache.org > > Subject: Error: Cannot get a connection, pool exhausted > > > > > > Frequently we were getting "Cannot get a connection, pool > > exhausted" error > > on production. I know in our application, we are closing the > > connection > > after each connection. Here is our context.xml parameters and > > values. We set > > maxActive connection to 100. Please let me know what we are > > doing wrong or > > if you need more information. > > > > <?xml version='1.0' encoding='utf-8'?> > > <Context displayName="TestWeb" docBase="test" path="/test" > > workDir="work\Catalina\localhost\test"> > > <Resource name="jdbc/TestDataSource" type="javax.sql.DataSource"/> > > <ResourceParams name="jdbc/TestDataSource"> > > <parameter> > > <name>url</name> > > <value>jdbc:oracle:thin:@test.com:1521:test</value> > > </parameter> > > <parameter> > > <name>password</name> > > <value>test123</value> > > </parameter> > > <parameter> > > <name>maxActive</name> > > <value>100</value> > > </parameter> > > <parameter> > > <name>maxWait</name> > > <value>10000</value> > > </parameter> > > <parameter> > > <name>driverClassName</name> > > <value>oracle.jdbc.driver.OracleDriver</value> > > </parameter> > > <parameter> > > <name>username</name> > > <value>test</value> > > </parameter> > > <parameter> > > <name>maxIdle</name> > > <value>30</value> > > </parameter> > > </ResourceParams> > > </Context> > > > > Thanks > > Kumar --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] --------------------------------------------------------------------------------------------------------- This e-mail message may contain privileged and/or confidential information, and is intended to be received only by persons entitled to receive such information. If you have received this e-mail in error, please notify the sender immediately. Please delete it and all attachments from any servers, hard drives or any other media. Other use of this e-mail by you is strictly prohibited. All e-mails and attachments sent and received are subject to monitoring, reading and archival by Monsanto. The recipient of this e-mail is solely responsible for checking for the presence of "Viruses" or other "Malware". Monsanto accepts no liability for any damage caused by any such code transmitted by or accompanying this e-mail or any attachment. --------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
