db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Army <a...@Golux.Com>
Subject Support for ODBC Metadata Calls?
Date Wed, 15 Dec 2004 21:32:35 GMT

BACKGROUND:

Derby has a defined set of SQL statements that are used to return metadata about a given database.
 These statements are 
the basis on which many of the Java methods in the java.sql.DatabaseMetaData class are implemented.
 For example, 
DatabaseMetaData.getColumns() is ultimately mapped to a call to a SQL statement called "getColumns"
that is defined in 
the file:

java\engine\org\apache\derby\impl\jdbc\metadata.properties

As can be seen from its definition, this statement returns a result set that agrees with the
result set defined for the 
Java getColumns() method.

The ODBC equivalent to DatabaseMetaData is a set of ODBC methods that offer similar functionality.
 For example, the 
ODBC equivalent to "DatabaseMetaData.getColumns()" is the ODBC call "SQLColumns()".

PROBLEM:

Right now, all metadata SQL statements return result sets that match the Java/JDBC standard,
since in the past, Derby 
has only been used by JDBC drivers.  However, now that Cloudscape/Derby has beta support for
ODBC clients (via Network 
Server; see http://www-106.ibm.com/developerworks/db2/library/techarticle/dm-0409cline2/index.html),
and since the 
expected result sets for ODBC are different in some ways than those for JDBC, Derby metadata
calls need to expand to 
accommodate ODBC clients.

An example of the need for this change can be found using Microsoft Access: if one tries to
do a "table link" to a Derby 
database via Network Server (using the DB2 ODBC driver), the attempt will fail with "Reserved
Error (-7734); there is no 
message for this error".  It turns out that the cause of this problem is the metadata result
set mentioned above.

PROPOSAL:

What I would like to propose is that we add support in the Derby engine to allow the metadata
methods to return result 
sets in two forms: one that conforms to the JDBC standard (which is what we do currently;
that would be the default), 
and another that conforms to the ODBC standard.  For more info on the latter, see the following
link:

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

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 mentioned above.  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.

----

Before filing a JIRA Enhancement, and before actually starting the work, I want to put this
idea out there to see if 
anyone has any objections/input about this proposal.  Does this seem like an acceptable addition
to Derby?

One of my biggest questions is if and how such a change would affect Derby upgrade policies.
 In playing with the 
metadata.properties file just to see what would happen, I noticed that the changes only take
effect on databases created 
_after_ the metadata.properties file was changed.  It seems to me like that could have upgrade
implications.  On the 
other hand, we wouldn't be changing existing metadata statements--we'd only be adding new
ones, so maybe upgrade 
wouldn't be affected...Can anyone offer any more knowledge in this area?

Any reactions/feedback regarding this would be much appreciated.  In the event that I hear
no objections up front, I'll 
go ahead and file a JIRA enhancement request, and put this on my "to-do" list for ODBC support...

Thanks,
Army

Mime
View raw message