File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes ResultSet is getting closed.. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "ResultSet is getting closed.." Watch "ResultSet is getting closed.." New topic
Author

ResultSet is getting closed..

Bansal Mahesh
Greenhorn

Joined: Sep 23, 2011
Posts: 10
Hi,
In my app, ResultSet is getting closed by following call. None of my code is suppose to do this. Once ResultSet get close, I can't scroll down on page to see more results. (P.S - initially my page only renders 25 records out of 1500 odd records)
Am I missing setting some property while defining datasource.

Env is based on Db2 + WAS
Driver Name - IBM Data Server Driver for JDBC and SQLJ and Version - 4.8.86

Thread Name, Status, Group, Priority, Daemon
WebContainer : 3, Current, Runnable, main, 5,
(j.java:345) com.ibm.db2.jcc.t4.j.b
(lb.java:4288) com.ibm.db2.jcc.am.lb.b
(b.java:3841) com.ibm.db2.jcc.t4.b.b
(db.java:1132) com.ibm.db2.jcc.t4.db.a
(db.java:1049) com.ibm.db2.jcc.t4.db.k
(db.java:260) com.ibm.db2.jcc.t4.db.h
(db.java:168) com.ibm.db2.jcc.t4.db.c
(r.java:42) com.ibm.db2.jcc.t4.r.c
(b.java:2564) com.ibm.db2.jcc.t4.b.Hb
(lb.java:1188) com.ibm.db2.jcc.am.lb.t
(lb.java:1149) com.ibm.db2.jcc.am.lb.r
(b.java:4830) com.ibm.db2.jcc.t4.b.r
(lb.java:1124) com.ibm.db2.jcc.am.lb.q
(lb.java:1112) com.ibm.db2.jcc.am.lb.rollback
(bf.java:176) com.ibm.db2.jcc.am.bf.rollback
(WSRdbManagedConnectionImpl.java:4760) com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl.cleanupTransactions
(WSRdbManagedConnectionImpl.java:4010) com.ibm.ws.rsadapter.spi.WSRdbManagedConnectionImpl.cleanup
(MCWrapper.java:1531) com.ibm.ejs.j2c.MCWrapper.cleanup
(FreePool.java:508) com.ibm.ejs.j2c.FreePool.returnToFreePool
(PoolManager.java:1853) com.ibm.ejs.j2c.PoolManager.release
(MCWrapper.java:2378) com.ibm.ejs.j2c.MCWrapper.releaseToPoolManager
(LocalTransactionWrapper.java:1251) com.ibm.ejs.j2c.LocalTransactionWrapper.afterCompletion
(LocalTranCoordImpl.java:1705) com.ibm.ws.LocalTransaction.LocalTranCoordImpl.driveSynchronization
(LocalTranCoordImpl.java:1599) com.ibm.ws.LocalTransaction.LocalTranCoordImpl.informSynchronizations
(LocalTranCoordImpl.java:1316) com.ibm.ws.LocalTransaction.LocalTranCoordImpl.cleanup
(LocalTranCoordImpl.java:1428) com.ibm.ws.LocalTransaction.LocalTranCoordImpl.end
(WebAppTransactionCollaboratorImpl.java:332) com.ibm.ws.webcontainer.collaborator.WebAppTransactionCollaboratorImpl.postInvoke
(ServletWrapper.java:1244) com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest
(ServletWrapper.java:502) com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest
(ServletWrapperImpl.java:179) com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest
(CacheServletWrapper.java:91) com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest
(WebContainer.java:864) com.ibm.ws.webcontainer.WebContainer.handleRequest
(WSWebContainer.java:1583) com.ibm.ws.webcontainer.WSWebContainer.handleRequest
(WCChannelLink.java:186) com.ibm.ws.webcontainer.channel.WCChannelLink.ready
(HttpInboundLink.java:452) com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination
(HttpInboundLink.java:511) com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewRequest
(HttpInboundLink.java:305) com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.processRequest
(HttpICLReadCallback.java:83) com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete
(AioReadCompletionListener.java:165) com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted
(AbstractAsyncFuture.java:217) com.ibm.io.async.AbstractAsyncFuture.invokeCallback
(AsyncChannelFuture.java:161) com.ibm.io.async.AsyncChannelFuture.fireCompletionActions
(AsyncFuture.java:138) com.ibm.io.async.AsyncFuture.completed
(ResultHandler.java:204) com.ibm.io.async.ResultHandler.complete
(ResultHandler.java:775) com.ibm.io.async.ResultHandler.runEventProcessingLoop
(ResultHandler.java:905) com.ibm.io.async.ResultHandler$2.run
(ThreadPool.java:1604) com.ibm.ws.util.ThreadPool$Worker.run

thanks in advance

Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2476
    
    7

Welcome to JavaRanch, Bansal!


OCUP UML fundamental and ITIL foundation
youtube channel
Bansal Mahesh
Greenhorn

Joined: Sep 23, 2011
Posts: 10
ny clue guys.. what's going on here .

Thanks in advance
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 36478
    
  16
Is it closed in the Java™ code? Please show us how you are using that ResultSet.
Bansal Mahesh
Greenhorn

