[
https://issues.apache.org/jira/browse/DERBY-6849?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15699986#comment-15699986
]
Bryan Pendleton commented on DERBY-6849:
----------------------------------------
I've been thinking about how to address the "where to release the generated
keys" problem
raised by Knut Anders's patch. To review, here's the challenge posed by the
patch:
{code}
+ // The InsertResultSet will be closed before Statement.execute()
+ // returns, so closing the holder for the generated keys here means
+ // it won't be available when Statement.getGeneratedKeys() is called.
+ // For now, just as a hack to get it working, comment it out.
+// if (autoGeneratedKeysRowsHolder != null) {
+// autoGeneratedKeysRowsHolder.close();
+// }
{code}
The issue, as I see it, is that the responsibility for tracking, and for
eventually
releasing, the information about the generated keys is spread across four
different classes:
- BaseActivation knows whether we are tracking generated keys, and which
particular ones:
{code}
//Following three are used for JDBC3.0 auto-generated keys feature.
//autoGeneratedKeysResultSetMode will be set true if at the time of
statement execution,
//either Statement.RETURN_GENERATED_KEYS was passed or an array of
(column positions or
//column names) was passed
private boolean autoGeneratedKeysResultSetMode;
private int[] autoGeneratedKeysColumnIndexes ;
private String[] autoGeneratedKeysColumnNames ;
{code}
- EmbedStatement contains the result set which can be used to retrieve the
generated keys:
{code}
//for jdbc3.0 feature, where you can get a resultset of rows inserted
//for auto generated columns after an insert
private java.sql.ResultSet autoGeneratedKeysResultSet;
{code}
- DMLWriteGeneratedColumnsResultSet records the actual generated keys
themselves:
{code}
//following is for jdbc3.0 feature auto generated keys resultset
protected ResultSet autoGeneratedKeysResultSet;
protected TemporaryRowHolderImpl autoGeneratedKeysRowsHolder;
protected int[] autoGeneratedKeysColumnIndexes;
{code}
- and InsertResultSet, which subclasses DMLWriteGeneratedColumnsResultSet,
releases that data:
{code}
public void close() throws StandardException {
close( constants.underMerge() );
if (autoGeneratedKeysRowsHolder != null) {
autoGeneratedKeysRowsHolder.close();
}
}
{code}
Moreover, this part of the code is quite tricky because it is built atop a much
older feature (IDENTITY_VAL_LOCAL) which tracked the AUTOINCREMENT
column feature and supports very old databases which implemented this
feature prior to the introduction of the SEQUENCE object into Derby.
So changing any of this stuff is quite delicate.
But it does seem to me that the information about the keys that were generated
is (at least partly) in the wrong place: it belongs with the statement, not with
the result set, because its lifetime should match the statements lifetime, not
the
InsertResultSet's lifetime.
So perhaps an incremental first step is to accomplish such a refactoring?
> Statement.RETURN_GENERATED_KEYS returns a 1 row result set even if there are
> no auto-generated fields
> -----------------------------------------------------------------------------------------------------
>
> Key: DERBY-6849
> URL: https://issues.apache.org/jira/browse/DERBY-6849
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects Versions: 10.9.1.0
> Reporter: John Hendrikx
> Attachments: DERBY6849Repro.java, firstExperimentNotForCommit.diff,
> generated-keys-with-tests.diff
>
>
> If:
> 1) A JDBC INSERT statement is executed, with Statement.RETURN_GENERATED_KEYS
> enabled, and
> 2) A call is then made to Statement.getGeneratedKeys, and
> 3) The table which was inserted into has *NO* generated columns,
> then getGeneratedKeys() returns a ResultSet object with a single row in it.
> This behavior seems incorrect; it seems that the correct behavior
> would be to return a ResultSet object which has *NO* rows in it, so
> that ResultSet.next() returns FALSE the first time it is called.
>
> I have a very simple table:
> {noformat}
> CREATE TABLE images (
> url varchar(1000) NOT NULL,
> image blob NOT NULL,
>
> CONSTRAINT images_url PRIMARY KEY (url)
> );
> {noformat}
> No auto-generated fields. However when I do an insert, JDBC tells me there
> are auto-generated keys (rs.next() does not return false and a LONG value is
> returned):
> {noformat}
> try(PreparedStatement statement = connection.prepareStatement(sql,
> Statement.RETURN_GENERATED_KEYS)) {
> setParameters(parameterValues, statement);
> statement.execute();
> try(ResultSet rs = statement.getGeneratedKeys()) {
> if(rs.next()) {
> return rs.getObject(1);
> }
> return null;
> }
> }
> catch(SQLException e) {
> throw new DatabaseException(this, sql + ": " + parameters, e);
> }
> {noformat}
> This sounds like a bug to me. For comparison, PostgreSQL does not have the
> same behaviour.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
