ResultSet.getStatement questions.

Thread view:

Bruce Marriner - 03 Oct 2003 21:12 GMT

Hello, I am setting up a ConnectionPooling dbHandler class for a JSP
application connecting to an AS400. We are using Tomcat 4.1.27 and
precompiling everything as servlets with ANT. I have a dbHandler
class that works just fine, along with the connecting pooling. My
problem is finding a good way to properly close resultsets,
statements, and connections. What I am wanting to do is beable to
call say, dbHandler.selectQuery("query");. This method will open a
pooled connection, a statement, execute the query, and return a
ResultSet. Now, I want to call a function that uses
ResultSet.getStatement and Statement.getConnection to gather the
parent objects. Then close those objects. So I could do this..
Resultset.getStatement.getConnection.close();,
Resultset.getStatement.close();, Resultset.close();.. The problem
is, it doesn't work. It does not actually close the parent connection
when I do this. I am not sure if getConnection/getStatement return a
"clone" of those objects or what. I first noticed this idea didn't
work when I tried it on a few pages then looked at the "Active"
connections in the pool and saw that the pages were not releasing the
connections back to the pool. If someone has some ideas, or would
like me to post some code.. I'd be happy to do so.

Reply to this Message

Joseph Weinstein - 03 Oct 2003 21:30 GMT

> Hello, I am setting up a ConnectionPooling dbHandler class for a JSP
> application connecting to an AS400. We are using Tomcat 4.1.27 and
[quoted text clipped - 16 lines]
> connections back to the pool. If someone has some ideas, or would
> like me to post some code.. I'd be happy to do so.

I'd suggest you return a wrapper around the original ResultSet. This wrapper
also implements ResultSet, and mostly just passes all calls to the actual
result set object. The exceptions are:
1 - make sure that it's getStatement() call returns an exception, otherwise
an applicaiton can subvert your pooling system by obtaining the connection
and use it later.
2 - Make the close() method close the actual result set, and the statement,
and put the connection back into the pool.
3 - In case any application code abandons a result set without closing it,
make the wrapper have a finalize() method that closes the wrapper if need be.

Joe Weinstein at BEA

Reply to this Message

Bruce Marriner - 05 Oct 2003 05:38 GMT

> I'd suggest you return a wrapper around the original ResultSet. This wrapper
> also implements ResultSet, and mostly just passes all calls to the actual
[quoted text clipped - 8 lines]
>
> Joe Weinstein at BEA

I'll have to read up a bit on "wrappers" because I really do not
know much about them or how they are implemented. But once I figure
that out I'll dig in deeper. But I think maybe my problem lies
somewhere else. Because this application I have runs on latops that
are sync'd to the company database whenever they connect to the
internet. These laptops use MSDE locally. Well the whole
ResultSet.getStatement.getConnection.close(); trick works perfectly
fine with them. So I dug into the differences from the dbHandler
classes. For the most part they are the same, and were exact copys
untill I started the connection pooling stuff. Mainly, the MSDE
version does not use any time of connection pool, and connects using a
single connection that is never closed. That connection is defined as
a global "private static Connection conn;" This is the first thing I
noticed because in the AS400 version I was not using defining the
datasource connection as a static variable. So I'm guessing it's
either that, or the difference is in the ConnectionPool side or the
actual DB drivers. Do the ResultSet, Statements, Connections depend
on code within the DB driver? Or do the Drivers mearly manage the
physical connection to the DB itself? Well I'll try out these ideas
come monday.

Reply to this Message

Bruce Marriner - 07 Oct 2003 16:01 GMT

Well it's not the static thing, it is connected to the ConnectionPool
part only. I created a datasource with
AS400JDBCConnectionPoolDataSource and used it's getConnection method.
Using that connection with in my dbHandler class it executes a query.
Then inside the JSP page I get the resultset back and execute the
resultset.getStatement.getConnection.close(); and it works perfectly.
It will close the connection just as it should. But once I change
the dbHandler class to make a pool from the dataSource, then get a
connection from the pool. It doesn't work. If I only get ONE
connection from the pool, the program will run the first time around.
It executes resultset.getStatement.getConnection.close(); and then
resultset.getStatement.getConnection.isClosed(); returns that it is
closed. But I made the conn variable inside the class public. So if
I do dbHandler.conn.isClosed(); it says that it is not closed? But
using that connection, I try to execute a second query.. I get an
exception that the connection does not exist. this is driving me
nuts.

I can only imagen that something involved with the ConnectionPool
requires this? So I am trying to come up with new ways to solve my
problem. If I make a getConnection function inside my dbHandler class
that returns a pooled connection. Then change my selectQuery method
to require a argument of a connection I can make it work. So in the
JSP page it will getConnection, execute the query with that
connection, then do a connection.close() inside the JSP page. This
works perfect, and returns the connection into the pool. I just don't
like it much and it looks messy to me.

I would really like to find away to beable to close the connection
using the resultset.getStatement.getConnection method so if anyone has
any additional ideas...

Reply to this Message

ResultSet.getStatement questions.

Free Magazines