I'm using JBOSS 7 AS and I have had problems using JDBC Oracle Extensions :
I need to use them because I need to work with complex Data Object like Types, CLOBs, etc...
The problem is that each service call consumes a new connection from the pool, it appears that it creates a new connection every time I invoke a service, doesn't reuse or doesn't close the connections, I don't know. In a just few clicks, and service calls, all connections from the pool are used
However if I use only JDBC Standard all works fine, the connections are closed and reused from the pool, but of course I need to use extra code to deal with complex data
What is causing this ?! why when I use OracleConnection and other oracle extensions doesn't reuse connections from the pool and creates new ones instead ?!
thanks in advance
Connection pools are not magic. If you don't return the connection to the pool then it is never re-added to the pool. The way these days to return it to the pool is to 'close' the connection (which is actually a proxy.
Which is what you are supposed to do regardless. And which is the only way using JDBC is going to free up connections. And "JDBC" doesn't have a "pool" so I am not sure what that statement is supposed to mean at all.
And of course you should close all other resources before returning to the pool as well. Like statements, result sets.
> What is causing this ?
Probably more than one thing. For starters normally connection pools do not expose functionality of JDBC drivers that is not already in JDBC. So if you want to use non-JDBC functionality then you use JDBC and not a pool.
> I need to use them because I need to work with complex Data Object
Been my experience is that statement is often actually a 'want' rather than a 'need'. And after that it is due to either rigid management (which has nothing to do with technology) and/or a lack of understanding of what Oracle can do.
I'm going to take a guess at what is going on.
Perhaps your problem is that when your code closes a connection it is not returned to the pool for reuse and the next time your code gets a connection the pool creates a new connection. That's not how one would expect a pool to work. If this is not your problem, the JSchell is probably correct.
But if my guess is right then I think I know what is going on. Your code gets a connection from the pool. Then it uses some mechanism to access Oracle proprietary methods. Then it closes the connection expecting it to be returned to the pool. But the JBoss pool doesn't understand Oracle proprietary methods so it doesn't know what you did to the connection. In order to protect the next user of a connection from some nastiness that you might have done, it closes the connection and removes it from the pool preventing it from being reused. You know your code didn't screw up the connection but the JBoss pool doesn't know that. The pool takes a very conservative approach that anything it doesn't understand is bad. So when your code uses proprietary methods JBoss declares the connection contaminated and trashes it.
If I'm right, then I can't suggest a solution. This is a JBoss issue, not a JDBC issue.
Putting on my JDBC Expert Group member hat I will point out that the standard unwrap method was designed with exactly this problem in mind. A comprehensive implementation of unwrap would permit the JBoss pool to protect against proprietary methods that did bad things without declaring all proprietary methods bad. If you are using unwrap then JBoss may need a better implementation.