[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - Trying to understand odd behavior in nested queries/result s
Hi everyone,
I am working for a company that is using a data warehouse to store information. There
are times where it is impossible to join various tables due to the nature of tracking the
history of how things change.
I am using jboss-4.0.3SP1 and the CMP contained within, as well as JDK 1.4.2 (Forced to
stay at 1.4 until third party vendors upgrade). We are on Oracle 9i using the
classes12.zip. The oracle-ds.xml file uses the <local-tx-datasource> setup.
I grab the connection from the CMP pool per normal method:
| InitialContext initialContext = new InitialContext();
| this.ds = (javax.sql.DataSource) initialContext
| .lookup("java:jdbc/reportsDS");
|
psuedo logic:
| initial Method(){
| try{
| loop through initial query:
| get various pieces of info from the DB
| getMoreInformation(pass PK)
| end loop
| }
| catch() {
| }
| finally {
| close things up
| }
| }
|
| getMoreInformation(ForeignKey){
| try {
| executeQuery
| loop through query:
| Get the PKs associated with the FK.
| getDetailOnPrimaryKey();
| end loop
| }
| catch() {
| }
| finally {
| close things up
| }
| }
|
| getDetailOnPrimaryKey(PK){
| try{
| exectueQuery
| get results
| }
| catch() {
| }
| finally {
| close things up
| }
| }
|
Now, what this does is essence creates 3 active result sets, and gets all the details
necessary and then finishes things up.
For whatever reason, the third active result set causes problems. I and three others
have verified that every instance grabs a connection from the pool, then closes the
connection, returning it to the pool within the finally block surrounding the code. Yet,
when it is run, the CMP pool opens up new connections only when the third result set is
then used, even if there are available connections in the pool. Basically, it looks like
it is duplicating the entire pool at that stage. Eventually, the max size for the pool is
reached, and the CMP can not open a new connection. What is really strange as well, is
even though all connections as stated in the Oracle Enterprise Manage as being inactive,
are not being used, and the pool can not open any more connections ... the entire web site
stops working!
We have tried many different things, and have fixed it the problem by completely
eliminating the RS within a RS idea: loop through the first RS, and store the results in
a list, then go over the list to do the sub-selects.
So the question to you all is:
1. Is the RS within a RS just a bad design/code?
2. If the RS within a RS is a bad design/code, what would you suggest instead? IS there
a more elegant way of doing things?
3. What would cause the pool to be duplicated in the situation as indicated above?
View the original post :
http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4028912#...
Reply to the post :
http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&a...