Getting number of rows inserted on IBM DB2 V6R1 (AS400) through JBDC

Question

We migrated recently to newer V6R1 version of DB2 on AS400, we are using Spring framework (v. 2.5.6.) to communicate with the database. We are calling Spring's NamedParameterJdbcTemplate.update() method to insert new rows, this method should return number of inserted rows, what is not happening (we got zero back as a result) although row is being inserted regularly.

We concluded that if there is no primary key column in insert statement everything is ok, so there is no problem when PK column is autoincrement, but in some situations we must insert PK value and then we must cope somehow with situation that regularly inserted row is not being registered with JDBC or Spring.

Can somebody help?

Pinette

I am assuming you are using the jt400.jar (ie, JTOpen for iSeries) to access the database? – Huddle

yes, latest version that can be downloaded from sourceforge: 7.3 – Pinette

we turned debug on for JDBC and got SQL which is somewhat different than in version 5, it has that famous new feature incorporated: Prepared STMT0001*, SQL Statement -->[SELECT *SQLGENCOLUMNS FROM FINAL TABLE(insert into test_v6r1 (id, value ) values (?, ?))]. – Pinette

Can you tell based off the call stack what it is calling? Is it calling AS400JDBCStatement.executeUpdate? If it is invoking just execute() that only returns a boolean. Perhaps try seeing if you can call getUpdateCount() as well on the AS400JDBCStatement. – Huddle

Hey, where are you at with this? – Huddle

we did put link on JTOpen forum, no answer by now,so we'll see, it's a bug on a driver level. We adjusted our code this way: inserted = this.namedParameterJdbcTemplate.update( this.sql, parameterSource, generatedKeyHolder ); so it works now.. – Pinette

I've added a ticekt on JT400 bug tracker: sourceforge.net/tracker/… – Sodden

This will be fixed in JTOpen 7.6. The fix is already available in the source for AS400JDBCStatement. sourceforge.net/tracker/… – Sodden

Answer 1

UPDATE: this issue has been fixed in JTOpen 7.6

---

Me and Ante work together... the issue has nothing to do with Spring, that's for sure, because I've setup a project which uses plain JDBC and PreparedStatements.

So I'll add some more info how to simulate the problem:

DDL:

CREATE TABLE TEST_V6R1 (
        ID INTEGER NOT NULL DEFAULT,
        VALUE VARCHAR(50)
    );

ALTER TABLE TEST_V6R1 ADD CONSTRAINT TEST_V6R1_PK PRIMARY KEY
    (ID);

SQL:

insert into test_v6r1 (id, value ) values (?, ?)

Java code:

public class TestV6R1 {

   public static void main( String[] args ) {
      Connection conn = null;
      PreparedStatement ps1 = null;
      PreparedStatement ps2 = null;
      try {
         conn = getNewConnection();
         conn.setAutoCommit( false );

         String value = "Test: " + new Timestamp( System.currentTimeMillis() );

         // First statement which uses RETURN_GENERATED_KEYS
         ps1 = conn.prepareStatement( "insert into test_v6r1 (id, value ) values (?, ?)", Statement.RETURN_GENERATED_KEYS );
         ps1.setInt( 1, 1 );
         ps1.setString( 2, value );
         int ps1Rows = ps1.executeUpdate();
         // in case of V5R4
         // ps1Rows is 1
         // in case of V6R1
         // ps1Rows is 0

         ResultSet ps1keys = ps1.getGeneratedKeys();
         int ps1KeySize = 0;
         if (ps1keys != null) {
            ps1keys.last();
            ps1KeySize = ps1keys.getRow();
         }
         System.out.println("PS1 - SQL insert affected " + ps1Rows + " rows and returned " + ps1KeySize + " keys");
         System.out.println("PS1 - getUpdateCount()="+ ps1.getUpdateCount());

         // Second statement which uses NO_GENERATED_KEYS
         ps2 = conn.prepareStatement( "insert into test_v6r1 (id, value) values (?, ?)", Statement.NO_GENERATED_KEYS );
         ps2.setInt( 1, 2 );
         ps2.setString( 2, value );
         int ps2Rows = ps2.executeUpdate();
         // in case of V5R4
         // ps2Rows is 1
         // in case of V6R1
         // ps2Rows is 1

         ResultSet ps2Keys = ps2.getGeneratedKeys();
         int ps2KeySize = 0;
         if (ps2Keys != null) {
            ps2Keys.last();
            ps2KeySize = ps2Keys.getRow();
         }

         System.out.println("PS2 - SQL insert affected " + ps2Rows + " rows and returned " + ps2KeySize + " keys");
         System.out.println("PS2 - getUpdateCount()="+ ps2.getUpdateCount());

         conn.commit();
      }
      catch ( Throwable e ) {

         e.printStackTrace();
         try {
            conn.rollback();
         }
         catch ( SQLException e1 ) {
            e1.printStackTrace();
         }
      }
      finally {
         try {
            if (ps1!=null) ps1.close();
            if (ps2!=null) ps2.close();
            if (conn!=null) conn.close();
         }
         catch ( SQLException e ) {
            e.printStackTrace();
         }
      }

   }

   public static Connection getNewConnection() {
      try {
         Class.forName( "com.ibm.as400.access.AS400JDBCDriver" ); // Or any other driver
      }
      catch ( Exception x ) {
         System.out.println( "Unable to load the driver class!" );
      }
      Connection dbConnection = null;
      try {
         // TEST - V6R1
         dbConnection = DriverManager
               .getConnection( "jdbc:as400://testServer;libraries=*libl;naming=system;sort=language;sort language=HRV;sort weight=shared;prompt=false;trace=true",
                               "username",
                               "password" );
         // PRODUCTION - V5R4
//         dbConnection = DriverManager
//         .getConnection( "jdbc:as400://productionServer;libraries=*libl;naming=system;sort=language;sort language=HRV;sort weight=shared;prompt=false;trace=true",
//                         "username",
//                         "password" );

      }
      catch ( SQLException x ) {
         System.out.println( "Couldn’t get connection!" );
      }

      return dbConnection;
   }

}

V5R4 console output:

Toolbox for Java - Open Source Software, JTOpen 7.3, codebase 5770-SS1 V7R1M0.03 2011/01/14 @B5
as400: Properties  (12122347) : access = "all".
as400: Properties  (12122347) : block size = "32".
as400: Properties  (12122347) : block criteria = "2".
as400: Properties  (12122347) : date format = "".
as400: Properties  (12122347) : date separator = "".
as400: Properties  (12122347) : decimal separator = "".
as400: Properties  (12122347) : errors = "basic".
as400: Properties  (12122347) : extended dynamic = "false".
as400: Properties  (12122347) : libraries = "*libl".
as400: Properties  (12122347) : naming = "system".
as400: Properties  (12122347) : package = "".
as400: Properties  (12122347) : package add = "true".
as400: Properties  (12122347) : package cache = "false".
as400: Properties  (12122347) : package clear = "false".
as400: Properties  (12122347) : package error = "warning".
as400: Properties  (12122347) : package library = "".
as400: Properties  (12122347) : password = "".
as400: Properties  (12122347) : prefetch = "true".
as400: Properties  (12122347) : prompt = "false".
as400: Properties  (12122347) : remarks = "system".
as400: Properties  (12122347) : sort = "language".
as400: Properties  (12122347) : sort language = "HRV".
as400: Properties  (12122347) : sort table = "".
as400: Properties  (12122347) : sort weight = "shared".
as400: Properties  (12122347) : time format = "".
as400: Properties  (12122347) : time separator = "".
as400: Properties  (12122347) : trace = "true".
as400: Properties  (12122347) : transaction isolation = "read uncommitted".
as400: Properties  (12122347) : translate binary = "false".
as400: Properties  (12122347) : user = "username".
as400: Properties  (12122347) : package criteria = "default".
as400: Properties  (12122347) : lob threshold = "32768".
as400: Properties  (12122347) : secure = "false".
as400: Properties  (12122347) : data truncation = "true".
as400: Properties  (12122347) : proxy server = "".
as400: Properties  (12122347) : secondary URL = "".
as400: Properties  (12122347) : data compression = "true".
as400: Properties  (12122347) : big decimal = "true".
as400: Properties  (12122347) : thread used = "true".
as400: Properties  (12122347) : cursor hold = "true".
as400: Properties  (12122347) : lazy close = "false".
as400: Properties  (12122347) : driver = "toolbox".
as400: Properties  (12122347) : bidi string type = "5".
as400: Properties  (12122347) : key ring name = "".
as400: Properties  (12122347) : key ring password = "".
as400: Properties  (12122347) : full open = "false".
as400: Properties  (12122347) : server trace = "0".
as400: Properties  (12122347) : database name = "".
as400: Properties  (12122347) : extended metadata = "false".
as400: Properties  (12122347) : cursor sensitivity = "asensitive".
as400: Properties  (12122347) : behavior override = "0".
as400: Properties  (12122347) : package ccsid = "13488".
as400: Properties  (12122347) : minimum divide scale = "0".
as400: Properties  (12122347) : maximum precision = "31".
as400: Properties  (12122347) : maximum scale = "31".
as400: Properties  (12122347) : translate hex = "character".
as400: Properties  (12122347) : toolbox trace = "".
as400: Properties  (12122347) : qaqqinilib = "".
as400: Properties  (12122347) : login timeout = "".
as400: Properties  (12122347) : true autocommit = "false".
as400: Properties  (12122347) : bidi implicit reordering = "true".
as400: Properties  (12122347) : bidi numeric ordering = "false".
as400: Properties  (12122347) : hold input locators = "true".
as400: Properties  (12122347) : hold statements = "false".
as400: Properties  (12122347) : rollback cursor hold = "false".
as400: Properties  (12122347) : variable field compression = "true".
as400: Properties  (12122347) : query optimize goal = "0".
as400: Properties  (12122347) : keep alive = "".
as400: Properties  (12122347) : receive buffer size = "".
as400: Properties  (12122347) : send buffer size = "".
as400: Properties  (12122347) : XA loosely coupled support = "0".
as400: Properties  (12122347) : translate boolean = "true".
as400: Properties  (12122347) : metadata source = "-1".
as400: Properties  (12122347) : query storage limit = "-1".
as400: Properties  (12122347) : decfloat rounding mode = "half even".
as400: Properties  (12122347) : autocommit exception = "false".
as400: Properties  (12122347) : auto commit = "true".
as400: Properties  (12122347) : ignore warnings = "".
as400: Properties  (12122347) : secure current user = "true".
as400: Properties  (12122347) : concurrent access resolution = "0".
as400: Properties  (12122347) : jvm16 synchronize = "true".
as400: Properties  (12122347) : socket timeout = "".
as400: Properties  (12122347) : use block update = "false".
as400: Properties  (12122347) : maximum blocked input rows = "32000".
as400: Driver AS/400 Toolbox for Java JDBC Driver (15779934) : Using IBM Toolbox for Java JDBC driver implementation.
as400: Properties  (12122347) : metadata source = "0".
as400: Toolbox for Java - Open Source Software, JTOpen 7.3, codebase 5770-SS1 V7R1M0.03 2011/01/14 @B5
as400: JDBC Level: 40
as400: Properties  (12122347) : package ccsid = "13488".
as400: Connection productionServer (367156) : Client CCSID = 13488.
as400: Connection productionServer (367156) : Setting server NLV = 2912.
as400: Connection productionServer (367156) : Client functional level = V7R1M01   .
as400: Connection productionServer (367156) : Data compression = RLE.
as400: Connection productionServer (367156) : ROWID supported = true.
as400: Connection productionServer (367156) : True auto-commit supported = true.
as400: Connection productionServer (367156) : 128 byte column names supported = true.
as400: Connection productionServer (367156) : Maximum decimal precision = 31.
as400: Connection productionServer (367156) : Maximum decimal scale = 31.
as400: Connection productionServer (367156) : Minimum divide scale = 0.
as400: Connection productionServer (367156) : Translate hex = character.
as400: Connection productionServer (367156) : query optimize goal = 0.
as400: Connection productionServer (367156) : Using extended datastreams.
as400: Connection productionServer (367156) : JDBC driver major version = 9.
as400: Connection productionServer (367156) : IBM i VRM = V5R4M0.
as400: Connection productionServer (367156) : Server CCSID = 870.
as400: Connection productionServer (367156) : Server functional level = V5R4M00014 (14).
as400: Connection productionServer (367156) : Server job identifier = 692621/QUSER/QZDASOINIT.
as400: Properties  (12122347) : decimal separator = ".".
as400: Properties  (12122347) : date format = "dmy".
as400: Properties  (12122347) : date separator = "/".
as400: Properties  (12122347) : time format = "hms".
as400: Properties  (12122347) : time separator = ":".
as400: Connection productionServer (367156)  open.
as400: Connection productionServer (367156) : Auto commit = "true".
as400: Connection productionServer (367156) : Read only = "false".
as400: Connection productionServer (367156) : Transaction isolation = "1".
as400: Connection productionServer (367156) : Auto commit = "false".
as400: PreparedStatement STMT0001 (24864323)  open. Parent: Connection productionServer (367156) .
as400: PreparedStatement STMT0001 (24864323) : Escape processing = "true".
as400: PreparedStatement STMT0001 (24864323) : Fetch direction = "1000".
as400: PreparedStatement STMT0001 (24864323) : Fetch size = "0".
as400: PreparedStatement STMT0001 (24864323) : Max field size = "0".
as400: PreparedStatement STMT0001 (24864323) : Max rows = "0".
as400: PreparedStatement STMT0001 (24864323) : Query timeout = "0".
as400: PreparedStatement STMT0001 (24864323) : Result set concurrency = "1007".
as400: PreparedStatement STMT0001 (24864323) : Result set holdability = "1".
as400: PreparedStatement STMT0001 (24864323) : Result set type = "1003".
as400: PreparedStatement STMT0001 (24864323) : Behavior Override = "0".
as400: PreparedStatement STMT0001 (24864323) : Data to correlate statement with cursor Cursor CRSR0001 (7792807) .
as400: PreparedStatement STMT0001 (24864323) : Preparing [insert into test_v6r1 (id, value ) values (?, ?)].
as400: PreparedStatement STMT0001 (24864323) : Prepared STMT0001*, SQL Statement -->[insert into test_v6r1 (id, value ) values (?, ?)].
as400: PreparedStatement STMT0001 (24864323) : setInt().
as400: PreparedStatement STMT0001 (24864323) : parameter index: 1 value: 1.
as400: PreparedStatement STMT0001 (24864323) : setString().
as400: PreparedStatement STMT0001 (24864323) : parameter index: 2 value: Test: 2011-04-27 16:34:30.981.
as400: PreparedStatement STMT0001 (24864323) : Descriptor 1 created or changed.
as400: PreparedStatement STMT0001 (24864323) : returnCode is: 0.
as400: PreparedStatement STMT0001 (24864323) : generated key from system is: null.
as400: ResultSet  (2850225)  open.
as400: ResultSet  (2850225) : Conncurrency = "1007".
as400: ResultSet  (2850225) : Fetch direction = "1000".
as400: ResultSet  (2850225) : Fetch size = "0".
as400: ResultSet  (2850225) : Max rows = "0".
as400: ResultSet  (2850225) : Type = "1004".
as400: PreparedStatement STMT0001 (24864323) : Executed STMT0001*, SQL Statement --> [insert into test_v6r1 (id, value ) values (?, ?)].
as400: PreparedStatement STMT0001 (24864323) : Update count = 1.
as400: PreparedStatement STMT0001 (24864323) : Result set = false.
as400: PreparedStatement STMT0001 (24864323) : Number of result sets = 0.
as400: PreparedStatement STMT0001 (24864323) : Row count estimate = -1.
PS1 - SQL insert affected 1 rows and returned 0 keys
PS1 - getUpdateCount()=1
as400: PreparedStatement STMT0002 (19098837)  open. Parent: Connection productionServer (367156) .
as400: PreparedStatement STMT0002 (19098837) : Escape processing = "true".
as400: PreparedStatement STMT0002 (19098837) : Fetch direction = "1000".
as400: PreparedStatement STMT0002 (19098837) : Fetch size = "0".
as400: PreparedStatement STMT0002 (19098837) : Max field size = "0".
as400: PreparedStatement STMT0002 (19098837) : Max rows = "0".
as400: PreparedStatement STMT0002 (19098837) : Query timeout = "0".
as400: PreparedStatement STMT0002 (19098837) : Result set concurrency = "1007".
as400: PreparedStatement STMT0002 (19098837) : Result set holdability = "1".
as400: PreparedStatement STMT0002 (19098837) : Result set type = "1003".
as400: PreparedStatement STMT0002 (19098837) : Behavior Override = "0".
as400: PreparedStatement STMT0002 (19098837) : Data to correlate statement with cursor Cursor CRSR0002 (12470752) .
as400: PreparedStatement STMT0002 (19098837) : Preparing [insert into test_v6r1 (id, value) values (?, ?)].
as400: PreparedStatement STMT0002 (19098837) : Prepared STMT0002*, SQL Statement -->[insert into test_v6r1 (id, value) values (?, ?)].
as400: PreparedStatement STMT0002 (19098837) : setInt().
as400: PreparedStatement STMT0002 (19098837) : parameter index: 1 value: 2.
as400: PreparedStatement STMT0002 (19098837) : setString().
as400: PreparedStatement STMT0002 (19098837) : parameter index: 2 value: Test: 2011-04-27 16:34:30.981.
as400: PreparedStatement STMT0002 (19098837) : Descriptor 2 created or changed.
as400: PreparedStatement STMT0002 (19098837) : Executed STMT0002*, SQL Statement --> [insert into test_v6r1 (id, value) values (?, ?)].
as400: PreparedStatement STMT0002 (19098837) : Update count = 1.
as400: PreparedStatement STMT0002 (19098837) : Result set = false.
as400: PreparedStatement STMT0002 (19098837) : Number of result sets = 0.
as400: PreparedStatement STMT0002 (19098837) : Row count estimate = -1.
PS2 - SQL insert affected 1 rows and returned 0 keys
PS2 - getUpdateCount()=1
as400: Connection productionServer (367156) : Testing to see if cursors should be held..
as400: Connection productionServer (367156) : Transaction commit.
as400: ResultSet  (2850225)  closed.
as400: PreparedStatement STMT0001 (24864323)  closed.
as400: PreparedStatement STMT0002 (19098837)  closed.
as400: Connection productionServer (367156)  closed.

V6R1 console output:

Toolbox for Java - Open Source Software, JTOpen 7.3, codebase 5770-SS1 V7R1M0.03 2011/01/14 @B5
.
.
.
as400: Driver AS/400 Toolbox for Java JDBC Driver (27979955) : Using IBM Toolbox for Java JDBC driver implementation.
as400: Properties  (16020374) : metadata source = "0".
as400: Toolbox for Java - Open Source Software, JTOpen 7.3, codebase 5770-SS1 V7R1M0.03 2011/01/14 @B5
as400: JDBC Level: 40
as400: Properties  (16020374) : package ccsid = "13488".
as400: Connection testServer (11463270) : Client CCSID = 13488.
as400: Connection testServer (11463270) : Setting server NLV = 2912.
as400: Connection testServer (11463270) : Client functional level = V7R1M01   .
as400: Connection testServer (11463270) : Data compression = RLE.
as400: Connection testServer (11463270) : ROWID supported = true.
as400: Connection testServer (11463270) : True auto-commit supported = true.
as400: Connection testServer (11463270) : 128 byte column names supported = true.
as400: Connection testServer (11463270) : Maximum decimal precision = 31.
as400: Connection testServer (11463270) : Maximum decimal scale = 31.
as400: Connection testServer (11463270) : Minimum divide scale = 0.
as400: Connection testServer (11463270) : Translate hex = character.
as400: Connection testServer (11463270) : query optimize goal = 0.
as400: Connection testServer (11463270) : query storage limit = -1.
as400: Connection testServer (11463270) : Using extended datastreams.
as400: Connection testServer (11463270) : JDBC driver major version = 9.
as400: Connection testServer (11463270) : IBM i VRM = V6R1M0.
as400: Connection testServer (11463270) : Server CCSID = 870.
as400: Connection testServer (11463270) : Server functional level = V6R1M00014 (14).
as400: Connection testServer (11463270) : Server job identifier = 072485/QUSER/QZDASOINIT.
as400: Properties  (16020374) : decimal separator = ".".
as400: Properties  (16020374) : date format = "dmy".
as400: Properties  (16020374) : date separator = "/".
as400: Properties  (16020374) : time format = "hms".
as400: Properties  (16020374) : time separator = ":".
as400: Connection S65AB7B0 (11463270)  open.
as400: Connection S65AB7B0 (11463270) : Auto commit = "true".
as400: Connection S65AB7B0 (11463270) : Read only = "false".
as400: Connection S65AB7B0 (11463270) : Transaction isolation = "1".
as400: Connection S65AB7B0 (11463270) : Auto commit = "false".
as400: PreparedStatement STMT0001 (15696851)  open. Parent: Connection S65AB7B0 (11463270) .
as400: PreparedStatement STMT0001 (15696851) : Escape processing = "true".
as400: PreparedStatement STMT0001 (15696851) : Fetch direction = "1000".
as400: PreparedStatement STMT0001 (15696851) : Fetch size = "0".
as400: PreparedStatement STMT0001 (15696851) : Max field size = "0".
as400: PreparedStatement STMT0001 (15696851) : Max rows = "0".
as400: PreparedStatement STMT0001 (15696851) : Query timeout = "0".
as400: PreparedStatement STMT0001 (15696851) : Result set concurrency = "1007".
as400: PreparedStatement STMT0001 (15696851) : Result set holdability = "1".
as400: PreparedStatement STMT0001 (15696851) : Result set type = "1003".
as400: PreparedStatement STMT0001 (15696851) : Behavior Override = "0".
as400: PreparedStatement STMT0001 (15696851) : Data to correlate statement with cursor Cursor CRSR0001 (12039161) .
as400: PreparedStatement STMT0001 (15696851) : Preparing [SELECT *SQLGENCOLUMNS FROM NEW TABLE(insert into test_v6r1 (id, value ) values (?, ?))].
as400: PreparedStatement STMT0001 (15696851) : Prepared STMT0001*, SQL Statement -->[SELECT *SQLGENCOLUMNS FROM NEW TABLE(insert into test_v6r1 (id, value ) values (?, ?))].
as400: PreparedStatement STMT0001 (15696851) : setInt().
as400: PreparedStatement STMT0001 (15696851) : parameter index: 1 value: 1.
as400: PreparedStatement STMT0001 (15696851) : setString().
as400: PreparedStatement STMT0001 (15696851) : parameter index: 2 value: Test: 2011-04-27 16:39:53.839.
as400: PreparedStatement STMT0001 (15696851) : Descriptor 1 created or changed.
as400: Cursor CRSR0001 (12039161)  open.
as400: ResultSet CRSR0001 (13725633)  open. Parent: PreparedStatement STMT0001 (15696851) .
as400: ResultSet CRSR0001 (13725633) : Conncurrency = "1007".
as400: ResultSet CRSR0001 (13725633) : Fetch direction = "1000".
as400: ResultSet CRSR0001 (13725633) : Fetch size = "0".
as400: ResultSet CRSR0001 (13725633) : Max rows = "0".
as400: ResultSet CRSR0001 (13725633) : Type = "1004".
as400: PreparedStatement STMT0001 (15696851) : Executed STMT0001*, SQL Statement --> [SELECT *SQLGENCOLUMNS FROM NEW TABLE(insert into test_v6r1 (id, value ) values (?, ?))].
as400: PreparedStatement STMT0001 (15696851) : Update count = 0.
as400: PreparedStatement STMT0001 (15696851) : Result set = false.
as400: PreparedStatement STMT0001 (15696851) : Number of result sets = 0.
as400: PreparedStatement STMT0001 (15696851) : Row count estimate = -1.
as400: Connection S65AB7B0 (11463270) : Fetching a block of data from the system.
PS1 - SQL insert affected 0 rows and returned 0 keys
PS1 - getUpdateCount()=0
as400: PreparedStatement STMT0002 (540190)  open. Parent: Connection S65AB7B0 (11463270) .
as400: PreparedStatement STMT0002 (540190) : Escape processing = "true".
as400: PreparedStatement STMT0002 (540190) : Fetch direction = "1000".
as400: PreparedStatement STMT0002 (540190) : Fetch size = "0".
as400: PreparedStatement STMT0002 (540190) : Max field size = "0".
as400: PreparedStatement STMT0002 (540190) : Max rows = "0".
as400: PreparedStatement STMT0002 (540190) : Query timeout = "0".
as400: PreparedStatement STMT0002 (540190) : Result set concurrency = "1007".
as400: PreparedStatement STMT0002 (540190) : Result set holdability = "1".
as400: PreparedStatement STMT0002 (540190) : Result set type = "1003".
as400: PreparedStatement STMT0002 (540190) : Behavior Override = "0".
as400: PreparedStatement STMT0002 (540190) : Data to correlate statement with cursor Cursor CRSR0002 (19287723) .
as400: PreparedStatement STMT0002 (540190) : Preparing [insert into test_v6r1 (id, value) values (?, ?)].
as400: PreparedStatement STMT0002 (540190) : Prepared STMT0002*, SQL Statement -->[insert into test_v6r1 (id, value) values (?, ?)].
as400: PreparedStatement STMT0002 (540190) : setInt().
as400: PreparedStatement STMT0002 (540190) : parameter index: 1 value: 2.
as400: PreparedStatement STMT0002 (540190) : setString().
as400: PreparedStatement STMT0002 (540190) : parameter index: 2 value: Test: 2011-04-27 16:39:53.839.
as400: PreparedStatement STMT0002 (540190) : Descriptor 2 created or changed.
as400: PreparedStatement STMT0002 (540190) : Executed STMT0002*, SQL Statement --> [insert into test_v6r1 (id, value) values (?, ?)].
as400: PreparedStatement STMT0002 (540190) : Update count = 1.
as400: PreparedStatement STMT0002 (540190) : Result set = false.
as400: PreparedStatement STMT0002 (540190) : Number of result sets = 0.
as400: PreparedStatement STMT0002 (540190) : Row count estimate = -1.
PS2 - SQL insert affected 1 rows and returned 0 keys
PS2 - getUpdateCount()=1
as400: Connection S65AB7B0 (11463270) : Testing to see if cursors should be held..
as400: Connection S65AB7B0 (11463270) : Transaction commit.
as400: Cursor CRSR0001 (12039161) : Closing with reuse flag = 240.
as400: Cursor CRSR0001 (12039161)  closed.
as400: ResultSet CRSR0001 (13725633)  closed.
as400: PreparedStatement STMT0001 (15696851)  closed.
as400: PreparedStatement STMT0002 (540190)  closed.
as400: Connection S65AB7B0 (11463270)  closed.

When using the flag RETURN_GENERATED_KEYS the most significant thing that should be perceived is the way prepared statements are prepared.

In case of V6R1 a statement like this:

insert into test_v6r1 (id, value ) values (?, ?)

is internally prepared as:

SELECT *SQLGENCOLUMNS FROM NEW TABLE(insert into test_v6r1 (id, value ) values (?, ?))

In case of V5R4 it's prepared normally:

insert into test_v6r1 (id, value ) values (?, ?)

Javadoc of PreparedStatement.executeUpdate() says:

Executes the SQL statement in this PreparedStatement object, which must be an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.

Returns: either (1) the row count for SQL Data Manipulation Language (DML) statements or (2) 0 for SQL statements that return nothing

and the executed statement is clearly a DML statement. I suspect it's a bug in JTOpen/JT400. I've tried with 6.1 and 7.3 and it's the same.

Sodden

according to the Javadocs for JTOpen, the AS400JDBCPreparedStatement class says the following: "Returns: Either the row count for INSERT, UPDATE, or DELETE, or 0 for SQL statements that return nothing." See the Javadoc here: publib.boulder.ibm.com/infocenter/iseries/v7r1m0/… To me this says that the select statement wraps that other statement and thus as a Select it will return zero rows. – Huddle

Take care that "select from insert" expression may have a big performance overhead because of the creation of temporary tables... at least with v6r1, which in fact remove any benefits of reducing the number of roundtrips. – Managua

Answer 2

In case it is helpful, I found this other post on StackOverflow very similar to what you are trying to do: How to get the rows affected by an update

Huddle