[ http://issues.apache.org/jira/browse/DERBY-107?page=all ] A B closed DERBY-107: ---------------------
> Add Support for ODBC Metadata Calls to Derby > -------------------------------------------- > > Key: DERBY-107 > URL: http://issues.apache.org/jira/browse/DERBY-107 > Project: Derby > Type: Improvement > Components: Network Server > Environment: Connection to Derby Network Server with an ODBC driver. > Reporter: A B > Assignee: A B > Fix For: 10.1.0.0 > Attachments: derby-107.I.patch, derby-107.II.patch, derby-107.III.patch > > 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" or "import" 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. -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira
