Leaking Connection pool

[roadrunner]

The problem:
I'm running out of connections to my database
The software:
JBoss 2.4.3 jdk1.3@win2k, DBMS:PostgreSQL 7.1 (later DB2)
The code:
class MySessionBean extends .... {
.
.
public Connection getConnection () {
Context context = new javax.naming.InitialContext();
Object object = context.lookup("java:/DefaultDS");
DataSource ds = (DataSource) PortableRemoteObject.narrow(object, DataSource.class);
return ds.getConnection ();
}

public void methodA () {
Connection conn = null;
try {
conn = getConnection ();
Statement stmt = conn.createStatement();
**BB**
ResultSet rs = stmt.executeQuery("SELECT * FROM BlahBlah");
}
catch (Exception ex) {
throw new EJBException(ex);
}
finally {
try {
if (conn != null) {
conn.close();
System.out.println("Closed DBConnection");
}
}
catch (Exception ex) {
throw new EJBException(ex);
}
}
Calling methodA 100 times works as expected and JBoss correctly is at [0/1/30] connections at the end.
Now replace **BB** with getConnection() and with each call of methodA a connection has leaked.
In the real world instead of getConnection there would be a call to another seesion bean (which could use getConnection or might call another bean)

Thats whats in the docs:
"If you request more than one connection from a DataSource in the context of the same transaction, JBoss will return the same connection every time."

As far as I understand my code should work because the second getConnection is in the same transaction and is returning the same connection.
Where is the error: my code, in the docs or jboss ?

bye
Sven

[johnmoore]

May be unrelated but make sure you closing the statement and result set.

[roadrunner]

John, you are right but closing the connection should close also resultset.
But this is not the problem because if I don't replace **B** with the second getConnection everything works fine.

regards
Sven

PS: just tried jboss 2.4.4b but it did't help

[davidjencks]

I think the docs are wrong, although I am mostly familiar with how it works in 3.0 or 2.4 using ConnectionFactoryLoaders. All the connections you get will be enrolled in the same transaction. If you are using LocalTransaction (i.e. a non xa jdbc driver) you will get different handles to the same underlying physical connection. If you are using an xa driver you will actually be using different underlying connections. In either case, it wouldn't make much sense to return the same Connection handle: you wouldn't want closing one to affect another.

So... be sure to close every Connection you get. Depending on how bad the jdbc implementation is, you may need to also close every resultset and statement.

[roadrunner]

After doing some research I found out that my interpretation of the docs were wrong.
My understanding was that if I get a second connection from jboss it is the same object and calling close() on the second connection would also close/invalidate my first connection.
So the docs are right but misleading.

[kar2000]

Well, I have a doubt in this regard - is there any means of finding in the jboss 3.0.7 server side and not in the Oracle 8 database server side,

how many connections are there in the pool,

how many connections are in use and

how many connections are being returned at a given point of time?