Joined: Sep 23, 2011
Posts: 10
Hi Ritchie,
Showing code of using resultSet would be too complicate for me. I have an application build using f/w. This issue only coming up when we deploy this app on WAS and use DB2 as backend.
I have isolate the stack trace ( which I have printed earlier), which is closing the result set.
We are creating PreparedStatement using stmt = mjdbcConnection.prepareStatement(str); where mjdbcConnection is of type "com.ibm.db2.jcc.am.bf" , and setting setMaxRows(0) on it.
After query executed, I am calling clearParameters() on this statement.

Once this all been done, I see IBM code (stack pasted earlier) comes into picture and closing the RS. I have tried setting "autocommit = false" as custom property for my datasouce defined in WAS , but that also doesn't seems to be solving the issue.
Does anything looks suspicious.
Any inputs will be highly appreciated.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 36478
    
  16
And giving an answer without seeing the code would be too complicated for us.
Bansal Mahesh
Greenhorn

Joined: Sep 23, 2011
Posts: 10
Hi Ritchie,
Totally agree. But looking at stack trace, would it be possible to guess why that might be happening. I don't see a reason why cleanupTransactions or rollback should get invoke at very first place.

thanks
Bansal Mahesh
Greenhorn

Joined: Sep 23, 2011
Posts: 10
After putting efforts, somehow I managed to replicate what's happening in my environment. Please see the sample custom method below :-

private void customMethod1() {
try {
javax.naming.Context ctx = new InitialContext();
DataSource ds = (DataSource) ctx.lookup("jndi/db2ds");
if(connection == null)
{
connection = ds.getConnection();
}
boolean autoCommitFlag = true;
connection.setAutoCommit(autoCommitFlag);

PreparedStatement mPreparedStatement2 = connection.prepareStatement("select * from WC_AS_CUSTATTR");
mPreparedStatement2.setMaxRows(0);
ResultSet rs2 = mPreparedStatement2.executeQuery();
rs = rs2;
for(int i =0 ;i < 25 ; i++)
{
rs2.next();
}
}
catch(Exception ex) {
ex.printStackTrace();
}
}

In my WAS server, I have defined a datasource with jndi name "jndi/db2ds" . I have bound this custom method with a Button on my page. When I click on Button, this code executes . Once the execution of this method finishes, I see the below printed stack trace ( totally from IBM stack) executes and tries to cleanup/rollback , during with , its closing the ResultSet rs2.

Any thoughts what's going on here. Please let me know if you need any further information.

Thanks in advance.
Bansal Mahesh
Greenhorn

Joined: Sep 23, 2011
Posts: 10
please ignore rs = rs2; line. I have defined rs as a class level member variable.
thanks
Bansal Mahesh
Greenhorn

Joined: Sep 23, 2011
Posts: 10
ny updates guys ?
I am looking for some setting which enable hang on to the connection across multiple requests.

thanks in advance.
amit punekar
Ranch Hand

Joined: May 14, 2004
Posts: 507
Hello,
There is not much information that I can gather from the code you have given.
Even though I feel
1) You should not keep the resultset open across two calls. Better if you fetch first 25 rows and then next one when "button" is clicked.
2) Not sure if you are using JSP or something else and how your JDBC code is being called. This becomes important as you have mentioned that you "rs" declared as instance level variable.

Can you try putting down pseudo code which might give more insight to this issue?

Regards,
Amit
Bansal Mahesh
Greenhorn

Joined: Sep 23, 2011
Posts: 10
Hi Amit,
Firstly , thanks for looking into the matter. Let me try to describe my requirement.

I have a jsp page having a button. On the click action of this button, I need to fetch only 25 rows at a time.

Step a) User click on the button first time. In my custom code which gets call on button click event, I look a datasource and create a new connection, followed by creating a PreparedStatement. Once done, I execute a query and build a Result Set. From this ResultSet, I fetched the initial 25 records only.

Step b) Now when user click the button second time, I want to fetch the next 25 records ( i.e from 26-50 rows) from the previously build ResultSet.

The problem I am facing is, between the two click events of button ( which happens in two different request), cleanup / rollback code from IBM is getting executed ( Please see the stack trace pasted in initial post). This ends up into closing the RS.
Now when we try to perform Step b), as RS has already been closed, hence not able to fetch the next 25 records.

I hope, I've made myself clear. Please let me know if you need any further inputs. Lets not worry about "rs" declared as instance level variable. That was just a typo.

Thanks in advance
amit punekar
Ranch Hand

Joined: May 14, 2004
Posts: 507
Hello there,
I have one question here though -

Where are you storing the Resultset on server side ? When user is going to click the button second time, new request is initiated to the server. Are you holding up this resultset somewhere in the HTTPSession for that user?


regards,
Amit


Bansal Mahesh
Greenhorn

Joined: Sep 23, 2011
Posts: 10
Hi Amit,
I have a backing bean at session scope. I have storing my ResultSet in it. My requirement is that which each request , I only fetch 25 set of records from ResultSet, but immaturely closing of RS in WAS environment is stopping me from achieving my requirement.

Thanks,
Mahesh
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ResultSet is getting closed..
 
Similar Threads
Are arrays supported by DB2 Driver for WebSphere
InvalidAlgorithmParameterException
InvalidAlgorithmParameterException
J2CA0081E: Method cleanup failed while trying to execute method cleanup on ManagedConnection
Hibernate error -result set not closed.