Derby Reference Manual at
http://incubator.apache.org/derby/manuals/reference/sqlj229.html#Header_296 (also at
http://publib.boulder.ibm.com/infocenter/cldscp10/index.jsp?topic=/com.ibm.cloudscape.doc/sqlj229.htm) has incorrect information about generated keys and JDBC api support.
In the middle of the first paragraph, the existing documentation says
"In JDBC 3.0, the method Statement.getGeneratedKeys can be called to retrieve the value of such a column. This method returns a ResultSet object with a column for each automatically generated key. Calling ResultSet.getMetaData on the ResultSet object returned by getGeneratedKeys produces a ResultSetMetaData object that can be used to determine the number, type, and properties of the generated keys."
The paragraph implies that a table can have more than one automatically generated key. Derby supports only one identity column per table. Also, the structure of ResultSet object returned by getGeneratedKeys is not what's documented in the paragraph above. In fact, the ResultSet object returned by Statement.getGeneratedKeys looks exactly similar to the ResultSet object returned by function IDENTITY_VAL_LOCAL. The result of this function is a DECIMAL (31,0), regardless of the actual data type of the corresponding identity column. Rather than duplicating the information under Autogenerated Keys section about the ResultSet, it might be helpful to point to IDENTITY_VAL_LOCAL documentation.
In addition, the Autogenerated Keys page at the link above says following
"Additional methods allow you to specify the ordinals or names of the specific columns to be returned. An exception is thrown for invalid column or position names.
There are three ways of using Autogenerated Keys for insert statements. You can:
Pass the flag Statement.RETURN_GENERATED_KEYS to execute or executeUpdate method.
Send an array of column names to execute or executeUpdate, so only those column's values are returned by getGeneratedKeys() resultset.
Send an array of column indexes to execute or executeUpdate. This array is an index of columns for the target table.
If the Statement.RETURN_GENERATED_KEYS flag is passed to the execute or executeUpdate method, rather than the column positions/names list, Derby returns a ResultSet containing columns with default values (this includes autoincrement column). To obtain a specific column, pass the column positions/names array. If Statement.getGeneratedKeys is executed for a non-insert statement, an exception is thrown.
The key indexes array in AutoGeneratedKey is an index of columns into the target table. You can send an array of column indexes to execute or executeUpdate. This array is an index of columns into the target table. For example:
create table t1(c11 int, c12 int);
int[ ] colIndexes = new int[1];
colIndexes[0] = 1;
statment.execute("insert into t1(c12, c11) values (2,1)",colIndexes);
s.getGeneratedKeys();
--- will return a resultset with column c11.
See the Derby Developer's Guide for more information about arrays."
This information is incorrect also. Derby supports only one way to using Autogenerated Keys for insert statement and that is via passing the flag Statement.RETURN_GENERATED_KEYS to execute or executeUpdate method. The other 2 methods with array of column names or array of column indexes on execute/executeUpdate will throw "not implemented" exception.
Bug
Closed
Major
Derby does not support array of column names or column indexes to execute or executeUpdate. Document incorrectly implies that Derby supports this functionality
This patch replaces the latter part of this same file (crefjavstateautogen.dita) so that it only discusses passing the flag Statement.RETURN_GENERATED_KEYS to execute or executeUpdate method in order to use Autogenerated Keys for insert statements.