Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Received-SPF: pass (asf.osuosl.org: local policy)
Date: Thu, 13 Jul 2006 13:00:19 -0400
From: "Lance J. Andersen" <Lance.Andersen@Sun.COM>
Subject: Re: behavior of Statement.getGeneratedKeys()
In-reply-to: <44B412FA.1080304@sun.com>
Sender: Lance.Andersen@Sun.COM
To: derby-dev@db.apache.org
Message-id: <44B67C23.5030803@sun.com>
MIME-version: 1.0
Content-type: text/plain; format=flowed; charset=ISO-8859-1
Content-transfer-encoding: 7BIT
References: <44B29397.4030608@sun.com> <44B2A411.3050000@sbcglobal.net>
 <44B2B783.1090102@sun.com> <44B2CE1F.4030800@apache.org>
 <44B2E248.3080904@sun.com> <44B332CF.6000604@sbcglobal.net>
 <44B33894.4070008@apache.org> <44B3BDE6.4020801@sun.com>
 <44B40B2C.2000708@sbcglobal.net> <44B412FA.1080304@sun.com>
User-Agent: Thunderbird 1.5.0.4 (Windows/20060516)

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.
>>
>