aspose file tools*
The moose likes JDBC and the fly likes Resultset processes 1 record Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Resultset processes 1 record" Watch "Resultset processes 1 record" New topic
Author

Resultset processes 1 record

Michael McAuliffe
Greenhorn

Joined: Aug 22, 2008
Posts: 14
Being a newbie to Java coding, I'm stump on what is causing this problem. I execute a query and able to process 1 record from the result set. When it attempts to get 2nd record, it state that the result set is closed.

What did I do incorrect?

Calling code

DBHandler dbHandler = DBHandler.getInstance();
dbHandler.connect();

SQL = "Select * from chq_resources where interfacedate is null";
rs = dbHandler.executeQuery(SQL);

// Process the rows
while (rs.next()) {

Database connection code

import java.sql.*;


public class DBHandler {
// the instance
private static DBHandler dbHandler = new DBHandler();
// Declare the JDBC objects
Connection con = null;
Statement s = null;
ResultSet resultset = null;
// private so no one can access it externally
DBHandler() {

}

public static DBHandler getInstance() {
return dbHandler;
}

// connect to DB
public void connect() {
try {
String connectionUrl =
"jdbc:sqlserver://dtw-xxxxxx:1433;databaseName=xxxxxxx;" +
"user=xxxxx;password=xxxxx";

// Establish the connection
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
con = DriverManager.getConnection(connectionUrl);

s = con.createStatement();
}
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public ResultSet executeQuery(String query) {
try {
resultset = s.executeQuery(query);
}
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return resultset;

}
public void executeUpdate(String query) {
try {
s.executeUpdate(query);
}
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
public void disconnect() {
// Close the connection
try {
s.close();
con.close();
}
catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

}
}
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

That depends on what you do while processing the rows. All we can see of that code right now is this:
And that doesn't tell us anything. Most likely inside that loop you do something that causes the result set to be closed.
Michael McAuliffe
Greenhorn

Joined: Aug 22, 2008
Posts: 14
Sorry! I thought the problem was in the DBHandler class. Here is the code

Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38881
    
  23
Have you really got that many columns in one table? I wouldn't be happy about a table that large.
Michael McAuliffe
Greenhorn

Joined: Aug 22, 2008
Posts: 14
Yes, the table is a working table, used to validate the information. It than updates other tables using database APIs.
Campbell Ritchie
Sheriff

Joined: Oct 13, 2005
Posts: 38881
    
  23
So you update the data in other tables.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081



No wonder if you have got this error
As Per java docs of ResultSet


A ResultSet object is automatically closed by the Statement object that generated it when that Statement object is closed, re-executed, or is used to retrieve the next result from a sequence of multiple results.



Just Take a look at your DBHandler Class



The statement here is a Class level variable, which means it will be shared among requests, and every new execution will close the previos resultset.

Hope now you have found the problem.

Thanks
Shailesh


Gravitation cannot be held responsible for people falling in love ~ Albert Einstein
Michael McAuliffe
Greenhorn

Joined: Aug 22, 2008
Posts: 14
Thank you for the help. As a newbie, that is learning OTJ it is a huge learning curve.
Shailesh Chandra
Ranch Hand

Joined: Aug 13, 2004
Posts: 1081

Originally posted by Michael McAuliffe:
As a newbie, that is learning OTJ it is a huge learning curve


All the best , ranchers are always around to help you in learning.

Shailesh
[ October 24, 2008: Message edited by: Shailesh Chandra ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Resultset processes 1 record