This week's book giveaway is in the Cloud/Virtualization forum.
We're giving away four copies of Grokking Bitcoin and have Kalle Rosenbaum on-line!
See this thread for details.
Win a copy of Grokking Bitcoin this week in the Cloud/Virtualization forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Paul Clapham
  • Devaka Cooray
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Frits Walraven
Bartenders:
  • Carey Brown
  • salvin francis
  • Claude Moore

Resultset processes 1 record  RSS feed

 
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • 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();
}

}
}
 
Sheriff
Posts: 24374
55
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • 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
  • Quote
  • Report post to moderator
Sorry! I thought the problem was in the DBHandler class. Here is the code

 
Marshal
Posts: 64157
215
  • Mark post as helpful
  • send pies
  • 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
  • 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: 64157
215
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
So you update the data in other tables.
 
Ranch Hand
Posts: 1087
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • 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
  • 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
Java Oracle Spring
  • Mark post as helpful
  • send pies
  • 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 ]
 
Would you turn that thing down? I'm controlling a mind here! Look ... look at the tiny ad ...
Create Edit Print & Convert PDF Using Free API with Java
https://coderanch.com/wiki/703735/Create-Convert-PDF-Free-Spire
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!