This is what my final working server.xml file looks like
<Resource name="SQLServerDS" scope="Shareable" type="javax.sql.DataSource"/>
<ResourceParams name="SQLServerDS">
<parameter>
<name>url</name>
<value>jdbc:microsoft:sqlserver://dnas07:1113;DatabaseName=NorthWind</value>
</parameter>
<parameter>
<name>validationQuery</name>
<value></value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>3</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>5</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>6000</value>
</parameter>
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>user</name>
<value>sa</value>
</parameter>
<parameter>
<name>removeAbandonedTimeout</name>
<value>5</value>
</parameter>
<parameter>
<name>password</name>
<value>sa</value>
</parameter>
</ResourceParams>
And this was my test program
try {
// assumes jndi.properties has been configured appropriately
Context initCtx = new InitialContext();
Context envCtx = (Context) initCtx.lookup("java:comp/env");
// Get a Connection
DataSource ds = (DataSource) envCtx.lookup(jndiName);
Connection con = null;
Statement st = null;
ResultSet res = null;
ResultSetMetaData meta = null;
try
{
con = ds.getConnection();
st = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
res = st.executeQuery(sql);
meta = res.getMetaData();
int cols = meta.getColumnCount();
// since this is a scrollable ResultSet,
// do something a little strange...
while (!res.isLast()) {
res.next();
for (int i=1; i<=cols; i++) {
Object val = res.getObject(i);
System.out.print("\t" + meta.getColumnLabel(i) + ": ");
System.out.print(val == null ? " " : val.toString());
}
System.out.print("\n");
}
}
catch (SQLException sqle)
{
sqle.printStackTrace();
}
finally
{
// PoolMan closes ResultSets and Statements whenever
// Connections are closed, no need for it here
if (null != res)
{
try
{
res.close();
}
catch(SQLException e)
{
}
}
if (null != st)
{
try
{
st.close();
}
catch(SQLException e)
{
}
}
if (con != null)
{
try
{
con.close();
}
catch (SQLException sqle2)
{
}
}
}
} catch (javax.naming.NameNotFoundException nne) {
System.out.println("ERROR: No DataSource is registered under the name "
+
jndiName + ", please check your poolman.props " +
" and deployment tool.\n");
return nne.getMessage();
} catch (javax.naming.NamingException nex) {
System.out.println("ERROR: JNDI Exception Occurred. Is your JNDI
resource available?\n");
nex.printStackTrace();
return nex.getMessage();
} catch (java.security.AccessControlException ae) {
System.out.println("ERROR: You cannot execute the DataSource example
with " +
"the security permissions you are using. Try using the
" +
"poolman.policy file: " +
"java -Djava.security.policy=../lib/poolman.policy
PoolManSample.\n");
return ae.getMessage();
}
I was facing the problem, when i was not explicitly closing the resultset
and statement. but if i do, then the connections are reused and the pool
grows correctly. But the removeabandoned definitly does not work with Tomcat
4.1.2. I am using this in a webservice and Tomcat 4.1.2 came packaged with
the jwsdk from sun. if i find tomcat bugy, i might downgrade.
Amitabh
-----Original Message-----
From: Glenn Nielsen [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, September 24, 2002 11:04 PM
To: Tomcat Users List
Subject: Re: DBCP pool always increasing
In the config you posted you didn't have RemoveAbandoned configured, by
default is is set to false. You also didn't set the maxActive, by default
I believe it is unlimited. An example of your code which uses the
JNDI named datasource would also be helpfull in debugging these type
of problems.
Regards,
Glenn
Amitabh Dubey wrote:
> I used performance monitor to view the number of user connections to the
> database. Although i was closing the connection in my client code, the
pool
> size / connections to the database were always increasing. Given that i
was
> executing only one program at a time, i would expect that this number not
go
> on increasing.
>
> So i closed not only the connections, but resultset and statements as
well.
> This solved my problem. However, the tomcat dbcp documentation suggests
that
> we have a removeAbandoned and the timeout for this property also set. My
> remove abandones was set to true and the timeout value was 5 secs. But
these
> values seem to be ignored. So the only sure way out is to close everything
> explictly.
>
> Amitabh
>
> -----Original Message-----
> From: Glenn Nielsen [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, September 24, 2002 7:22 PM
> To: Tomcat Users List
> Subject: Re: DBCP pool always increasing
>
>
> What do you mean by your pool is increasing in size? That the number of
> open
> connections to the db is increaing? What is the indicator that this
> is happening? The more specific you can be the better chance that someone
> can answer your question.
>
> Glenn
>
> Amitabh Dubey wrote:
>
>>Hello All,
>> I managed to use DBCP with SQL Server and am able to get connections
>
> from
>
>>the pool. After i execute my query, i close the connection also, but it is
>>going back to my pool and i have verified that. However, what I do not
>>understand is this : Why does the pool go on increasing and never
>
> decreasing
>
>>in size. This is what my server.xml looks like
>>
>><ResourceParams name="SQLServerDS">
>> <parameter>
>> <name>validationQuery</name>
>> <value></value>
>> </parameter>
>> <parameter>
>> <name>user</name>
>> <value>sa</value>
>> </parameter>
>> <parameter>
>> <name>url</name>
>>
>>
>
<value>jdbc:microsoft:sqlserver://dnas07:1113;DatabaseName=NorthWind</value>
>
>> </parameter>
>> <parameter>
>> <name>password</name>
>> <value>sa</value>
>> </parameter>
>> <parameter>
>> <name>maxActive</name>
>> <value>3</value>
>> </parameter>
>> <parameter>
>> <name>maxWait</name>
>> <value>120</value>
>> </parameter>
>> <parameter>
>> <name>driverClassName</name>
>> <value>com.microsoft.jdbc.sqlserver.SQLServerDriver</value>
>> </parameter>
>> <parameter>
>> <name>maxIdle</name>
>> <value>5</value>
>> </parameter>
>> </ResourceParams>
>>
>>
>>If i understand the parameters correctly, then
>>
>>maxActive --> Maximum number of connections allowed to the database (What
>>happens when this number is reached? For me i get a new connection and the
>>pool increases. Is this the expected behavior? Can i change it to fail or
>>block instead?)
>>
>>maxidle --> Maximum number of idle connections that the pool should hold
>>(For me my pool never goes down to this limit)
>>
>>maxWait --> Maximum time to wait for a dB connection to become available
>
> in
>
>>ms.
>>
>>removeAbandoned --> recycle connections if the removeAbandonedTimeout is
>>reached and the connection is idle. in our case it is true.
>>
>>removeAbandonedTimeout --> 5
>>
>>
>>If i am correct, why is my pool growing forever and not reducing in size?
>>
>>Any ideas?
>>
>>Amitabh
>>
>>
>>--
>>To unsubscribe, e-mail:
>
> <mailto:[EMAIL PROTECTED]>
>
>>For additional commands, e-mail:
>
> <mailto:[EMAIL PROTECTED]>
>
>
>
>
> --
> To unsubscribe, e-mail:
> <mailto:[EMAIL PROTECTED]>
> For additional commands, e-mail:
> <mailto:[EMAIL PROTECTED]>
>
>
>
> --
> To unsubscribe, e-mail:
<mailto:[EMAIL PROTECTED]>
> For additional commands, e-mail:
<mailto:[EMAIL PROTECTED]>
--
To unsubscribe, e-mail:
<mailto:[EMAIL PROTECTED]>
For additional commands, e-mail:
<mailto:[EMAIL PROTECTED]>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
