Hi,

How to force hibernate to use a server side cursor when scrolling with the query.scroll(...) feature and Microsoft SqlServer.

The documentation http://technet.microsoft.com/en-us/library/ms378405.aspx explains perfectly what I need.

I have to use the result set type TYPE_SS_SERVER_CURSOR_FORWARD_ONLY and not the TYPE_FORWARD_ONLY which loads all rows in memory.

If I understand, the solution is the ScrollMode.FORWARD_ONLY with the option 'selectMethod=cursor' with the ms sqlserver driver and the 'useCursors=true' with the jTDS driver.

But the only solution I found is to modify the jdbc url, but this option will be applied to all queries.
So, how to force this feature only when I need it ?

An other solution is to use ScrollMode.SCROLL_SENSITIVE but the result set will be fully scrollable, updatable and sensitive to changes.
For best performances and isolation, I think it's not the best choice for my need.

Thanks in advance.

Stéphane