9.2.4 Notes

(1) Executing implicit commit processing

If the SQL statements registered with addBatch contain any of the SQL statements shown below, you must exercise caution in using the batch update functionality for the SQL statements because the HADB server performs commit processing implicitly when such an SQL statement is executed:

• Definition SQL statements

• PURGE CHUNK statement

• TRUNCATE TABLE statement

To Page Top

(2) Processing by the batch update functionality when addBatch specifications for parameters and SQL statements are combined

When addBatch lines specifying parameters are mixed in with addBatch lines for SQL statements, the batch update functionality executes the addBatch statements sequentially, instead of in a single batch update. An example is shown below:

PreparedStatement pstmt = con.prepareStatement("UPDATE T1 SET C1=? WHERE C2=?");
pstmt.setInt(1, 1);
pstmt.setInt(2, 1);
pstmt.addBatch();
pstmt.setInt(1, 2);
pstmt.setInt(2, 2);
pstmt.addBatch();
pstmt.addBatch("INSERT INTO T2 VALUES(1,2,3)");
pstmt.setInt(1, 3);
pstmt.setInt(2, 4);
pstmt.addBatch();
pstmt.setInt(1, 4);
pstmt.setInt(2, 4);
pstmt.addBatch();
pstmt.executeBatch();

When this application program is executed, an SQL statement is executed at each addBatch line, because an addBatch line specifying parameters is mixed in with addBatch lines for the SQL statements. Therefore, executing this application program produces the same results as executing the following application program:

PreparedStatement pstmt = con.prepareStatement("UPDATE T1 SET C1=? WHERE C2=?");
pstmt.setInt(1, 1);
pstmt.setInt(2, 1);
pstmt.executeUpdate();
pstmt.setInt(1, 2);
pstmt.setInt(2, 2);
pstmt.executeUpdate();
pstmt.executeUpdate("INSERT INTO T2 VALUES(1,2,3)");
pstmt.setInt(1, 3);
pstmt.setInt(2, 4);
pstmt.executeUpdate();
pstmt.setInt(1, 4);
pstmt.setInt(2, 4);
pstmt.executeUpdate();

If you use the batch update functionality in code that contains a mix of addBatch lines specifying parameters and addBatch lines for SQL statements, we recommend that you disable the automatic commit mode for the Connection class.

Note

For code that contains a mix of addBatch parameters and SQL statements, individual lines are executed sequentially. However, if the automatic commit mode is enabled, commit processing is performed implicitly for each execution unit. If an error occurs during batch update processing, only the portion of the processing up to the error is committed, making it impossible to identify the point at which commit processing occurred. Therefore, we recommend that you disable the automatic commit mode.

To Page Top

(3) Registering many parameters with the addBatch method

The JDBC driver retains all the parameters registered with the addBatch method until the executeBatch method or the executeLargeBatch method is executed. Be aware of the amount of memory that will be used when you register many parameters.

To Page Top

(4) Update count reported by BatchUpdateException

The following shows the update count that is reported in the return value of the getUpdateCounts method of the BatchUpdateException that occurs during batch update processing:

• The array that is returned contains as many elements as the number of SQL statements that executed.

• The number of updated rows is set in each array element.

If the processing is rolled back internally because of an exception, an array containing no elements is returned.

The following shows an example of an update count.

■ Example program of consecutive execution using a JDBC driver

Statement stmt = con.createStatement();
stmt.addBatch("INSERT INTO T1 VALUES(1,'aaaa')");
stmt.addBatch("INSERT INTO T1 VALUES(2,'bbbbbbbb')");...[A]
stmt.addBatch("INSERT INTO T1 VALUES(3,'cccc')");
stmt.executeBatch();

The following shows the update count that is returned by the getUpdateCounts method when this example program is executed and the parameter or SQL statement registered in [A] results in an error:

• Array containing one element

• Value of element 0: Number of updated rows

If the number of updated rows might exceed Integer.MAX_VALUE, use the executeLargeBatch method instead of the executeBatch method. Similarly, use the getLargeUpdateCounts method instead of the getUpdateCounts method.

To Page Top