Hi,
It was suggested to me in another forum that I could change my ResultSet Type flags from TYPE_SCROLL_SENSITIVE to TYPE_FORWARD_ONLY in order to speed up performance. I did that. Then my code broke all over the place. Generally it broke because I was calling ResultSet.first() in a lot of the code. I fixed that. Once I did, I got errors in my code stating that Connection is busy with results of another hstmt.
I tracked through the code and found a few places where I call the DB and ask for some ResultSet which is now TYPE_FORWARD_ONLY and then did not close my ResultSet and Statement. The reason I have not been concerned about closing them was due to the fact that I use a Singleton Connection object which returns a single Connection for a
thread and then closes that Connection just before the Thread dies. Still, it's better to close them so I'm glad this change made me aware that I was keeping so many open. As I closed those ResultSets I made it further and further in the execution of my code until I got stuck.
In one part of my code, I open a Connection and create a ResultSet. From that ResultSet, I build an array of information to be returned to the caller. Some of the information I have in that array needs to come from unrelated queries so I call the methods that will return me the data I need from those queries. This is where I run into a problem with the Connection being busy:
As you can see, there are a lot of ResultSet objects in all these methods I'm calling which use the same Connection (returned by my Singleton for this sessionID) I'm using in this ResultSet. I can't close the ResultSet because I'm looping over it in my while. Is there an easy way I can solve this?
Best,
Al