db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Army <a...@golux.com>
Subject Re: On DERBY-107 : ODBC Metadata functions
Date Tue, 25 Jan 2005 19:18:42 GMT
Thanks much for your input, Kathey.  My comments below.

> Kathey Marsden wrote:
> After talking to Army some more about this, it seems there are three
> different types of difference between JDBC and ODBC,
> 1) Changes that could be made to our metadata.properties that would make
> it continue to be JDBC compliant but would satisfy the ODBC
> requirements. [ snip ] For these I think we change the metadata.properties
> as much as possible to conform to both.
> 2) Column Name changes. e.g JDBC returns RADIX for getProcedureColumns
> whereas ODBC uses NUM_PREC_RADIX and rearranging of columns.  For these
> we could either make a VTI or perhaps there is some more direct internal
> way to hack at the metadata. [ snip ]
> 3) Extra columns in ODBC.
> For getProcedureColumns, the spec explicitly says it's ok to have extra
> columns.  I wonder if that would be ok for other methods too.

[ snip ]

My tentative guess is that Yes, this okay.  At least, it IS okay in ODBC--I found the following
in the ODBC
specification regarding all of the metadata functions in question:

"The result set for each catalog function is described in the reference entry for that function.
In addition to the 
listed columns, the result set can contain driver-specific columns after the last predefined
column. These columns (if
any) are described in the driver documentation."

This page goes on to describe how user applications should reference driver-specific columns
in order minimize the need
for changes when "new columns are added to the result set in future versions of OBDC or the
driver".  This text can be
found at the following ODBC spec URL:


> [ snip ]
> Army, could you verify that these are the only three types of changes?

Those are the three kinds of changes that I've seen, yes.  It's worth mentioning, though,
that as I went through and
double-checked the set of changes I've had to make, I noticed the following minor issues:

A. For ODBC, we'd need to cast any "boolean" columns to SMALLINT, such as for the getTypeInfo()
method.  That said,
though, I think this still falls under #1 of your list, assuming that a call to getBoolean()
on a SMALLINT column will
still work...

B. CHAR_OCTET_LENGTH: JDBC says "for char types", ODBC says for "character _or_ binary type"--so
if we return a value
for a binary column, is JDBC going to complain?  I doubt it, but I thought I'd mention it.

C. For NUM_PREC_RADIX columns, Derby currently returns 0 for non-numeric types, which is okay
under the vague Java spec
of "usually 2 or 10".  However, the ODBC spec says that if the column isn't numeric, these
columns should be NULL.  I
think a similar thing occurs with SCALE/DECIMAL_DIGITS.  If we start returning a NULL for
non-numeric columns in JDBC,
will there be problems?

> If done with a VTI I would see it happenning like this:

[ snip ]

Thanks for the overview of how this might work.  I will look at it to see if I can get this

> In DD_Version.upgradeIfNeeded()  the JDBC Metadata  SPS's get dropped
> and recreated with any version change up or down so changes to the
> stored prepared statements should be ok as the version changes with this
> change

Ok, good to know.  Thanks for looking that up!

> I really think copying the queries, especially the really 
> complex ones is a bad idea, especially if we start having
> to support multiple ODBC versions.


On a related but different note, in looking at the Java spec while writing this email, I noticed
that getColumns has 4
new columns that are not in metadata.properties (SCOPE_CATLOG, SCOPE_SCHEMA, SCOPE_TABLE,
and SOURCE_DATA_TYPE)--is that
something Derby should be concerned about?

Thanks again for great feedback, Kathey!

View raw message