db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gurvan Le Guernic <glegu...@gmail.com>
Subject Bug with getGeneratedKeys() ?
Date Thu, 30 Apr 2009 13:10:51 GMT
   Hi,
 I have a problem with the function "getGeneratedKeys()", it is either a 
mistake I do but do not see, or a bug.

 I have two tables created as follows:
    private static final String typesTableCreationStr =
    "CREATE TABLE types (" +
    "  id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY," +
    "  name VARCHAR(128) NOT NULL UNIQUE," +
    "  shortName VARCHAR(64) NOT NULL" +
    ")";
    private static final String methodsTableCreationStr =
    "CREATE TABLE methods (" +
    "  id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY," +
    "  class INT REFERENCES types (id)," +
    "  name VARCHAR(128) NOT NULL," +
    "  isConstructor VARCHAR(5) NOT NULL," +
    "  isStatic VARCHAR(5) NOT NULL," +
    "  returnType INT REFERENCES types (id)" +
    ")";

I want to add an entry in table "methods" and retrieve the generated key 
(it works well with a simple INSERT INTO types).
I have already 4 entries in table types with id 1, 2, 3, and 4; table 
methods is empty.
I do:
    private static final String insertStr_method =
    "INSERT INTO methods" +
    "  (class, name, isConstructor, isStatic, returnType)" +
    "  SELECT c.id, ?, ?, ?, r.id" +
    "  FROM types AS c" +
    "     , types AS r" +
    "  WHERE c.name = ?" +
    "    AND r.name = ?";
    ...
        stmt = dbConnection.prepareStatement(insertStr_method, 
Statement.RETURN_GENERATED_KEYS);
        ...
        stmt.executeUpdate();
        ResultSet res = stmt.getGeneratedKeys();
        if ( res.next() ) {
          id = res.getInt(1);
          ...
        }
Then id equals 4. I would have expected 1 as it is the first insert into 
table methods. 4 is the last generated key of table "types" but the 
insert is done into table "methods", so I would have expected to get the 
last generated key of table "methods".
 I am using db-derby-10.4.2.0 and jdk1.6.0_11 under Linux
 I attached the java files I coded.

 So, is it a bug or something I do wrong.

   Thank you,
   Gurvan

Mime
View raw message