Batch update in EJB

[Sri Rangan]

I am trying to make a batch update work in EJB.
The following sample code works fine inside EJB:

 
public void test() {
PreparedStatement ps = null;
Connection con = null;
try {
con = jdbcFactory.getConnection();
ps =
con.prepareStatement(
" insert into test_table values( ?,?,?,?,?,?,?,? ) ");
ps.setInt(1, 1);
ps.setInt(2, 1);
ps.setInt(3, 1);
ps.setInt(4, 1);
ps.setInt(5, 1);
ps.setInt(6, 1);
ps.setInt(7, 1);
ps.setInt(8, 1);
ps.addBatch();
ps.setInt(1, 2);
ps.setInt(2, 2);
ps.setInt(3, 2);
ps.setInt(4, 2);
ps.setInt(5, 2);
ps.setInt(6, 2);
ps.setInt(7, 2);
ps.setInt(8, 2);
ps.addBatch();
int[] updateCounts = ps.executeBatch();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
ps.close();
con.close();
} catch (Exception e) {
// TODO: handle exception
}
}
} 

But the foll0wing code causes "invalid batch command" Exception. I am not able to understand what is going wrong. The only difference here is I am creating the arraylist of objects and looping thru the array list to create my batch.

 
public void runBatch(ArrayIntList ids) {
ArrayList listofobjects = new ArrayList();
int size = ids.size();
for (int i = 0; i < size; i++) {
                     // For each ID call calculate () which
                     // goes to database to get some data 
                    // and return the LIST of ResultObject 
List result = calculate(ids.get(i), true);
for (int j = 0; j < result.size(); j++) {
ResultObject bucket = (ResultObject ) result.get(j);
listofobjects.add(bucket);
}
}
                Connection con = null;
PreparedStatement stmt = null;
try {
con = jdbcFactory.getConnection();
stmt = con.prepareStatement(QUERY_RET_DETAILS);
for (int k = 0; k < listofobjects.size(); k++) {
ResultObject buck = (ResultObject )  listofobjects.get(k);
StringBuffer sb = new StringBuffer();
stmt.setInt(1, 1);
stmt.setInt(2, buck.getAbId());
stmt.setInt(3, buck.getDenominator());
stmt.setInt(4, buck.getNumerator());
stmt.setInt(5, buck.getDenominatorExclude());
stmt.setInt(6, buck.getDenominatorMissing());
stmt.setInt(7, buck.getNumeratorExclude());
stmt.setInt(8, 1);
stmt.addBatch();
}
System.out.println("START BATCH " + new java.util.Date());
int[] updateCounts = stmt.executeBatch();
System.out.println("END BATCH " + new java.util.Date());
} catch (Exception ex) {
System.out.println("BATCH UPDATE FAILED: " + ex.getMessage());
} finally {
try {
stmt.close();
con.close();
} catch (Exception e) {
}
}
} 

Thanks for your help

[Sri Rangan]

In fact i am getting the "java.sql.Exception: missing in or out parameter at index:: 1"
But it seems I am setting all the parameter correctly.

[Jamie Robertson]

Originally posted by Sri Rangan:
In fact i am getting the "java.sql.Exception: missing in or out parameter at index:: 1"
But it seems I am setting all the parameter correctly.

you'll have to let us know what SQL is prepared ( QUERY_RET_DETAILS ). Otherwise, it looks fine.
Jamie