Hi All,
I am trying to do batch update with CallableStatement.
First I set autocommit off.
Then in the while loop,I add the batch statement to my CallableStatement. After the loop I call executeBatch on CallableStatement.
At the end I do a commit.
Eventhough I am adding multiple records for batch update, only 1 record is updated. I verified in the database that,it is updating only one record no matter how many records I set for batch update.

Did anyone run into same issue?
Thanks

Hi,

I have never had any such problem with batch update.
Check the values you are trying to insert - are they legal i.e. ensure that they are not violating any db constraint e.g. inserting duplicate records. That is the only possible reason I can think of at the moment since you are not getting any exception from the java end.
Are you giving a statement.execute() or statement.executeBatch()...?

Why dont you paste your code, that would make it clearer.

This is an example from the sun's site. I am sure your code must be pretty much the same. but just to verify again:

con.setAutoCommit(false);
Statement stmt = con.createStatement();
stmt.addBatch("INSERT INTO COFFEES " +
"VALUES('Amaretto', 49, 9.99, 0, 0)");
stmt.addBatch("INSERT INTO COFFEES " +
"VALUES('Hazelnut', 49, 9.99, 0, 0)");
stmt.addBatch("INSERT INTO COFFEES " +
"VALUES('Amaretto_decaf', 49, 10.99, 0, 0)");
stmt.addBatch("INSERT INTO COFFEES " +
"VALUES('Hazelnut_decaf', 49, 10.99, 0, 0)");
//** ==========> stmt.executeBatch() <===========
int [] updateCounts = stmt.executeBatch();

cheers!

I seem to have missed that you are trying to do a batch update with CallableStatement.
The CallableStatement interface is used to execute SQL stored procedures.
Use a regular Statment object and just call addBatch on it (as in the example posted above)

I am calling a stored procedure. You can do a batchupdate with CallableStatement too.

Hiya - I dont know why I was under the impression that you were adding regular SQL sts to ur callableStatement ;)
Yes, its true that you can execute stored procs as a batch, by supplying multiple input parameters that would be required by the stored procs.

Here is a bit of working code:
================================
CallableStatement cstmt = con.prepareCall("{call insertEmp(?, ?)}");
cstmt.setString(1, "John Smith");
cstmt.setFloat(2, 35);
cstmt.addBatch();

cstmt.setString(1, "Jane Smith");
cstmt.setFloat(2, 27);
cstmt.addBatch();

int [] updateCounts = cstmt.executeBatch();
================================

Why dont you post a code snippet if theres still a prob.

cheers!
Preeti

First I get the data from another database through the resultset orderDetailsResultSet and then I do the following

conn.setAutoCommit(false);
while(orderDetailsResultSet.next())
{
addOrderDetailStatement = conn.prepareCall("begin
ORDER_PROCESS.add_order_detail( ?, ?, ?, ? ,? ,?); end;" );
addOrderDetailStatement.setString(1,orderNum);
addOrderDetailStatement.setString(2,orderDetailsResultSet.getString("Detail_seq_num")); addOrderDetailStatement.setString(3,orderDetailsResultSet.getString("Item_num")); addOrderDetailStatement.setString(4,orderDetailsResultSet.getString("Tie_num")); addOrderDetailStatement.setInt(5,orderDetailsResultSet.getInt("Order_qty")); addOrderDetailStatement.setString(6,orderDetailsResultSet.getString("Item_class"));
addOrderDetailStatement.addBatch();

}
int[] noOfRowsAffected = addOrderDetailStatement.executeBatch();

conn.commit();

It is inserting only the last record in the batch. Is it a bug in driver???

Hi,

In your code, you have the addOrderDetailStatement = conn.prepareCall(...); inside the loop. Everytime it enters the loop, it overwrites the previous addOrderDetailStatement.
Just put the addOrderDetailStatement = conn.prepareCall(blah blah)
outside the loop like so:

conn.setAutoCommit(false);
addOrderDetailStatement = conn.prepareCall("begin ORDER_PROCESS.add_order_detail( ?, ?, ?, ? ,? ,?); end;" );

while(orderDetailsResultSet.next())
{
addOrderDetailStatement.setString(1,orderNum);
addOrderDetailStatement.setString(2,orderDetailsResultSet.getString("Detail_seq_num"));
addOrderDetailStatement.setString(3,orderDetailsResultSet.getString("Item_num"));
addOrderDetailStatement.setString(4,orderDetailsResultSet.getString("Tie_num"));
addOrderDetailStatement.setInt(5,orderDetailsResultSet.getInt("Order_qty")); addOrderDetailStatement.setString(6,orderDetailsResultSet.getString("Item_class"));
addOrderDetailStatement.addBatch();
}
int[] noOfRowsAffected = addOrderDetailStatement.executeBatch();

conn.commit();

Hi I think you are using the batch update for callable in wrong way.
You can only do batch update after adding multiple calls to procedure i.e here ur calling one proc and trying to set the params in a loop which will over write all ur previous values so u could only insert 1 rec i.e last.

Add mutliple calls to the statement and do the batch update..

Since this post is years old, I'm sure it is dead, but I am experiencing the exact same problem. This works on the production server but not on the local server.