my dog learned polymorphism*
The moose likes JDBC and the fly likes CachedRowSet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "CachedRowSet" Watch "CachedRowSet" New topic
Author

CachedRowSet

J. Alenis
Greenhorn

Joined: Jul 12, 2004
Posts: 16
Hello,

I copied the example for CachedRowSet from Sun and I have a problem that I solved but now I want to know why I have to do this.

With the original I only get the first 10 objects and than java.sql.SQLException: Invalid Cursor position.

Original:

crs.setCommand("SELECT * from DB.table");
crs.setPageSize(10);
crs.execute(con);
while (crs.next())
{ System.out.println(crs.getString("Name") + " " +
crs.getString("Address"));
}
while (crs.nextPage())
{ while (crs.next())
{ System.out.println(crs.getString("Name") + " " +
crs.getString("Address"));
}
}

So I tried to use crs.nextPage() before the while and I get all the data. Is this normal?

crs.setCommand("SELECT * from DB.table");
crs.setPageSize(10);
crs.execute(con);
while (crs.next())
{ System.out.println(crs.getString("Name") + " " +
crs.getString("Address"));
}
crs.nextPage();
while (crs.nextPage())
{ while (crs.next())
{ System.out.println(crs.getString("Name") + " " +
crs.getString("Address"));
}
}

Thanks
Thomas Risberg
Greenhorn

Joined: Aug 17, 2004
Posts: 4
Have you tried this:

crs.setCommand("SELECT * from DB.table");
crs.setPageSize(10);
crs.execute(con);
while (crs.nextPage())
{ while (crs.next())
{ System.out.println(crs.getString("Name") + " " +
crs.getString("Address"));
}
}


Thomas
J. Alenis
Greenhorn

Joined: Jul 12, 2004
Posts: 16
Thanks, yes that did it.
Subhani Sk
Greenhorn

Joined: Oct 04, 2003
Posts: 5
Hi Thomas,

I have a problem in using CachedRowSet. I saw ur reply and sending you this mail since this is an urgent issue for me.

When I am using "crs.nextPage()", I am getting the mentioned exception. the query retrieving 10000 records.

public HashMap getCompanyByAgentWeeklyProdReportRowList() {

//ArrayList reportRowList = new ArrayList();
HashMap reportRowMap = new HashMap();
ProdScreenReportRow resultRow = null;

java.sql.Date startDate = null;
java.sql.Date endDate = null;

Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
CachedRowSet crs = null;

try {
conn = getConnection();
crs = new CachedRowSetImpl();
crs.setPageSize(100);

endDate = getEndDate(ProdScreenReport.WEEKLY_VIEW);
startDate = getStartDate(endDate, ProdScreenReport.WEEKLY_VIEW);


stmt = conn.prepareStatement(GET_COMPANY_BY_AGENT_DATA);
stmt.setDate(1, startDate);
stmt.setDate(2, endDate);
rs = stmt.executeQuery();
crs.populate(rs);

// retrieve the data
// Result set code Here

int noOfPagesCount=0;

System.out.println("No. of rows in crs : "+crs.size());

while(crs.nextPage()) {
while (crs.next()) {
System.out.println("weekly prod count : "+crs.getDouble("WKLY_PROD_CNT"));

System.out.println("Page : "+(noOfPagesCount++));
}
}

closeResultSet(rs);
closePreparedStatement(stmt);
crs.close();

}
catch (Exception e) {
logger.error("Exception encountered in getWeeklyProdReportRowList()", e);
}
finally {
try{
crs.close();
}
catch(Exception e){
logger.error("Exception encountered in getWeeklyProdReportRowList() - may be with CachedRowSet : ", e);
}

closeResultSet(rs);
closePreparedStatement(stmt);
closeConnection(conn);
}

return reportRowMap;

}


Exception:
==========

