I discussed this briefly at my JDBC EG meeting yesterday.
As i expected, all of the vendors on the call indicated that they return
the same data type for key returned in the case of column defined as
identity via the getGeneratedKeys() method. The consensus was that this
is what a user would expect.
As to the unique key question posed by Dan, this is going to be an
ongoing EG discussion as some vendors do return identity columns values
in cases that are not unique (because the backend like Derby allows for
it) which gets complex as some vendors also in some cases support
returning a ROWID currently (but this is a difference scenario then
using a defined column in the table).
The behavior of how JDBC methods on the ResultSet/ResultSetMetaData work
do not change. The value returned will differ for ResultSetMetaData for
the returned column type.
This issue pointed out a problem in the JDBC EoD RI which made the
assumption that the value returned matched the column type in the base
table.
A Derby user encountered this issue as well, trying to use 10.2 and JDBC
EoD http://binkley.blogspot.com/2006/04/nifty-jdbc-40.html.
HTH
-lance
Rick Hillegas wrote:
Hi Kathey,
Thanks for your responses. Some replies follow. Regards-Rick
Kathey Marsden wrote:
Rick Hillegas wrote:
I'd like to try to summarize where I think the discussion stands:
1) Lance, our JDBC expert, has confirmed that this is not a
compliance problem. That means this is not a bug.
2) Lance would like to change the behavior of
Statement.getGeneratedKeys(). Currently this method always returns a
ResultSet whose column has the canonical type DECIMAL( 31, 0). He
would like this method to return a ResultSet whose column type
changes depending on the type of the actual autogenerated column in
the affected table; that is, the column could have type SMALLINT,
INT, or BIGINT.
3) It does not seem that this change would have a very big impact on
customers. At least, we have not been able to imagine how this would
impact customers adversely. However, this is just theory and we have
not polled the user community yet.
We not only have not polled the user community, we do not have
anything we can poll them with yet. getGeneratedKeys returns a
result set. Users will call certain methods on that ResultSet and
the return values will be different. We need to define what those
are and the potential impact. Then we map them to the user symptom
and then we can define scenarios that might be affected. If it is
important that we break our current documented behavior we have to
take these painful steps to assess risk. A vague poll without
understanding the possible impact ourselves and presenting it
clearly is not effective or fair to the user base as we found with
DERBY-1459.
Can you please complete the list below with any other changes in the
result set returned by getGeneratedKeys or confirm that there are no
other calls impacted? Let's not include the likely of each happening
yet. We just want to understand what has changed and what symptoms
users might see.
I agree with what we have so far the risk is low but w need to go
through the whole exercise. How has the result set returned
changed? What symptoms might users see? Define user scenarios and
risk. Then poll the user community.
Certainly there would be these changes for the ResultSet returned by
getGeneratedKeys():
o getMetaData() would correspond to the ResultSetMetadata of the
base table column and so will have different types, columnwidths etc,
so formatting and other decisions based on this information may be
affected.
Agreed.
o getObject() would return a different type and applications
making casts based on the assumption it is a BigDecimal may see cast
exceptions or other problematic behavior because of this assumption.
Agreed.
o getString() would return a different String representation which
might be problematic if a particular format was expected and parsed.
This doesn't appear to be true for the small integers with which I've
experimented. Are there problems in the toString() methods of
BigDecimal and (perhaps) Derby's j2me decimal object?
Would other ResultSet methods might be affected? For instance,
would getInt(), getLong(), getShort() etc. all still work as they
did before and return the same values?
They should.
So what do we think?
A) Does anyone prefer the current behavior over Lance's proposed
behavior?
Only in that it saves a lot of time in risk assesssment and not
changing it prevents us from setting a precedent for changing
documented and compliant behaviour to something else with no really
significant benefit to users, but rather just the sake of tidiness
and the convenience of writing code that is not guaranteed to be
portable to other JDBC Drivers.
http://wiki.apache.org/db-derby/ForwardCompatibility#head-1aa64e215b1979230b8d9440e3e21d43c3d85778
Also I would think the client implementation might be a little hairy.
We'd need to make a new system procedure returning a result set and
there would be mixed version compatibility issues no doubt and maybe
transactional related issues and I'm not so keen on reviewing all
that, but of course if someone wants to invest their time in it, the
review time is just the price I pay for being a compatibility zealot.
One possible other benefit and I think the one that Lance is getting
at with the change is that if changed, Derby could over time become
a defacto standard and help clarify the spec for new JDBC Driver
implementations . It is probably the best reason I see for the
change and an interesting role for Derby to play. If that is the
reason why we are doing it, that should be a conscious decision.
B) Does this kind of change violate our policies for minor releases?
I think the main thing is that breaking compatibility just has to be
a conscious decision by the community. Once the analysis is made
that decision can happen.
C) Are there other objections to this change?
I think due diligence is needed as described above. If that is done
and there is no further impact identified I wouldn't veto it, but
without it I would.
D) Would we like to poll the user community now?
No see above.