org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool exhausted

[Nagendra Batchu]

Hi,

I am using connection pooling in tomcat 5.5 the following changes i had in the respective files.

In Server.xml

<Context path="/SMS" docBase="SMS" debug="0"
reloadable="false" override="true">
<Resource name="jdbc/DefaultDS" auth="Container"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/DefaultDS">
<parameter><name>user</name><value>cdmsprod</value></parameter>
<parameter><name>password</name><value>cdmsprod</value></parameter>
<parameter><name>driverClassName</name>
<value>oracle.jdbc.driver.OracleDriver</value></parameter>
<parameter>
<name>driverName</name>
<value>jdbcracle:thin:@172.18.109.126:1521:DMSDB</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>8</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>4</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>6</value>
</parameter>
</ResourceParams>
</Context>

3. Add the follwing tag in the web.xml file:

<resource-ref>
<res-ref-name>jdbc/DefaultDS</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>

4. Add following code in the JSP file:

InitialContext jndiCntx = new InitialContext();
Context ctx=(Context)jndiCntx.lookup("java:comp/env");
System.out.println("Looking up jdbc/DefaultDS");
ds = (javax.sql.DataSource)ctx.lookup("jdbc/DefaultDS");
System.out.println("Found");


5.In META_INF/context file add the following contants

<?xml version='1.0' encoding='utf-8'?>
<Context path="/SMS" debug="1" reloadable="true"
docBase='${catalina.home}/server/webapps/SMS'>
<Resource name="jdbc/DefaultDS" auth="Container"
type="javax.sql.DataSource" username="cdmsdvlp" password="cdmsdvlp"
driverClassName="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@172.18.109.126:1521:DMSDB"
maxActive="8" maxIdle="4" maxWait="4"/>

</Context>

It is working fine if we send one request at a time , if the number of users exceeds 5 then the following exception is coming

org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot get a connection, pool exhausted
org.apache.tomcat.dbcp.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:103)
org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:540)
org.apache.jsp.recieveSMS_jsp._jspService(recieveSMS_jsp.java:92)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:334)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:314)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:264)
javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

Please suggest