problem with excuteBatch return codes from PreparedStatements
Dawn
differentiate between successful and unsuccessful updates (no record
in table to update). According to the javadoc for executeBatch, we
should receive an array of update counts for each statement in the
batch. Each update count will tell us how many rows were affected by
that particular statement.
We are running servers at level DB2/6000 7.2.0 and DB2 OS/390 6.1.1
We are running windows and AIX clients at level DB2 SDK 7.2.0 and 7.1
with JDBC2.0
I have tried all the different permutations of updates (Statement
executeUpdate, PreparedStatement executeUpdate, Statement
executeBatch, PreparedStatement executeBatch). All of the updates
give the expected result (rc=1 for successful update or insert and
rc=0 for no update) except for prepared batch which always returns
rc=-2 on client level 7.2 or a rc=0 on client level 7.1, regardless of
whether the update was successful or unsuccessful. The only exception
to this is that an unsuccessful update from a 7.2 client to a 6.1.1
server generates an exception on prepared batch. All other types of
updates return a 0 in this case.
I have tried this using both jdk1.2.2 and jdk1.3.1 with identical
results.
I assume that the results returned from the prepared executeBatch are
not correct. Is there a work-around to allow me to differentiate
between successful and unsuccessful updates?
Here are the results of my test from a 7.2 client to 7.2 server. Each
test processes 5 inserts or updates. Return codes that are not equal
to 1 are printed out. The first run shows return codes from 5
successful inserts, the second run shows return codes from 5
successful updates, the last run shows return codes from 1 unsuccesful
update and 4 successful ones.
successful inserts
INSERT RECORDS
deleted=200
start of time
***TEST 0***
Statement executeUpdate
***TEST 1***
PreparedStatement executeUpdate
***TEST 2***
Statement executeBatch
updateCounts.length=5
***TEST 3***
PreparedStatement executeBatch
updateCounts.length=5
0=-2
1=-2
2=-2
3=-2
4=-2
successful updates
UPDATE RECORDS
deleted=0
start of time
***TEST 0***
Statement executeUpdate
***TEST 1***
PreparedStatement executeUpdate
***TEST 2***
Statement executeBatch
updateCounts.length=5
***TEST 3***
PreparedStatement executeBatch
updateCounts.length=5
0=-2
1=-2
2=-2
3=-2
4=-2
1 unsuccessful update
UPDATE RECORDS
deleted=0
start of time
***TEST 0***
Statement executeUpdate
3 rc =0
***TEST 1***
PreparedStatement executeUpdate
3 rc =0
***TEST 2***
Statement executeBatch
updateCounts.length=5
3=0
***TEST 3***
PreparedStatement executeBatch
updateCounts.length=5
0=-2
1=-2
2=-2
3=-2
4=-2
Here is the exception for unsuccessful update from 7.2 client to 6.1.1
server
java.sql.BatchUpdateException: [IBM][CLI Driver][DB2] SQL0301N The
value of a host variable in the EXECUTE or OPEN statement cannot be
used because of its data type. SQLSTATE=02000
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(SQLExceptionGenerator.java:255)
at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_batch_return_code(SQLExceptionGenerator.java:803)
at COM.ibm.db2.jdbc.app.DB2PreparedStatement.executeBatch(DB2PreparedStatement.java:2966)
at BatchTest.main(BatchTest.java:189)