Hi ,

I am using hibernate to fetch large number of records around 10,00,000.

I want to use server side cursor. How can i use the same with hibernate. I am using SQL Server and Microsoft SQL Server 2005 JDBC Driver.



Regards,
Jamunt



2006-12-06 13:34:09.308 - [ERROR : 1001 ] could not execute query using scroll

DETAIL : In com.nec.infocage.iclogviewer.model.dao.SummaryDAO -> showSummaryView Exception Raised

StackTrace :

org.hibernate.exception.GenericJDBCException: could not execute query using scroll

at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:03)

at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)

at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)

at org.hibernate.loader.Loader.scroll(Loader.java:2253)

at org.hibernate.loader.hql.QueryLoader.scroll(QueryLoader.java:433)

at org.hibernate.hql.ast.QueryTranslatorImpl.scroll(QueryTranslatorImpl.java:328)

at org.hibernate.engine.query.HQLQueryPlan.performScroll(HQLQueryPlan.java:195)

at org.hibernate.impl.SessionImpl.scroll(SessionImpl.java:1170)

at org.hibernate.impl.QueryImpl.scroll(QueryImpl.java:67)

at com.nec.infocage.iclogviewer.model.dao.SummaryDAO.showSummaryView(SummaryDAO.java:130)

at com.nec.infocage.iclogviewer.controller.bo.BriefLogsBO.showSearch(BriefLogsBO.java:137)

at com.nec.infocage.iclogviewer.controller.action.BriefLogsAction.showSearchView(BriefLogsAction.java:303)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:585)

at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:270)

at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:187)

at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:431)

at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:236)

at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1196)

at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:432)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)

at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)

at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:869)

at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:664)

at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527)

at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)

at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)

at java.lang.Thread.run(Thread.java:595)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The system is out of memory. Use server side cursors for large result sets:Java heap space. Result set size:214,432,538. JVM total memory size:66,650,112.

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(Unknown Source)

at com.microsoft.sqlserver.jdbc.DBComms.receive(Unknown Source)

at com.microsoft.sqlserver.jdbc.IOBuffer.sendCommand(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.sendExecute(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteQuery(Unknown Source)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(Unknown Source)

at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)

at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)

at org.hibernate.loader.Loader.scroll(Loader.java:2218)

... 34 more

Use the .scroll() method. You will likely want to use .evict() to remove entities from 1st level cache. Also, consider using a StatelessSession.

Curtis ...

It is still giving the same driver.

Does all driver support scrollable result set. Or there is some gotcha in my implementation. Presently i am using microsoft sql jdbc driver.

What all steps are required to use scrollable result set. I have done only following

1. Changed the connection string - selectMethod=Cursor

Regards,
Jamunt