Re: [DBCP] Anther lame pool exhausted exception question
Hi Brian, Examples can be found here: http://svn.apache.org/repos/asf/jakarta/commons/proper/dbcp/trunk/doc/ If you are creating a BasicDataSource yourself then you need to keep it in a static variable somewhere. You create it once and only close the datasource when your application is shutting down. The BasicDataSource contains the pool and you only want one of these around for each database(scheme) you want to connect to. Your program then uses ds.getConnection() / conn.close() to get/return connections from/to the pool. Use the pattern Craig gave you to make sure you always close the rs/stmt/conn. So key point here is to only create one datasource (and store it in a static somewhere) and not to close the datasource after each call. Cheers Dirk Brian Cook wrote: I have several apps written that use org.apache.commons.dbcp. And I am getting the org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted exception. When I run netstat sure enough it shows about 60 - 100 ESTABLISHED connections open at any given time. Looking deeper it looks like new connections are created most but not each time the Java Bean the uses the data base is called. So it seems pretty clear that I am failing to close all of the connections in my app. My problem is that I can not find where that is. My understanding is that as long as I close every ResultSet, and each Connection object I should have my bases covered. I have gone back though the app repeatedly and verified that all the ResultSets, Connections, and BasicDataSource objects are all being closed. Due to an internal political problem I am not using JNDI for the connections. Instead I have a class file that is called when a connection is need. After the object is used a close() method is called and closes the Statement, Connection, ResultSet, BasicDataSource objects. So my questions are .. 1. Is there something else I should be doing to return the connections to the pool? 2. Does not using JNDI with org.apache.commons.dbcp cause problems with closing the connection pool some how? I have been though about 6 months of the archives for this list and did not find any hints, I went to look at the examples lnik at on the Commons.DBCP page but the link to http://cvs.apache.org/downviewcvs.html/jakarta-commons/dbcp/doc/ seems to be broken. Googling has only just returned the usall advice of make sure you are closing every connection. I am open to any ideas or thoughts to what I am failing to due here. Details and the code are included below. OS: Linux Red Hat 9.0 Container: Tomcat 4.127 DBCP:commons-dbcp-1.2.1.jar, commons-pool-1.2.jar JDK: 1.5 Database:MySQL 4.1 JDBC Driver: mysql-connector-java-3.1.7-bin.jar public class DBConector { Omited Code/ public DBConector(String dataBaseName) { try{ String DataBaseURL = jdbc:mysql://+DataBaseHost+/+dataBaseName+ ?user=+DataBaseUser+password=+DataBasePassword; mysqlCon = getPooledConection(DataBaseURL); sqlStatement = mysqlCon.createStatement(); } catch(Exception s) { System.err.println(new java.util.Date()+ Error throw by + DBConector()\n+s); } } Omited Code/ public ResultSet read(String SQL) { try{ rs = sqlStatement.executeQuery( SQL ); } // Close try catch(Exception s) { System.err.println(new java.util.Date()+ Error throw by an SQL + call in DBConector.read() class : \n+s+\n+s.getMessage()+ \nSQL Queary : +SQL+\n); } // Close catch return(DataFromDB); } // Close the read method Omited Code/ protected static Connection getPooledConection(String connectURI) { ds = new BasicDataSource(); ds.setDriverClassName(DBDriver); ds.setUsername(DataBaseUser); ds.setPassword(DataBasePassword); ds.setUrl(connectURI); ds.setInitialSize(3); ds.setMaxActive(32); ds.setMaxIdle(8); ds.setMinIdle(3); Connection con = null; try { con = ds.getConnection(); } catch(Exception a){System.out.println(new java.util.Date() + PrintTimeDataBase6.getPooledConection() has thrown an exception.\n+a+ \n+ a.getMessage() ); } return con; } // Close method Omited Code/ public void close() { try { sqlStatement.close(); } catch(Exception a) { } try { mysqlCon.close(); } catch(Exception a) { } try { rs.close(); } catch(Exception a) { } try { ds.close(); } catch(Exception a) { } }// Close method } // Close the PrintTimeWebServices Class
Re: [DBCP] Anther lame pool exhausted exception question
Ding Ding Ding. We have a winner. That is exactly what I am doing wrong. A new BasicDataSource is created every time a Java Bean is called. I guess that also explains why I am seeing 60 - 100 active connections in netstat when the maxActive value was set to 32. I will look into using JNDI and or have a servlet that runs continually in the background. Thanks to everyone who responded the ideas provided are great. You guys rock! Dirk Verbeeck wrote: Hi Brian, Examples can be found here: http://svn.apache.org/repos/asf/jakarta/commons/proper/dbcp/trunk/doc/ If you are creating a BasicDataSource yourself then you need to keep it in a static variable somewhere. You create it once and only close the datasource when your application is shutting down. The BasicDataSource contains the pool and you only want one of these around for each database(scheme) you want to connect to. Your program then uses ds.getConnection() / conn.close() to get/return connections from/to the pool. Use the pattern Craig gave you to make sure you always close the rs/stmt/conn. So key point here is to only create one datasource (and store it in a static somewhere) and not to close the datasource after each call. Cheers Dirk Brian Cook wrote: I have several apps written that use org.apache.commons.dbcp. And I am getting the org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted exception. When I run netstat sure enough it shows about 60 - 100 ESTABLISHED connections open at any given time. Looking deeper it looks like new connections are created most but not each time the Java Bean the uses the data base is called. So it seems pretty clear that I am failing to close all of the connections in my app. My problem is that I can not find where that is. My understanding is that as long as I close every ResultSet, and each Connection object I should have my bases covered. I have gone back though the app repeatedly and verified that all the ResultSets, Connections, and BasicDataSource objects are all being closed. Due to an internal political problem I am not using JNDI for the connections. Instead I have a class file that is called when a connection is need. After the object is used a close() method is called and closes the Statement, Connection, ResultSet, BasicDataSource objects. So my questions are .. 1. Is there something else I should be doing to return the connections to the pool? 2. Does not using JNDI with org.apache.commons.dbcp cause problems with closing the connection pool some how? I have been though about 6 months of the archives for this list and did not find any hints, I went to look at the examples lnik at on the Commons.DBCP page but the link to http://cvs.apache.org/downviewcvs.html/jakarta-commons/dbcp/doc/ seems to be broken. Googling has only just returned the usall advice of make sure you are closing every connection. I am open to any ideas or thoughts to what I am failing to due here. Details and the code are included below. OS: Linux Red Hat 9.0 Container: Tomcat 4.127 DBCP:commons-dbcp-1.2.1.jar, commons-pool-1.2.jar JDK: 1.5 Database:MySQL 4.1 JDBC Driver: mysql-connector-java-3.1.7-bin.jar public class DBConector { Omited Code/ public DBConector(String dataBaseName) { try{ String DataBaseURL = jdbc:mysql://+DataBaseHost+/+dataBaseName+ ?user=+DataBaseUser+password=+DataBasePassword; mysqlCon = getPooledConection(DataBaseURL); sqlStatement = mysqlCon.createStatement(); } catch(Exception s) { System.err.println(new java.util.Date()+ Error throw by + DBConector()\n+s); } } Omited Code/ public ResultSet read(String SQL) { try{ rs = sqlStatement.executeQuery( SQL ); } // Close try catch(Exception s) { System.err.println(new java.util.Date()+ Error throw by an SQL + call in DBConector.read() class : \n+s+\n+s.getMessage()+ \nSQL Queary : +SQL+\n); } // Close catch return(DataFromDB); } // Close the read method Omited Code/ protected static Connection getPooledConection(String connectURI) { ds = new BasicDataSource(); ds.setDriverClassName(DBDriver); ds.setUsername(DataBaseUser); ds.setPassword(DataBasePassword); ds.setUrl(connectURI); ds.setInitialSize(3); ds.setMaxActive(32); ds.setMaxIdle(8); ds.setMinIdle(3); Connection con = null; try { con = ds.getConnection(); } catch(Exception a){System.out.println(new java.util.Date() + PrintTimeDataBase6.getPooledConection() has thrown an exception.\n+a+ \n+ a.getMessage() ); } return con; }
Re: [DBCP] Anther lame pool exhausted exception question
The key to returning connections to the connection pool is to call close() on the *connection* itself. The most common way people get themselves in trouble is to skip this somehow (perhaps because an exception is thrown). To avoid that sort of problem, I tend to use an idiom like this for JDBC access code: DataSource ds = ...; // Acquire a reference from JNDI or wherever Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { conn = ds.getConnection(); // Or whatever stmt = conn.prepareStatement(...); rs = stmt.executeQuery(); ... process the results ... } catch (SQLException e) { ... process the exception ... } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { ... record or ignore ... } rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { ... record or ignore ... } rs = null; } if (conn != null) { try { conn.close(); } catch (SQLException e) { ... record or ignore ... } conn = null; } } Courtesy of the finally logic, I'm guaranteed to always execute my cleanup code, no matter where an exception might get thrown. You'll note that it can be pretty tedious to do all this stuff with raw JDBC. You might also want to look at using one of the SQL frameworks that encapsulate this sort of stuff, but still give you the guarantee that the connection will ultimately be returned to the pool. Craig On 7/14/05, Brian Cook [EMAIL PROTECTED] wrote: I have several apps written that use org.apache.commons.dbcp. And I am getting the org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted exception. When I run netstat sure enough it shows about 60 - 100 ESTABLISHED connections open at any given time. Looking deeper it looks like new connections are created most but not each time the Java Bean the uses the data base is called. So it seems pretty clear that I am failing to close all of the connections in my app. My problem is that I can not find where that is. My understanding is that as long as I close every ResultSet, and each Connection object I should have my bases covered. I have gone back though the app repeatedly and verified that all the ResultSets, Connections, and BasicDataSource objects are all being closed. Due to an internal political problem I am not using JNDI for the connections. Instead I have a class file that is called when a connection is need. After the object is used a close() method is called and closes the Statement, Connection, ResultSet, BasicDataSource objects. So my questions are .. 1. Is there something else I should be doing to return the connections to the pool? 2. Does not using JNDI with org.apache.commons.dbcp cause problems with closing the connection pool some how? I have been though about 6 months of the archives for this list and did not find any hints, I went to look at the examples lnik at on the Commons.DBCP page but the link to http://cvs.apache.org/downviewcvs.html/jakarta-commons/dbcp/doc/ seems to be broken. Googling has only just returned the usall advice of make sure you are closing every connection. I am open to any ideas or thoughts to what I am failing to due here. Details and the code are included below. OS: Linux Red Hat 9.0 Container: Tomcat 4.127 DBCP:commons-dbcp-1.2.1.jar, commons-pool-1.2.jar JDK: 1.5 Database:MySQL 4.1 JDBC Driver: mysql-connector-java-3.1.7-bin.jar public class DBConector { Omited Code/ public DBConector(String dataBaseName) { try{ String DataBaseURL = jdbc:mysql://+DataBaseHost+/+dataBaseName+ ?user=+DataBaseUser+password=+DataBasePassword; mysqlCon = getPooledConection(DataBaseURL); sqlStatement = mysqlCon.createStatement(); } catch(Exception s) { System.err.println(new java.util.Date()+ Error throw by + DBConector()\n+s); } } Omited Code/ public ResultSet read(String SQL) { try{ rs = sqlStatement.executeQuery( SQL ); } // Close try catch(Exception s) { System.err.println(new java.util.Date()+ Error throw by an SQL + call in DBConector.read() class : \n+s+\n+s.getMessage()+ \nSQL Queary : +SQL+\n); } // Close catch return(DataFromDB); } // Close the read method Omited Code/ protected static Connection
RE: [DBCP] Anther lame pool exhausted exception question
Hello. Maybe you can use DbUtils class from dbutils project to code less in the finally clause; methods closeQuietly and close makes this tedious stuff easier. DataSource ds = ...; // Acquire a reference from JNDI or wherever Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { conn = ds.getConnection(); // Or whatever stmt = conn.prepareStatement(...); rs = stmt.executeQuery(); ... process the results ... } catch (SQLException e) { ... process the exception ... } finally { DbUtils.closeQuietly(rs); DbUtils.closeQuietly(stmt); DbUtils.closeQuietly(conn); } Even further, you can avoid ResultSet and Statement closing, using methods from QueryRunner to execute or update a database. Look into documentation, it's pretty useful. I hope this help. Regards, Alfredo Ledezma Meléndez. Gerencia de Sistemas CRM Consultor Externo de Sistemas de Atención a Clientes RadioMovil DIPSA, S. A. de C. V. Ejército Nacional No. 488, Col. Anahuac, C.P. 11570 México D.F. -Original Message- From: Craig McClanahan [mailto:[EMAIL PROTECTED] Sent: Thursday, July 14, 2005 5:10 PM To: Jakarta Commons Users List; [EMAIL PROTECTED] Subject: Re: [DBCP] Anther lame pool exhausted exception question The key to returning connections to the connection pool is to call close() on the *connection* itself. The most common way people get themselves in trouble is to skip this somehow (perhaps because an exception is thrown). To avoid that sort of problem, I tend to use an idiom like this for JDBC access code: DataSource ds = ...; // Acquire a reference from JNDI or wherever Connection conn = null; PreparedStatement stmt = null; ResultSet rs = null; try { conn = ds.getConnection(); // Or whatever stmt = conn.prepareStatement(...); rs = stmt.executeQuery(); ... process the results ... } catch (SQLException e) { ... process the exception ... } finally { if (rs != null) { try { rs.close(); } catch (SQLException e) { ... record or ignore ... } rs = null; } if (stmt != null) { try { stmt.close(); } catch (SQLException e) { ... record or ignore ... } rs = null; } if (conn != null) { try { conn.close(); } catch (SQLException e) { ... record or ignore ... } conn = null; } } Courtesy of the finally logic, I'm guaranteed to always execute my cleanup code, no matter where an exception might get thrown. You'll note that it can be pretty tedious to do all this stuff with raw JDBC. You might also want to look at using one of the SQL frameworks that encapsulate this sort of stuff, but still give you the guarantee that the connection will ultimately be returned to the pool. Craig On 7/14/05, Brian Cook [EMAIL PROTECTED] wrote: I have several apps written that use org.apache.commons.dbcp. And I am getting the org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted exception. When I run netstat sure enough it shows about 60 - 100 ESTABLISHED connections open at any given time. Looking deeper it looks like new connections are created most but not each time the Java Bean the uses the data base is called. So it seems pretty clear that I am failing to close all of the connections in my app. My problem is that I can not find where that is. My understanding is that as long as I close every ResultSet, and each Connection object I should have my bases covered. I have gone back though the app repeatedly and verified that all the ResultSets, Connections, and BasicDataSource objects are all being closed. Due to an internal political problem I am not using JNDI for the connections. Instead I have a class file that is called when a connection is need. After the object is used a close() method is called and closes the Statement, Connection, ResultSet, BasicDataSource objects. So my questions are .. 1. Is there something else I should be doing to return the connections to the pool? 2. Does not using JNDI with org.apache.commons.dbcp cause problems with closing the connection pool some how? I have been though about 6 months of the archives for this list and did not find any hints, I went to look at the examples lnik at on the Commons.DBCP page but the link to http://cvs.apache.org/downviewcvs.html/jakarta-commons/dbcp/doc/ seems to be broken. Googling has only just returned the usall advice of make sure you are closing every connection. I am open to any ideas or thoughts to what I am failing to due here. Details and the code are included below. OS