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 needs connection? 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 needs connection?" Watch "ResultSet needs connection?" New topic
Author

ResultSet needs connection?

Jason Allen
Greenhorn

Joined: Jan 25, 2001
Posts: 26
I am creating my first application in Java and JSP. I am querying the database to get a ResultSet, I am then returning this ResultSet to the calling application. However, prior to returning ResultSet, I do a connection.close(). This seems to be killing my ResultSet, I no longer can process through it using next() etc. Is there a way to create a disconnected ResultSet, or am I going about this totally wrong?
I am on AIX, DB2 7.1 and am using JDBC.
Thanks for your help.
Jason.
Thomas Paul
mister krabs
Ranch Hand

Joined: May 05, 2000
Posts: 13974
Yes, closing the Connection will close the ResultSet. remember, the ResultSet doesn't actually contain any data... it only holds a way to get data from the database. Imaging doing a select * from a where a is a table with 100,000,000 rows. Do you really want to copy 100 million rows into your ResultSet?


Associate Instructor - Hofstra University
Amazon Top 750 reviewer - Blog - Unresolved References - Book Review Blog
Peter Tran
Bartender

Joined: Jan 02, 2001
Posts: 783
Thomas,
I'm not sure that this is a written rule with JDBC. We're using JDBC drivers implemented by Merant, and we close the db connection all the time, but the ResultSet still gives us valid data. Hmmm...Then again, we are using connection pooling, so the close() doesn't physically release the DB connection but rather puts the connection object back into the pool of available DB connetions.
Okay...Let me get back with you.
-Peter
Jason Allen
Greenhorn

Joined: Jan 25, 2001
Posts: 26
Thanks for the replies.
If the connection is established in a JavaBean, and you are passing the resultset back to the JSP page, how is the connection maintained? I have made the change in my JavaBean to leave the connection open, I have tested this in Java - Java class instantiation and it works fine. However, when I use the exact same code in JSP, I get a NullPointerException, on the next() statement.
I had read an example in a link posted HERE, someone here at had recommended it. In this case, it is closing the connection once the statement has been executed. I'm guessing it is a difference in the functionality of the driver.
The solution is figuring out how to maintain the connection state between the JavaBean and the JSP. Any ideas would be appreciated.
Thanks,
Jason.
Thomas Paul
mister krabs
Ranch Hand

Joined: May 05, 2000
Posts: 13974
Then again, we are using connection pooling, so the close() doesn't physically release the DB connection but rather puts the connection object back into the pool of available DB connetions.
The danger is that the connection is now available for someone elese to use. If the connection is given to someone else and they run a statement against it, your ResultSet will be closed automatically.
I had read an example in a link posted HERE, someone here at had recommended it.
I guarantee that listing 3 is wrong and will never work.

I have made the change in my JavaBean to leave the connection open, I have tested this in Java - Java class instantiation and it works fine. However, when I use the exact same code in JSP, I get a NullPointerException, on the next() statement.
After you changed the bean, did you restart the server? Without seeing some code, there is no way to determine why your code isn't working.
Jason Allen
Greenhorn

Joined: Jan 25, 2001
Posts: 26
Here is the simple code I am trying to get to work.
Let me know if you see something.
Thanks,
Jason.
****************************************
JAVA CLASS CODE:
****************************************
import java.io.*;
import java.sql.*;
import java.util.*;
import COM.ibm.db2.jdbc.net.*;
// A JavaBean that queries a database. For use in JavaServer Pages.
public class db2test implements Serializable {
public db2test() {} // a no argument constructor
// public method to submit query to database and return resultset
public ResultSet executeQuery() {
// connect to database
Connection con = null;
ResultSet rs = null;
try {
// Load your JDBC Driver class file
Class.forName("COM.ibm.db2.jdbc.net.DB2Driver").newInstance();
// Connect to Database
con = DriverManager.getConnection("jdbc:db2:dhswd");
// Create and Execute a query
String queryString = ("SELECT *");
queryString += (" FROM metam.fieldcat ");
Statement stmt = con.createStatement();
rs = stmt.executeQuery(queryString);
}
catch (SQLException sqle) {
System.err.println(sqle.getMessage());
}
catch (ClassNotFoundException cnfe) {
System.err.println(cnfe.getMessage());
}
catch (Exception e) {
System.err.println(e.getMessage());
}
return rs;// the ResultSet is sent back to the JSP
} // end of executeQuery
} //EOF
*******************************************
JSP Code, Running on Apache Tomcat
*******************************************
<HTML>
<HEAD>
<TITLE>Appointment Schedule<TITLE>
</HEAD>
<%@ page language="java" import="java.sql.*,db2test" errorPage="errorpage.jsp"%>
<jsp:useBean id="db2test" scope="session" class="db2test" />

<Table>
<TR><TD>
<%! ResultSet rs; %>
<%
try {
rs = db2test.executeQuery();
rs.next(); // <--- Seems to bomb here with NullPointer Exception<br /> } // Code has been removed for simplification<br /> catch(SQLException sqle){<br /> System.err.println(sqle.getMessage());<br /> }<br /> %>
</TD>
</TR>
</Table>
</BODY>
</HTML>

[This message has been edited by Thomas Paul (edited January 30, 2001).]
Thomas Paul
mister krabs
Ranch Hand

Joined: May 05, 2000
Posts: 13974
I noticed that if any Exceptions are thrown, all you do is print a message to System.err and then return a null ResultSet to the JSP. This probably is not a good way to handle exceptions and could be the cause of your problem.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: ResultSet needs connection?
 
Similar Threads
why Invalide cursor state exception
BaseResultSet being returned instead of a resultSet
How to close the statement object in the method that returns a resultset
Problem with rs.isAfterLast() and rs.isFirst()
SQL Server 2008 date problem