Hi, I'm still having problems with use of executeBatch with stored
procedures. I've tried the following code out against DB2 V7 for OS/390
and V8 for NT; the same problem exists either way; only the insert goes
on the table and the rest do not appear, despite an apparently
successful commit.

Can anyone help please? Is there anything wrong with my code?

I'm using the type 2 driver, JDBC 2 (db2java.zip), but it's been against
as I say different versions of DB2 and different stored procedures
written in different languages (COBOL and SQL PL), so it seems likely
(although not impossible) that these aren't the issue.

Many thanks in advance.

Keith.



public static void main(String[] args) throws Exception {

Connection connection = DriverManager.getConnection("jdbc:db2:SAMPLE");
connection.setAutoCommit(false);

short i = 1;

CallableStatement cs = connection.prepareCall("{CALL
KEITH.INSERTSTAFF(?)}");

for (int x=1; x<100; x++) {
cs.setShort(1, i);
i++;
cs.addBatch();
}

int[] results = cs.executeBatch();

connection.commit();

}


The procedure itself is trivial:

CREATE PROCEDURE KEITH.INSERTSTAFF (IN ID SMALLINT)

DYNAMIC RESULT SETS 0
MODIFIES SQL DATA

P1: BEGIN

INSERT INTO STAFF VALUES (ID, 'Keith', 20, 'Mgr', 5, 30000.00, 100.00);

END P1

To answer my own question for future reference, it looks as though the
type 2 driver doesn't support it.

I would have preferred it if some exception were thrown rather than
continuing successfully but only committing one of the changes!

Turns out we need to look into using the type 4 driver.

Keith.