In my relational db, I have a one to many where the many is very larger, > 5000 rows.

So I can run into errors where my returned Set can cause out of memory issues.

I tried to use, as recommended in the performance section, a scrollabe results set, but then got this error: "scrollable result sets are not enabled"

I am using hibernate 3.1 with Sybase 12.5.2 with jConnect 5.0 (jconn2.jar). Upon further reading, it looks like jConnect 5.0 does not support scrollable cursors. "As of jConnect 5.0, TDS does not support scrollable cursors."

Any body have any suggestions then how to handle large results sets?

We use scroll with forward only. That doesn't require the JDBC driver to support scrollable cursor.

So, try

scroll(ScrollMode.FORWARD_ONLY)

Hope this helps

That is what I tried..
ScrollableResults loanXrefs = q.scroll(ScrollMode.FORWARD_ONLY);

And I get this error:

org.hibernate.AssertionFailure: scrollable result sets are not enabled
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:368)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:334)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:88)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1162)
at org.hibernate.loader.Loader.scroll(Loader.java:1634)
at org.hibernate.loader.hql.QueryLoader.scroll(QueryLoader.java:443)
at org.hibernate.hql.ast.QueryTranslatorImpl.scroll(QueryTranslatorImpl.java:291)
at org.hibernate.impl.SessionImpl.scroll(SessionImpl.java:960)
at org.hibernate.impl.QueryImpl.scroll(QueryImpl.java:62)

can you post your query? The particular query might not be supported for scroll.

Also, we are using 3.0.5. Perhaps you can switch jars and see if it works with that.

Here is the query. How do you get it to support scrolling? That is interesting....


Query q = session.createQuery("from Cats where Cat_Obj_ID = :id");
q.setInteger("id", dealPK.intValue());
ScrollableResults cats = q.scroll(ScrollMode.FORWARD_ONLY);

I had problems getting some queries to work when there was a Fetch-join with one-many. That is why I asked.

I just noticed something...where is the Cat_Obj_ID coming from.
I think your query should be something like...

"select c from Cats c where c.id=:id"

assuming that Cats's id property name is id.

The Cat_Obj_ID is the Cat's id defined in the xml.

I'm at a loss...
I have a query...

that is executed using..

It works on a few different drivers, without jdbc scroll cursors.

Only difference I can think of is the hibernate version. I am using 3.0.5. Did you try with 3.0.5?

Anar

Is your DB Sybase and is the JDBC verion you are using 5.0?

no.
...but the assertion is from hibernate. I would expect it to not even request a scroll cursor for forward_only mode.

forward only is really same as hanging on to result set and returning next line by line

That makes me think there is a difference in the implementation between 3.0.5 and 3.1

Perhaps someone from hibernate team can comment.

You can try the new collection lazy="extra" feature of Hibernate 3.1

Set property hibernate.jdbc.use_scrollable_resultset=true