My question is from the Oracle Driver implementation viewpoint. I know the sequence of commands below is not typical but this is just because of some design. Just wanted to know the behavior of clearBatch method. With Oracle, it's failing and getting java.sql.SQLException: Missing IN or OUT parameter at index:: 1. Whereas with DB2 it's working properly. Looks like the Oracle driver clearing the prepared statement details which are not yet added to the batch.
con.setAutoCommit(false);
String sqlStatement = "INSERT INTO TEMP (FName,LName) VALUES (?,?)";
PreparedStatement pstmt = con.prepareStatement(sqlStatement);
pstmt.setString(1, "Tom");
pstmt.setString(2, "Cat");
pstmt.addBatch();
System.out.println("First batch added......");
//Adding parameter to the second statement
pstmt.setString(1, "Jerry");
pstmt.setString(2, "Mouse");
System.out.println("Parameter added in statement but statement not added in the batch yet......");
pstmt.executeBatch(); //This will execute only first statement.
System.out.println("executeBatch 1 finsihed ...…");
//Below we are clearing the batch before adding the second statement in the batch
pstmt.clearBatch();
System.out.println("clearBatch......");
//Adding the second statement in the batch after clearing
pstmt.addBatch();
System.out.println("Second batch added......");
//Below line will give exception -- java.sql.SQLException: Missing IN or OUT parameter at index:: 1
pstmt.executeBatch();
System.out.println("executeBatch 2 finshed...…");
con.commit();
The above way of code works properly on DB2.
One more point would like to know is whether executeBatch is clear and empties the statement.
I appreciate your response on that.