jGuru: Using Batch Updates

Print-friendly Version

by

[Help | API Docs | Short Course| Exercises]

Exercise Note: UDB2/NT returns false from DatabaseMetaData.supportsBatchUpdates(). The exercise has been tested against Cloudscape and DB2/400.

This exercise will convert the exercise program built in Using Transactions, to include batch update functionality and BatchUpdateException handling. See the demonstration section for details on using EBRun to load data into the CoffeeValT and CoffeeComp tables.

Prerequisites

• Using Transactions

Skeleton Code

• ConnectU.properties
• LoadCoffeeValT.properties
• LoadCoffeeComp.properties
• EBRun.java

Tasks

1. In doUpdate() get DatabaseMetaData using dbmd and determine if the driver supports Batch Updates. Report, close the Connection and return if Batch Updates are not supported.

2. In doUpdate(), insert code to add the sUpdate SQL statements to stmt's list of commands.

3. In doUpdate(), after all commands have been added, perform the executeBatch method, returning the update count array to aiupdateCounts.

4. In doUpdate(), add a catch block to handle BatchUpdateExceptions. Put the result from BatchUpdateException.UpdateCounts() into aiupdateCounts.

5. In doUpdate(), in the finally block, add code to report statements submitted and results encountered. Report each possible outcome for each element in the aiupdateCounts array.

Where help exists, the task numbers above are linked to the step-by-step help page.

Solution Source

• ConnectU.properties
• LoadCoffeeValT.properties
• LoadCoffeeComp.properties
• EBRun.java

Demonstration

When EBRun has been compiled and run, all DML statements from the ResourceBundle will be committed or all will be rolled back, depending on error conditions. The program will rollback any DDL statements because support for DDL in transactions is dependent on the DBMS used. All statements are executed with Statement.executeBatch() and results and errors, if any are reported.

The reader should run the Batch Update program EBRun with both properties files as java EBRun LoadCoffeeValT and java EBRun LoadCoffeeComp, in that order. This assumes that the tables were created and java EUTrans LoadCoffeeVend was run in the exercise Using Transactions.

To test Batch Update transaction processing and error checking, copy the any of the DML properties files, make any changes you like and rerun EBRun against the new file. To restore the data to the expected condition, run java EURun2 DropCoffeeTables, then perform the two steps in the Demonstration/Behavior section of the exercise Using Transactions.

The following is sample output from a successful run against LoadCoffeeVend on both Cloudscape and DB2/400 ( UDB2/NT does not support the Batch Update Facility ):

java EBRun LoadCoffeeVend
Update Count: 10 statements submitted.
For statement number:
1 was successful, affected rowcount: 1
2 was successful, affected rowcount: 1
3 was successful, affected rowcount: 1
4 was successful, affected rowcount: 1
5 was successful, affected rowcount: 1
6 was successful, affected rowcount: 1
7 was successful, affected rowcount: 1
8 was successful, affected rowcount: 1
9 was successful, affected rowcount: 1
10 was successful, affected rowcount: 1
10 statements processed, 10 rows affected.

This output resulted from an intentional error on Cloudscape:

java EBRun LoadCoffeeVend2
BatchUpdateException:
Syntax error: Encountered "aINTO" at line 1, column 8.
SQL State: 42X01
Vendor Error Code: 20000
Syntax error: Encountered "aINTO" at line 1, column 8.
SQL State: 42X01
Vendor Error Code: 20000
Update Count: 25 statements submitted.
For statement number:
1 was successful, affected rowcount: 1
2 was successful, affected rowcount: 1
3 was successful, affected rowcount: 1
3 statements processed, 3 rows affected.

ALL Statements for LoadCoffeeVend2 were rolled back due to error condition.

The double reporting appears to be a vendor bug.

This output resulted from an intentional error on DB2/400:

java EBRun LoadCoffeeVend2
BatchUpdateException:
[SQL0104] Token AINTO was not valid. Valid tokens: INTO.
SQL State: 42601
Vendor Error Code: -104
Update Count: 25 statements submitted.
For statement number:
1 was successful, affected rowcount: 1
2 was successful, affected rowcount: 1
3 was successful, affected rowcount: 1
3 statements processed, 3 rows affected.

ALL Statements for LoadCoffeeVend2 were rolled back due to error condition.

The error was reported once here, as expected.

Next Exercise

Exercises

Short Course

Copyright 1996-2000 jGuru.com. All Rights Reserved.