The following notes apply to batch update with the Statement class.

• Use the addBatch method to register multiple update SQL statements.
• Use the executeBatch method to execute registered update SQL statements collectively.
• An array of the numbers of rows updated by the individual update SQL statements is returned as the batch execution results.
• If an error occurs during batch update, the batch update facility throws a BatchUpdateException.
• If the registered SQL statements include a retrieval SQL statement, the batch update facility throws a BatchUpdateException when the executeBatch method is called.

Because the JDBC driver cannot execute multiple SQL statements simultaneously, it executes the registered SQL statements consecutively.

The following notes apply to batch update with the PreparedStatement class.

• Use the normal procedure (setXXX method) to specify the ? parameters for the update SQL statements specified during PreparedStatement instance generation.
• Use the addBatch method to register the ? parameter sets.
• Use the executeBatch method to execute the registered ? parameter sets collectively.
• An array of the number of rows updated by the individual ? parameter sets is returned as the batch execution results.
• If an error occurs during batch execution, the batch update facility throws a BatchUpdateException.
• If an SQL statement specified during PreparedStatement instance generation is a retrieval SQL statement, the batch update facility throws a BatchUpdateException when the executeBatch method is called.

The JDBC driver executes processing by using facilities that use HiRDB arrays.

Notes

You must pay close attention to subsequent executions of addBatch, because the values that were set for the previous execution are inherited when the number of parameters specified by the setXXX method is insufficient.

The following example has two INTEGER-type arrays (array 1 and array 2):

Specification example

prepstmt.setInt(1,100);
prepstmt.setInt(2,100);
prepstmt.addBatch();
prepstmt.setInt(1,200);
prepstmt.addBatch();
prepstmt.executeBatch();
 

Explanation

• The values that are set by the first addBatch are array 1=100 and array 2=100.
  If the number of parameters specified by addBatch is insufficient, an error occurs.
  
• The values that are set by the second addBatch are array 1=200 and array 2=100.
  The second addBatch does not update the information for array 2, so the array 2 information is inherited from the first addBatch.
  

(a) Implicit commit by the HiRDB server

If the SQL statements registered with addBatch contain one of the following SQL statements, you must use the batch update facility for SQL statements carefully, because the HiRDB server commits that SQL statement implicitly when the statement is executed:

• PURGE TABLE statement
• Any definition SQL statement in which YES is specified for the PDCMMTBFDDL client environment variable

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

When addBatch specifications for parameters and addBatch specifications for SQL statements are combined, the batch update facility executes the addBatch specifications sequentially instead of by 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 UAP is executed, each addBatch unit becomes an SQL execution, because there are both addBatch specifications for parameters and addBatch specifications for SQL statements. Therefore, executing this UAP produces the same results as executing the following UAP:

 
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();
 

When you use the batch update facility on a combination of addBatch for parameters and addBatch for SQL statements, it is recommended that you disable the auto-commit mode for the Connection class.

(c) Batch update with SQL statements that contain a ? parameter for HiRDB's BINARY type

When batch update is executed with SQL statements that contain a ? parameter for HiRDB's BINARY type, sequential execution is executed instead of batch update when the following condition applies:

• The length of the data to be set with the setXXX method for the ? parameter exceeds 32,000 bytes (if character data is specified with the setString method, the data length after the data has been encoded into data to be passed to HiRDB exceeds 32,000 bytes).

(d) Batch update for SQL statements that contain a ? parameter for HiRDB's BLOB type

When batch update is executed with SQL statements that include a ? parameter for HiRDB's BLOB type, the statements are executed sequentially instead of by batch update.

(e) Registering multiple parameters with the addBatch method

The JDBC driver accumulates in the driver all parameters registered with the addBatch method until the executeBatch method is executed. You should make note of the amount of memory being used when you are registering multiple parameters.

When batch update is executed with a facility that uses HiRDB arrays, the maximum number of executions that the JDBC driver can request to the HiRDB server is 30,000. To register more than 30,000 parameters, you must divide them into groups of no more than 30,000 and request SQL execution to the HiRDB server for each group. Note also that because of the amount of memory in the JDBC driver that is used in this case, the performance enhancement expected for batch updating may not be realized. When more than 30,000 SQL executions are necessary, it is recommended that you execute the executeBatch method in units of 30,000 or fewer SQL executions.