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.

Note that the JDBC driver executes SQL statements consecutively in the following cases:

• During batch updating when addBatch specifications for parameters and SQL statements are combined
• During batch updating with SQL statements that contain ? parameters for HiRDB's BINARY type (the length of the data to be set for a BINARY-type ? parameter exceeds 32,000 bytes)
• During batch updating for SQL statements that contain ? parameters for HiRDB's BLOB type

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.
  

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

• Use the normal procedure (setXXX method) to specify the input parameters for the Java stored routines specified during CallableStatement class instance generation.
• Use the addBatch method to register the input parameter sets.
• Use the executeBatch method to execute the registered input parameter sets collectively.
• An array of the return values (number of updated rows) of the Java stored routines executed by the input parameter sets is returned as the batch execution results.
• If an error occurs during batch execution, the batch update facility throws a BatchUpdateException.
• If a Java stored routine specified during CallableStatement instance generation is not a routine that returns the number of updated rows, the batch update facility throws a BatchUpdateException when the executeBatch method is called.
• If a Java stored routine specified during CallableStatement instance generation has output and input-output parameters, the batch update facility throws a BatchUpdateException when the executeBatch method is called.

Because the JDBC driver cannot execute multiple rows of ? parameters for a stored procedure in the batch mode, it executes them consecutively.

Note

Whether a stored procedure that returns a result set (ResultSet) will actually return a result set will not be known until the stored procedure has executed during batch updating. Therefore, if data is updated within the stored procedure, updated information might be applied. For example, if you perform batch updating on a stored procedure that searches and acquires the results of update processing, a BatchUpdateException occurs, but updated information might still be applied.

(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.

(f) Update count reported by BatchUpdateException

The following table shows the update count (int-type array) that is reported in the return value of the getUpdateCounts method of BatchUpdateException that occurs during batch update processing.

Table 18-56 Update count reported in the return value of the getUpdateCounts method

#1

Specified by one of the following:

• User property used when HiRDB is connected: HiRDB_for_Java_BATCHEXCEPTION_BEHAVIOR
• BATCHEXCEPTION_BEHAVIOR for URL
• setBatchExceptionBehavior method of a DataSource interface

When the connected HiRDB's version is 08-01 or earlier, it is assumed that TRUE is specified.

#2

Statement.SUCCESS_NO_INFO is set if a CALL statement is executed using the executeBatch method of the CallableStatement class.

The following are examples of update count:

Example program of batch execution using the array facility

 
PreparedStatement pstmt = con.prepareStatement("INSERT INTO T1 VALUES(?,?)");
pstmt.setInt(1, 1);
pstmt.setString(2,"aaaa");
pstmt.addBatch();
pstmt.setInt(1, 2);
pstmt.setString(2,"bbbbbbbb");
pstmt.addBatch();.......................................[A]
pstmt.setInt(1, 3);
pstmt.setString(2,"cccc");
pstmt.addBatch();
pstmt.executeBatch();
 

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 table shows the update count that is returned by the getUpdateCounts method when these example programs are executed and the parameter or SQL statement registered in [A] results in an error: