Name: rmT116609 Date: 06/17/2004


A DESCRIPTION OF THE REQUEST :
// use of executeQuery(String sql, int autoGeneratedKeys):
      rs = stmt.executeQuery("SELECT * FROM "+TABLE+" ORDER BY id",
                              Statement.RETURN_GENERATED_KEYS);
      rs.moveToInsertRow();
      rs.updateString( STRING1, "inserted by ResultSet");
      rs.insertRow();
      // retrieve autoincremented id :
      ResultSet generated = stmt.getGeneratedKeys();
      generated.next();
      id = generated.getInt("id");


JUSTIFICATION :
It is not secure to get the right autoincremented id by moving the cursor to the last
--> see workaround

EXPECTED VERSUS ACTUAL BEHAVIOR :
EXPECTED -
--> see source code
ACTUAL -
--> see source code

---------- BEGIN SOURCE ----------
/*
 * RFE_getGeneratedFields.java
 *
 * Created on 16. June 2004, 13:46
 */

package de.CoSoCo.bugs;

import java.sql.*;

/**
 *
 * @author Ulf Zibis
 */
public class RFE_getGeneratedFields {
  
  private final static String URL = "jdbc:odbc:TestData";
  private final static String TABLE = "Test1";
  private final static String STRING1 = "string1";
  private final static String INT1 = "int1";

  
  /**
   * @param args the command line arguments
   */
  public static void main(String[] args) {

    Connection con = null;
    try {
      Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
      con = DriverManager.getConnection( URL);
      createEmptyTable( con);

      Statement stmt = con.createStatement( ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
      ResultSet rs;
      int id;

// use of executeQuery(String sql, int autoGeneratedKeys):
      rs = stmt.executeQuery("SELECT * FROM "+TABLE+" ORDER BY id",
                              Statement.RETURN_GENERATED_KEYS);
      rs.moveToInsertRow();
      rs.updateString( STRING1, "inserted by ResultSet");
      rs.insertRow();
      // retrieve autoincremented id :
      ResultSet generated = stmt.getGeneratedKeys();
      generated.next();
      id = generated.getInt("id");

// instead of using of rs.last(), rs.getInt("id"):
      rs = stmt.executeQuery("SELECT * FROM "+TABLE+" ORDER BY id");
      rs.moveToInsertRow();
      rs.updateString( STRING1, "inserted by ResultSet");
      rs.insertRow();
      if (rs.getRow() == 0) rs.afterLast(); // avoid Exception in next line
      rs.last();
      // retrieve autoincremented id :
      id = rs.getInt("id");

    }
    catch (SQLException se) {
      System.out.println( se+", Error: "+se.getErrorCode()+", SQLState: "+se.getSQLState());
      se.printStackTrace();
    }
    catch (Throwable t) {
     t.printStackTrace();
    }
    finally {
      try { if (con != null) con.close(); }
      catch (Throwable t) { t.printStackTrace(); }
    }
  }
  

  static void createEmptyTable(Connection con) throws Throwable {

    Statement stmt = con.createStatement();
    try {
      ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM "+TABLE);
      rs.next();
      System.out.println( TABLE+" had "+rs.getInt( 1)+" record(s).");
      stmt.executeUpdate("DROP TABLE "+TABLE);
    }
    catch (SQLException se) {
      if (se.getErrorCode() == -1305)
        System.out.println( TABLE+" was not existent.");
      else throw se;
    }
    stmt.executeUpdate("CREATE TABLE "+TABLE+"( id COUNTER PRIMARY KEY, "
                                               +STRING1+" VARCHAR(100), "
                                               +INT1+" INT)");
    System.out.println("New empty table "+TABLE+" created !");
    System.out.println();
    stmt.close();
  }
  
}

---------- END SOURCE ----------

CUSTOMER SUBMITTED WORKAROUND :
// instead of using of rs.last(), rs.getInt("id"):
      rs = stmt.executeQuery("SELECT * FROM "+TABLE+" ORDER BY id");
      rs.moveToInsertRow();
      rs.updateString( STRING1, "inserted by ResultSet");
      rs.insertRow();
      if (rs.getRow() == 0) rs.afterLast(); // avoid Exception in next line
      rs.last();
      // retrieve autoincremented id :
      id = rs.getInt("id");
(Incident Review ID: 280188)
======================================================================