# Licensed to the Apache Software Foundation (ASF) under one or more # contributor license agreements. See the NOTICE file distributed with # this work for additional information regarding copyright ownership. # The ASF licenses this file to you under the Apache License, Version 2.0 # (the "License"); you may not use this file except in compliance with # the License. You may obtain a copy of the License at # # http://www.apache.org/licenses/LICENSE-2.0 # # Unless required by applicable law or agreed to in writing, software # distributed under the License is distributed on an "AS IS" BASIS, # WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. # See the License for the specific language governing permissions and # limitations under the License. # # # # ANY CHANGE MADE TO THIS FILE SHOULD PROBABLY ALSO BE MADE TO # $WS/java/com/ibm/db2j/impl/Connectivity/JDBC/Local/metadata.properties # # This file is used by DRDA to determine values for methods # from java.sql.DatabaseMetadata. We try to emulate the SYSIBM # stored procedures that are used in DB2 for the MetaData. # These are called from # $WS/drda/jcc/src/com/ibm/db2/jcc/am/DatabaseMetaData # # JDBC driver will take the value and perform # a statement.execute() request on it, returning the result set or using the # result set to produce an answer of the required type and format for the # DatabaseMetadata method. If the query has AS clauses in its select list items, # you can be pretty sure that the JDBC driver was looking for a result set, # because it specifies what it wants the column names to be. # # # Note that property values can span multiple lines, by ending the line with a \ # # Note: be sure to always enter SQL keywords in CAPS, this is both # for convention, and because of the way the parser uppercases # things (so it is purportedly faster that way). # # REMIND: using SYSALIASES; need to deal with ProcedureColumns still. # there are no procedures or metadata about them in our system yet. # NOTE: the order by would include PROCEDURE_SCHEM, but it is always '' # so we leave it out. # # PROCEDURE_TYPE is always procedureResultUnknown as it could map to # multiple methods, some of which could return a value and some not. # # Now that we have java Stored procedures we can more easily implement these calls # Thus, $WS/java/com/ibm/db2j/catalog/SystemProcedures now calls the LocalDatabaseMetadata methods. # call stored procedure SQLProcedures # SYSIBM.SQLProcedures( # CatalogName varchar(128), # SchemaName varchar(128), # ProcName varchar(128), # Options varchar(4000)) # SQLPROCEDURES=CALL SYSIBM.SQLPROCEDURES(?, ?, ?, ?) # getSchemas # DB2 overloads the SQLTABLES stored procedure for this # but jcc has specific code now to make an exception for Cloudscape GETSCHEMAS=CALL SYSIBM.SQLTABLES('', '', '', '', 'GETSCHEMAS=1') #getTables # SYSIBM.SQLTABLES( # CatalogName varchar(128), # SchemaName varchar(128), # TableName varchar(128), # TaleType varchar(4000), # Options varchar(4000)) # SQLTABLES=CALL SYSIBM.SQLTABLES(?, ?, ?, ?, ?) # getCrossReference, getExportedKeys, getImportedKeys # SYSIBM.SQLFOREGNKEYS( # PKCatalogName varchar(128), # PKSchemaName varchar(128), # PKTableName varchar(128), # FKCatalogName varchar(128), # FKSchemaName varchar(128), # FKTableName varchar(128), # Options varchar(4000)) # SQLFOREIGNKEYS=CALL SYSIBM.SQLFOREIGNKEYS(?, ?, ?, ?, ?, ?, ?) # getProcedureColumns # SYSIBM.SQLProcedureCols( # CatalogName varchar(128), # SchemaName varchar(128), # ProcName varchar(128), # ParamName varchar(128), # Options varchar(4000)) # # # SQLPROCEDURECOLS=CALL SYSIBM.SQLPROCEDURECOLS(?, ?, ?, ?, ?) # This wants a result set with a specific column name, # DB2 overloads SQLGETTABLES, but jcc now has exception code for Cloudscape... GETTABLETYPES=CALL SYSIBM.SQLTABLES('', '', '', '', 'GETTABLETYPES=1') #getColumns # SYSIBM.SQLColumns( # CatalogName varchar(128), # SchemaName varchar(128), # TableName varchar(128), # ColumnName varchar(128), # Options varchar(4000)) # REMIND: the true or is to guard against nulls # REMIND: need to check that sqltype column starts with SERIALIZE... # # DECIMAL_DIGITS returns scale for DECIMAL and NUMERIC, # otherwise it returns null. This is as per JavaSoft. # # CHAR_OCTET_LENGTH returns maximumWidth() * 2 for # character types to account for the fact that we # java uses 2 bytes per char and getMaximumWidth() # returns the string length, not the length in bytes. # SQLCOLUMNS=CALL SYSIBM.SQLCOLUMNS(?, ?, ?, ?, ?) # SYSIBM.SQLColPrivileges( # CatalogName varchar(128), # SchemaName varchar(128), # TableName varchar(128), # ColumnName varchar(128), # Options varchar(4000)) # # REMIND: this query is set up to return 0 rows of the right shape, since # there are none of these or metadata about them in our system yet. SQLCOLPRIVILEGES=CALL SYSIBM.SQLCOLPRIVILEGES(?, ?, ?, ?, ?) # SYSIBM.SQLTablePrivileges( # CatalogName varchar(128), # SchemaName varchar(128), # TableName varchar(128), # Options varchar(4000)) # # REMIND: this query is set up to return 0 rows of the right shape, since # there are none of these or metadata about them in our system yet. SQLTABLEPRIVILEGES=CALL SYSIBM.SQLTABLEPRIVILEGES(?, ?, ?, ?) # SYSIBM.SQLPrimaryKeys( # CatalogName varchar(128), # SchemaName varchar(128), # TableName varchar(128), # Options varchar(4000)) # SQLPRIMARYKEYS=CALL SYSIBM.SQLPRIMARYKEYS(?, ?, ?, ?) # getTypeInfo # SYSIBM.SQLGetTypeInfo (IN DATATYPE SMALLINT, # IN Options VARCHAR(4000)) # # # SQLGETTYPEINFO=CALL SYSIBM.SQLGETTYPEINFO(?, ?) # getIndexInfo # SYSIBM.SQLStatistics( # CatalogName varchar(128), # SchemaName varchar(128), # TableName varchar(128), # Unique Smallint, // WARNING: 0 = unique, 1 = all keys # Reserved Smallint, # Options varchar(4000)) # SQLSTATISTICS=CALL SYSIBM.SQLSTATISTICS(?, ?, ?, ?, ?, ?) ############################################ # # getBestRowIdentifier queries # ############################################ #getBestRowIdentifier, getVersionColumns # call stored procedure # SYSIBM.SQLSPECIALCOLUMNS ( IN COLTYPE SMALLINT, # IN CATALOG_NAME VARCHAR(128), # IN SCHEMA_NAME VARCHAR(128), # IN TABLE_NAME VARCHAR(128), # IN SCOPE SMALLINT, # IN NULLABLE SMALLINT, # IN OPTIONS VARCHAR(4000) ) # SQLSPECIALCOLUMNS=CALL SYSIBM.SQLSPECIALCOLUMNS(?, ?, ?, ?, ?, ?, ?) # SYSIBM.SQLUDTS ( IN CATALOG_NAME VARCHAR(128), # IN SCHEMA_NAME_PATTERN VARCHAR(128), # IN TYPE_NAME_PATTERN VARCHAR(128), # IN UDTTYPES VARCHAR(128), # IN OPTIONS VARCHAR(4000) ) SQLUDTS=CALL SYSIBM.SQLUDTS(?, ?, ?, ?, ?) # REMIND: this query is set up to return 0 rows of the right shape, since # there are no catalogs or metadata about them in our system yet. # would have order by table_cat if it mattered... # ORDER BY TABLE_CAT # DB2 overloads SQLGETTABLES, but jcc now has exception code for Cloudscape... GETCATALOGS=CALL SYSIBM.SQLTABLES('', '', '', '', 'GETCATALOGS=1') #SYSIBM.Metadata gets several miscellanous database metadata values # for caching on the JCC side # # METADATA column comments # # column 97: # Encoding for DatabaseMetaData#supportsResultSetConcurrency. # If a concurrency is given for a type, supportsResultSetConcurrency # will return true for that combination. Absence means false. # String syntax: # { "," }* { ";" { "," }* }}* # # ::= # ::= # # Please keep in synch with the client's decoding (in # DatabaseMetaData.java) # # columns 98--106: # # For the following *AreVisible and *AreDetected methods. # The encoding is: # If a type is given, the metadata method will give true for # that type, else false. # # column 98 Encoding for DatabaseMetaData#ownUpdatesAreVisible # column 99 Encoding for DatabaseMetaData#ownDeletesAreVisible # column 100 Encoding for DatabaseMetaData#ownInsertsAreVisible # # column 101 Encoding for DatabaseMetaData#othersUpdatesAreVisible # column 102 Encoding for DatabaseMetaData#othersDeletesAreVisible # column 103 Encoding for DatabaseMetaData#othersInsertsAreVisible # # column 104 Encoding for DatabaseMetaData#updatesAreDetected # column 105 Encoding for DatabaseMetaData#deletesAreDetected # column 106 Encoding for DatabaseMetaData#insertsAreDetected # # # NOTE: DERBY-1252: Values for 98,99 and 104,105 do not reflect # truth, since DERBY-775 (SUR) is now implemented in 10.2. # When we move to major version 11, compatible clients shall handle # down negotiating so we can start returning truth again without risk # of breaking apps in mixed client/Server mode, i.e. old client/new # server. See also comments on this in # org.apache.derby.client.am.DatabaseMetaData # Value for 97 does reflect truth, though, since all < 10.2 clients # (and JCC) were broken parsing this anyway. METADATA=\ SELECT \ M->allProceduresAreCallable(), \ M->allTablesAreSelectable(), \ M->nullsAreSortedHigh(), \ M->nullsAreSortedLow(), \ M->nullsAreSortedAtStart(), \ M->nullsAreSortedAtEnd(), \ M->usesLocalFiles(), \ M->usesLocalFilePerTable(), \ M->storesUpperCaseIdentifiers(), \ M->storesLowerCaseIdentifiers(), \ M->storesMixedCaseIdentifiers(), \ M->storesUpperCaseQuotedIdentifiers(), \ M->storesLowerCaseQuotedIdentifiers(), \ M->storesMixedCaseQuotedIdentifiers(), \ M->getSQLKeywords(), \ M->getNumericFunctions(), \ M->getStringFunctions(), \ M->getSystemFunctions(), \ M->getTimeDateFunctions(), \ M->getSearchStringEscape(), \ M->getExtraNameCharacters(), \ M->supportsAlterTableWithAddColumn(), \ M->supportsAlterTableWithDropColumn(), \ M->supportsConvert(), \ '1', \ M->supportsDifferentTableCorrelationNames(), \ M->supportsExpressionsInOrderBy(), \ M->supportsOrderByUnrelated(), \ M->supportsGroupBy(), \ M->supportsGroupByUnrelated(), \ M->supportsGroupByBeyondSelect(), \ M->supportsMultipleResultSets(), \ M->supportsMultipleTransactions(), \ M->supportsCoreSQLGrammar(), \ M->supportsExtendedSQLGrammar(), \ M->supportsANSI92IntermediateSQL(), \ M->supportsANSI92FullSQL(), \ M->supportsIntegrityEnhancementFacility(), \ M->supportsOuterJoins(), \ M->supportsFullOuterJoins(), \ M->supportsLimitedOuterJoins(), \ M->getSchemaTerm(), \ M->getProcedureTerm(), \ M->getCatalogTerm(), \ M->isCatalogAtStart(), \ M->getCatalogSeparator(), \ M->supportsSchemasInDataManipulation(), \ M->supportsSchemasInProcedureCalls(), \ M->supportsSchemasInTableDefinitions(), \ M->supportsSchemasInIndexDefinitions(), \ M->supportsSchemasInPrivilegeDefinitions(), \ M->supportsCatalogsInDataManipulation(), \ M->supportsCatalogsInProcedureCalls(), \ M->supportsCatalogsInTableDefinitions(), \ M->supportsCatalogsInIndexDefinitions(), \ M->supportsCatalogsInPrivilegeDefinitions(), \ M->supportsPositionedDelete(), \ M->supportsPositionedUpdate(), \ M->supportsSelectForUpdate(), \ M->supportsStoredProcedures(), \ M->supportsSubqueriesInComparisons(), \ M->supportsUnion(), \ M->supportsUnionAll(), \ M->supportsOpenCursorsAcrossCommit(), \ M->supportsOpenCursorsAcrossRollback(), \ M->supportsOpenStatementsAcrossCommit(), \ M->supportsOpenStatementsAcrossRollback(), \ M->getMaxBinaryLiteralLength(), \ M->getMaxCharLiteralLength(), \ M->getMaxColumnNameLength(), \ M->getMaxColumnsInGroupBy(), \ M->getMaxColumnsInIndex(), \ M->getMaxColumnsInOrderBy(), \ M->getMaxColumnsInSelect(), \ M->getMaxColumnsInTable(), \ M->getMaxConnections(), \ M->getMaxCursorNameLength(), \ M->getMaxIndexLength(), \ M->getMaxSchemaNameLength(), \ M->getMaxProcedureNameLength(), \ M->getMaxCatalogNameLength(), \ M->getMaxRowSize(), \ M->doesMaxRowSizeIncludeBlobs(), \ M->getMaxStatementLength(), \ M->getMaxStatements(), \ M->getMaxTableNameLength(), \ M->getMaxTablesInSelect(), \ M->getMaxUserNameLength(), \ M->getDefaultTransactionIsolation(), \ M->supportsTransactions(), \ '1,2,4,8' , \ M->supportsDataDefinitionAndDataManipulationTransactions(), \ M->supportsDataManipulationTransactionsOnly(), \ M->dataDefinitionCausesTransactionCommit(), \ M->dataDefinitionIgnoredInTransactions(), \ (cast (RTRIM(CAST(java.sql.ResultSet::TYPE_FORWARD_ONLY as CHAR(10))) AS VARCHAR(10)) || (',') || (cast (RTRIM(CAST(java.sql.ResultSet::TYPE_SCROLL_INSENSITIVE AS CHAR(10))) as VARCHAR(10)))), \ '1003,1007,1008;1004,1007,1008;1005', \ '',\ '',\ '',\ '1003',\ '1003',\ '1003',\ '',\ '',\ '',\ M->supportsBatchUpdates()\ from (values(getCurrentConnection()->getMetaData())) as DBMetaData(M)