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:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcdata_returned_by_catalog_functions.asp)
[ 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 implemented...
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.
Agreed.
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! Army
