Scalable Database Server, HiRDB Version 8 UAP Development Guide

[Contents][Index][Back][Next]

16.3.2 Batch updating

The JDBC2.0 basic standard adds the batch updating feature to the Statement, PreparedStatement, and CallableStatement classes. The batch update facility enables multiple SQL statements or multiple parameter values to be registered for batch execution.

To use the batch update facility, you need to set the Connection class's AUTO commit mode to off. This is because, if an error occurs during the batch updating, the application needs to control the transaction's validity. If the AUTO commit mode is on (initial status) and an error occurs during the batch updating, the SQL execution immediately preceding the error takes effect.

When you execute batch updating, you can use HiRDB facilities using arrays.

The facilities using arrays are useful for updating a large amount of HiRDB data at high speed. For details about the facilities using arrays, see 4.8 Facilities using arrays.

Notes about using the facilities using arrays
  1. The facilities using arrays are supported by HiRDB version 07-01 or later.
  2. During Connect, you must specify the BLOCK_UPDATE=TRUE property (if DataSource is used, specify setBlockUpdate(true)) or setBlockUpdate(true) in JdbcDbpsvPreparedStatement.
  3. If you specify the HiRDB_for_Java_BLOCK_UPDATE=TRUE system property, you can enable the array facilities. For details about HiRDB_for_Java_BLOCK_UPDATE, see BLOCK_UPDATE in Table 16-3.
  4. The SQL statement to be executed must contain at least one ? parameter (this does not apply to stored procedures). Additionally, you must use the addBatch() method of the CallableStatement class or the PreparedStatement class (using the addBatch(String sql) method of the Statement class results in a HiRDB error).
    Executable SQL statements include INSERT, UPDATE, and DELETE. All other SQL statements are executed sequentially, not in batch mode.
  5. There must be two or more parameter sets that have been registered by the addBatch() method. If there is only one parameter set, it is processed normally, not in batch mode. If there are more than 30,000 parameter sets, each group of 30,000 parameter sets is executed at one time.
  6. If the length of BINARY data specified in the ? parameter is 32,001 bytes or greater, sequential execution takes place because facilities using arrays are not applied.
  7. If the length of data specified for HiRDB BLOB-type columns is 32,001 bytes or greater, sequential execution takes place because facilities using arrays are not applied.2
  8. Make sure that the same data type is specified for each and every column.1
  9. When DECIMAL-type data is inserted, the precision and scaling of the DECIMAL-type data specified for array are replaced by HiRDB's table definition attributes. If the length of integer part of the DECIMAL-type data specified for array is greater than that of the HiRDB table definition attribute, an overflow occurs, resulting in an error.
  10. If you specify HiRDB's repetition column in the ? parameter, you cannot use the facilities using arrays.
  11. If an error occurs during batch updating with facilities using arrays, the execution results immediately preceding the error are ignored.
  12. Facilities using arrays cannot be used from the basic Cosminexus J2EE server mode.
  13. When facilities using arrays are used from Cosminexus, the setBlockUpdate method of PreparedStatement is not available.
  14. When a large amount of data is updated using the addBatch function, a large amount of Java memory is used. Depending on the performance of Java memory, the advantages of batch updating may not be obtained. When you use a large amount of data, specify a heap size at the start of Java (java -Xms32m JavaUP: set the Java heap at the start of Java to 32 megabytes).
1 For example, if you use setInt() to specify the first addBatch for the column 1 data, you must also use setInt() for the subsequent addBatch.
2 If you use facilities using arrays and specify the ? parameter for HiRDB's BLOB-type columns, note the following:
  • If the length of data specified in the ? parameter is less than 32,001 bytes, the data is treated as BINARY-type data in the JDBC driver, thereby executing facilities using arrays. If the length is 32,001 bytes or greater, facilities using arrays are not executed.
Organization of this subsection
(1) Batch updating with the Statement class
(2) Batch updating with the PreparedStatement class
(3) Batch updating with the CallableStatement class

(1) Batch updating with the Statement class

Following are notes about batch updating with the Statement class:

The JDBC driver executes registered SQL statements sequentially because it cannot execute them in batch mode.

(2) Batch updating with the PreparedStatement class

Following are notes about batch updating with the PreparedStatement class:

When facilities using arrays are used, the JDBC driver can execute multiple lines of ? parameters in batch mode. When facilities using arrays are not used, multiple lines of ? parameters are executed sequentially.

Notes
  • If you use HiRDB facilities using arrays, see the notes in 16.3.2 Batch updating.
  • In the second or subsequent addBatch, if there are not enough parameters to be specified in the setXXX method, the previous values are inherited. The following shows an example.
Example: When there are 2 INTEGER-type columns (columns 1 and 2)
 
prepstmt.setInt(1,100);
prepstmt.setInt(2,100);
prepstmt.addBatch();
prepstmt.setInt(1,200);
prepstmt.addBatch();
prepstmt.executeBatch();
Explanation
  • The values that are set in the first addBatch are 100 for both columns 1 and 2.
    If there are not enough parameters in the first addBatch, an error occurs.
  • The values that are set in the second addBatch are 200 for column 1 and 100 for column 2.
    Because information for column 2 has not been updated by the second addBatch, information for the first addBatch is inherited.

(3) Batch updating with the CallableStatement class

Following are notes about batch updating with the CallableStatement class:

The JDBC driver cannot execute multiple lines of ? parameters in stored procedures; therefore, multiple lines of ? parameters in stored procedures are executed sequentially.

Notes
  • Batch updating of stored procedures is supported only for the IN parameter. If an OUT parameter, INOUT parameter, or result set (ResultSet) is used, an error results.
  • In the case of a stored procedure that returns a result set (ResultSet), whether or not it returns a result set is unknown until the stored procedure is executed during batch updating. Therefore, if data is updated within the stored procedure, updated information may be applied.1
  • Facilities using arrays are not supported in stored procedures. They are supported only in the SQL statements with ? parameters.
  • In the second or subsequent addBatch, if there are not enough parameters to be specified in the setXXX method, the previous values are inherited.2
  • If you use the facilities using arrays, see the notes in 16.3.2 Batch updating.
1 For example, if a stored procedure that searches and acquires the result of updating is executed during batch updating, BatchUpdateException occurs, but updated information may still be applied.
2 Example: When there are 2 INTEGER-type columns (columns 1 and 2)
 
callstmt.setInt(1,100);
callstmt.setInt(2,100);
callstmt.addBatch();
callstmt.setInt(1,200);
callstmt.addBatch();
callstmt.executeBatch();
Explanation
  • The values that are set in the first addBatch are 100 for both columns 1 and 2.
    If there are not enough parameters in the first addBatch, an error occurs.
  • The values that are set in the second addBatch are 200 for column 1 and 100 for column 2.
    Because information for column 2 has not been updated by the second addBatch, information for the first addBatch is inherited.

[Contents][Back][Next]

[Trademarks]

All Rights Reserved. Copyright (C) 2007, Hitachi, Ltd.