Error while closing connection from a pool

[rohit.macherla]

Hi,
I am using JBoss 4.2.2.GA on a HP Unix platform. For the sake of delegating connection management to the application server, I have started using connection pooling.
The code I used was :

InitialContext jndiCntx = new InitialContext();
DataSource ds = (DataSource) jndiCntx.lookup("java:/testapi");
Connection con = (Connection)ds.getConnection();

But with this, I was unable to invoke any Oracle Stored Procedures that takes in an Oracle Object. I have resolved this issue by fetching the connection as :

InitialContext jndiCntx = new InitialContext();
WrapperDataSource ds = (WrapperDataSource) jndiCntx.lookup
("java:/clarityapi");
org.jboss.resource.adapter.jdbc.WrappedConnection wrappedCon = (WrappedConnection) ds.getConnection(
);
java.sql.Connection connection = wrappedCon.getUnderlyingConnection();

Using this, I am able to send in any Oracle Objects by using StructDescriptor's and ArrayDescriptor's wherever necessary. Also, necessary validations were put in to check if the 'wrappedCon' is 'closed' and if so I was retrying for a maximum of 4 times to check for stale connections.
However, the problem comes in when I am trying to close the connection. If i close it using :
connection.close();
there is no problem. But in the next invocations of the :
ds.getConnection()
I receive the following statements:

[CachedConnectionManager] Closing a connection for you. Please close them yourself: org.jboss.resource.ada
pter.jdbc.WrappedConnection@1c1ec0e

and the subsequent connections i receive the following error :

java.sql.SQLException: Closed Connection
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:187)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:229)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:292)

With the debug statements, I have found the following status for the subsequent calls:
wrappedCon.isClosed : false
wrappedCon.getUnderlyingConnection.isClosed() : true .
So, the WrappedConnection object is not closed, but the underlying java.sql.Connection is closed.
This "Closing a connection for you..." may be only a debug statement given for my benefit but even if I disable it, the fact is that the connection is getting closed when I wanted it to be just put back into the connection pool.

The same scenario exists when I do :
wrappedCon.close(); instead of connection.close();

Please suggest me a valid way to close these wrapped connections. I was under the impression that these close() methods would place it back into the connection pool, but probably with this output I think the JBoss connection pooling needs to be handled differently in my code.

Thanks in advance.

[vickyk]

But in the next invocations of the :
ds.getConnection()
I receive the following statements:

This is not clear, what exactly you are doing after closing the wrapped connection?
Can you explain the code in detail, I don't want to guess/assume things here?

[rohit.macherla]

Hi Vickyk,
Thanks for the reply.
My WebService does just this :

Fetches the connection (Using the connection pool)
Invokes a PL/SQL procedure and fetches the result
Closes the connection and the JDBC statements.

The WebService ends after closing the connection.
What I meant by the 'next invocation' was :
The first invocation of the WebService works fine. But from the second invocation, the part where I 'fetch the connection' fails.
I tried to print the wrapped connection's status and the underlying connection's status just after I fetch the connection and I find that both are open for the first invocation, and from the second invocation onwards, the wrapped connection is open and the underlying connection is closed.

[vickyk]

"rohit.macherla" wrote:
the wrapped connection is open and the underlying connection is closed.

Can you simulate this behaviour through a simple jsp page, you can attach the jsp and I can have look at it?

[jboss-user-1234]

Never close underlying connection.
Always close Wrapped Connection.

[rohit.macherla]

Hello vickyk and jboss-user-1234,

Thanks for the suggestion. I have rechecked the code and found out that I overlooked commenting one piece of code. Finally, closing the wrapped connection is working. Closing the underlying connection is closing the actual connection.
Thanks.