Win a copy of Svelte and Sapper in Action this week in the JavaScript 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
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Need help with a JDBC error

 
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);
}
}
 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • 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.
 
machines help you to do more, but experience less. Experience this tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic