db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Lance J. Andersen" <Lance.Ander...@Sun.COM>
Subject Re: On DERBY-107 : ODBC Metadata functions
Date Thu, 27 Jan 2005 17:15:54 GMT

>> > 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

View raw message