Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Need help with a JDBC error

 
Ramesh Kumar Koyya
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • 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);
}
}
 
chris webster
Bartender
Posts: 2407
33
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • 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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic