JDBC4 Spec exegesis needed, was: [jira] Commented: (DERBY-925) Implement new JDBC 4 metadata API getFunctionParameters()

[Rick Hillegas <Ri...@Sun.COM>]

Hi Lance,

Is it OK for a JDBC3 implementation to return more information than the 
spec demands? In particular, consider the various result sets returned 
by DatabaseMetaData calls. Is it OK for these result sets to contain 
additional, trailing columns, above and beyond the columns required by 
the JDBC3 spec? To be even more pedantic, can JDBC3 implementations 
return the fatter JDBC4 result sets?

Thanks,
-Rick

Dyre Tjeldvoll (JIRA) wrote:

>    [ http://issues.apache.org/jira/browse/DERBY-925?page=comments#action_12368699 ] 
>
>Dyre Tjeldvoll commented on DERBY-925:
>--------------------------------------
>
>I just realized that DatabaseMetaData.getProcedureColumns() has been changed in JDBC 4.0. The result set it returns now conatains a number of new columns and is, in fact, a super set of the columns returned by getFunctionParameters. For JDBC 4.0 it will likely be necessary to extend the existing GetProcedureColumns.java VTI with much of the same information that I was thinking about putting into the new VTI. Perhaps both methods can be implemented with queries against a single VTI? We will probably need a separate getProcedureColumns40 query in metadata.properties to maintain backward compatibility?
>
>  
>
>>Implement new JDBC 4 metadata API getFunctionParameters()
>>---------------------------------------------------------
>>
>>         Key: DERBY-925
>>         URL: http://issues.apache.org/jira/browse/DERBY-925
>>     Project: Derby
>>        Type: New Feature
>>  Components: JDBC
>>    Versions: 10.2.0.0
>> Environment: JDK 1.6
>>    Reporter: David Van Couvering
>>    Assignee: Dyre Tjeldvoll
>> Attachments: TypePrinter.java
>>
>>I am currently implementing this to return an empty result set so at least we're compliant, but we should be able to provide real metadata here.
>>    
>>
>
>  
>

Re: JDBC4 Spec exegesis needed, was: [jira] Commented: (DERBY-925) Implement new JDBC 4 metadata API getFunctionParameters()

["Lance J. Andersen" <La...@Sun.COM>]

Daniel John Debrunner wrote:
> Rick Hillegas wrote:
>
>   
>> Hi Lance,
>>
>> Is it OK for a JDBC3 implementation to return more information than the
>> spec demands? In particular, consider the various result sets returned
>> by DatabaseMetaData calls. Is it OK for these result sets to contain
>> additional, trailing columns, above and beyond the columns required by
>> the JDBC3 spec? To be even more pedantic, can JDBC3 implementations
>> return the fatter JDBC4 result sets?
>>     
>
> JDBC tutorial & reference, version 2.0, sdection 15.2.2, page 369 says:
>
> "A DBMS may define additional columns for a ResultSet object that is
> returned by a DatabaseMetaData method".
>
> Not officially part of the spec, but I've always assumed the tutorial
> books are a good guideline of the spec, given the authors.
>   
While it is a good guideline, it in fact has differed at times resulting 
in driver implementation differences.

The methods in DatabaseMetaData which allow additional columns is 
documented in the javadocs.    However, it would not have been wise as a 
programmer to use anything but the column name when accessing 
non-standard columns.

-lance
> Dan.
>
>
>   

Re: JDBC4 Spec exegesis needed, was: [jira] Commented: (DERBY-925) Implement new JDBC 4 metadata API getFunctionParameters()

["Lance J. Andersen" <La...@Sun.COM>]

We had a long EG discussion on this and the wording changes was the only 
way we could provide portability and extendability going forward.

Having vendors just throw their own entries into the ResultSet is not 
very portable either nor is the ODBC way of changing column names which 
is something we chose not to do for JDBC.

-lance

Army wrote:
> Dyre.Tjeldvoll@Sun.COM wrote:
>> On a related note, getProcedureColumns() already does that today. The
>> returned result set has two additional columns (METHOD_ID &
>> PARAMETER_ID) that aren't part of the spec. This does not seem to
>> cause any problems, but where should these two columns go when we add
>> 7 new JDBC4 columns to the result set?
>> The JDBC4 javadoc says:
>>
>> "Additional columns beyond SPECIFIC_NAME can be defined by the
>> database and must be accessed by their column name."
>
> For what it's worth, the ODBC API says this with respect to the 
> metadata methods:
>
> "Applications should bind driver-specific columns relative to the end 
> of the result set."
>
> Odd that ODBC says the extra columns should be accessed by position, 
> while JDBC4 says they should be accessed by name.  Or am I misreading 
> something?  One of the arguments for doing it this way is that the 
> column names could change from one version to the other--as they have, 
> for example, between ODBC 2.0 and ODBC 3.0...
>
> There is some additional detail at this link:
>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcdata_returned_by_catalog_functions.asp 
>
>
> <begin-quote>
>
> Applications should bind driver-specific columns relative to the end 
> of the result set. That is, they should calculate the number of a 
> driver-specific column as the number of the last column — retrieved 
> with SQLNumResultCols — less the number of columns that occur after 
> the required column. This saves having to change the application when 
> new columns are added to the result set in future versions of ODBC or 
> the driver. For this scheme to work, drivers must add new 
> driver-specific columns before old driver-specific columns so that 
> column numbers do not change relative to the end of the result set.
>
> <end-quote>
>
>> Does that mean it is safe to put these extra columns at the end? Will
>> that not potentially break existing applications? One could argue that
>> only poorly written apps will have a problem with this I suppose, but
>> still...
>
> If I'm understanding correctly, that's the argument ODBC makes ;)  
> I.e. a "well-written" app should reference the extra columns by 
> position relative to the end of the result set; if they do that, then 
> their apps will still work even if there are extra columns at the 
> end--*ASSUMING* that the driver, in turn, does its part by adding "new 
> driver-specific columns before old driver-specific columns so that 
> column numbers do not change relative to the end of the result set."
>
> Maybe that's all irrelevant, but it seems like the general trend in 
> the metadata area is for JDBC to follow ODBC leads (in fact, I think 
> Lance said that very thing some time long long ago...?), so I thought 
> I'd mention it.
>
>> But maybe this is all hypothetical... :)
>
> Ditto.
>
> Army
>

Re: JDBC4 Spec exegesis needed, was: [jira] Commented: (DERBY-925) Implement new JDBC 4 metadata API getFunctionParameters()

[Army <qo...@sbcglobal.net>]

Dyre.Tjeldvoll@Sun.COM wrote:
> On a related note, getProcedureColumns() already does that today. The
> returned result set has two additional columns (METHOD_ID &
> PARAMETER_ID) that aren't part of the spec. This does not seem to
> cause any problems, but where should these two columns go when we add
> 7 new JDBC4 columns to the result set? 
> 
> The JDBC4 javadoc says:
> 
> "Additional columns beyond SPECIFIC_NAME can be defined by the
> database and must be accessed by their column name."

For what it's worth, the ODBC API says this with respect to the metadata methods:

"Applications should bind driver-specific columns relative to the end of the 
result set."

Odd that ODBC says the extra columns should be accessed by position, while JDBC4 
says they should be accessed by name.  Or am I misreading something?  One of the 
arguments for doing it this way is that the column names could change from one 
version to the other--as they have, for example, between ODBC 2.0 and ODBC 3.0...

There is some additional detail at this link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcdata_returned_by_catalog_functions.asp

<begin-quote>

Applications should bind driver-specific columns relative to the end of the 
result set. That is, they should calculate the number of a driver-specific 
column as the number of the last column — retrieved with SQLNumResultCols — less 
the number of columns that occur after the required column. This saves having to 
change the application when new columns are added to the result set in future 
versions of ODBC or the driver. For this scheme to work, drivers must add new 
driver-specific columns before old driver-specific columns so that column 
numbers do not change relative to the end of the result set.

<end-quote>

> Does that mean it is safe to put these extra columns at the end? Will
> that not potentially break existing applications? One could argue that
> only poorly written apps will have a problem with this I suppose, but
> still...

If I'm understanding correctly, that's the argument ODBC makes ;)  I.e. a 
"well-written" app should reference the extra columns by position relative to 
the end of the result set; if they do that, then their apps will still work even 
if there are extra columns at the end--*ASSUMING* that the driver, in turn, does 
its part by adding "new driver-specific columns before old driver-specific 
columns so that column numbers do not change relative to the end of the result set."

Maybe that's all irrelevant, but it seems like the general trend in the metadata 
area is for JDBC to follow ODBC leads (in fact, I think Lance said that very 
thing some time long long ago...?), so I thought I'd mention it.

> But maybe this is all hypothetical... :)

Ditto.

Army

Re: JDBC4 Spec exegesis needed, was: [jira] Commented: (DERBY-925) Implement new JDBC 4 metadata API getFunctionParameters()

[Daniel John Debrunner <dj...@apache.org>]

Dyre.Tjeldvoll@Sun.COM wrote:

> Daniel John Debrunner <dj...@apache.org> writes:
> 
> 
>>Rick Hillegas wrote:
>>
>>
>>>Hi Lance,
>>>
>>>Is it OK for a JDBC3 implementation to return more information than the
>>>spec demands? In particular, consider the various result sets returned
>>>by DatabaseMetaData calls. Is it OK for these result sets to contain
>>>additional, trailing columns, above and beyond the columns required by
>>>the JDBC3 spec? To be even more pedantic, can JDBC3 implementations
>>>return the fatter JDBC4 result sets?
>>
>>JDBC tutorial & reference, version 2.0, sdection 15.2.2, page 369 says:
>>
>>"A DBMS may define additional columns for a ResultSet object that is
>>returned by a DatabaseMetaData method".
> 
> 
> On a related note, getProcedureColumns() already does that today. The
> returned result set has two additional columns (METHOD_ID &
> PARAMETER_ID) that aren't part of the spec. This does not seem to
> cause any problems, but where should these two columns go when we add
> 7 new JDBC4 columns to the result set? 
> 
> The JDBC4 javadoc says:
> 
> "Additional columns beyond SPECIFIC_NAME can be defined by the
> database and must be accessed by their column name."
> 
> Does that mean it is safe to put these extra columns at the end? Will
> that not potentially break existing applications? One could argue that
> only poorly written apps will have a problem with this I suppose, but
> still...
> 
> These two columns are also used to order the result set:

And that's all they are used for, basically internal use. There is no
exernal documentation saying these columns exist or what they mean so I
don't think applications can be seen to be relying on them. Thus I then
they can be moved to the end, without breaking existing applications.

I think there are really there to stop the test output being
inconsistent. It may not be relevant now, it did when a procedure meant
a Cloudscape method alias which mapped to a set of Java methods. The
parameters for this method were found using reflection and the ordering
of infomation from reflection is not guaranteed, leading to different
ordering on different JVMs.

> ...
> ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, METHOD_ID, PARAMETER_ID
> 
> The JDBC4 javadoc states that
> 
> "They are ordered by PROCEDURE_SCHEM, PROCEDURE_NAME and SPECIFIC_NAME." 
> 
> So if we keep the existing ordering we're not compliant, but if we
> change it we risk breaking clients that rely on the existing ordering.

The only ordering clients should be relying on, is that documented by
the DatabaseMetadata which for JDBC 3.0 is PROCEDURE_SCHEM, PROCEDURE_NAME.

So changing the ordering to this would be compatible with JDBC 3.0 and
JDBC 4.0 and applications that are written against the specs.

ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, SPECIFIC_NAME, METHOD_ID,
PARAMETER_ID

A different issue could be investigating if we still need the additional
ordering now that Derby only supports well defined procedures and not
method aliases.

Thanks for looking into this Dyre.
Dan.

Re: JDBC4 Spec exegesis needed, was: [jira] Commented: (DERBY-925) Implement new JDBC 4 metadata API getFunctionParameters()

[Dy...@Sun.COM]

Daniel John Debrunner <dj...@apache.org> writes:

> Rick Hillegas wrote:
>
>> Hi Lance,
>> 
>> Is it OK for a JDBC3 implementation to return more information than the
>> spec demands? In particular, consider the various result sets returned
>> by DatabaseMetaData calls. Is it OK for these result sets to contain
>> additional, trailing columns, above and beyond the columns required by
>> the JDBC3 spec? To be even more pedantic, can JDBC3 implementations
>> return the fatter JDBC4 result sets?
>
> JDBC tutorial & reference, version 2.0, sdection 15.2.2, page 369 says:
>
> "A DBMS may define additional columns for a ResultSet object that is
> returned by a DatabaseMetaData method".

