Resultset processes 1 record

[Michael McAuliffe]

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]

That depends on what you do while processing the rows. All we can see of that code right now is this:

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

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]

Sorry! I thought the problem was in the DBHandler class. Here is the code

// Validate the input in CHQ_Resources table
private static void validateData() {
 
//  Declare the JDBC objects
 
Statement stmt = null;
ResultSet rs = null;
ResultSet count = null;
String errorcodes = "";
String SQL = null;
String ResourceId;

// Get current date and format it to month day year and time
Date currentTime = new Date();
DateFormat formatDate = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
String date = formatDate.format(currentTime);
 

// Define variable names for the CHQ_Resources table
String ChrisResourceId = null;
String Address = null;
String AnnualTargetHours = null;
String BaseCurrency = null;
String CertificationCycle = null;
String CertificationHours = null;
String City = null;
String Country = null;
String EmailAddress = null;
String EmployeeType = null;
String ExpenseApprover = null;
String FirstName = null;
String FirstPayroll = null;
String GlobalWorkgroup = null;
String HireDate = null;
String HourlyCostRate = null;
String HoursPerDay = null;
String Language = null;
String LastName = null;
String Location = null;
String MiddleName = null;
String NTUserId =  null;
String OfficeNumber = null;
String OfficeNumberExt = null;
String PayrollCycle = null;
String PayrollLagDays = null;
String PostalCode = null;
String Province = null;
String ResourceRole = null;
String ResourceText1 =  null;
String ResourceText2 = null;
String ResourceText3 = null;
String ResourceType = null;
String ReportsTo = null;
String TerminationDate = null;
String TimeApprover = null;
String Title = null;
String UserDefinedResourceId =  null;
String Workgroup = null;
String WebPassword = null;
String InterfaceStatus = null;
String InterfaceDate = null;
String ErrorCodes = null;
String ResourceText6 = null;
String ReportsToId  = null;
 


try {
//  Establish the connection
DBHandler dbHandler = DBHandler.getInstance();
dbHandler.connect();


//  Create and execute an SQL statement that returns some data
SQL = "Select * from chq_resources where interfacedate is null";
rs = dbHandler.executeQuery(SQL);

// Process the rows
while (rs.next()) {
// Reset errorcodes for record
errorcodes = "";
// set table variables
ChrisResourceId = rs.getString(1);
Address = rs.getString(2);
AnnualTargetHours = rs.getString(3);
BaseCurrency = rs.getString(4);
CertificationCycle = rs.getString(5);
CertificationHours = rs.getString(6);
City = rs.getString(7);
Country = rs.getString(8);
EmailAddress = rs.getString(9);
EmployeeType = rs.getString(10);
ExpenseApprover = rs.getString(11);
FirstName = rs.getString(12);
FirstPayroll = rs.getString(13);
GlobalWorkgroup = rs.getString(14);
HireDate = rs.getString(15);
HourlyCostRate =  rs.getString(16);
HoursPerDay = rs.getString(17);
Language = rs.getString(18);
LastName = rs.getString(19);
Location = rs.getString(20);
MiddleName = rs.getString(21);
NTUserId = rs.getString(22);
OfficeNumber = rs.getString(23);
OfficeNumberExt = rs.getString(24);
PayrollCycle = rs.getString(25);
PayrollLagDays = rs.getString(26);
PostalCode = rs.getString(27);
Province = rs.getString(28);
ResourceRole = rs.getString(29);
ResourceText1 = rs.getString(30);
ResourceText2 = rs.getString(31);
ResourceText3 = rs.getString(32);
ResourceType = rs.getString(33);
ReportsTo = rs.getString(34);
TerminationDate = rs.getString(35);
TimeApprover = rs.getString(36);
Title = rs.getString(37);
UserDefinedResourceId = rs.getString(38);
Workgroup = rs.getString(39);
WebPassword = rs.getString(40);
InterfaceStatus = rs.getString(41);
InterfaceDate = rs.getString(42);
ErrorCodes = rs.getString(43);
ResourceText6 = rs.getString(44);
ReportsToId = rs.getString(45);


//Multiple records for the same resource within a a single source file
System.out.println("Return row = " + UserDefinedResourceId);
SQL = "SELECT count(*) FROM CHQ_resources " +
"WHERE userdefinedresourceid = '" + UserDefinedResourceId + "'";
 
count = dbHandler.executeQuery(SQL);
count.next();
//System.out.println("Row count is " + count.getInt(1));
if (count.getInt(1) > 1) {
errorcodes = errorcodes + "10,";
}
  //No resource ID found in source data
if((UserDefinedResourceId == null) || (UserDefinedResourceId.equals(""))) {
errorcodes = errorcodes + "104,";
}
//No matching country code found in Changepoint Country table
if ((Country == null)|| (Country.equals(""))) {
errorcodes = errorcodes + "401,";
} 
else {
SQL = "Select Code from Country Where Code = '" + Country + 
"' and Deleted = 0";
count = dbHandler.executeQuery(SQL);
 
if (!count.next()) {
errorcodes = errorcodes + "23,";
}
}
     //No NTUserId found in source data
//System.out.println("NTUserID = " + NTUserId);
if ((NTUserId == null ) || (NTUserId.equals(""))){
errorcodes = errorcodes + "24,";
}
  //First Name required
if ((FirstName == null) || (FirstName.equals(""))) {
errorcodes = errorcodes + "106,";
}
else {
   //First name cannot contain [ or ]
if ((FirstName.indexOf("[") > -1) || (FirstName.indexOf("]") > -1)) {
errorcodes = errorcodes + "107,";
}
}
   //Last Name required
if ((LastName == null) || (LastName.equals(""))) {
errorcodes = errorcodes + "108,";
}
else {
   //Last name cannot contain [ or ]
if ((FirstName.indexOf("[") > -1) || (FirstName.indexOf("]") > -1)) {
errorcodes = errorcodes + "109,";
}
}
   //Middle Name cannot contain [ or ]
if ((MiddleName == null) || (MiddleName.equals(""))) {
// Nothing to do, no error
}
else {
if ((MiddleName.indexOf("[") > -1) || (MiddleName.indexOf("]") > -1)){
errorcodes = errorcodes + "110,";
}
}
   //A resource with the same name already exists in CSV file
SQL = "Select Count(*) From CHQ_Resources where FirstName = '" +
FirstName + "' And LastName = '" + LastName + "' ";
System.out.println("Value of SQL is " + SQL);
count = dbHandler.executeQuery(SQL);
count.next();
if (count.getInt(1) > 1) {
errorcodes = errorcodes + "111,";

}
   // A resource with the same name exists on the Resources table
SQL = "Select Count(*) From Resources where FirstName = '" +
FirstName + "' And LastName = '" + LastName + "' " +
"And Deleted = 0 And UserDefinedResourceId <> '" + UserDefinedResourceId + "' ";
count = dbHandler.executeQuery(SQL);
 
count.next();
if (count.getInt(1) > 0) {
errorcodes = errorcodes + "111,";
}
    //A resource with the same NT login already exists in CSV file
SQL = "Select Count(*) From CHQ_Resources where NTUserId = '" +
NTUserId + "' ";
count = dbHandler.executeQuery(SQL);
count.next();
if (count.getInt(1) > 1) {
errorcodes = errorcodes + "113,";
}
     //A resource with the same NT login already exists in Resources table
SQL = "Select Count(*) From SQLLogin s join Resources r " + 
"on s.ResourceId = r.ResourceId Where LoginId = '" + NTUserId +
"' And Deleted = 0 And UserDefinedResourceId = '" + UserDefinedResourceId + "' ";
count = dbHandler.executeQuery(SQL);
count.next();
if (count.getInt(1) > 0 ) {
errorcodes = errorcodes + "112,";
}
//Email address has been chosen by another resource on CSV file
SQL = "Select Count(*) from CHQ_Resources Where EmailAddress = '" +
EmailAddress + "' ";
count = dbHandler.executeQuery(SQL);
count.next();
//System.out.println("Value of count is " + count.getInt(1));
if (count.getInt(1) > 1) {
errorcodes = errorcodes + "114,";
}
//Email address has been chosen by another resource on ResourceAddress table
SQL = "Select Count(*) from ResourceAddress ra Join Resources r " +
"on ra.ResourceId = r.ResourceId Where EmailAddress = '" + EmailAddress +
"' And Deleted = 0 And UserDefinedResourceId != '" + UserDefinedResourceId + 
"' And EmailAddress != null";
count = dbHandler.executeQuery(SQL);
count.next();
if (count.getInt(1) > 0) {
errorcodes = errorcodes + "114,";
}
   //Base currency is required
if ((BaseCurrency == null) || (BaseCurrency.equals(""))) {
errorcodes = errorcodes + "121,";
}
  //State is required
if ((Province == null) || (Province.equals(""))) {
errorcodes = errorcodes + "400,";
}
   //Country is required
if ((Country == null) || (Country.equals(""))) {
errorcodes = errorcodes + "401,";
}
   //Annual Target Hours is required
if ((AnnualTargetHours == null) || (AnnualTargetHours.equals(""))) {
errorcodes = errorcodes + "402,";
}
  //Certification Cycle is required
if ((CertificationCycle == null) || (CertificationCycle.equals(""))) {
errorcodes = errorcodes + "403,";
}
   //Certification Hours is required
if ((CertificationHours == null) || (CertificationHours.equals(""))) {
errorcodes = errorcodes + "404,";
}
   //First Payroll required
if ((FirstPayroll == null) || (FirstPayroll.equals(""))){
errorcodes = errorcodes + "405,";
}
   //Hourly Cost Rate is required
if ((HourlyCostRate == null) || (HourlyCostRate.equals(""))) {
errorcodes = errorcodes + "406,";
}
  //Hours Per Day is required
if ((HoursPerDay == null) || (HoursPerDay.equals(""))) {
errorcodes = errorcodes + "407,";
}
   //Payroll Cycle is required
if ((PayrollCycle == null) || (PayrollCycle.equals(""))) {
errorcodes = errorcodes + "408,";
}
  //Payroll Lag Days is required
if ((PayrollLagDays == null) || (PayrollLagDays.equals(""))) {
errorcodes = errorcodes + "409,";
}
   //Resource Type is required
if ((ResourceType == null) || (ResourceType.equals(""))) {
errorcodes = errorcodes + "410,";
}
  //Employee Type is required
if ((EmployeeType == null) || (EmployeeType.equals(""))) {
errorcodes = errorcodes + "411,";
}
   //Email Address cannot contain /
if (EmailAddress.indexOf("/") > -1) {
errorcodes = errorcodes + "412,";
}
   //The ResourceText6 must be specified for a resource of type sub-contractor
if (((ResourceText6 == null) || (ResourceText6.equals(""))) && EmployeeType.equals("CO")) {
errorcodes = errorcodes + "414,";
}
   //The ResourceText6 must not be specified for a resource if it is not of type sub-contractor
if ((ResourceText6 != null ) && (EmployeeType.equals("CO"))) {
errorcodes = errorcodes + "416,";
}
// Terminated Resource has active projects
if ((TerminationDate != null) && (!TerminationDate.equals("1900-01-01 00:00:00.0") )) {
SQL="Select r.resourceid from chq_resources cr " +
"join resources r on r.userdefinedresourceid = cr.userdefinedresourceid " +
"where cr.userdefinedresourceid = '" + UserDefinedResourceId + "'";
count = dbHandler.executeQuery(SQL);
count.next();
ResourceId = count.getString(1);
SQL ="Select Count(*) from ManageMember mm  " +
"join Project p on p.projectid = mm.projectid " +
"where mm.resourceid = '" + ResourceId + "' " +
"And ProjectStatus = 'A'"; 
count = dbHandler.executeQuery(SQL);
count.next();
//System.out.println("Value of SQL is " + SQL);
//System.out.println("Value of count is " + count.getInt(1));
if (count.getInt(1) > 0) {
errorcodes = errorcodes + "415,";
}
}
// Terminated Resource is a ReportsTo
if ((TerminationDate != null) && (!TerminationDate.equals("1900-01-01 00:00:00.0") )) {
SQL="Select r.resourceid from chq_resources cr " +
"join resources r on r.userdefinedresourceid = cr.userdefinedresourceid " +
"where cr.userdefinedresourceid = '" + UserDefinedResourceId + "'";
count = dbHandler.executeQuery(SQL);
count.next();
ResourceId = count.getString(1);
System.out.println(ResourceId);
SQL ="Select count(*) from resources " +
"where reportsto = '" + ResourceId  + "' " +
"and TerminationDate is null";;
count = dbHandler.executeQuery(SQL);
count.next();
//System.out.println("Value of SQL is " + SQL);
//System.out.println("Value of count is " + count.getInt(1));
if (count.getInt(1) > 0) {
errorcodes = errorcodes + "417,";
}
}
// Update CHQ_Resources table with errorcodes
   if (errorcodes.equals("")) {
   errorcodes = "0";
   InterfaceStatus = "0";
   }
   else {
   InterfaceStatus = null;
   }
   SQL = "Update CHQ_Resources Set ErrorCodes = '" + errorcodes +
   "', InterfaceDate = '" + date + "' Where ChrisResourceId = '" + ChrisResourceId + "' ";
//   System.out.println("SQL = " + SQL);
   dbHandler.executeUpdate(SQL);

System.out.println("errorcodes = " + errorcodes);
}
//dbHandler.disconnect();
}
catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} 
finally {
 
}
 
}

[Campbell Ritchie]

Have you really got that many columns in one table? I wouldn't be happy about a table that large.

[Michael McAuliffe]

Yes, the table is a working table, used to validate the information. It than updates other tables using database APIs.

[Campbell Ritchie]

So you update the data in other tables.

[Shailesh Chandra]

DBHandler dbHandler = DBHandler.getInstance();
.
.
rs = dbHandler.executeQuery(SQL);
while (rs.next()) {
.
.
.
count = dbHandler.executeQuery(SQL);
count.next();
.
.
.
}
 

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

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

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]

Thank you for the help. As a newbie, that is learning OTJ it is a huge learning curve.

[Shailesh Chandra]

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 ]