You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by PANTA-RHEI WOLF <PA...@DIN.DE> on 2004/08/26 10:21:47 UTC
[DBCP] using dbcp in struts throws sometimes a
NoSuchElementException / SQLNestedException
Hi there, i have a very strange behaviour using DBCP. I have a small set of JSPs talking to an oracledatabase. after a while
it looks if my application hang. 2 till 3 minutes later it seems to work fine again. When i have a look in my logfile, i can see
the following exception. I do not know what happen exactly, can anyone help me please?
Another question, i check always if the resultset/statement and connection is available and then i close the connection.
Is it correct closing it after use?
Below is the Exception, the datasource-config and a snippet from my application.
Thank you in advance
Mirko
The Exception is the following:
04/08/24 15:51:59 org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted 04/08/24 15:51:59
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:103) 04/08/24 15:51:59
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540) 04/08/24 15:51:59
at de.orb.quick.model.CommunicationLayer.executeStatement(CommunicationLayer.java:86) 04/08/24 15:51:59
at de.orb.quick.model.TreeDataBean.getResult(TreeDataBean.java:57) 04/08/24 15:51:59
....
at org.apache.commons.dbcp.AbandonedObjectPool.borrowObject(AbandonedObjectPool.java:74) 04/08/24 15:51:59
at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:95) 04/08/24 15:51:59 ... 62 more
The datasource Configuration in struts-config.xml:
<data-source type="org.apache.commons.dbcp.BasicDataSource">
<set-property property="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
<set-property property="url" value="jdbc:oracle:thin:@host:1521:SID" />
<set-property property="username" value="user" />
<set-property property="password" value="pass" />
<set-property property="initialSize" value="5" />
<set-property property="maxActive" value="40" />
<set-property property="maxIdle" value="5" />
<set-property property="testOnBorrow" value="true" />
<set-property property="maxWait" value="120000" />
<set-property property="defaultAutoCommit" value="false" />
<set-property property="validationQuery" value="select sysdate from dual" />
<set-property property="removeAbandoned" value="true" />
</data-source>
the application snippet:
// -----------------------------------------------------------------
public ArrayList getResult( ... ) {
ArrayList data = new ArrayList();
ResultSet rset = null;
int counter = 0;
try {
rset = executeStatement( "my sql statement" );
if(rset != null){
while( rset.next() ) {
.. do something with the result
}
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
try {
if (rset!= null && rset.getStatement() != null && rset.getStatement().getConnection() != null) { // Close resultset & statements
rset.getStatement().getConnection().close();
}
} catch (Exception ignored) { }
}
return data;
}
// -----------------------------------------------------------------
public ResultSet executeStatement( List parameter ) {
ResultSet rset = null;
CallableStatement cstmt = null;
try {
Connection connection = dataSource.getConnection();
String dbBuffer = new StringBuffer( parameter.get(0).toString() );
if (connection != null) {
cstmt = connection.prepareCall( dbBuffer );
rset = cstmt.executeQuery();
}
} catch (SQLException ex) {
ex.printStackTrace();
}
return rset;
}
Mit freundlichen Grüßen
Mirko Wolf
-----------------------------------------------------------------------------------------------------------------------------------------
panta rhei systems gmbh
budapester straße 31
10787 berlin
tel +49.30.26 01-14 17
fax +49.30.26 01-414 13
wolf@panta-rhei.de
www.panta-rhei.de
Diese Nachricht ist vertraulich und ausschliesslich für den Adressaten bestimmt. Jeder Gebrauch durch Dritte ist verboten. Falls Sie die Daten irrtuemlich erhalten haben, nehmen Sie bitte Kontakt mit dem Absender auf und loeschen Sie die Daten auf jedem Computer und Datentraeger. Der Absender ist nicht verantwortlich für die ordnungsgemaesse, vollstaendige oder verzoegerungsfreie Übertragung dieser Nachricht.
This message is confidential and intended solely for the use by the addressee. Any use of this message by a third party is prohibited. If you received this message in error, please contact the sender and delete the data from any computer and data carrier. The sender is neither liable for the proper and complete transmission of the information in the message nor for any delay in its receipt.
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org
Re: [DBCP] using dbcp in struts throws sometimes a NoSuchElementException
/ SQLNestedException
Posted by Jason Lea <ja...@kumachan.net.nz>.
First don't use removeAbandoned. It could cause random errors by
removing connections which are still in use. Better to test the system
to find leaks than have them cleaned up by removeAbandoned in a
production system. TestOnBorrow should be enough for the system to hand
out working connections to your application.
So do this
<set-property property="removeAbandoned" value="false" />
Next, for testing use maxActive of 1, so only 1 connection can be given
out. This should cause the system to fail quickly if you have forgotten
to close a connection somewhere. This should mean your system will
always fail at the same point, if there is a problem, instead of randomly.
Then rewrite your method as follows:
public ArrayList getResult( ... ) {
ArrayList data = new ArrayList();
int counter = 0;
Connection connection = null;
CallableStatement cstmt = null;
ResultSet rset = null;
try {
connection = dataSource.getConnection();
cstmt=conn.prepareCall( "my sql statement" );
rset = cstmt.executeQuery()
while( rset.next() ) {
.. do something with the result
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
close(conn,rset,ctmt);
}
return data;
}
//you could put this code somewhere handy where it can be called where you need it
//eg make it a static method somewhere so can use DbUtils.close(conn,rs,stmt) from anywhere
private void close(Connection conn, ResultSet rs, Statement stmt) {
try {
if (null != stmt)
stmt.close();
} catch (SQLException s) {
//ignore
}
try {
if (null != rs)
rs.close();
} catch (SQLException s) {
//ignore
}
try {
if (null != conn)
conn.close();
} catch (SQLException s) {
//ignore
}
}
The conn.close() does not close the connection, instead it passes it
back to the pool so it can be reused. I normally use call a method to
open my connection, and in that method put debugging information such as
when the conneciton is opened and what the current number of connections
in the pool is.
Once you have it working with maxActive set to 1, test it with maxActive
40 then deploy to the production system.
PANTA-RHEI WOLF wrote:
>Hi there, i have a very strange behaviour using DBCP. I have a small set of JSPs talking to an oracledatabase. after a while
>it looks if my application hang. 2 till 3 minutes later it seems to work fine again. When i have a look in my logfile, i can see
>the following exception. I do not know what happen exactly, can anyone help me please?
>
>Another question, i check always if the resultset/statement and connection is available and then i close the connection.
>Is it correct closing it after use?
>
>Below is the Exception, the datasource-config and a snippet from my application.
>
>Thank you in advance
>
>Mirko
>
>The Exception is the following:
>
>04/08/24 15:51:59 org.apache.commons.dbcp.SQLNestedException: Cannot get a connection, pool exhausted 04/08/24 15:51:59
> at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:103) 04/08/24 15:51:59
> at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540) 04/08/24 15:51:59
> at de.orb.quick.model.CommunicationLayer.executeStatement(CommunicationLayer.java:86) 04/08/24 15:51:59
> at de.orb.quick.model.TreeDataBean.getResult(TreeDataBean.java:57) 04/08/24 15:51:59
> ....
> at org.apache.commons.dbcp.AbandonedObjectPool.borrowObject(AbandonedObjectPool.java:74) 04/08/24 15:51:59
> at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:95) 04/08/24 15:51:59 ... 62 more
>
>The datasource Configuration in struts-config.xml:
>
> <data-source type="org.apache.commons.dbcp.BasicDataSource">
> <set-property property="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
> <set-property property="url" value="jdbc:oracle:thin:@host:1521:SID" />
> <set-property property="username" value="user" />
> <set-property property="password" value="pass" />
> <set-property property="initialSize" value="5" />
> <set-property property="maxActive" value="40" />
> <set-property property="maxIdle" value="5" />
> <set-property property="testOnBorrow" value="true" />
> <set-property property="maxWait" value="120000" />
> <set-property property="defaultAutoCommit" value="false" />
> <set-property property="validationQuery" value="select sysdate from dual" />
> <set-property property="removeAbandoned" value="true" />
> </data-source>
>
>
>the application snippet:
>// -----------------------------------------------------------------
> public ArrayList getResult( ... ) {
> ArrayList data = new ArrayList();
> ResultSet rset = null;
> int counter = 0;
>
> try {
> rset = executeStatement( "my sql statement" );
> if(rset != null){
> while( rset.next() ) {
>
> .. do something with the result
>
> }
> }
> } catch (Exception ex) {
> ex.printStackTrace();
> } finally {
> try {
> if (rset!= null && rset.getStatement() != null && rset.getStatement().getConnection() != null) { // Close resultset & statements
> rset.getStatement().getConnection().close();
> }
> } catch (Exception ignored) { }
> }
> return data;
> }
>
>// -----------------------------------------------------------------
> public ResultSet executeStatement( List parameter ) {
> ResultSet rset = null;
> CallableStatement cstmt = null;
> try {
>
> Connection connection = dataSource.getConnection();
> String dbBuffer = new StringBuffer( parameter.get(0).toString() );
>
> if (connection != null) {
> cstmt = connection.prepareCall( dbBuffer );
> rset = cstmt.executeQuery();
> }
> } catch (SQLException ex) {
> ex.printStackTrace();
> }
> return rset;
> }
>
>
>
>Mit freundlichen Grüßen
>
>Mirko Wolf
>
>-----------------------------------------------------------------------------------------------------------------------------------------
>panta rhei systems gmbh
>budapester straße 31
>10787 berlin
>tel +49.30.26 01-14 17
>fax +49.30.26 01-414 13
>wolf@panta-rhei.de
>www.panta-rhei.de
>
>Diese Nachricht ist vertraulich und ausschliesslich für den Adressaten bestimmt. Jeder Gebrauch durch Dritte ist verboten. Falls Sie die Daten irrtuemlich erhalten haben, nehmen Sie bitte Kontakt mit dem Absender auf und loeschen Sie die Daten auf jedem Computer und Datentraeger. Der Absender ist nicht verantwortlich für die ordnungsgemaesse, vollstaendige oder verzoegerungsfreie Übertragung dieser Nachricht.
>
>This message is confidential and intended solely for the use by the addressee. Any use of this message by a third party is prohibited. If you received this message in error, please contact the sender and delete the data from any computer and data carrier. The sender is neither liable for the proper and complete transmission of the information in the message nor for any delay in its receipt.
>
>
>---------------------------------------------------------------------
>To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
>For additional commands, e-mail: commons-user-help@jakarta.apache.org
>
>
>
>
--
Jason Lea
---------------------------------------------------------------------
To unsubscribe, e-mail: commons-user-unsubscribe@jakarta.apache.org
For additional commands, e-mail: commons-user-help@jakarta.apache.org