On a related note, getProcedureColumns() already does that today. The
returned result set has two additional columns (METHOD_ID &
PARAMETER_ID) that aren't part of the spec. This does not seem to
cause any problems, but where should these two columns go when we add
7 new JDBC4 columns to the result set? 

The JDBC4 javadoc says:

"Additional columns beyond SPECIFIC_NAME can be defined by the
database and must be accessed by their column name."

Does that mean it is safe to put these extra columns at the end? Will
that not potentially break existing applications? One could argue that
only poorly written apps will have a problem with this I suppose, but
still...

These two columns are also used to order the result set:

...
ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, METHOD_ID, PARAMETER_ID

The JDBC4 javadoc states that

"They are ordered by PROCEDURE_SCHEM, PROCEDURE_NAME and SPECIFIC_NAME." 

So if we keep the existing ordering we're not compliant, but if we
change it we risk breaking clients that rely on the existing ordering.

But maybe this is all hypothetical... :)

-- 
dt

Re: JDBC4 Spec exegesis needed, was: [jira] Commented: (DERBY-925) Implement new JDBC 4 metadata API getFunctionParameters()

[Daniel John Debrunner <dj...@apache.org>]

Rick Hillegas wrote:

> Hi Lance,
> 
> Is it OK for a JDBC3 implementation to return more information than the
> spec demands? In particular, consider the various result sets returned
> by DatabaseMetaData calls. Is it OK for these result sets to contain
> additional, trailing columns, above and beyond the columns required by
> the JDBC3 spec? To be even more pedantic, can JDBC3 implementations
> return the fatter JDBC4 result sets?

JDBC tutorial & reference, version 2.0, sdection 15.2.2, page 369 says:

"A DBMS may define additional columns for a ResultSet object that is
returned by a DatabaseMetaData method".

Not officially part of the spec, but I've always assumed the tutorial
books are a good guideline of the spec, given the authors.

Dan.

Re: JDBC4 Spec exegesis needed, was: [jira] Commented: (DERBY-925) Implement new JDBC 4 metadata API getFunctionParameters()

["Lance J. Andersen" <La...@Sun.COM>]

Hi Rick,

There is a limited number of DatabaseMetaData methods have allowed this 
to occur as part of the functionality.  This was unfortunate IMHO.

-lance

Rick Hillegas wrote:
> Hi Lance,
>
> Is it OK for a JDBC3 implementation to return more information than 
> the spec demands? In particular, consider the various result sets 
> returned by DatabaseMetaData calls. Is it OK for these result sets to 
> contain additional, trailing columns, above and beyond the columns 
> required by the JDBC3 spec? To be even more pedantic, can JDBC3 
> implementations return the fatter JDBC4 result sets?
>
> Thanks,
> -Rick
>
> Dyre Tjeldvoll (JIRA) wrote:
>
>>    [ 
>> http://issues.apache.org/jira/browse/DERBY-925?page=comments#action_12368699 
>> ]
>> Dyre Tjeldvoll commented on DERBY-925:
>> --------------------------------------
>>
>> I just realized that DatabaseMetaData.getProcedureColumns() has been 
>> changed in JDBC 4.0. The result set it returns now conatains a number 
>> of new columns and is, in fact, a super set of the columns returned 
>> by getFunctionParameters. For JDBC 4.0 it will likely be necessary to 
>> extend the existing GetProcedureColumns.java VTI with much of the 
>> same information that I was thinking about putting into the new VTI. 
>> Perhaps both methods can be implemented with queries against a single 
>> VTI? We will probably need a separate getProcedureColumns40 query in 
>> metadata.properties to maintain backward compatibility?
>>
>>  
>>
>>> Implement new JDBC 4 metadata API getFunctionParameters()
>>> ---------------------------------------------------------
>>>
>>>         Key: DERBY-925
>>>         URL: http://issues.apache.org/jira/browse/DERBY-925
>>>     Project: Derby
>>>        Type: New Feature
>>>  Components: JDBC
>>>    Versions: 10.2.0.0
>>> Environment: JDK 1.6
>>>    Reporter: David Van Couvering
>>>    Assignee: Dyre Tjeldvoll
>>> Attachments: TypePrinter.java
>>>
>>> I am currently implementing this to return an empty result set so at 
>>> least we're compliant, but we should be able to provide real 
>>> metadata here.
>>>   
>>
>>  
>>
>