[5/28/06 3:35:34:843 PDT] 2f352f35 SystemOut O 2006-05-28 03:35:34,812 [ERROR] app.service.prodscreen.ProdScreenDAO - Exception encountered in getWeeklyProdReportRowList()
java.sql.SQLException: Populate data before calling
at java.lang.Throwable.<init>(Throwable.java:59)
at java.lang.Throwable.<init>(Throwable.java:73)
at java.sql.SQLException.<init>(SQLException.java:92)
at com.sun.rowset.CachedRowSetImpl.nextPage(Unknown Source)
at com.farmers.eagent.ade.app.service.prodscreen.ProdScreenDAO.getCompanyByAgentWeeklyProdReportRowList(ProdScreenDAO.java)
at com.farmers.eagent.ade.app.service.prodscreen.ProdScreenService.getReportCompanyBy(ProdScreenService.java)
at com.farmers.eagent.ade.busn.prodscreen.ProdScreenDelegateHnd.execute(ProdScreenDelegateHnd.java)
at com.farmers.eagent.ade.busn.delegate.AdeDelegateHandler.execute(AdeDelegateHandler.java:47)
at com.farmers.frameworks.eagent.busn.delegate.DelegateChain.doNext(DelegateChain.java:72)
at com.farmers.eagent.ade.busn.NavigationBarDelegateHnd.execute(NavigationBarDelegateHnd.java:249)
at com.farmers.eagent.ade.busn.delegate.AdeDelegateHandler.execute(AdeDelegateHandler.java:47)
at com.farmers.frameworks.eagent.busn.delegate.DelegateChain.doNext(DelegateChain.java:72)
at com.farmers.eagent.frameworks.service.alternateView.AltViewDelegateHnd.execute(AltViewDelegateHnd.java:389)
at com.farmers.frameworks.eagent.busn.delegate.DelegateChain.doNext(DelegateChain.java:72)
at com.farmers.eagent.ade.busn.UserMgrServDelegateHnd.performExecute(UserMgrServDelegateHnd.java:55)
at com.farmers.eagent.ade.busn.UserMgrServDelegateHnd.execute(UserMgrServDelegateHnd.java:66)
at com.farmers.frameworks.eagent.busn.delegate.DelegateChain.doNext(DelegateChain.java:72)
at com.farmers.frameworks.eagent.busn.delegate.ExceptionDelegateHnd.execute(ExceptionDelegateHnd.java:24)
at com.farmers.frameworks.eagent.busn.delegate.DelegateChain.doNext(DelegateChain.java:72)
at com.farmers.eagent.ade.busn.delegate.AdeDelegateMgr.dispatch(AdeDelegateMgr.java:138)
at com.farmers.frameworks.eagent.web.request.RequestChain.dispatchToDelegate(RequestChain.java:107)
at com.farmers.frameworks.eagent.web.request.RequestChain.doNext(RequestChain.java:92)
at com.farmers.eagent.ade.web.request.ProdScreenRequestHnd.execute(ProdScreenRequestHnd.java)
at com.farmers.eagent.ade.web.request.AdeRequestHandler.execute(AdeRequestHandler.java:41)
at com.farmers.frameworks.eagent.web.request.RequestChain.doNext(RequestChain.java:79)
at com.farmers.eagent.frameworks.web.request.AltViewReqHnd.execute(AltViewReqHnd.java:110)
at com.farmers.frameworks.eagent.web.request.RequestChain.doNext(RequestChain.java:79)
at com.farmers.eagent.ade.web.request.UserRequestHnd.execute(UserRequestHnd.java:115)
at com.farmers.eagent.ade.web.request.UserRequestHnd.execute(UserRequestHnd.java:130)
at com.farmers.frameworks.eagent.web.request.RequestChain.doNext(RequestChain.java:79)
at com.farmers.eagent.common.web.request.DefaultRequestMgr.dispatch(DefaultRequestMgr.java:145)
at com.farmers.frameworks.eagent.web.ControllerServlet.performTask(ControllerServlet.java:191)
at com.farmers.frameworks.eagent.web.ControllerServlet.performTask(ControllerServlet.java:173)
at com.farmers.frameworks.eagent.web.ControllerServlet.doPost(ControllerServlet.java:150)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at com.ibm.ws.webcontainer.servlet.StrictServletInstance.doService(StrictServletInstance.java:110)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet._service(StrictLifecycleServlet.java:174)
at com.ibm.ws.webcontainer.servlet.IdleServletState.service(StrictLifecycleServlet.java:313)
at com.ibm.ws.webcontainer.servlet.StrictLifecycleServlet.service(StrictLifecycleServlet.java:116)
at com.ibm.ws.webcontainer.servlet.ServletInstance.service(ServletInstance.java:283)
at com.ibm.ws.webcontainer.servlet.ValidServletReferenceState.dispatch(ValidServletReferenceState.java:42)
at com.ibm.ws.webcontainer.servlet.ServletInstanceReference.dispatch(ServletInstanceReference.java:40)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.handleWebAppDispatch(WebAppRequestDispatcher.java:983)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.dispatch(WebAppRequestDispatcher.java:564)
at com.ibm.ws.webcontainer.webapp.WebAppRequestDispatcher.forward(WebAppRequestDispatcher.java:200)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.doForward(WebAppInvoker.java:119)
at com.ibm.ws.webcontainer.srt.WebAppInvoker.handleInvocationHook(WebAppInvoker.java:276)
at com.ibm.ws.webcontainer.cache.invocation.CachedInvocation.handleInvocation(CachedInvocation.java:71)
at com.ibm.ws.webcontainer.cache.invocation.CacheableInvocationContext.invoke(CacheableInvocationContext.java:116)
at com.ibm.ws.webcontainer.srp.ServletRequestProcessor.dispatchByURI(ServletRequestProcessor.java:186)
at com.ibm.ws.webcontainer.oselistener.OSEListenerDispatcher.service(OSEListener.java:334)
at com.ibm.ws.webcontainer.http.HttpConnection.handleRequest(HttpConnection.java:56)
at com.ibm.ws.http.HttpConnection.readAndHandleRequest(HttpConnection.java:618)
at com.ibm.ws.http.HttpConnection.run(HttpConnection.java:439)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:672)


Please help me.

Subhani.
Preetam Palwe
Greenhorn

Joined: Sep 26, 2006
Posts: 2
this link might help !
http://forum.java.sun.com/thread.jspa?threadID=769985
 
jQuery in Action, 2nd edition
 
subject: CachedRowSet
 
Similar Threads
Resultset with million rows
CachedRowSet giving "SQLException" when using nextPage( ) method on it.
Strange behaviour when accessing Oracle 8i table from servlet
CachedRowSet - Stored Procedure
cachedrowset acceptchanges() NOT working