JDBC problem (Connection pooling)

[Priti]

Hi All,

I need help with one issue that I am facing.

I am developing a web-based application, using Struts.
I am using JDK Version 1.4.1 and working with Tomcat
server version 4.1.

I am trying to use the Connection pooling using the
Datasource class,
provided by Apache. ( commons-dbcp.jar component )
Backend dbs used is MS SQL Server 2000.
JDBC driver used is the one provided by Microsoft.
(com.microsoft.jdbc.sqlserver.SQLServerDriver)

The actions performed are -
1) Connection is acquired using the Datasource.
2) A record is inserted into a table. Connection is closed.
3) The control goes to another method, which again
acquires a connection

using the Datasource and fetches all the records from the
same table
(for listing purpose).

Following Exception occurs in the 'while' loop, which is
written for the

resultset , in step (3) above.

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver
for JDBC]Object

has been closed.
at
com.microsoft.jdbc.base.BaseExceptions.createException
(Unknown Source)
at
com.microsoft.jdbc.base.BaseExceptions.getException(Unknown
Source)
at
com.microsoft.jdbc.base.BaseStatement.validateClosedState
(Unknown Source)
at
com.microsoft.jdbc.base.BaseResultSet.validateClosedState
(Unknown Source)
at
com.microsoft.jdbc.base.BaseResultSet.commonFetchInitialize
(Unknown Source)
at com.microsoft.jdbc.base.BaseResultSet.next
(Unknown Source)
at
org.apache.commons.dbcp.DelegatingResultSet.next
(DelegatingResultSet.java:135)

The code for the step (3) above looks like something
this...

method1
{
Connection conn = null;
ResultSet rs = null;
Statement stmt = null;

try
{
conn = Pool.getConnection(); //Pool is a class which
returns the
connection using the Datasource
stmt = conn.createStatement();
String sqlQry = "select * from table order by fld1";
rs = stmt.executeQuery(sqlQry);
while (rs.next())
{
long id = rs.getLong("fld1");
//some processing followed
}
catch(Exception ex)
{
ex.printStackTrace();
}

finally
{
try
{
if(rs != null) { rs.close(); }
if(stmt != null) { stmt.close(); }
if(conn != null) { conn.close(); }
}
catch (SQLException ex)
{
ex.printStackTrace();
}
}

} //method over.

I have reviewed my code & ensured that I do not close the
connection/statement anywhere in between. Yet, the
exception says that
the connection is closed. There are about more than 20
records, which
are normally fetched without any problems. But, If I
insert a record &
then try to fetch the records, above exception occurs.

Surprisingly, the code works fine if instead of using the
Datasource, I
load the connections by using the DriverManager directly.
(i.e. no
pooling)

What could be the reason & solution ?

If anyone out there could help me....
SOS...Please help....

--
TIA,
Priti

[alta]

hi Priti,

There might be something wrong in your steps before step
3. Some db resource is possibly left open, such as
Statement. Please check and ensure every db resource is
closed before step 3 begins. Or, you could paste your
codes before step 3 here.

Good luck!

>.
>

[Frank Wang]

Hi, Priti:
I'm developing a web application based on Tomcat & MSSQL too.
Recently I noticed a unusual log entry of my application:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Object has
bee
n closed.

I did some search about this exception, noticed that there are many
people( also in this newsgroup) has encountered similar exception.
Many are using Connection pools, Jakarta's DBCP especially. I doubt there
must be something behind the DBCP and MSSQL
jdbc driver. This message is new to me, everything was fine before I
upgraded my code few days ago to utilize the DBCP.
I'm still trying to locate the exact code which caused the problem. But
after surfing the internet and read the messages of this group, I think I
have enough reason
to focus on the DBCP / MSSQL jdbc driver combination. Maybe some simple
configure can solve the problem, I don't know.
So, Paiti, how's your problem? did you solve it?

Frank

"Priti" <send...@hotmail.com> ????
news:08d601c3566b$66e551e0$3501...@phx.gbl...