Would you be willing to log a JIRA feature request for this...
David
Lance J. Andersen wrote:
> FWIW, we should support column names for autoGeneratedKeys. Most
> vendors now support this feature so adding it will ease migration issues.
>
> Regards
> lance
>
> Mamta A. Satoor (JIRA) wrote:
>
>> Derby does not support array of column names or column indexes to
>> execute or executeUpdate. Document incorrectly implies that Derby
>> supports this functionality
>> ---------------------------------------------------------------------------------------------------------------------------------------------------------------
>>
>>
>> Key: DERBY-199
>> URL: http://issues.apache.org/jira/browse/DERBY-199
>> Project: Derby
>> Type: Bug
>> Components: Documentation Versions: 10.1.0.0 Reporter:
>> Mamta A. Satoor
>>
>>
>> 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.
>>
>>
>>
>>