Win a copy of JDBC Workbook this week in the JDBC and Relational Databases forum
or A Day in Code in the A Day in Code forum!

Subhani Sk

Greenhorn
+ Follow
since Oct 04, 2003
Cows and Likes
Cows
Total received
0
In last 30 days
0
Total given
0
Likes
Total received
0
Received in last 30 days
0
Total given
0
Given in last 30 days
0
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by Subhani Sk

Can you cross check the Datasource names you are referring.
What do you mean by search in database?

You can use WHERE Clause in SQL to search.
As per the info. i got from Net,

It depends on the driver implementation. It may go back to database and fetch the next row.
Or all data may come and sit inside the Java memory as objects and can be served.

I have questions on these too... If we go back and forth again and again, it is costly and time consuming operation.
On the otherhand, i twe kepp inside the memory, it may consume all memory and no space for the remaining objects.
In production, there might be chances that because of above reason, we may get lot of 'OutOfMemoryException', is not it?

How doe sit handles in realty. We are using DB2 type 2 driver.
When I try to move across result sets, what happens then?

Please share your thoughts.
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.
Hi,

Requirement: 2 lakhs records are coming from backend.
By considering the performance issues with App server (WebSphere), i am using CachedRowSet. When I am trying to get the records in chunks from the backend, I got SQLException.

Here is the piece of code i am using :

public HashMap getCompanyByAgentWeeklyProdReportRowList() {

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);
crs.setMaxRows(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);

int noOfPagesCount=0;
//while (crs.next()) {
//System.out.println("weekly prod count : "+crs.getDouble("WKLY_PROD_CNT"));
//}

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

// THIS LINE IS GIVING EXCEPTION.
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;
}

Please help me how to solve this?
Can you suggest me any other good solution to solve this problem ( Fetching 2 lakhs of records (DB2) and display them in front end, I do need to use Pagination. In this case, when user hits on page 5, how can I get records from 501 to 600 if number of recrods per page is 100? )

Thanks in advance,
Subhani.