Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Issue with clearBatch method

user-e9x22Sep 1 2023 — edited Sep 1 2023

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.

Comments
Post Details
Added on Sep 1 2023
4 comments
104 views