ADBA feedback

[Lance Andersen]

> On Apr 30, 2018, at 12:25 PM, Douglas Surber <douglas.surber at oracle.com> wrote:
> 
> I have maintained two JDBC apps. One uses column indexes and the other column names. Maintenance on the one that uses indexes is a nightmare. Everyone that works on it spends an inordinate amount of time counting and recounting columns trying to get the indexes right. Every time a query or table changes there is a lot of breakage because the column indexes shift. Fixing that is painful. Using names these problems do not occur.
> 
> I understand your arguments for column indexes. My experience has been that they are problematic. I’d like to find a solution that addresses the use cases you describe and at the same time addresses the maintenance problems I have seen with using indexes.
> 
> The following is not strictly a serious proposal. It is a attempt to inspire alternate solutions.
> 
>  /**
>   * Return the value of the column identified by name at the specified index. If both name and
>   * index are specified then the column at the specified index must be identified by name. Either
>   * but not both may be null in which case only the non-null value is used.
>   */
>  public <T> T get(String name, int index, Class<T> type) throws IllegalArgumentException;
> 
> If there are several similar columns in sequence, then an off-by-one error may be difficult to detect. The redundant specification of the column makes it easier to detect such errors. While it allows runtime detection of errors, it doesn’t eliminate column counting and index shifting.
> 
> With respect to case, I’d say the spec should be silent. Vendors can do whatever is appropriate for their database. For example Oracle Database would likely ignore case but would support case-sensitive double quoted identifiers, “\”lower case identifier with spaces\”’.

There does need to be something said to set expectations 
> 
> Douglas
> 
>> On Apr 30, 2018, at 1:59 AM, Mark Rotteveel <mark at lawinegevaar.nl> wrote:
>> 
>> On 30-4-2018 10:35, Lukas Eder wrote:
>>> 2018-04-30 10:28 GMT+02:00 Mark Rotteveel <mark at lawinegevaar.nl <mailto:mark at lawinegevaar.nl>>:
>>>   Retrieval by column label in JDBC is case insensitive by
>>>   specification, and I think that ADBA should maintain that. That is
>>>   irrespective if the label was originally case sensitive or not.
>>> Interesting, thanks I wasn't aware of that (you see, I've never really used the API) :)
>> 
>> Not sure if you're joking or not :|
>> 
>>> So, in the event of a query like this:
>>> SELECT 1 AS "A", 2 AS "a"
>>> In JDBC, the index-based API is the only one that works, despite the query being correct SQL, producing two explicitly distinct column names (even if that's an edge case).
>> 
>> Yes, and in JDBC that is also in the specification :):
>> 
>> """
>> Column names used as input to getter methods are case insensitive.

Right that is basically stating what the SQL Standard does indirectly.

The SQL Standard indicates that lowercase letters are converted to uppercase, basically case insensitive.  Unfortunately nothing was said above to deal with delimited identifiers.  But this part of the JDBC spec was from 1997
>> When a getter method is called with a column name and several columns have the same name, the value of the first matching column will be returned. The column name option is designed to be used when column names are used in the SQL query that generated the result set. For columns that are NOT explicitly named in the query, it is best to use column numbers. If column names are used, the programmer should take care to guarantee that they uniquely refer to the intended columns, which can be assured with the SQL AS clause.
>> “”

>> "
>> 
>> -- 
>> Mark Rotteveel
> 

 <http://oracle.com/us/design/oracle-email-sig-198324.gif>
 <http://oracle.com/us/design/oracle-email-sig-198324.gif> <http://oracle.com/us/design/oracle-email-sig-198324.gif>
 <http://oracle.com/us/design/oracle-email-sig-198324.gif>Lance Andersen| Principal Member of Technical Staff | +1.781.442.2037
Oracle Java Engineering 
1 Network Drive 
Burlington, MA 01803
Lance.Andersen at oracle.com <mailto:Lance.Andersen at oracle.com>