Urgent!--error in SQL:maximun open cursors excceed

[zhuzhengyu]

I use jdbc to connect Oracle database,jdbc driver is "classes111.zip" which provided by Oracle8i.
I know if we open too much resultset and forget to close there could occurs such error,but in my program I do close after use RecordSet ,but I code like this because of the complex logic:

Connection con=...;
for(...){
Statement st=con,createStatement();
ResultSet rs=st.excecuteQuery("..."
while(rs.next()){
Statement st1=...;
ResultSet rs1=...;
...;
rs1.close();
st1.close();
}
rs.close();st.close();
}

is there any problem?

if someone knows ,ps. tell me as quick as possible.thanks a lot


0 replies in this thread

 

[ChrisCarroll]

From the depths of my ignorance...

Does close() rely on the garbage collector, which might not come along till it's too late?

I wonder if by having

    Statement st1=...; 
    ResultSet rs1=...;

inside the while loop you are forcing the creation of unncessary new objects for everytime round, which might be avoided by instead using

    st1=...; 
    rs1=...;

and moving the new object creation statements above the loop:

Connection con=...; 
    Statement st1= new ...; //Create objects here
    ResultSet rs1= new ...; 
for(...){ 
  Statement st=con,createStatement(); 
  ResultSet rs=st.excecuteQuery("..."); 
  while(rs.next()){ 
    st1=...; //reuse, rather than recreate here
    rs1=...; //
    ...; 
    rs1.close(); 
    st1.close(); 
   } 
   rs.close();st.close(); 
}

but I could be wrong

 

[palbano]

> Does close() rely on the garbage collector

Yes, that is likely to be the reason for the error. The problem on the other hand might be this:

for(...){
Statement st=con,createStatement();
ResultSet rs=st.excecuteQuery("..."


Why are you executing multiple SQL statements inside a 'for' loop? Many times when I see this it is due to the developers lack of SQL skills and the multiple querys could be written as a single query to produce the desired results.

"But, that's just my opinion... I could be wrong".
-pete

 

[zhuzhengyu]

thanks a lot to you both.
My real project is complex payroll calculation that could adaptable for all areas including Singapore,Hongkong,etc.So many database tables--about 100 tables involed.Because of complex logic,I msut sovle problem one by one and I've to write lots of classes and function so that re-use is possible.It's really a difficult decision before coding that get balance between flexibiliy and effectivity.
So the above is just my simulation but not real code.
Before you code,you only know the prototype and its logic ,but not know use how many "Statements",so I pass a "Connection" as parameter.
What you said is correct.I have found creating a statement is just like creating a socket thread,so it is not a good idea that creates statement frequently.
I use a global counter(use singleton) to monitor all the calculating procedure and found the to a small company(50 persons & 20 payitems) the counter excceed 15000.So I use 2 ways to optimize the code:
1.reduce open-close statements
2.add open-close Connection
Now the system runs well.

thanks