Hi all,

should I have to close or flush anything when using iterate on Query interface (to clean resources on database ) ?

I use intensively this method on a table (in the same hibernate session) and obtain an exception with maximum open cursors exceeded after several minutes of execution.

I think I mistake using correctly this API, but I do not know where.

Greetings
Alain


Hibernate version:
Hibernate 2.1.8

Mapping documents:

Code between sessionFactory.openSession() and session.close():

public Iterator iterateOnQuery(String pQuery, Map pParams)
throws DatabaseException {
try {
Query query = currentSession().getNamedQuery(pQuery);
for (Iterator iParam = pParams.keySet().iterator(); iParam
.hasNext();) {
String key = (String) iParam.next();
query.setString(key, (String) pParams.get(key));
}
return query.iterate();
} catch (HibernateException hExc) {
throw new DatabaseException(DatabaseException.CODE.DATABASE_QUERY,
DatabaseException.RAISON.QUERY_SELECT_FAILED, hExc,
new Object[] {
pQuery
});
}
}


I do not close session on every call since I use a single session to perform a batch.

Full stack trace of any exception that occurs:

Name and version of the database you are using:
Oracle 8.1.7.0
The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

At short glance I'd say you aren't closing any sessions if you have a method that just reads getCurrentSession(), but what you posted is not enough to tell.

Please provide the class in which this method is used.

Exactly, I do not close session after calling iterate on the Query interface.

My object session is used only by one thread and it is a batch.

I think I could use always the same session for all treatments in my batch as I do not know when I can close it because of lazy initialization.

I perform select queries and I update some rows on a particular table.

The problem is also I had multiple queries I iterate on like this :


init_session();
// Distinct rows
for (Iterator iRows = iterate_on_table_A; iRows.hasNext();) {

LIST : Select all rows of table_A that have same values as iRows.next();

Other select on other tables;

Then update a column on elements of LIST

}

close_session();

for iterating on the first query, I must not close session and since I use the same session to iterate the outter loop and the inner loops, I do not see when I could safely close the session.

Should I have to use one object session for each iteration ?

Thanks for helping.
Alain

in the online api docs for the iterate() funciton, it says

Entities returned as results are initialized on demand. The first SQL query returns identifiers only.

which leads me to believe that it is an iterator over a list of proxies, which creates a running total of open cursors to the database, and when you finally use the objects you have to leave that session open or else you'll get your lazy initialization exception when it actually queries for the object.

Maybe try using the list() method as it appears to retrieve all the objects when it is called and put them into a List.

This might work in conjunction with setting your objects to lazy="false" if you are going to go ahead and use all the information from the object anyways. If it has any children you can leave them lazy="true" if you don't want to retrieve them.

and please close the session when you are done because this will gobble up sessions that a database or webserver is allowed to keep open and will make these resources unusable by other threads or programs

Of course I close session at the end of the outter loop.

I'll test with list() to see if it suppresses the exception in Oracle.

Maby thanks
Alain

I solved my problem.

In the hibernate Api, the Query interface iterate() method returns an Iterator, but in fact, it returns an HibernateIterator which has a close() method to close result set and prepared statement used for iteration.

So, when needed, I close the iterator I iterate on to free resources on database and the exception disappears.

Many thanks for help
Alain