Prepared Statement not closing cursor

[Mars Mondal]

Hi,
I have a method which returns a resultset. The problem is that the cursor remains open and it exceeds the total number of cursors allowed and errors out. What is wrong with the following code?

private ResultSet createRecordSet(long entityID, String tableName, String linkType, long runID, String extraSQL) throws Exception
	{
                ResultSet rs = null;        
		
            String sqlString = " SELECT runID, recordNum, seqNum " +
                               " FROM (" +
                                   " SELECT a.RUN_ID AS runID, " +
                                   "        a.VALUE_SEQ_NUM AS seqNum " +
                                   " FROM FORM_VALUE_LINK a, " +
                                   "      DATA_SOURCE_CATALOG b, " +
                                   "      DATA_SOURCE_DETAIL c, " +
                                   "   RUN_DETAIL d, " +
                                          tableName + " e " +
                                   " WHERE b.DATA_SOURCE = c.DATA_SOURCE " +
                                   " AND b.CATALOG_ID = d.CATALOG_ID " +
                                   " AND d.RUN_ID = a.RUN_ID " +
                                   " AND a.ENTITY_ID = ? " +
                                   " AND a.ENTITY_ID = e.ENTITY_ID " +
                                   " AND a.LINK_TYPE = ? " +
                                   " AND a.VALUE_SEQ_NUM = e.SEQ_NUM " +
                                   " AND c.PRIMARY_SRC_RUN_ID = ? " +
                                     extraSQL +
                               " ) WHERE ROWNUM < 2";
                
                try
                {
                    
                    ps = con.prepareStatement(sqlString);
                    ps.setLong(1,entityID);
                    ps.setString(2, linkType);
                    ps.setLong(3, runID);
                    
                    rs = ps.executeQuery();
           

                }
                catch(SQLException sqle)
                {
                    System.out.println("Error in createRecordSet() method");
                    sqle.printStackTrace();
                }                
                return rs;		 
	}

Note that the extraSQL is for adding some extra sql logic that specific calls may need.

Thanks a lot in advance for your help

[Sudheer Bhat]

Thats because, you are not closing your PreparedStatement and thus leaving the implicit cursor open in the DB.

[Philip Grove]

The problem will not be fixed by closing the PreparedStatement. The method returns a ResultSet and closing the Statement will also close the ResultSet. Likewise a close on a Connection will trigger a close on all Statements. So not closing objects in JDBC is dangerous, but closing them can be messy as well.

Unless you really need it to be a ResultSet you return, you are probably better off creating another object with the data and closing the Statement before the return. Returning JDBC objects can be really dangerous because once you reach that return statement you loose the reference to the Statement, but it still exists.

[Mars Mondal]

Philip Grove wrote:The problem will not be fixed by closing the PreparedStatement. The method returns a ResultSet and closing the Statement will also close the ResultSet. Likewise a close on a Connection will trigger a close on all Statements. So not closing objects in JDBC is dangerous, but closing them can be messy as well.

Unless you really need it to be a ResultSet you return, you are probably better off creating another object with the data and closing the Statement before the return. Returning JDBC objects can be really dangerous because once you reach that return statement you loose the reference to the Statement, but it still exists.

Philip you are right this code was return by someone else and this was the way it was set up unfortunately. And yes it was being closed in the method where the result set was being returned. I think because of throwing JDBC objects we are getting closed statements error.
I think that changing the return type would solve the problem but it involves changing a lot of code written by someone else and I am working on that.

[Mars Mondal]

We tried closing the ResultSet and PreparedStatement and passed a null but yet we got the Closed statement error.

The way I fixed it was using a Statement instead of a PreparedStatement. I am curious though

Why did this not lead to open cursor issues when we changed it to a Statement ? And why was the cursor still remaining open when we passed null and had closed the PreparedStatement in the method?

[Paul Clapham]

In my opinion you should consider fixing that by creating the PreparedStatement only once, instead of re-creating it many times. That way you'll only have one open cursor at the database end.

Note that PreparedStatement is designed to be optimized by the database, so reusing it amortizes the optimization costs across the number of times you reuse it.