You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by Army <ar...@golux.com> on 2005/01/21 23:49:43 UTC
On DERBY-107 : ODBC Metadata functions
Kathey Marsden and I had an offline discussion earlier today regarding the addition of ODBC-compliant metadata functions
to the Derby engine. That discussion is summarized below. While feedback on all aspects of this discussion are of
course encouraged, sections labelled with "[ **** COMMUNITY INPUT? **** ]" are specific subjects/questions for which
input from the Derby community would be helpful and appreciated.
If you don't have time or motivation to read this entire email, you might at least want to search for the community
input tag (there are 3 in this message) and see what the questions at hand are; we could certainly use any input people
might have.
-----------------
BACKGROUND:
Per Derby-107, I (Army) am working to add ODBC-compliant metadata functions to the Derby engine. This will allow
connections made to Derby Network Server via an ODBC driver to retrieve Derby metadata in a format that conforms to the
ODBC specification, which can be found here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsqlcolumns.asp
The following is pasted from the proposal description of DERBY-107:
-- begin paste --
Metadata calls over Network Server are executed via system procedures that are built-in to Derby--and these system
procedures then map to the SQL statements [ found in Derby's "metadata.properties" file]. The procedures already have
an "OPTION" parameter that accepts a string parameter, so what I would propose is that we add logic to recognize a
"DATATYPE" keyword as part of this string--basically, if we see the string "DATATYPE='ODBC'" in the OPTION parameter,
then the stored procedure would map to a new set of SQL statements in the "metadata.properties" file--and these new
statements would return result sets that conform to the ODBC standard. Otherwise, if "DATATYPE='JDBC'" or else no
DATATYPE keyword is specified, the current SQL metadata statements would be used by default.
-- end paste --
At this point, I have 1) written the relevant ODBC-compliant SQL statements (though I still need to revise and test
them, of course!), 2) added those SQL statements to "metadata.properties" file, and 3) added logic to recognize the
"DATATYPE='ODBC'" option in the org.apache.derby.catalog.SystemProcedures.java file.
EXAMPLE:
(Note: this is written insofar as I understand it; if I'm saying anything wrong here, feel free to correct me)
Consider the java.sql.DatabaseMetaData method "getColumns". Currently, if an application makes a JDBC call to this
getColumns() method against a Derby driver, Derby will map it to an internal system procedure called "SQLCOLUMNS", the
definition of which resides in the SystemProcedures class. That method, in turn, fetches an instance of Derby's
EmbedDatabaseMetadata class, and then calls Derby's implementation of getColumns() on that instance. The
EmbedDatabaseMetadata class then executes the appropriate prepared SQL statement from the "metadata.properties" file,
and returns the ResultSet.
That said, it is the SQLCOLUMNS procedure in SystemProcedures that takes the OPTION parameter mentioned above, and so it
is that method which must recognize the "DATATYPE='ODBC'" keyword.
DISCUSSION:
The question is now this: what's the best/preferred way to propagate this ODBC/JDBC duality from the
"SystemProcedures.java" file to the corresponding methods in org.apache.derby.impl.jdbc.EmbedDatabaseMetadata.java
(hereafter referred to as "EDM")?
Option I:
Add new SQL statements, such as "getColumnsForODBC", to the existing metadata.properties file, as described in the
proposal for DERBY-107. Then, since EDM has to know which version of a given SQL statement to execute--for example,
should it call the regular "getColumns" version, or should it call the new "getColumnsForODBC" version?--we could add
new methods (such as "setForODBC()") to EDM that could be used by SystemProcedures to indicate (to EDM) that ODBC
metadata should be returned, intead of JDBC metadata. Note that, since SystemProcedures is in a different package than
EDM, the new methods on EDM would (I think) have to be _public_.
Regarding this approach, one must ask:
[ #1 **** COMMUNITY INPUT? **** ]
What's the general attitude toward adding public methods to a Derby class that is implementing a specific JDBC class?
In the context of this discussion, is it or is it not acceptable/desireable to add Derby-specific public methods to a
class like EmbedDatabaseMetadata.java, which is an implementation of java.sql.DatabaseMetaData? Technically speaking, I
don't think the addition of public classes breaks the JDBC standard (so long as we aren't telling people that they can
import EmbedDatabaseMetadata in their apps--which we aren't), but I'm curious as to whether there's a "good programming
practice" here that the Derby community would like to (or already does?) hold to?
[ #1 **** End **** ]
Option II:
Add new SQL statements, such as "getColumnsForODBC", to the existing metadata.properties file, as described in the
proposal for DERBY-107. Then we could extend the EDM class with a new, simple class that sets ODBC-related state, and
modify EDM to check the state and execute the appropriate statements. For example, we could add a protected variable
"forODBC" to EDM, default it to "false", and then set it to true in the extended class for ODBC. EDM would then check
the flag and execute the corresponding metadata statement. The presumption here is that SystemProcedures would check
for the ODBC indicator and, if found, use an instance of the new subclass for the metadata calls, instead of using an
instance of the existing EDM.
This approach allows us to avoid adding new (non-JDBC) public classes to EDM, at the cost of creating another (albeit
fairly simple) metadata class.
With this approach, we could even go further and add another file, say "odbc_metadata.properties" that holds the ODBC
metadata statements (instead of adding them to the existing metadata.properties file). The new subclass could then load
_that_ file instead of the current metadata.properties file, which gives us a nice separation of functionality: all of
the ODBC code cleanly separated from the JDBC code. Of course, that could be a bad thing, too, since 1) we'd then have
TWO metadata files to worry about in the codeline, instead of just one, which introduces room for error if/when
metadata-related processing changes occur in Derby, and 2) we'd have to duplicate any SQL statements that are the same
for ODBC and JDBC (ex. several of the "getBestRowIdentifier" queries) in both files. So I'm guessing we wouldn't want
to create another metadata file...but I thought I'd bring it up, just in case.
Option III:
Create some kind of internal VTI for ODBC metadata and use that. I have to admit that I don't know too much about how
VTIs work, but Kathey gave me some places to look, so I'm going to read up. Apparently, we can execute the same
metadata SQL statements that already exist for JDBC, then use a VTI to "massage" the result set into something that
complies with ODBC specifications. This might be a good choice given that most of the differences between ODBC and JDBC
are in the types of the columns returned. For example, JDBC might say that any String will do, whereas ODBC will say it
has to be VARCHAR. In that case, a literal value ' ' will be fine for JDBC, but since it's treated as a CHAR value by
Derby, it would be breaking ODBC standard. With a VTI, we could theoretically accomplish the same things that we'd be
doing with new SQL statements--such as casting ' ' to VARCHAR in this particular case. Other modifications we'd have to
implement include casting certain integer columns to smallints, and replacing null values in JDBC (such as for
"sql_data_type" and "buffer_length" columns) to legal values (neither column is supposed to be null for ODBC).
Upside to this is that we still only have a single metadata.properties file, which (theoretically) makes maintenance of
metadata procedures easier. As I don't know much about VTIs, I can't say what else this approach would require, but it
seems safe to say that it would at least require another class to serve as the ODBC VTI. How that would tie into the
SystemProcedures and EmbedDatabaseMetadata classes, I don't know yet...
So...
[ #2 **** COMMUNITY INPUT? **** ]
What are people's feelings/reactions/comments in terms of which of the above three options is most appropriate for the
Derby codeline? In a nutshell, it comes to 1) new public methods on EmbedDatabaseMetadata, 2) new subclass to
EmbedDatabaseMetadata that sets ODBC-related state (with or without creating a new odbc_metadata.properties file), and
3) create an ODBC VTI to manipulate existing JDBC result sets into ODBC-compliant ones.
NOTE: another thing we have to decide is if we're going to support different levels of ODBC metadata--for example, ODBC
1.0 metadata differs from ODBC 2.0 in some respects, and likewise for ODBC 3.0. Do we want to support all of the
different levels? If so, which of the above approaches is going to make that most feasible? My guess is that option
III would be the best choice in that case...
[ #2 **** End **** ]
The final part of Kathey's and my discussion involved the issue of Derby upgrade. While it IS true that, as Dan said,
"no upgrade code has been added to support upgrading databases from 10.0 to 10.1" (see
http://nagoya.apache.org/eyebrowse/ReadMsg?listName=derby-dev@db.apache.org&msgNo=1703), it nonetheless seems like a
good idea to consider what effects on upgrade the changes to Derby metadata might have.
[ #3 **** COMMUNITY INPUT? **** ]
Does anyone know anything about the upgrade implications for any of the above-mentioned approaches? Are there caveats
to be considered, or is there any advice that might save us headaches in the future when support for upgrades is
developed? For example, with Cloudscape upgrade/downgrade, did Cloudscape drop and then recreate the system stored
prepared statements? And if so, what are the odds that Derby will do the same? Would that be automatic, or would we
have to add logic for each of the prepared statements in question? Apologies in advance for my lack of knowledge in
that area...
[ #3 **** End **** ]
-----------------
Thanks to everyone who took the time to read all or part of this email.
Feeback is greatly appreciated...
Army
Re: On DERBY-107 : ODBC Metadata functions
Posted by Jack Klebanoff <kl...@Mutagen.Net>.
Army wrote:
> [snip]
>
> DISCUSSION:
>
> The question is now this: what's the best/preferred way to propagate
> this ODBC/JDBC duality from the "SystemProcedures.java" file to the
> corresponding methods in
> org.apache.derby.impl.jdbc.EmbedDatabaseMetadata.java (hereafter
> referred to as "EDM")?
>
> Option I:
>
> Add new SQL statements, such as "getColumnsForODBC", to the existing
> metadata.properties file, as described in the proposal for DERBY-107.
> Then, since EDM has to know which version of a given SQL statement to
> execute--for example, should it call the regular "getColumns" version,
> or should it call the new "getColumnsForODBC" version?--we could add
> new methods (such as "setForODBC()") to EDM that could be used by
> SystemProcedures to indicate (to EDM) that ODBC metadata should be
> returned, intead of JDBC metadata. Note that, since SystemProcedures
> is in a different package than EDM, the new methods on EDM would (I
> think) have to be _public_.
>
> Regarding this approach, one must ask:
>
> [ #1 **** COMMUNITY INPUT? **** ]
>
> What's the general attitude toward adding public methods to a Derby
> class that is implementing a specific JDBC class? In the context of
> this discussion, is it or is it not acceptable/desireable to add
> Derby-specific public methods to a class like
> EmbedDatabaseMetadata.java, which is an implementation of
> java.sql.DatabaseMetaData? Technically speaking, I don't think the
> addition of public classes breaks the JDBC standard (so long as we
> aren't telling people that they can import EmbedDatabaseMetadata in
> their apps--which we aren't), but I'm curious as to whether there's a
> "good programming practice" here that the Derby community would like
> to (or already does?) hold to?
>
> [ #1 **** End **** ]
I would prefer that the ODBC support not be put in the
EmbedDatabaseMetadata class. Then applications that do not use ODBC do
not have to load ODBC support into their JVM.
>
> Option II:
>
> Add new SQL statements, such as "getColumnsForODBC", to the existing
> metadata.properties file, as described in the proposal for DERBY-107.
> Then we could extend the EDM class with a new, simple class that sets
> ODBC-related state, and modify EDM to check the state and execute the
> appropriate statements. For example, we could add a protected
> variable "forODBC" to EDM, default it to "false", and then set it to
> true in the extended class for ODBC. EDM would then check the flag
> and execute the corresponding metadata statement. The presumption
> here is that SystemProcedures would check for the ODBC indicator and,
> if found, use an instance of the new subclass for the metadata calls,
> instead of using an instance of the existing EDM.
>
> This approach allows us to avoid adding new (non-JDBC) public classes
> to EDM, at the cost of creating another (albeit fairly simple)
> metadata class.
>
> With this approach, we could even go further and add another file, say
> "odbc_metadata.properties" that holds the ODBC metadata statements
> (instead of adding them to the existing metadata.properties file).
> The new subclass could then load _that_ file instead of the current
> metadata.properties file, which gives us a nice separation of
> functionality: all of the ODBC code cleanly separated from the JDBC
> code. Of course, that could be a bad thing, too, since 1) we'd then
> have TWO metadata files to worry about in the codeline, instead of
> just one, which introduces room for error if/when metadata-related
> processing changes occur in Derby, and 2) we'd have to duplicate any
> SQL statements that are the same for ODBC and JDBC (ex. several of the
> "getBestRowIdentifier" queries) in both files. So I'm guessing we
> wouldn't want to create another metadata file...but I thought I'd
> bring it up, just in case.
>
> Option III:
>
> Create some kind of internal VTI for ODBC metadata and use that. I
> have to admit that I don't know too much about how VTIs work, but
> Kathey gave me some places to look, so I'm going to read up.
> Apparently, we can execute the same metadata SQL statements that
> already exist for JDBC, then use a VTI to "massage" the result set
> into something that complies with ODBC specifications. This might be
> a good choice given that most of the differences between ODBC and JDBC
> are in the types of the columns returned. For example, JDBC might say
> that any String will do, whereas ODBC will say it has to be VARCHAR.
> In that case, a literal value ' ' will be fine for JDBC, but since
> it's treated as a CHAR value by Derby, it would be breaking ODBC
> standard. With a VTI, we could theoretically accomplish the same
> things that we'd be doing with new SQL statements--such as casting ' '
> to VARCHAR in this particular case. Other modifications we'd have to
> implement include casting certain integer columns to smallints, and
> replacing null values in JDBC (such as for "sql_data_type" and
> "buffer_length" columns) to legal values (neither column is supposed
> to be null for ODBC).
>
> Upside to this is that we still only have a single metadata.properties
> file, which (theoretically) makes maintenance of metadata procedures
> easier. As I don't know much about VTIs, I can't say what else this
> approach would require, but it seems safe to say that it would at
> least require another class to serve as the ODBC VTI. How that would
> tie into the SystemProcedures and EmbedDatabaseMetadata classes, I
> don't know yet...
>
> So...
>
> [ #2 **** COMMUNITY INPUT? **** ]
>
I think that the VTI option offers the most flexibility. VTIs are not
limited to massaging the results of standard JDBC metadata queries, they
can query the system tables in an entirely different way if necessary.
They are not loaded until necessary so JDBC only servers are not
burdened with ODBC support.
[snip]
Jack
Re: On DERBY-107 : ODBC Metadata functions
Posted by Army <ar...@golux.com>.
> RPost wrote:
> So then what is the difference between using a VTI and using a view?
Good question. My use of VTIs has been limited to this particular discussion, but I think the biggest difference is the
language we use: with views, our playing field is the scope of SQL; with VTIs, we have pretty much all of Java to work
with, which gives us more freedom to do result set manipulations.
That said, though, it DOES look like everything we'd want to be able to do _can_ be done in SQL (via subqueries), and
hence option #2 in my previous email. In effect, we'd be creating "view-like" functionality through use of subqueries.
If we were to add an option #3 that is kind of like #2 but would actually be _creating_ views, then we'd have to
answer the additional question of when do we create the views and where do we store them? Would be they become another
set of SYS objects? Such an approach would be more complicated than #2, I _think_...
The advantage of VTIs over subqueries and views is that we wouldn't have to change anything at build time or database
creation time--the general flow would remain exactly as it is now. If we opt for subqueries and/or Views, then we have
to figure out how to create those subqueries/views automatically from the existing JDBC metadata procedures (because
again, we want to avoid having duplicate, humanly-maintained sets of queries), which requires additional work at build
or database creation time.
But then, like I said, since we _are_ able to do all the manipulations that ODBC requires using SQL, maybe option #2 (or
a view-based #3) would be preferable to doing it in Java...
If anyone with more experience with VTIs knows of other/more significant differences between them and SQL views, feel
free to say so...
Army
Re: On DERBY-107 : ODBC Metadata functions
Posted by Daniel John Debrunner <dj...@debrunners.com>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Army wrote:
[a lot of stuff :-)]
I think VTI's are the wrong way to go. They are overkill I think for
what is required and will increase the static code footprint. In
addition there maybe platform issues where the correct type of result
set is needed to be returned for JSR169/JDBC2/JDBC3. It all depends on
exactly how the VTI (an implementation of ResultSet) would be handled in
the code.
I think that static modified queries based upon the JDBC queries are the
way to go. Since these can be created at derby compile time the
subsequent handling can be just like the existing JDBC meta-data
queries, rather than creating a new mechanism.
And as Lance pointed out, we need to maintain the JDBC metadata queries
in line with the spec, so no changing from INT to SMALLINT. Changes to
VARCHAR from CHAR I think are in line with the JDBC spec, as it defines
'String' as the type.
Dan.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFB+THgIv0S4qsbfuQRAi4JAJ9sYBF1jYs2npMZAT70oViqCwhhvQCggukZ
1NXKTQymK9nIEF6DEcTmC7E=
=gDOK
-----END PGP SIGNATURE-----
Re: On DERBY-107 : ODBC Metadata functions
Posted by RPost <rp...@pacbell.net>.
> Army wrote
> 1) Use VTIs (Virtual Table Interfaces), which are internal to the Derby
engine and allow representation of > > ResultSets as virtual tables that can
then be used in other table operations.
So then what is the difference between using a VTI and using a view?
Re: On DERBY-107 : ODBC Metadata functions
Posted by Army <ar...@golux.com>.
Because of various server issues, I didn't receive most of the emails on this thread until long after they were sent
out. Here's a brief hodgepodge of where we're at right now, based on the various responses from last week.
Note: unlike my previous emails, this one really isn't _that_ long. It's just a lot of cut-and-paste from the emails I
missed last week...
I) On the issue of changing JDBC types:
Jack Klebanoff wrote:
> I think that it is best to remain strictly compatible with JDBC. There
> always seem to be a few programs that depend on corner cases in the
> spec.
Lance J. Andersen wrote:
> You do not want to violate what the JDBC spec indicates as the expected
> columns to be returned as then your implementation is not compatible nor
> would it be compliant.
Daniel John Debrunner wrote:
> And as Lance pointed out, we need to maintain the JDBC metadata queries
> in line with the spec, so no changing from INT to SMALLINT. Changes to
> VARCHAR from CHAR I think are in line with the JDBC spec, as it defines
> 'String' as the type.
**** THEREFORE: No changing types from INT to SMALLINT, nor any other kinds of type-casting, for JDBC clients. Any such
changes will happen for ODBC clients ONLY, with the exception of CHAR to VARCHAR.
II) On the issue of adding columns to JDBC result sets:
Jack Klebanoff wrote:
> I don't think that we should add extra columns to the JDBC metadata
> ResultSets. It may cause a problem for a few programs now and it will
> put us in a real bind later if new columns are added to getTypeInfo in
> later versions of JDBC.
Kathey Marsden wrote:
> Well, I figure as long as we have to deal with item name changes too for
> getTypeInfo, might as well make the resultset right.
**** THEREFORE: No adding extra columns to result sets for JDBC clients. This will happen ONLY for ODBC clients, and
will occur alongside the column renaming that has to happen for the statements in question.
III) On VTIs vs. Auto-generated Subqueries
Jack Klebanoff wrote:
> Automatically generating the ODBC metadata queries from the JDBC queries
> (or generating both from a common source) is a clever idea [...]
Daniel John Debrunner wrote:
> I think VTI's are the wrong way to go. They are overkill I think for
> what is required and will increase the static code footprint.
[ snip ]
> I think that static modified queries based upon the JDBC queries are the
> way to go. Since these can be created at derby compile time the
> subsequent handling can be just like the existing JDBC meta-data
> queries, rather than creating a new mechanism.
**** THEREFORE: We won't use VTIs for this; we'll have a compile-time process that automatically generates the ODBC
version of the queries, and then we'll handle the new queries in a way similar to the existing JDBC metadata queries.
IV) Miscellaneous Comments
Jack Klebanoff wrote:
> [...] but it might not be worth the trouble. We still have to
> maintain two things: the query source and the transformation
> process.
>
> I am not convinced that maintaining separate metadata queries for JDBC
> and ODBC is worse than the alternatives.
On the scale of major metadata changes, I think you're right. Any changes to metadata will have to pass the current
JDBC metadata tests as well as the ODBC ones (to be submitted with the ODBC-related patch(es)), and anyone making the
changes is going to have to verify that both JDBC and OBDC have been updated accordingly. So we don't gain much value
there. However, for cases where we're fixing bugs with the metadata or otherwise altering the _value_ of a metadata
column (as opposed to the type/name/existence of the column), we _do_ save time by automatically generating the ODBC set
of queries.
For example, suppose we make a call to "getScale" in the existing JDBC queries and we later find out that it's supposed
to be "getPrecision" (note: there's a reason I'm using this example! More to come on that in a different email...): we
can just make the change in the JDBC query, and it will automatically be propagated to the ODBC query at build time. We
don't have to track it down and make the change twice. This is a change to the _value_ of the result set, not to the
result set itself, and thus should be safe with respect to both JDBC and OBDC.
Any addition feedback/comments?
Army
Re: On DERBY-107 : ODBC Metadata functions
Posted by Kathey Marsden <km...@Sourcery.Org>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Army wrote:
> Having seen no other posts on this since my most recent one, this is
> where we stand right now with ODBC metadata support for ODBC clients
> running against Derby Network Server:
>
[snip]
>
> It turns out that, aside from "getProcedureColumns", the only other
> metadata function for which ODBC specifies columns that JDBC does not
> have is "getTypeInfo". That said, do people think it's okay to add an
> extra column to the result set of this function, or not? The Java spec
> doesn't explicitly say that it's okay, so it's not as clean as it is
> with getProcedureColumns...
>
> That's one option (add the columns to both JDBC and ODBC metadata
> resultsets). The other is to use whatever means are deemed best (by the
> Derby community) to resolve the following issue:
Well, I figure as long as we have to deal with item name changes too for
getTypeInfo, might as well make the resultset right.
>> 2) Column Name changes.
[snip]
> At this point, it seems like there are two possibilities for handling
> this.
> 1) Use VTIs (Virtual Table Interfaces)
[snip]
> 2) Do some "under-the-covers" work to automatically generate
> ODBC-compliant SQL statements based on the existing JDBC statements,
[snip]
> Anyone have any preferences/feedback/knowledge to throw in?
I prefer option #1 because I understand it #:). I am not quite sure how
all that query rewriting is going to work, when the stored prepared
statements would get built and exactly how it will all work.
>
> Thanks!
> Army
>
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFB+SjpG0h36bFmkocRAtyEAKCeCl0MN+9uGOEP1It87uD9+MhSxACguwck
Dp2UQPsrEJ2UqgBMqctL8YM=
=3Jtd
-----END PGP SIGNATURE-----
Re: On DERBY-107 : ODBC Metadata functions
Posted by "Lance J. Andersen" <La...@Sun.COM>.
>
>
>>
>> > 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.
>>
>> It turns out that, aside from "getProcedureColumns", the only other
>> metadata function for which ODBC specifies columns that JDBC does not
>> have is "getTypeInfo". That said, do people think it's okay to add
>> an extra column to the result set of this function, or not? The Java
>> spec doesn't explicitly say that it's okay, so it's not as clean as
>> it is with getProcedureColumns...
>
>
> I don't think that we should add extra columns to the JDBC metadata
> ResultSets. It may cause a problem for a few programs now and it will
> put us in a real bind later if new columns are added to getTypeInfo in
> later versions of JDBC. Programs that look at the ResultSetMetadata to
> decide how to handle the ResuleSet will suddenly behave different.
> Think of display utilities such as ij. If a later version of JDBC adds
> a new column to the getTypeInfo ResultSet then we will be in the
> unenviable position of having to choose between breaking compatibility
> with JDBC or breaking compatibility with older versions of Derby.
You do not want to violate what the JDBC spec indicates as the expected
columns to be returned as then your implementation is not compatible nor
would it be compliant.
>
>>
>> That's one option (add the columns to both JDBC and ODBC metadata
>> resultsets). The other is to use whatever means are deemed best (by
>> the Derby community) to resolve the following issue:
>>
>> > 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 ]
>>
>> At this point, it seems like there are two possibilities for handling
>> this. It's clear that we do NOT want to have 2 sets of
>> humanly-maintained metadata functions, one for JDBC and another,
>> _slightly_ different one for ODBC. That can lead to a maintenance
>> headache if/when metadata processing changes in the future. That
>> said, we could either:
>>
>> 1) Use VTIs (Virtual Table Interfaces), which are internal to the
>> Derby engine and allow representation of ResultSets as virtual tables
>> that can then be used in other table operations.
>>
>> With this approach, we would execute the regular, JDBC version of the
>> metadata functions, and then we would take the result set and
>> "massage" it into a virtual table that has ODBC-compliant column
>> names. If we decide to go this route for extra ODBC columns, we
>> would also use the VTI to add those extra columns. Then we would
>> just execute a statement like "SELECT * FROM ODBCColumnsVTI" and
>> return the result set to the client.
>>
>> Upsides: 1) Can base it on existing VTI structure in the engine,
>> which makes for quicker development (I already have a VTI working for
>> getProcedureColumns, which is probably the most complicated of the
>> metadata functions, so it's a good proof-of-concept); 2)
>> upgrade/downgrade, when it is implemented, should be easy: just drop
>> the statements in metadata.properties and re-load them from the
>> metadata.properties file of the target version of Derby.
>>
>> Downside: We're doing all the work in Java, when SQL and the Derby
>> engine have the ability to do it for us.
>>
>> 2) Do some "under-the-covers" work to automatically generate
>> ODBC-compliant SQL statements based on the existing JDBC statements,
>> write the new queries out to file, and use the statements in that
>> file for ODBC clients. This statement generation work could be done
>> either at build time (via some kind of Ant process) or else at
>> database creation time. The end result would in fact be two copies of
>> the metadata functions, but only ONE of them would be manually
>> maintained; the other would be generated automatically based on the
>> first. For example, if we had some JDBC query "SELECT A FROM
>> MYTABLE" (I'm simplifying, obviously ;) and the ODBC version required
>> the column to be called "B" instead of "A", we would automatically
>> generate a new query "SELECT VT.A AS B FROM (SELECT A FROM MYTABLE) VT".
>>
>> Upside: 1) Exploits functionality that already exists in the Derby
>> engine--namely, uses the SQL engine to do the ODBC column renaming
>> for us.
>>
>> Downsides: 1) Seems less intuitive than using VTIs, and requires
>> further digging into the code to figure out just how it would work;
>> 2) upgrade/downgrade becomes more tricky, since we'd have to figure
>> out how to manage the existing metadata.properties file alongside the
>> newly generated one.
>>
>> Anyone have any preferences/feedback/knowledge to throw in?
>>
>> Thanks!
>> Army
>>
> I do not have a strong opinion on the best way of supporting both JDBC
> and ODBC metadata.
>
> Automatically generating the ODBC metadata queries from the JDBC
> queries (or generating both from a common source) is a clever idea,
> but it might not be worth the trouble. We still have to maintain two
> things: the query source and the transformation process.
>
> I am not convinced that maintaining separate metadata queries for JDBC
> and ODBC is worse than the alternatives. Having a common source is
> good if we change our metadata tables. Then changing one query source
> fixes both JDBC and ODBC. However, if we have to change the query to
> accomodate a change in the JDBC or ODBC spec then having common source
> may be a drawback: fixing JDBC may break ODBC and vice versa. We can
> easily share source for metadata queries where ODBC and JDBC are in
> agreement.
>
> Jack
Re: On DERBY-107 : ODBC Metadata functions
Posted by Jack Klebanoff <kl...@Mutagen.Net>.
I think that it is best to remain strictly compatible with JDBC. There
always seem to be a few programs that depend on corner cases in the
spec. More comments below.
Army wrote:
> Having seen no other posts on this since my most recent one, this is
> where we stand right now with ODBC metadata support for ODBC clients
> running against Derby Network Server:
>
> > 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.
>
> I'll plan to do that. For example, if JDBC says a column is INT and
> ODBC says it should be SMALLINT, I'll change the metadata function in
> question to return SMALLINT for _BOTH_ ODBC and JDBC clients, since
> doing so will 1) satisfy the ODBC requirement and 2) still be
> compatible with JDBC apps, since a call to "ResultSet.getInt()" on a
> SMALLINT column will still return the correct value.
Some programs look at ResultSet metadata and behave differently
depending on what they see there. For instance, SMALLINT probably has a
smaller display width than INT. The difference will cause the output of
some programs to change. There may be some programs that look at the
column type and behave differently with SMALLINTs than INTs.
>
> > 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.
>
> It turns out that, aside from "getProcedureColumns", the only other
> metadata function for which ODBC specifies columns that JDBC does not
> have is "getTypeInfo". That said, do people think it's okay to add an
> extra column to the result set of this function, or not? The Java
> spec doesn't explicitly say that it's okay, so it's not as clean as it
> is with getProcedureColumns...
I don't think that we should add extra columns to the JDBC metadata
ResultSets. It may cause a problem for a few programs now and it will
put us in a real bind later if new columns are added to getTypeInfo in
later versions of JDBC. Programs that look at the ResultSetMetadata to
decide how to handle the ResuleSet will suddenly behave different. Think
of display utilities such as ij. If a later version of JDBC adds a new
column to the getTypeInfo ResultSet then we will be in the unenviable
position of having to choose between breaking compatibility with JDBC or
breaking compatibility with older versions of Derby.
>
> That's one option (add the columns to both JDBC and ODBC metadata
> resultsets). The other is to use whatever means are deemed best (by
> the Derby community) to resolve the following issue:
>
> > 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 ]
>
> At this point, it seems like there are two possibilities for handling
> this. It's clear that we do NOT want to have 2 sets of
> humanly-maintained metadata functions, one for JDBC and another,
> _slightly_ different one for ODBC. That can lead to a maintenance
> headache if/when metadata processing changes in the future. That
> said, we could either:
>
> 1) Use VTIs (Virtual Table Interfaces), which are internal to the
> Derby engine and allow representation of ResultSets as virtual tables
> that can then be used in other table operations.
>
> With this approach, we would execute the regular, JDBC version of the
> metadata functions, and then we would take the result set and
> "massage" it into a virtual table that has ODBC-compliant column
> names. If we decide to go this route for extra ODBC columns, we would
> also use the VTI to add those extra columns. Then we would just
> execute a statement like "SELECT * FROM ODBCColumnsVTI" and return the
> result set to the client.
>
> Upsides: 1) Can base it on existing VTI structure in the engine, which
> makes for quicker development (I already have a VTI working for
> getProcedureColumns, which is probably the most complicated of the
> metadata functions, so it's a good proof-of-concept); 2)
> upgrade/downgrade, when it is implemented, should be easy: just drop
> the statements in metadata.properties and re-load them from the
> metadata.properties file of the target version of Derby.
>
> Downside: We're doing all the work in Java, when SQL and the Derby
> engine have the ability to do it for us.
>
> 2) Do some "under-the-covers" work to automatically generate
> ODBC-compliant SQL statements based on the existing JDBC statements,
> write the new queries out to file, and use the statements in that file
> for ODBC clients. This statement generation work could be done either
> at build time (via some kind of Ant process) or else at database
> creation time. The end result would in fact be two copies of the
> metadata functions, but only ONE of them would be manually maintained;
> the other would be generated automatically based on the first. For
> example, if we had some JDBC query "SELECT A FROM MYTABLE" (I'm
> simplifying, obviously ;) and the ODBC version required the column to
> be called "B" instead of "A", we would automatically generate a new
> query "SELECT VT.A AS B FROM (SELECT A FROM MYTABLE) VT".
>
> Upside: 1) Exploits functionality that already exists in the Derby
> engine--namely, uses the SQL engine to do the ODBC column renaming for
> us.
>
> Downsides: 1) Seems less intuitive than using VTIs, and requires
> further digging into the code to figure out just how it would work; 2)
> upgrade/downgrade becomes more tricky, since we'd have to figure out
> how to manage the existing metadata.properties file alongside the
> newly generated one.
>
> Anyone have any preferences/feedback/knowledge to throw in?
>
> Thanks!
> Army
>
I do not have a strong opinion on the best way of supporting both JDBC
and ODBC metadata.
Automatically generating the ODBC metadata queries from the JDBC queries
(or generating both from a common source) is a clever idea, but it might
not be worth the trouble. We still have to maintain two things: the
query source and the transformation process.
I am not convinced that maintaining separate metadata queries for JDBC
and ODBC is worse than the alternatives. Having a common source is good
if we change our metadata tables. Then changing one query source fixes
both JDBC and ODBC. However, if we have to change the query to
accomodate a change in the JDBC or ODBC spec then having common source
may be a drawback: fixing JDBC may break ODBC and vice versa. We can
easily share source for metadata queries where ODBC and JDBC are in
agreement.
Jack
Re: On DERBY-107 : ODBC Metadata functions
Posted by Army <ar...@golux.com>.
Having seen no other posts on this since my most recent one, this is where we stand right now with ODBC metadata support
for ODBC clients running against Derby Network Server:
> 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.
I'll plan to do that. For example, if JDBC says a column is INT and ODBC says it should be SMALLINT, I'll change the
metadata function in question to return SMALLINT for _BOTH_ ODBC and JDBC clients, since doing so will 1) satisfy the
ODBC requirement and 2) still be compatible with JDBC apps, since a call to "ResultSet.getInt()" on a SMALLINT column
will still return the correct value.
> 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.
It turns out that, aside from "getProcedureColumns", the only other metadata function for which ODBC specifies columns
that JDBC does not have is "getTypeInfo". That said, do people think it's okay to add an extra column to the result set
of this function, or not? The Java spec doesn't explicitly say that it's okay, so it's not as clean as it is with
getProcedureColumns...
That's one option (add the columns to both JDBC and ODBC metadata resultsets). The other is to use whatever means are
deemed best (by the Derby community) to resolve the following issue:
> 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 ]
At this point, it seems like there are two possibilities for handling this. It's clear that we do NOT want to have 2
sets of humanly-maintained metadata functions, one for JDBC and another, _slightly_ different one for ODBC. That can
lead to a maintenance headache if/when metadata processing changes in the future. That said, we could either:
1) Use VTIs (Virtual Table Interfaces), which are internal to the Derby engine and allow representation of ResultSets as
virtual tables that can then be used in other table operations.
With this approach, we would execute the regular, JDBC version of the metadata functions, and then we would take the
result set and "massage" it into a virtual table that has ODBC-compliant column names. If we decide to go this route
for extra ODBC columns, we would also use the VTI to add those extra columns. Then we would just execute a statement
like "SELECT * FROM ODBCColumnsVTI" and return the result set to the client.
Upsides: 1) Can base it on existing VTI structure in the engine, which makes for quicker development (I already have a
VTI working for getProcedureColumns, which is probably the most complicated of the metadata functions, so it's a good
proof-of-concept); 2) upgrade/downgrade, when it is implemented, should be easy: just drop the statements in
metadata.properties and re-load them from the metadata.properties file of the target version of Derby.
Downside: We're doing all the work in Java, when SQL and the Derby engine have the ability to do it for us.
2) Do some "under-the-covers" work to automatically generate ODBC-compliant SQL statements based on the existing JDBC
statements, write the new queries out to file, and use the statements in that file for ODBC clients. This statement
generation work could be done either at build time (via some kind of Ant process) or else at database creation time.
The end result would in fact be two copies of the metadata functions, but only ONE of them would be manually maintained;
the other would be generated automatically based on the first. For example, if we had some JDBC query "SELECT A FROM
MYTABLE" (I'm simplifying, obviously ;) and the ODBC version required the column to be called "B" instead of "A", we
would automatically generate a new query "SELECT VT.A AS B FROM (SELECT A FROM MYTABLE) VT".
Upside: 1) Exploits functionality that already exists in the Derby engine--namely, uses the SQL engine to do the ODBC
column renaming for us.
Downsides: 1) Seems less intuitive than using VTIs, and requires further digging into the code to figure out just how it
would work; 2) upgrade/downgrade becomes more tricky, since we'd have to figure out how to manage the existing
metadata.properties file alongside the newly generated one.
Anyone have any preferences/feedback/knowledge to throw in?
Thanks!
Army
Re: On DERBY-107 : ODBC Metadata functions
Posted by Army <ar...@golux.com>.
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
Re: On DERBY-107 : ODBC Metadata functions
Posted by Kathey Marsden <km...@Sourcery.Org>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
RPost wrote:
> For any given instance of EDM will a user potentially be calling both
> "getColumns" and "getColumnsForODBC"? Your comments for option II (add a
> protected variable "forODBC) seem to imply that only one or the other will
> get called.
>
> If only one set of queries will be executed it there may be a 4th option:
> change the metadata.properties load process to load the version
needed.
>
> This could be done in a static block when the driver is first loaded by
> adding an option to the URL at connect time.
>
I don't think this would work as a single network server (derby
instance) might have both JDBC and ODBC clients. These clients would
call the Stored Procedures with the appropriate options setting for what
they need. Also we found a while back when we had two metadata files,
one for embedded and one for network server that we had a lot of
maintenance issues associated with the slightly different queries.
I'll post a separate response to Army's mail.
An example of how we currently branch based on the options can be seen
in the SQLTables function of:
d:/p4/marsden_derby/opensource/java/engine/org/apache/derby/catalog/SystemProcedures.java
I'll send a separate response to Army's mail.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFB9O77G0h36bFmkocRAsHOAJkBruVj88mm2IN03ILe2eTKJDnTOgCgn+W/
IXu4+klxLHw0Ix+lrcLBJxA=
=+zS/
-----END PGP SIGNATURE-----
Re: On DERBY-107 : ODBC Metadata functions
Posted by RPost <rp...@pacbell.net>.
I'm still researching but have some questions/comments.
> Option I:
>
> Add new SQL statements, such as "getColumnsForODBC", to the existing
metadata.properties file, as described in the
> proposal for DERBY-107. Then, since EDM has to know which version of a
given SQL statement to execute--for example,
> should it call the regular "getColumns" version, or should it call the new
"getColumnsForODBC" version?--we could add
> new methods (such as "setForODBC()") to EDM that could be used by
SystemProcedures to indicate (to EDM) that ODBC
> metadata should be returned, intead of JDBC metadata. Note that, since
SystemProcedures is in a different package than
> EDM, the new methods on EDM would (I think) have to be _public_.
>
For any given instance of EDM will a user potentially be calling both
"getColumns" and "getColumnsForODBC"? Your comments for option II (add a
protected variable "forODBC) seem to imply that only one or the other will
get called.
If only one set of queries will be executed it there may be a 4th option:
change the metadata.properties load process to load the version needed.
This could be done in a static block when the driver is first loaded by
adding an option to the URL at connect time.
If the EDM file needs to be modified it may be desireable to move the
current load to a static block anyway rather than wait for the first
request.
Re: On DERBY-107 : ODBC Metadata functions
Posted by RPost <rp...@pacbell.net>.
While researching the three alternatives I found this in
DataDictionaryImpl.java:
private final synchronized Properties getQueryDescriptions(boolean net) {
spsSet = net ? "metadata_net.properties" :
"/org/apache/derby/impl/jdbc/metadata.properties";
Would this method need to be ODBC aware?
Would any changes re ODBC need to be made to values in the
metadata_net.properties file?
Re: On DERBY-107 : ODBC Metadata functions
Posted by Kathey Marsden <km...@Sourcery.Org>.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Option I:
>
> Add new SQL statements, such as "getColumnsForODBC", to the existing
> metadata.properties file
[snip]
- -1 because we had so many problems with maintenance of two sets of
almost identical queries when we tried to do that before and if we need
ODBC 2 and ODBC3 it gets really messy.
Option 2:
[snip]
- -1 for the same reason as Option 1.
Army wrote:
> Option III:
>
> Create some kind of internal VTI for ODBC metadata and use that. I have
> to admit that I don't know too much about how VTIs work, but Kathey gave
> me some places to look, so I'm going to read up. Apparently, we can
> execute the same metadata SQL statements that already exist for JDBC,
> then use a VTI to "massage" the result set into something that complies
> with ODBC specifications. This might be a good choice given that most
> of the differences between ODBC and JDBC are in the types of the columns
> returned. For example, JDBC might say that any String will do, whereas
> ODBC will say it has to be VARCHAR. In that case, a literal value ' '
> will be fine for JDBC, but since it's treated as a CHAR value by Derby,
> it would be breaking ODBC standard. With a VTI, we could theoretically
> accomplish the same things that we'd be doing with new SQL
> statements--such as casting ' ' to VARCHAR in this particular case.
> Other modifications we'd have to implement include casting certain
> integer columns to smallints, and replacing null values in JDBC (such as
> for "sql_data_type" and "buffer_length" columns) to legal values
> (neither column is supposed to be null for ODBC).
>
> Upside to this is that we still only have a single metadata.properties
> file, which (theoretically) makes maintenance of metadata procedures
> easier. As I don't know much about VTIs, I can't say what else this
> approach would require, but it seems safe to say that it would at least
> require another class to serve as the ODBC VTI. How that would tie into
> the SystemProcedures and EmbedDatabaseMetadata classes, I don't know
yet...
>
> So...
>
> [ #2 **** COMMUNITY INPUT? **** ]
>
> What are people's feelings/reactions/comments in terms of which of the
> above three options is most appropriate for the Derby codeline? In a
> nutshell, it comes to 1) new public methods on EmbedDatabaseMetadata, 2)
> new subclass to EmbedDatabaseMetadata that sets ODBC-related state (with
> or without creating a new odbc_metadata.properties file), and 3) create
> an ODBC VTI to manipulate existing JDBC result sets into ODBC-compliant
> ones.
>
> NOTE: another thing we have to decide is if we're going to support
> different levels of ODBC metadata--for example, ODBC 1.0 metadata
> differs from ODBC 2.0 in some respects, and likewise for ODBC 3.0. Do
> we want to support all of the different levels? If so, which of the
> above approaches is going to make that most feasible? My guess is that
> option III would be the best choice in that case...
>
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. For example fields where we return a CHAR column now but
need a VARCHAR for ODBC. The JDBC spec just says string so I think a
VARCHAR would be ok. SQL_DATA_TYPE, unused in JDBC but required in ODBC
can be filled in. Also I think it would be ok to change our INTEGER
columns to SMALLINT where required as a getObject would still return
Integer, but that might be more controversial.
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. I don't know.
If done with a VTI I would see it happenning like this:
Make a new class which is a wrapper for the DatabaseMetaData object.
ODBCMetaData implements java.sql.DatabaseMetaData
The constructor takes a Connection object and initializes
dbmd = conn.getMetaData().
Where there is no difference between ODBC and JDBC it just has something
like
return dbmd.getTypeInfo()
Where ODBC is different we call the VTI. Maybe there could be
statements in the metadata.properties but just to call the VTI's.
If there are new VTI's they could take the options passed to the
stored procedures.
Later that class could be extended for ODBC3MetaData etc.
In SystemProcedures the getDMD() method could be changed to take the
options and for ODBC create the wrapper instead of getting the
DatabaseMetadata. e.g. return new ODBCMetaData(conn.getMetaData());
The VTI itself would use the metadata call as it's underlying resultset
and then for the columninfo it would have something like
EmbedResultSetMetaData.getResultColumnDescriptor("NUM_PREC_RADIX",
Types.SMALLINT, false),
Again maybe all of this could be avoided if we could somehow internally
change the columnname of the resultset.
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.
> [ #2 **** End **** ]
>
> The final part of Kathey's and my discussion involved the issue of Derby
> upgrade. While it IS true that, as Dan said, "no upgrade code has been
> added to support upgrading databases from 10.0 to 10.1" (see
>
http://nagoya.apache.org/eyebrowse/ReadMsg?listName=derby-dev@db.apache.org&msgNo=1703),
> it nonetheless seems like a good idea to consider what effects on
> upgrade the changes to Derby metadata might have.
>
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, for instance if this change went to the maintenance branch it
would need to be bumped from 10.0.2.1 to 10.0.2.2.
Army, could you verify that these are the only three types of changes?
If we could find an easier way to change the names, maybe this isn't all
that hard, but 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.
Kathey
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFB9V7EG0h36bFmkocRApJJAJ9NPPBe5hMaw0qCS2UBB9eGsXUZpACfVG2u
M7ScC4YKGV2bV78RC2iMTx4=
=dK2c
-----END PGP SIGNATURE-----