Win a copy of Terraform in Action this week in the Cloud 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
Sheriffs:
  • Jeanne Boyarsky
  • Rob Spoor
  • Bear Bibeault
Saloon Keepers:
  • Jesse Silverman
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • Al Hobbs
  • salvin francis

Need help with a JDBC error

 
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Im getting this error can anybody solve me

error; Column Index out of range SQLException range :4 column 3

private List<EtsEmployeeAdditionalAttribute> listEmployeeCostCentersOfDate(
List<Integer> stwdIdList, Date date)
throws STWInternalServerException

{
Connection conn = null;
PreparedStatement stmt = null;
StringBuffer sb = new StringBuffer();

sb.append(" select t1.* from ( select * from employee_additional_attributes where stwid in (1,2,3,4) ) ");
sb.append(" as t1 join ( select stwid, max(employee_additional_attribute_id) as employee_additional_attribute_id from employee_additional_attributes ");
sb.append(" where stwid in (?) and mis_attribute_id = (?) and start_time <= date(?) group by stwid ) ");
sb.append(" as t2 using ( stwid, employee_additional_attribute_id ); ");

List<EtsEmployeeAdditionalAttribute> costCenterList = new ArrayList<EtsEmployeeAdditionalAttribute>();
int i = 1;
try {
conn = STWTransactionManager.get(Thread.currentThread().getId());
stmt = conn.prepareStatement(sb.toString());

stmt.setString(i++, StringUtils.prepareSqlArray(stwdIdList));
stmt.setString(i++, StringUtils.prepareSqlArray(stwdIdList));
stmt.setInt(i++, MISAttributeTypeConstants.EMPLOYEE_COST_CENTER);
stmt.setDate(i++, new java.sql.Date(date.getTime()));

ResultSet rs = stmt.executeQuery();

while (rs.next()) {

{
EtsEmployeeAdditionalAttribute employeeCostCenter = new EtsEmployeeAdditionalAttribute(
rs.getInt("stwid"), rs.getInt("mis_attribute_id"),
rs.getString("attribute_value"),
rs.getDate("start_time"), rs.getDate("end_time"));
costCenterList.add(employeeCostCenter);
}
}
return costCenterList;
} catch (SQLException ex) {
throw new STWInternalServerException(ex);
}
}
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have no idea what your code is trying to achieve, but I would suggest you start by de-bugging your SQL statement (or simply print it out) so you see what SQL you are actually executing, and which columns are being returned. Take the actual SQL this program is generating and run it using your database's SQL shell to make sure it works correctly, and you'll also be able to see what columns and rows are returned. Once you are sure your SQL is correct, it will be much easier to integrate it with your Java code correctly.

Also it's usually best to avoid "SELECT * FROM..." because you don't know if your DBA has added or removed columns in the table or changed their names. Specify the columns you want to retrieve, so you can trace errors more easily at runtime e.g. the database will tell you if a specified column doesn't exist, and also so that other programmers can see what you're trying to do in your code.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic