• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Resultset processes 1 record

 
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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();
}

}
}
 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sorry! I thought the problem was in the DBHandler class. Here is the code

 
Marshal
Posts: 79153
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Have you really got that many columns in one table? I wouldn't be happy about a table that large.
 
Michael McAuliffe
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, the table is a working table, used to validate the information. It than updates other tables using database APIs.
 
Campbell Ritchie
Marshal
Posts: 79153
377
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
So you update the data in other tables.
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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
 
Michael McAuliffe
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you for the help. As a newbie, that is learning OTJ it is a huge learning curve.
 
Shailesh Chandra
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 ]
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic