Chapter 2: Programming Information

Implementation notes

jConnect implements batch updates as specified in the JDBC 2.0 API, except as described below.

If the JDBC 2.0 standard for implementing –BatchUpdateException.getUpdateCounts( ) is modified or relaxed in the future, jConnect will continue to implement the original standard by having BatchUpdateException.getUpdateCounts( ) return an int[ ] length of M < N, indicating that the first M statements in the batch succeeded, that the M+1 statement failed, and M+2..N statements were not executed; where “N” equals the total statements in the batch.
Batch updates of stored procedures – to call stored procedures in batch (unchained) mode, you must create the stored procedure in unchained mode. For more information, see “Stored procedure executed in unchained transaction mode”.
Adaptive Server version 11.5.x and later – if the server encounters an error during batch execution, BatchUpdateException.getUpdateCounts( ) will return only an int[ ] length of zero. The entire transaction is rolled back if an error is encountered, resulting in zero successful rows.

The transaction is not rolled back if the error is a duplicate key row insert (see next note for more information).
Adaptive Server – a duplicate key row insertion does not result in the termination and rollback of batch statements. The server will continue processing the statements in the batch until you issue a cancel, or the batch completes or encounters an error, other than a duplicate key row insertion. Because jConnect sends a cancel to the server when it detects any exception (including duplicate key row insertion) during batch processing, it is impossible to determine exactly how much of the batch the server executed before receiving the cancel. Therefore, Sybase strongly recommends that in accordance with the JDBC specification, you should execute batches inside of transactions with autoCommit set to false. In doing it this way, you can roll back your transactions and return the database to a known state before retrying the batch.
Adaptive Server version 11.0.1 – returns 0 (zero) rows affected for stored procedures.
SQL Anywhere version 5.5.x:
- SQL Anywhere version 5.5.x does not allow you to obtain inserted row counts from stored procedures that contain inserts. For example:
```
create proc sp_A as insert tableA values (1, ‘hello A’)
```
```
create proc sp_B
as
  insert tableA values (1, ‘hello A’)
update tableA set col1=2
```
```
create proc sp_C
as
update tableA set col1=2
delete tableA
```
  Running executeBatch on the preceding stored procedures would result in, respectively:
```
0 Rows Affected
1 Rows Affected
2 Rows Affected
```
- There is no support for dynamic PreparedStatements in batch.
- Because SQL Anywhere 5.5.x does not natively support batch updates according to the JDBC 2.0 specification, batch updates are carried out in an executeUpdate loop.
Batch updates in databases that do not support batch updates – jConnect carries out batch updates in an executeUpdate loop even if your database does not support batch updates. This allows you to use the same batch code, regardless of the database to which you are pointing.

See Sun Microsystems, Inc. JDBC™ 2.0 API for more details on batch updates.

Updating a database from a result set of a stored procedure

View this book as PDF