# # # 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. # # # This file is used by our JDBC driver to determine values for methods # from java.sql.DatabaseMetadata whose names match the property keys here. # # 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 ** At build time, this file is treated as INPUT into an ODBC # query generation process that reads these queries and, where required, # performs alterations on them to create ODBC-compliant versions. The # output of that query generation process is a file containing 1) all of # the queries in this file, PLUS 2) an additional set of ODBC-compliant # queries based on the queries here. That automatically-generated file # is the one that makes it into the CLASSES directory, and the one from # which the Derby engine will load and process metadata queries. That # said, please realize that changes you make here could affect the ODBC # metadata, as well--so in the even that you make any changes here, you # should make SURE you run all of the metadata tests (JDBC and ODBC alike) # to verify that the results are correct for BOTH types of clients. # # 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). # And also because it is the only way it will all work in Turkey where # the turkish locale uppercases i to an upper case dotted i. ( bug 5362) # # REMIND: using SYSALIASES; need to deal with ProcedureColumns still. # there are no procedures or metadata about them in our system yet. # # PROCEDURE_TYPE is always procedureResultUnknown for method aliases as it could map to # multiple methods, some of which could return a value and some not. # # 'REMARKS' column is VARCHAR(32672), which is the max length allowed # for a VARCHAR. This is because Java methods with the complete # package name plus possible signature can grow to be rather long. # getProcedures=\ SELECT CAST ('' AS VARCHAR(128)) AS PROCEDURE_CAT, \ SCHEMANAME AS PROCEDURE_SCHEM, \ ALIAS AS PROCEDURE_NAME, \ CAST (NULL AS INT) AS RESERVED1, CAST (NULL AS INT) AS RESERVED2, \ CAST (NULL AS INT) AS RESERVED3, \ CAST ((JAVACLASSNAME||'.'|| ALIASINFO->getMethodName()) AS VARCHAR(32672)) AS REMARKS, \ CAST (java.sql.DatabaseMetaData::procedureNoResult AS SMALLINT) AS PROCEDURE_TYPE \ FROM SYS.SYSALIASES, SYS.SYSSCHEMAS \ WHERE ((1=1) OR ? IS NOT NULL) \ AND (ALIASTYPE = 'P' AND SCHEMANAME LIKE ?) \ AND SYS.SYSALIASES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID \ AND ALIAS LIKE ? \ ORDER BY 2,3 # This is the JDBC 4.0 version of getProcedures, with a new name. The # old query is kept to ensure that ODBCMetaDataGenerator will work as # before. The old query may be removed when ODBCMetaDataGenerator is # modified to correctly transform the new query. getProcedures40=\ SELECT CAST ('' AS VARCHAR(128)) AS PROCEDURE_CAT, \ SCHEMANAME AS PROCEDURE_SCHEM, \ ALIAS AS PROCEDURE_NAME, \ CAST (NULL AS INT) AS RESERVED1, CAST (NULL AS INT) AS RESERVED2, \ CAST (NULL AS INT) AS RESERVED3, \ CAST ((JAVACLASSNAME||'.'|| ALIASINFO->getMethodName()) AS VARCHAR(32672)) AS REMARKS, \ CAST (java.sql.DatabaseMetaData::procedureNoResult AS SMALLINT) AS PROCEDURE_TYPE, \ SPECIFICNAME AS SPECIFIC_NAME \ FROM SYS.SYSALIASES, SYS.SYSSCHEMAS \ WHERE ((1=1) OR ? IS NOT NULL) \ AND (ALIASTYPE = 'P' AND SCHEMANAME LIKE ?) \ AND SYS.SYSALIASES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID \ AND ALIAS LIKE ? \ ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, SPECIFIC_NAME # getSchemas # # parameter 1 = catalog name pattern (not used) # parameter 2 = schema name pattern # getSchemas=\ SELECT SCHEMANAME AS TABLE_SCHEM, \ CAST(NULL AS VARCHAR(128)) AS TABLE_CATALOG \ FROM SYS.SYSSCHEMAS \ WHERE ((1=1) OR ? IS NOT NULL) \ AND SCHEMANAME LIKE ? \ ORDER BY TABLE_SCHEM # REMIND: presently table_type is a single char, we match JDBC # recommendations closer and make it a more obvious word. # REMIND: fillers for catalog names' comparisons # parameter 1 = catalog name pattern # parameter 2 = schema name pattern # parameter 3 = table name pattern (should have like comparison) # parameter 4,5,6,7 = a list of table types. In Derby 10.3, there # are 4 tables types, 'T','S','V','A' and hence only 4 parameters # are needed for table types. More information can be found in # EmbedDatabaseMetaDate.getTables # # IMPORTANT NOTE: # -------------- # DERBY-2896 # When we add a new table type in VALUES clause below, we should # also add a ? in TABLETYPE IN clause. In addition, we should # modify EmbedDatabaseMetaData.getTables to account for that new ? # getTables=\ SELECT CAST ('' AS VARCHAR(128)) AS TABLE_CAT, \ SCHEMANAME AS TABLE_SCHEM, \ TABLENAME AS TABLE_NAME, \ (CAST (RTRIM(TABLE_TYPE) AS VARCHAR(12))) \ AS TABLE_TYPE, CAST ('' AS VARCHAR(128)) AS REMARKS, \ CAST (NULL AS VARCHAR(128)) AS TYPE_CAT, \ CAST (NULL AS VARCHAR(128)) AS TYPE_SCHEM, \ CAST (NULL AS VARCHAR(128)) AS TYPE_NAME, \ CAST (NULL AS VARCHAR(128)) AS SELF_REFERENCING_COL_NAME, \ CAST (NULL AS VARCHAR(128)) AS REF_GENERATION \ FROM \ SYS.SYSTABLES, \ SYS.SYSSCHEMAS, \ (VALUES ('T','TABLE'), ('S','SYSTEM TABLE'), \ ('V', 'VIEW'), ('A', 'SYNONYM')) T(TTABBREV,TABLE_TYPE) \ WHERE (TTABBREV=TABLETYPE \ AND (SYS.SYSTABLES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID) \ AND ((1=1) OR ? IS NOT NULL) \ AND (SYS.SYSSCHEMAS.SCHEMANAME LIKE ?) \ AND (TABLENAME LIKE ?) AND TABLETYPE IN (?, ?, ?, ?)) \ ORDER BY TABLE_TYPE, TABLE_SCHEM, TABLE_NAME # 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 getCatalogs=\ SELECT TABLE_CAT \ FROM ( VALUES ('') ) AS CATALOGS(TABLE_CAT) \ WHERE (1=0) # parameter 1 = pattern for schema (must be empty string to return rows) # parameter 2 = pattern for procedure (method) name # parameter 3 = pattern for column (parameter) name # # vti.GetProcedureColumns does not convert the case of its column names to # the language connection context casing. So delimited identifiers must be # used to specify these column names and display names are used so that # the column names of the result set use the database casing. # # # getProcedureColumns=\ SELECT CAST (NULL AS VARCHAR(128)) AS PROCEDURE_CAT, \ SCHEMANAME AS PROCEDURE_SCHEM, \ A.ALIAS AS PROCEDURE_NAME, \ V."COLUMN_NAME" AS COLUMN_NAME, \ V."COLUMN_TYPE" AS COLUMN_TYPE, \ V."DATA_TYPE" AS DATA_TYPE, \ V."TYPE_NAME" AS TYPE_NAME, \ V."PRECISION" AS PRECISION, \ V."LENGTH" AS LENGTH, \ CASE WHEN (V."DATA_TYPE" IN (java.sql.Types::DECIMAL, \ java.sql.Types::NUMERIC, java.sql.Types::INTEGER, \ java.sql.Types::SMALLINT, java.sql.Types::TINYINT, \ java.sql.Types::BIGINT, java.sql.Types::DATE, \ java.sql.Types::TIME, java.sql.Types::TIMESTAMP)) \ THEN V."SCALE" \ ELSE CAST (NULL AS SMALLINT) END \ AS SCALE, \ CASE WHEN (V."DATA_TYPE" IN (java.sql.Types::DECIMAL, \ java.sql.Types::NUMERIC, java.sql.Types::INTEGER, \ java.sql.Types::SMALLINT, java.sql.Types::TINYINT, \ java.sql.Types::BIGINT, java.sql.Types::DOUBLE, \ java.sql.Types::FLOAT, java.sql.Types::REAL, \ java.sql.Types::DATE, java.sql.Types::TIME, \ java.sql.Types::TIMESTAMP)) \ THEN V."RADIX" \ ELSE CAST (NULL AS SMALLINT) END \ AS RADIX, \ V."NULLABLE" AS NULLABLE, \ V."REMARKS" AS REMARKS, \ V."METHOD_ID" AS METHOD_ID, \ V."PARAMETER_ID" AS PARAMETER_ID \ FROM \ SYS.SYSALIASES A, SYS.SYSSCHEMAS, \ NEW org.apache.derby.catalog.GetProcedureColumns(A.ALIASINFO, A.ALIASTYPE) V \ WHERE \ ALIASTYPE = 'P' AND SCHEMANAME LIKE ? \ AND A.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID \ AND A.ALIAS LIKE ? AND \ V."COLUMN_NAME" LIKE ? \ ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, METHOD_ID, PARAMETER_ID # This is the JDBC 4.0 version of getProcedureColumns, with a new name. The # old query is kept to ensure that ODBCMetaDataGenerator will work as # before. The old query may be removed when ODBCMetaDataGenerator is # modified to correctly transform the new query. getProcedureColumns40=\ SELECT \ CAST (NULL AS VARCHAR(128)) AS PROCEDURE_CAT, \ S.SCHEMANAME AS PROCEDURE_SCHEM, \ A.ALIAS AS PROCEDURE_NAME, \ V."COLUMN_NAME" AS COLUMN_NAME, \ V."COLUMN_TYPE" AS COLUMN_TYPE, \ V."DATA_TYPE" AS DATA_TYPE, \ V."TYPE_NAME" AS TYPE_NAME, \ V."PRECISION" AS PRECISION, \ V."LENGTH" AS LENGTH, \ CASE WHEN (V."DATA_TYPE" IN (java.sql.Types::DECIMAL, \ java.sql.Types::NUMERIC, java.sql.Types::INTEGER, \ java.sql.Types::SMALLINT, java.sql.Types::TINYINT, \ java.sql.Types::BIGINT, java.sql.Types::DATE, \ java.sql.Types::TIME, java.sql.Types::TIMESTAMP)) \ THEN V."SCALE" \ ELSE CAST (NULL AS SMALLINT) END AS SCALE, \ CASE WHEN (V."DATA_TYPE" IN (java.sql.Types::DECIMAL, \ java.sql.Types::NUMERIC, java.sql.Types::INTEGER, \ java.sql.Types::SMALLINT, java.sql.Types::TINYINT, \ java.sql.Types::BIGINT, java.sql.Types::DOUBLE, \ java.sql.Types::FLOAT, java.sql.Types::REAL, \ java.sql.Types::DATE, java.sql.Types::TIME, \ java.sql.Types::TIMESTAMP)) \ THEN V."RADIX" \ ELSE CAST (NULL AS SMALLINT) END AS RADIX, \ V."NULLABLE" AS NULLABLE, \ V."REMARKS" AS REMARKS, \ CAST (NULL AS VARCHAR(254)) AS COLUMN_DEF, \ CAST (NULL AS INTEGER) AS SQL_DATA_TYPE, \ CAST (NULL AS INTEGER) AS SQL_DATETIME_SUB, \ CASE WHEN (V."DATA_TYPE" IN (java.sql.Types::CHAR, \ java.sql.Types::VARCHAR, java.sql.Types::BINARY, \ java.sql.Types::VARBINARY)) \ THEN V."LENGTH" \ ELSE CAST (NULL AS INTEGER) END AS CHAR_OCTET_LENGTH, \ CAST ((V."PARAMETER_ID" + 1) AS INT) AS ORDINAL_POSITION, \ CAST ((CASE WHEN (V."NULLABLE" IN \ (java.sql.DatabaseMetaData::procedureNullable)) \ THEN 'YES' \ ELSE 'NO' END) AS VARCHAR(128)) AS IS_NULLABLE, \ A.SPECIFICNAME AS SPECIFIC_NAME, \ V."METHOD_ID" AS METHOD_ID, \ V."PARAMETER_ID" AS PARAMETER_ID \ FROM \ SYS.SYSALIASES A, SYS.SYSSCHEMAS S, \ NEW org.apache.derby.catalog.GetProcedureColumns(A.ALIASINFO, A.ALIASTYPE) V \ WHERE \ A.ALIASTYPE = 'P' \ AND S.SCHEMANAME LIKE ? \ AND A.SCHEMAID = S.SCHEMAID \ AND A.ALIAS LIKE ? \ AND V."COLUMN_NAME" LIKE ? \ ORDER BY PROCEDURE_SCHEM, PROCEDURE_NAME, SPECIFIC_NAME, METHOD_ID, PARAMETER_ID # This wants a result set with a specific column name, # so we wrap the values in a select to name it. getTableTypes=\ SELECT CAST (RTRIM(TABLE_TYPE) AS VARCHAR(12)) AS TABLE_TYPE \ FROM (VALUES 'SYNONYM', 'SYSTEM TABLE', 'TABLE', 'VIEW') \ TABLETYPES(TABLE_TYPE) \ ORDER BY TABLE_TYPE # parameter 1 = pattern for catalog name # parameter 2 = pattern for schema name # parameter 3 = pattern for table name # parameter 4 = pattern for column name # 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. # getColumns=\ SELECT CAST ('' AS VARCHAR(128)) AS TABLE_CAT, \ S.SCHEMANAME AS TABLE_SCHEM, \ T.TABLENAME AS TABLE_NAME, \ C.COLUMNNAME AS COLUMN_NAME, \ C.COLUMNDATATYPE.getJDBCTypeId() AS DATA_TYPE, \ (CAST (C.COLUMNDATATYPE.getTypeName() \ AS VARCHAR(128))) AS TYPE_NAME, \ CASE WHEN (C.COLUMNDATATYPE.getJDBCTypeId() IN (java.sql.Types::INTEGER, \ java.sql.Types::SMALLINT, \ java.sql.Types::TINYINT, \ java.sql.Types::BIGINT, \ java.sql.Types::REAL, \ java.sql.Types::DOUBLE,\ java.sql.Types::DECIMAL, \ java.sql.Types::NUMERIC, \ java.sql.Types::FLOAT)) THEN \ C.COLUMNDATATYPE.getPrecision() ELSE \ C.COLUMNDATATYPE.getMaximumWidth() END AS COLUMN_SIZE, \ CAST( NULL AS INT) AS BUFFER_LENGTH, \ CASE WHEN (C.COLUMNDATATYPE.getJDBCTypeId() IN (java.sql.Types::DECIMAL, \ java.sql.Types::NUMERIC, java.sql.Types::INTEGER, \ java.sql.Types::SMALLINT, java.sql.Types::TINYINT, \ java.sql.Types::BIGINT, java.sql.Types::DATE, \ java.sql.Types::TIME, java.sql.Types::TIMESTAMP)) \ THEN C.COLUMNDATATYPE.getScale() \ ELSE CAST(NULL AS INT) END \ AS DECIMAL_DIGITS, \ CASE WHEN (C.COLUMNDATATYPE.getJDBCTypeId() IN (java.sql.Types::REAL, \ java.sql.Types::DOUBLE, java.sql.Types::FLOAT)) \ THEN 2 \ ELSE (CASE WHEN (C.COLUMNDATATYPE.getJDBCTypeId() IN ( \ java.sql.Types::INTEGER, java.sql.Types::SMALLINT, \ java.sql.Types::TINYINT, java.sql.Types::BIGINT, \ java.sql.Types::DECIMAL, java.sql.Types::NUMERIC, \ java.sql.Types::DATE, java.sql.Types::TIME, \ java.sql.Types::TIMESTAMP)) \ THEN 10 \ ELSE CAST (NULL AS INTEGER) END) END \ AS NUM_PREC_RADIX, \ CASE WHEN C.COLUMNDATATYPE.isNullable() THEN \ java.sql.DatabaseMetaData::columnNullable ELSE \ java.sql.DatabaseMetaData::columnNoNulls END AS NULLABLE, \ CAST ('' AS VARCHAR(128)) AS REMARKS, \ CASE WHEN (COLUMNDEFAULT IS NULL) THEN \ (CASE WHEN (AUTOINCREMENTINC is NULL) THEN \ CAST (NULL AS VARCHAR(254)) ELSE \ 'AUTOINCREMENT: start ' || \ (CAST (RTRIM(CAST(AUTOINCREMENTSTART AS CHAR(12))) AS VARCHAR(12))) || \ ' increment ' || \ (CAST (RTRIM(CAST(AUTOINCREMENTINC AS CHAR(12))) AS VARCHAR(12))) END ) ELSE \ CAST (COLUMNDEFAULT AS VARCHAR(254)) END AS COLUMN_DEF, \ CAST( NULL AS INT) AS SQL_DATA_TYPE, \ CAST( NULL AS INT) AS SQL_DATETIME_SUB, \ CASE WHEN (C.COLUMNDATATYPE.getJDBCTypeId() IN (java.sql.Types::CHAR, \ java.sql.Types::VARCHAR)) THEN \ (CASE WHEN (C.COLUMNDATATYPE.getMaximumWidth() * 2.0 > 2147483647) THEN \ 2147483647 ELSE (C.COLUMNDATATYPE.getMaximumWidth() * 2) END) ELSE \ CAST(NULL AS INT) END \ AS CHAR_OCTET_LENGTH, \ C.COLUMNNUMBER AS ORDINAL_POSITION, \ CAST ((CASE WHEN C.COLUMNDATATYPE.isNullable() THEN 'YES' ELSE 'NO' END) AS VARCHAR(128)) \ AS IS_NULLABLE, \ CAST (NULL AS VARCHAR(128)) AS SCOPE_CATLOG, \ CAST (NULL AS VARCHAR(128)) AS SCOPE_SCHEMA, \ CAST (NULL AS VARCHAR(128)) AS SCOPE_TABLE, \ CAST (NULL AS SMALLINT) AS SOURCE_DATA_TYPE, \ CAST ((CASE WHEN (AUTOINCREMENTINC is not NULL) THEN 'YES' ELSE 'NO' END) AS VARCHAR(128)) \ AS IS_AUTOINCREMENT \ FROM SYS.SYSSCHEMAS S, \ SYS.SYSTABLES T, \ SYS.SYSCOLUMNS C \ WHERE C.REFERENCEID = T.TABLEID AND S.SCHEMAID = T.SCHEMAID \ AND ((1=1) OR ? IS NOT NULL) \ AND (S.SCHEMANAME LIKE ?) AND (T.TABLENAME LIKE ?) AND (C.COLUMNNAME LIKE ?) \ ORDER BY TABLE_SCHEM, TABLE_NAME, ORDINAL_POSITION # parameter 1 = pattern for catalog name # parameter 2 = pattern for schema name # parameter 3 = table name # parameter 4 = pattern for column name getColumnPrivileges=\ SELECT CAST ('' AS VARCHAR(128)) AS TABLE_CAT, \ S.SCHEMANAME AS TABLE_SCHEM, \ T.TABLENAME AS TABLE_NAME, \ C.COLUMNNAME AS COLUMN_NAME, \ CAST( P.GRANTOR AS VARCHAR(128)) AS GRANTOR, \ CAST( P.GRANTEE AS VARCHAR(128)) AS GRANTEE, \ CASE WHEN (P.TYPE = 's' OR P.TYPE = 'S') THEN CAST( 'SELECT' AS VARCHAR(128)) \ ELSE CASE WHEN (P.TYPE = 'd' OR P.TYPE = 'D') THEN CAST( 'DELETE' AS VARCHAR(128)) \ ELSE CASE WHEN (P.TYPE = 'i' OR P.TYPE = 'I') THEN CAST( 'INSERT' AS VARCHAR(128)) \ ELSE CASE WHEN (P.TYPE = 'u' OR P.TYPE = 'U') THEN CAST( 'UPDATE' AS VARCHAR(128)) \ ELSE CASE WHEN (P.TYPE = 'r' OR P.TYPE = 'R') THEN CAST( 'REFERENCES' AS VARCHAR(128)) \ ELSE CAST( 'TRIGGER' AS VARCHAR(128)) \ END \ END \ END \ END \ END AS PRIVILEGE, \ CASE WHEN (P.TYPE = 's' OR P.TYPE = 'd' OR P.TYPE = 'i' OR P.TYPE = 'u' OR P.TYPE = 'r' OR P.TYPE = 't') \ THEN CAST( 'NO' AS VARCHAR(128)) ELSE CAST( 'YES' AS VARCHAR(128)) END AS IS_GRANTABLE \ FROM SYS.SYSCOLPERMS P, SYS.SYSCOLUMNS C, SYS.SYSTABLES T, SYS.SYSSCHEMAS S \ WHERE P.TABLEID = T.TABLEID AND C.REFERENCEID = T.TABLEID AND S.SCHEMAID = T.SCHEMAID \ AND ((1=1) OR ? IS NOT NULL) \ AND (S.SCHEMANAME LIKE ?) AND (T.TABLENAME=?) AND (C.COLUMNNAME LIKE ?) \ AND P.COLUMNS.isSet( C.COLUMNNUMBER - 1) \ ORDER BY COLUMN_NAME, PRIVILEGE # parameter 1 = pattern for catalog name # parameter 2 = pattern for schema name # parameter 3 = pattern for table name getTablePrivileges=\ SELECT CAST ('' AS VARCHAR(128)) AS TABLE_CAT, \ S.SCHEMANAME AS TABLE_SCHEM, \ T.TABLENAME AS TABLE_NAME, \ CAST( P.GRANTOR AS VARCHAR(128)) AS GRANTOR, \ CAST( P.GRANTEE AS VARCHAR(128)) AS GRANTEE, \ X.PRIV AS PRIVILEGE, \ X.GRANTABLE AS IS_GRANTABLE \ FROM SYS.SYSTABLEPERMS P, SYS.SYSTABLES T, SYS.SYSSCHEMAS S, \ (VALUES (CAST('SELECT' AS VARCHAR(128)), CAST('YES' AS VARCHAR(128))), \ (CAST('SELECT' AS VARCHAR(128)), CAST('NO' AS VARCHAR(128))), \ (CAST('DELETE' AS VARCHAR(128)), CAST('NO' AS VARCHAR(128))), \ (CAST('DELETE' AS VARCHAR(128)), CAST('YES' AS VARCHAR(128))), \ (CAST('INSERT' AS VARCHAR(128)), CAST('NO' AS VARCHAR(128))), \ (CAST('INSERT' AS VARCHAR(128)), CAST('YES' AS VARCHAR(128))), \ (CAST('UPDATE' AS VARCHAR(128)), CAST('NO' AS VARCHAR(128))), \ (CAST('UPDATE' AS VARCHAR(128)), CAST('YES' AS VARCHAR(128))), \ (CAST('REFERENCES' AS VARCHAR(128)), CAST('NO' AS VARCHAR(128))), \ (CAST('REFERENCES' AS VARCHAR(128)), CAST('YES' AS VARCHAR(128))), \ (CAST('TRIGGER' AS VARCHAR(128)), CAST('NO' AS VARCHAR(128))), \ (CAST('TRIGGER' AS VARCHAR(128)), CAST('YES' AS VARCHAR(128)))) AS X(PRIV,GRANTABLE) \ WHERE P.TABLEID = T.TABLEID AND S.SCHEMAID = T.SCHEMAID \ AND ((1=1) OR ? IS NOT NULL) \ AND (S.SCHEMANAME LIKE ?) AND (T.TABLENAME LIKE ?) \ AND ((P.SELECTPRIV = 'y' AND X.PRIV = 'SELECT' AND X.GRANTABLE = 'NO') \ OR (P.SELECTPRIV = 'Y' AND X.PRIV = 'SELECT' AND X.GRANTABLE = 'YES') \ OR (P.SELECTPRIV = 'y' AND X.PRIV = 'SELECT' AND X.GRANTABLE = 'NO') \ OR (P.DELETEPRIV = 'Y' AND X.PRIV = 'DELETE' AND X.GRANTABLE = 'YES') \ OR (P.DELETEPRIV = 'y' AND X.PRIV = 'DELETE' AND X.GRANTABLE = 'NO') \ OR (P.INSERTPRIV = 'Y' AND X.PRIV = 'INSERT' AND X.GRANTABLE = 'YES') \ OR (P.INSERTPRIV = 'y' AND X.PRIV = 'INSERT' AND X.GRANTABLE = 'NO') \ OR (P.UPDATEPRIV = 'Y' AND X.PRIV = 'UPDATE' AND X.GRANTABLE = 'YES') \ OR (P.UPDATEPRIV = 'y' AND X.PRIV = 'UPDATE' AND X.GRANTABLE = 'NO') \ OR (P.REFERENCESPRIV = 'Y' AND X.PRIV = 'REFERENCES' AND X.GRANTABLE = 'YES') \ OR (P.REFERENCESPRIV = 'y' AND X.PRIV = 'REFERENCES' AND X.GRANTABLE = 'NO') \ OR (P.TRIGGERPRIV = 'Y' AND X.PRIV = 'TRIGGER' AND X.GRANTABLE = 'YES') \ OR (P.TRIGGERPRIV = 'y' AND X.PRIV = 'TRIGGER' AND X.GRANTABLE = 'NO')) \ ORDER BY TABLE_SCHEM, TABLE_NAME, PRIVILEGE # This query is set up to return 0 rows of the right shape, for pre-10.2 # versions of Derby databases # parameter 1 = pattern for catalog name # parameter 2 = pattern for schema name # parameter 3 = pattern for table name # parameter 4 = pattern for column name getColumnPrivileges_10_1=\ SELECT TABLE_CAT, TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, \ GRANTOR, GRANTEE, PRIVILEGE, IS_GRANTABLE \ FROM ( VALUES (CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128)), \ CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128)), \ CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128)), \ CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128))) ) \ AS COLUMNPRIVILEGES ( TABLE_CAT, TABLE_SCHEM, TABLE_NAME, \ COLUMN_NAME, GRANTOR, GRANTEE, \ PRIVILEGE, IS_GRANTABLE ) \ WHERE (1=0) AND (((1=1) OR ? IS NOT NULL) OR ''=? OR ''=? OR ''=?) # ORDER BY COLUMN_NAME, PRIVILEGE # REMIND: this query is set up to return 0 rows of the right shape for # pre-10.2 versions of Derby databases # parameter 1 = pattern for catalog name # parameter 2 = pattern for schema name # parameter 3 = pattern for table name getTablePrivileges_10_1=\ SELECT TABLE_CAT, TABLE_SCHEM, TABLE_NAME, \ GRANTOR, GRANTEE, PRIVILEGE, IS_GRANTABLE \ FROM ( VALUES (CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128)), \ CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128)), \ CAST ('' AS VARCHAR(128)), CAST ('' AS VARCHAR(128)), \ CAST ('' AS VARCHAR(128))) ) \ AS TABLEPRIVILEGES (TABLE_CAT, TABLE_SCHEM, TABLE_NAME, \ GRANTOR, GRANTEE, PRIVILEGE, IS_GRANTABLE ) \ WHERE (1=0) AND (((1=1) OR ? IS NOT NULL) OR ''=? OR ''=?) \ ORDER BY TABLE_SCHEM, TABLE_NAME, PRIVILEGE # 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. # parameter 1 = pattern for catalog name # parameter 2 = pattern for schema name # parameter 3 = pattern for table name getVersionColumns=\ SELECT SCOPE, COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE, \ BUFFER_LENGTH, DECIMAL_DIGITS, PSEUDO_COLUMN \ FROM ( VALUES (SMALLINT(1), CAST ('' AS VARCHAR(128)), 1, \ CAST ('' AS VARCHAR(128)), 1, 1, SMALLINT(1), SMALLINT(1)) ) \ AS VERSIONCOLUMNS (SCOPE, COLUMN_NAME, DATA_TYPE, \ TYPE_NAME, COLUMN_SIZE, BUFFER_LENGTH, \ DECIMAL_DIGITS, PSEUDO_COLUMN ) \ WHERE (1=0) AND (((1=1) OR ? IS NOT NULL) OR ''=? OR ''=?) # REMIND: update query when we have catalogs and SCHEMAS # parameter 1 = pattern for catalog name # parameter 2 = pattern for schema name # parameter 3 = table name getPrimaryKeys=\ SELECT CAST ('' AS VARCHAR(128)) AS TABLE_CAT, \ S.SCHEMANAME AS TABLE_SCHEM, T.TABLENAME AS TABLE_NAME, \ COLS.COLUMNNAME AS COLUMN_NAME, \ CAST (CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) AS SMALLINT) AS KEY_SEQ, \ CONS.CONSTRAINTNAME AS PK_NAME \ FROM --DERBY-PROPERTIES joinOrder=FIXED \n \ SYS.SYSTABLES T --DERBY-PROPERTIES index='SYSTABLES_INDEX1' \n\ , SYS.SYSSCHEMAS S --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSSCHEMAS_INDEX1' \n\ , SYS.SYSCONSTRAINTS CONS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCONSTRAINTS_INDEX3' \n\ , SYS.SYSKEYS KEYS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSKEYS_INDEX1' \n\ , SYS.SYSCONGLOMERATES CONGLOMS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\ , SYS.SYSCOLUMNS COLS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index ='SYSCOLUMNS_INDEX1' \n\ WHERE ((1=1) OR ? IS NOT NULL) AND S.SCHEMANAME LIKE ? AND T.TABLENAME=? AND \ T.SCHEMAID = S.SCHEMAID AND \ T.TABLEID = COLS.REFERENCEID AND T.TABLEID = CONGLOMS.TABLEID AND \ CONS.TABLEID = T.TABLEID AND CONS.TYPE = 'P' AND \ CONS.CONSTRAINTID = KEYS.CONSTRAINTID AND \ (CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \ CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE \ 0 END) <> 0 AND \ KEYS.CONGLOMERATEID = CONGLOMS.CONGLOMERATEID \ ORDER BY COLUMN_NAME ######## # getCrossReference # # This query gives information about referenced keys # and foreign keys. It is used to satisfy # and getExportedKeys() AS well as getCrossReference(). # # NOTE: this is the same query as getImportedKeys() # except is has a different ORDER BY and extra parameters. # #param1 = pattern for the PRIMARY CATALOG name #param2 = pattern for the PRIMARY SCHEMA name #param3 = PRIMARY TABLE name # #param4 = pattern for the FOREIGN CATALOG name ('%' for getExportedKeys()) #param5 = pattern for the FOREIGN SCHEMA name ('%' for getExportedKeys()) #param6 = pattern for the FOREIGN TABLE name ('%' for getExportedKeys()) # DERBY-2610: did not change from pattern matching to "T2.TABLENAME=?" # because getExportedKeys uses this query with '%' for foreign table # Future: may want to add a new query for getExportedKeys to remove the # "T2.TABLENAME LIKE ?" pattern getCrossReference=\ SELECT CAST ('' AS VARCHAR(128)) AS PKTABLE_CAT, \ PKTABLE_SCHEM, \ PKTABLE_NAME, \ PKCOLUMN_NAME, \ CAST ('' AS VARCHAR(128)) AS FKTABLE_CAT, \ S2.SCHEMANAME AS FKTABLE_SCHEM, \ T2.TABLENAME AS FKTABLE_NAME, \ COLS2.COLUMNNAME AS FKCOLUMN_NAME, \ CAST (CONGLOMS2.DESCRIPTOR.getKeyColumnPosition( \ COLS2.COLUMNNUMBER) \ AS SMALLINT) AS KEY_SEQ, \ CAST ((CASE WHEN F2.UPDATERULE='S' \ THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE \ (CASE WHEN F2.UPDATERULE='R' \ THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE \ java.sql.DatabaseMetaData::importedKeyNoAction END) END) \ AS SMALLINT) AS UPDATE_RULE, \ CAST ((CASE WHEN F2.DELETERULE='S' \ THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE \ (CASE WHEN F2.DELETERULE='R' \ THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE \ (CASE WHEN F2.DELETERULE='C' \ THEN java.sql.DatabaseMetaData::importedKeyCascade ELSE \ (CASE WHEN F2.DELETERULE='U' \ THEN java.sql.DatabaseMetaData::importedKeySetNull ELSE \ java.sql.DatabaseMetaData::importedKeyNoAction END)END)ENd)END) \ AS SMALLINT) AS DELETE_RULE, \ C2.CONSTRAINTNAME AS FK_NAME, \ PK_NAME, \ CAST (java.sql.DatabaseMetaData::importedKeyNotDeferrable \ AS SMALLINT) AS DEFERRABILITY \ FROM --DERBY-PROPERTIES joinOrder=FIXED \n\ (SELECT C.CONSTRAINTID AS PK_ID, \ CONSTRAINTNAME AS PK_NAME, \ PKTB_SCHEMA AS PKTABLE_SCHEM, \ PKTB_NAME AS PKTABLE_NAME, \ COLS.COLUMNNAME AS PKCOLUMN_NAME, \ CONGLOMS.DESCRIPTOR.getKeyColumnPosition( \ COLS.COLUMNNUMBER) AS KEY_SEQ \ FROM --DERBY-PROPERTIES joinOrder=FIXED \n\ (SELECT T.TABLEID AS PKTB_ID, \ S.SCHEMANAME AS PKTB_SCHEMA, \ T.TABLENAME AS PKTB_NAME \ FROM \ SYS.SYSTABLES t --DERBY-PROPERTIES index = 'SYSTABLES_INDEX1' \n\ , SYS.SYSSCHEMAS s --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX1' \n\ WHERE \ ((1=1) OR ? IS NOT NULL) \ AND S.SCHEMANAME LIKE ? \ AND T.TABLENAME=? \ AND S.SCHEMAID = T.SCHEMAID \ ) AS PKTB (PKTB_ID, PKTB_SCHEMA, PKTB_NAME), \ SYS.SYSCONSTRAINTS C --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX3' \n\ , SYS.SYSKEYS K --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSKEYS_INDEX1' \n\ , SYS.SYSCONGLOMERATES CONGLOMS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\ , SYS.SYSCOLUMNS COLS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \n\ WHERE \ PKTB.PKTB_ID = C.TABLEID \ AND K.CONSTRAINTID = C.CONSTRAINTID \ AND PKTB.PKTB_ID = COLS.REFERENCEID \ AND (CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \ CONGLOMS.DESCRIPTOR.getKeyColumnPosition( \ COLS.COLUMNNUMBER) ELSE \ 0 END) <> 0 \ AND K.CONGLOMERATEID = CONGLOMS.CONGLOMERATEID \ ) AS PKINFO(PK_ID, \ PK_NAME, \ PKTABLE_SCHEM, \ PKTABLE_NAME, \ PKCOLUMN_NAME, \ KEY_SEQ), \ SYS.SYSFOREIGNKEYS F2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSFOREIGNKEYS_INDEX1' \n\ , SYS.SYSCONSTRAINTS c2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX1' \n\ , SYS.SYSTABLES T2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSTABLES_INDEX2' \n\ , SYS.SYSSCHEMAS S2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX2' \n\ , SYS.SYSCONGLOMERATES CONGLOMS2 --DERBY-PROPERTIES joinStrategy = NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\ , SYS.SYSCOLUMNS COLS2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \n\ WHERE F2.keyCONSTRAINTID = PKINFO.PK_ID \ AND PKINFO.KEY_SEQ = CONGLOMS2.DESCRIPTOR.getKeyColumnPosition( \ COLS2.COLUMNNUMBER) \ AND T2.TABLEID = C2.TABLEID \ AND ((1=1) OR ? IS NOT NULL) \ AND S2.SCHEMANAME LIKE ? \ AND T2.TABLENAME LIKE ? \ AND S2.SCHEMAID = T2.SCHEMAID \ AND F2.CONSTRAINTID = C2.CONSTRAINTID \ AND (CASE WHEN CONGLOMS2.DESCRIPTOR IS NOT NULL THEN \ CONGLOMS2.DESCRIPTOR.getKeyColumnPosition(COLS2.COLUMNNUMBER) ELSE 0 END) <> 0 \ AND F2.CONGLOMERATEID = CONGLOMS2.CONGLOMERATEID \ AND C2.TABLEID = COLS2.REFERENCEID \ ORDER BY FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, FK_NAME, KEY_SEQ ######## # getImportedKeys # # NOTE: this is the same query AS getCrossReference() # except is has a different ORDER BY and it doesn't take # the primary key parameters # #param1 = pattern for the FOREIGN CATALOG name #param2 = pattern for the FOREIGN SCHEMA name #param3 = FOREIGN TABLE name getImportedKeys=\ SELECT CAST ('' AS VARCHAR(128)) AS PKTABLE_CAT, \ S.SCHEMANAME AS PKTABLE_SCHEM, \ TABLENAME AS PKTABLE_NAME, \ COLS.COLUMNNAME AS PKCOLUMN_NAME, \ CAST ('' AS VARCHAR(128)) AS FKTABLE_CAT, \ FKTABLE_SCHEM, \ FKTABLE_NAME, \ FKCOLUMN_NAME, \ CAST (CONGLOMS.DESCRIPTOR.getKeyColumnPosition( \ COLS.COLUMNNUMBER) \ AS SMALLINT) AS KEY_SEQ, \ CAST ((CASE WHEN FK_UPDATERULE='S' \ THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE \ (CASE WHEN FK_UPDATERULE='R' \ THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE \ java.sql.DatabaseMetaData::importedKeyNoAction END) END) \ AS SMALLINT) AS UPDATE_RULE, \ CAST ((CASE WHEN FK_DELETERULE='S' \ THEN java.sql.DatabaseMetaData::importedKeyRestrict ELSE \ (CASE WHEN FK_DELETERULE='R' \ THEN java.sql.DatabaseMetaData::importedKeyNoAction ELSE \ (CASE WHEN FK_DELETERULE='C' \ THEN java.sql.DatabaseMetaData::importedKeyCascade ELSE \ (CASE WHEN FK_DELETERULE='U' \ THEN java.sql.DatabaseMetaData::importedKeySetNull ELSE \ java.sql.DatabaseMetaData::importedKeyNoAction END) END) END) END) \ AS SMALLINT) AS DELETE_RULE, \ FK_NAME, \ CONSTRAINTNAME AS PK_NAME, \ CAST (java.sql.DatabaseMetaData::importedKeyNotDeferrable \ AS SMALLINT) AS DEFERRABILITY \ FROM --DERBY-PROPERTIES joinOrder=FIXED \n\ (SELECT F2.keyCONSTRAINTID AS FK_ID, \ FKTB_SCHEMA AS FKTABLE_SCHEM, \ FKTB_NAME AS FKTABLE_NAME, \ COLS2.COLUMNNAME AS FKCOLUMN_NAME, \ CONGLOMS2.DESCRIPTOR.getKeyColumnPosition( \ COLS2.COLUMNNUMBER) AS KEY_SEQ, \ C2.CONSTRAINTNAME AS FK_NAME, \ F2.DELETERULE AS FK_UPDATERULE, \ F2.DELETERULE AS FK_DELETERULE \ FROM --DERBY-PROPERTIES joinOrder=FIXED \n\ (SELECT T2.TABLEID AS FKTB_ID, \ S2.SCHEMANAME AS FKTB_SCHEMA, \ T2.TABLENAME AS FKTB_NAME \ FROM \ SYS.SYSTABLES T2 --DERBY-PROPERTIES index = 'SYSTABLES_INDEX1' \n\ , SYS.SYSSCHEMAS S2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX1' \n\ WHERE \ ((1=1) OR ? IS NOT NULL) \ AND S2.SCHEMANAME LIKE ? \ AND T2.TABLENAME=? \ AND S2.SCHEMAID = T2.SCHEMAID \ ) AS FKTB (FKTB_ID, FKTB_SCHEMA, FKTB_NAME), \ SYS.SYSCONSTRAINTS c2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX3' \n\ , SYS.SYSFOREIGNKEYS F2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSFOREIGNKEYS_INDEX1' \n\ , SYS.SYSCONGLOMERATES CONGLOMS2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\ , SYS.SYSCOLUMNS COLS2 --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \n\ WHERE \ FKTB.FKTB_ID = C2.TABLEID \ AND F2.CONSTRAINTID = C2.CONSTRAINTID \ AND FKTB.FKTB_ID = COLS2.REFERENCEID \ AND (CASE WHEN CONGLOMS2.DESCRIPTOR IS NOT NULL THEN \ CONGLOMS2.DESCRIPTOR.getKeyColumnPosition( \ COLS2.COLUMNNUMBER) ELSE \ 0 END) <> 0 \ AND F2.CONGLOMERATEID = CONGLOMS2.CONGLOMERATEID \ ) AS FKINFO(FK_ID, \ FKTABLE_SCHEM, \ FKTABLE_NAME, \ FKCOLUMN_NAME, \ KEY_SEQ, \ FK_NAME, \ FK_UPDATERULE, \ FK_DELETERULE), \ SYS.SYSCONSTRAINTS c --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCONSTRAINTS_INDEX1' \n\ , SYS.SYSTABLES T --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSTABLES_INDEX2' \n\ , SYS.SYSSCHEMAS S --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSSCHEMAS_INDEX2' \n\ , SYS.SYSKEYS K --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSKEYS_INDEX1' \n\ , SYS.SYSCONGLOMERATES CONGLOMS --DERBY-PROPERTIES joinStrategy = NESTEDLOOP, index = 'SYSCONGLOMERATES_INDEX1' \n\ , SYS.SYSCOLUMNS COLS --DERBY-PROPERTIES joinStrategy=NESTEDLOOP, index = 'SYSCOLUMNS_INDEX1' \n\ WHERE T.TABLEID = C.TABLEID \ AND C.CONSTRAINTID = FKINFO.FK_ID \ AND FKINFO.KEY_SEQ = CONGLOMS.DESCRIPTOR.getKeyColumnPosition( \ COLS.COLUMNNUMBER) \ AND S.SCHEMAID = T.SCHEMAID \ AND K.CONSTRAINTID = C.CONSTRAINTID \ AND (CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \ CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE 0 END) <> 0 \ AND K.CONGLOMERATEID = CONGLOMS.CONGLOMERATEID \ AND C.TABLEID = COLS.REFERENCEID \ ORDER BY PKTABLE_CAT, \ PKTABLE_SCHEM, \ PKTABLE_NAME, \ PK_NAME, \ KEY_SEQ getTypeInfo=\ SELECT CAST (RTRIM(CAST (T1 AS CHAR(128))) AS VARCHAR(128)) AS TYPE_NAME, \ T2 AS DATA_TYPE, \ T3 AS PRECISION, \ CAST (RTRIM(CAST(T4 AS CHAR(10))) AS VARCHAR(10)) AS LITERAL_PREFIX, \ CAST (RTRIM(T5) AS VARCHAR(10)) AS LITERAL_SUFFIX, \ CAST (RTRIM(CAST(T6 AS CHAR(20))) AS VARCHAR(20)) AS CREATE_PARAMS, \ CAST (T7 AS SMALLINT) AS NULLABLE, \ T8 AS CASE_SENSITIVE, \ CAST (T9 AS SMALLINT) AS SEARCHABLE, \ T10 AS UNSIGNED_ATTRIBUTE, \ T11 AS FIXED_PREC_SCALE, \ T12 AS AUTO_INCREMENT, \ CAST (RTRIM(CAST(T1 AS CHAR(128))) AS VARCHAR(128)) AS LOCAL_TYPE_NAME, \ CAST (T14 AS SMALLINT) AS MINIMUM_SCALE, \ CAST (T15 AS SMALLINT) AS MAXIMUM_SCALE, \ CAST (NULL AS INT) AS SQL_DATA_TYPE, \ CAST (NULL AS INT) AS SQL_DATETIME_SUB, \ T18 AS NUM_PREC_RADIX \ FROM ( VALUES \ ('BIGINT',-5,19,CAST (NULL AS CHAR),CAST (NULL AS CHAR),CAST (NULL AS CHAR), \ 1,FALSE,2,FALSE,FALSE,TRUE,0,0,10),\ ('LONG VARCHAR FOR BIT DATA',-4,32700,'X''','''',CAST (NULL AS CHAR), \ 1,FALSE,0,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER), \ CAST (NULL AS INTEGER)), \ ('VARCHAR () FOR BIT DATA',-3,32672,'X''','''','length', \ 1,FALSE,2,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER), \ CAST (NULL AS INTEGER)), \ ('CHAR () FOR BIT DATA',-2,254,'X''','''','length', \ 1,FALSE,2,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER), \ CAST (NULL AS INTEGER)), \ ('LONG VARCHAR',-1,32700,'''','''',CAST (NULL AS CHAR), \ 1,TRUE,1,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER), \ CAST (NULL AS INTEGER)), \ ('CHAR',1,254,'''','''','length', \ 1,TRUE,3,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER), \ CAST (NULL AS INTEGER)), \ ('NUMERIC',2,31,CAST (NULL AS CHAR),CAST (NULL AS CHAR),'precision,scale', \ 1,FALSE,2,FALSE,TRUE,FALSE,0,31,10),\ ('DECIMAL',3,31,CAST (NULL AS CHAR),CAST (NULL AS CHAR),'precision,scale', \ 1,FALSE,2,FALSE,TRUE,FALSE,0,31,10),\ ('INTEGER',4,10,CAST (NULL AS CHAR),CAST (NULL AS CHAR),CAST (NULL AS CHAR), \ 1,FALSE,2,FALSE,FALSE,TRUE,0,0,10),\ ('SMALLINT',5,5,CAST (NULL AS CHAR),CAST (NULL AS CHAR),CAST (NULL AS CHAR), \ 1,FALSE,2,FALSE,FALSE,TRUE,0,0,10),\ ('FLOAT',6,52,CAST (NULL AS CHAR),CAST (NULL AS CHAR),'precision', \ 1,FALSE,2,FALSE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),2),\ ('REAL',7,23,CAST (NULL AS CHAR),CAST (NULL AS CHAR),CAST (NULL AS CHAR), \ 1,FALSE,2,FALSE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),2),\ ('DOUBLE',8,52,CAST (NULL AS CHAR),CAST (NULL AS CHAR),CAST (NULL AS CHAR), \ 1,FALSE,2,FALSE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),2),\ ('VARCHAR',12,32672,'''','''','length', \ 1,TRUE,3,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER), \ CAST (NULL AS INTEGER)), \ ('BOOLEAN',16,1,CAST (NULL AS CHAR),CAST (NULL AS CHAR),CAST (NULL AS CHAR), \ 1,FALSE,2,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS INTEGER)),\ ('DATE',91,10,'DATE''','''',CAST (NULL AS CHAR), \ 1,FALSE,2,TRUE,FALSE,FALSE,0,0,10),\ ('TIME',92,8,'TIME''','''',CAST (NULL AS CHAR), \ 1,FALSE,2,TRUE,FALSE,FALSE,0,0,10),\ ('TIMESTAMP',93,26,'TIMESTAMP''','''',CAST (NULL AS CHAR), \ 1,FALSE,2,TRUE,FALSE,FALSE,0,6,10),\ ('OBJECT',2000,CAST (NULL AS INTEGER),CAST (NULL AS CHAR),CAST (NULL AS CHAR),CAST (NULL AS CHAR), \ 1,FALSE,2,TRUE,FALSE,FALSE,CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS INTEGER)),\ ('BLOB',2004,2147483647,CAST (NULL AS CHAR),CAST (NULL AS CHAR),'length', \ 1,FALSE,0,CAST (NULL AS BOOLEAN),FALSE,CAST (NULL AS BOOLEAN), \ CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS INTEGER)),\ ('CLOB',2005,2147483647,'''','''','length', \ 1,TRUE,1,CAST (NULL AS BOOLEAN),FALSE,CAST (NULL AS BOOLEAN), \ CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS INTEGER)), \ ('XML',2009,CAST (NULL AS INTEGER),CAST (NULL AS CHAR),CAST (NULL AS CHAR),CAST (NULL AS CHAR), \ 1,TRUE,0,FALSE,FALSE,FALSE, \ CAST (NULL AS INTEGER),CAST (NULL AS INTEGER),CAST (NULL AS INTEGER)) \ ) AS TYPEINFO(T1,T2,T3,T4,T5,T6,T7,T8,T9,T10,T11,T12,T14,T15,T18) # parameter 1 = pattern for catalog name # parameter 2 = pattern for schema name # parameter 3 = table name # parameter 4 = only get unique Indexes if TRUE # parameter 5 = approximate information allowed if TRUE getIndexInfo=\ SELECT CAST ('' AS VARCHAR(128)) AS TABLE_CAT, S.SCHEMANAME AS TABLE_SCHEM, T.TABLENAME AS TABLE_NAME, \ (CASE WHEN CONGLOMS.DESCRIPTOR.isUnique() THEN FALSE ELSE TRUE END) AS NON_UNIQUE, \ CAST ('' AS VARCHAR(128)) AS INDEX_QUALIFIER, \ CONGLOMS.CONGLOMERATENAME AS INDEX_NAME, \ java.sql.DatabaseMetaData::tableIndexOther AS TYPE, \ CAST (CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) AS SMALLINT) AS ORDINAL_POSITION, \ COLS.COLUMNNAME AS COLUMN_NAME, \ CASE WHEN CONGLOMS.DESCRIPTOR.isAscending( \ CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER)) THEN 'A' ELSE 'D' END AS ASC_OR_DESC, \ CAST(NULL AS INT) AS CARDINALITY, \ CAST(NULL AS INT) AS PAGES, \ CAST(NULL AS VARCHAR(128)) AS FILTER_CONDITION \ FROM SYS.SYSSCHEMAS S, \ SYS.SYSTABLES T, \ SYS.SYSCONGLOMERATES CONGLOMS, \ SYS.SYSCOLUMNS COLS \ WHERE T.TABLEID = CONGLOMS.TABLEID AND T.TABLEID = COLS.REFERENCEID \ AND T.SCHEMAID = S.SCHEMAID \ AND CONGLOMS.ISINDEX \ AND (CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \ CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE \ 0 END) <> 0 \ AND ((1=1) OR ? IS NOT NULL) AND S.SCHEMANAME LIKE ? AND T.TABLENAME=? \ AND ( CASE WHEN ? THEN CONGLOMS.DESCRIPTOR.isUnique() ELSE (1=1) END) AND ((1=1) OR ?<>0) \ ORDER BY NON_UNIQUE, TYPE, INDEX_NAME, ORDINAL_POSITION ############################################ # # getBestRowIdentifier queries # ############################################ # getBestRowIdentifierEmpty # # Used when bad params passed into # getBestRowIdentifier(); return empty result set # of the right shape # getBestRowIdentifierEmpty=\ SELECT SCOPE, COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE, \ BUFFER_LENGTH, DECIMAL_DIGITS, PSEUDO_COLUMN \ FROM (VALUES \ (CAST (2 AS SMALLINT), \ CAST ('' AS VARCHAR(128)), \ 0, \ CAST ('INT' AS VARCHAR(128)), \ 0, \ 0, \ CAST (0 AS SMALLINT), \ CAST (0 AS SMALLINT)) \ ) AS BESTROWIDENTIFIER( \ SCOPE, COLUMN_NAME, DATA_TYPE, TYPE_NAME, COLUMN_SIZE, BUFFER_LENGTH, \ DECIMAL_DIGITS, PSEUDO_COLUMN) \ WHERE (1=0) # getBestRowIdentifierPrimaryKey # # Find a primary key on the given table # # parameter1 - catalog # parameter2 - schema # parameter3 - table # getBestRowIdentifierPrimaryKey=\ SELECT conS.CONSTRAINTID \ FROM SYS.SYSSCHEMAS SCHEMAS, SYS.SYSTABLES TABS, SYS.SYSCONSTRAINTS cons, SYS.SYSKEYS keys \ WHERE TABS.TABLEID = conS.TABLEID \ AND SCHEMAS.SCHEMAID = TABS.SCHEMAID \ AND conS.CONSTRAINTID = KEYS.CONSTRAINTID \ AND conS.type = 'P' \ AND ((1=1) OR ? IS NOT NULL) \ AND (SCHEMAS.SCHEMANAME LIKE ?) \ AND (TABS.TABLENAME=?) # getBestRowIdentifierPrimaryKeyColumns # # Return the columns that make up the primary key # # parameter1 - CONSTRAINTID from SYS.SYSCONSTRAINTS # parameter2 - CONSTRAINTID from SYS.SYSKEYS # getBestRowIdentifierPrimaryKeyColumns=\ SELECT \ CAST (java.sql.DatabaseMetaData::bestRowSession AS SMALLINT) AS SCOPE, \ COLS.COLUMNNAME AS COLUMN_NAME, \ COLS.COLUMNDATATYPE.getJDBCTypeId() AS DATA_TYPE, \ CAST (COLS.COLUMNDATATYPE.getTypeName() AS VARCHAR(128)) AS TYPE_NAME, \ COLS.COLUMNDATATYPE.getMaximumWidth() AS COLUMN_SIZE, \ CAST (NULL AS INT) AS BUFFER_LENGTH, \ CAST ((CASE WHEN (COLS.COLUMNDATATYPE.getJDBCTypeId() IN ( \ java.sql.Types::DECIMAL, java.sql.Types::NUMERIC, \ java.sql.Types::INTEGER, java.sql.Types::SMALLINT, \ java.sql.Types::TINYINT, java.sql.Types::BIGINT, \ java.sql.Types::DATE, java.sql.Types::TIME, \ java.sql.Types::TIMESTAMP)) \ THEN COLS.COLUMNDATATYPE.getPrecision() \ ELSE CAST (NULL AS SMALLINT) END) AS SMALLINT) \ AS DECIMAL_DIGITS, \ CAST (java.sql.DatabaseMetaData::bestRowNotPseudo AS SMALLINT) AS PSEUDO_COLUMN \ FROM SYS.SYSSCHEMAS SCHEMAS, SYS.SYSTABLES TABS, \ SYS.SYSCONSTRAINTS cons, SYS.SYSKEYS keys, \ SYS.SYSCONGLOMERATES CONGLOMS, SYS.SYSCOLUMNS COLS \ WHERE TABS.TABLEID = conS.TABLEID \ AND TABS.TABLEID = COLS.REFERENCEID \ AND SCHEMAS.SCHEMAID = TABS.SCHEMAID \ AND conS.CONSTRAINTID = ? \ AND KEYS.CONSTRAINTID = ? \ AND conS.CONSTRAINTID = KEYS.CONSTRAINTID \ AND TABS.TABLEID = CONGLOMS.TABLEID \ AND KEYS.CONGLOMERATEID = CONGLOMS.CONGLOMERATEID \ AND (CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \ CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE \ 0 END) <> 0 # getBestRowIdentifierUniqueConstraint # # See if there is a unique constraint on the given table # # parameter1 - catalog # parameter2 - schema # parameter3 - table # getBestRowIdentifierUniqueConstraint=\ SELECT CONS.CONSTRAINTID, IDX.DESCRIPTOR.numberOfOrderedColumns() AS NUMCOLS \ FROM SYS.SYSSCHEMAS SCHEMAS, SYS.SYSTABLES TABS, \ SYS.SYSCONSTRAINTS cons, SYS.SYSKEYS keys, SYS.SYSCONGLOMERATES IDX \ WHERE TABS.TABLEID = conS.TABLEID AND SCHEMAS.SCHEMAID = TABS.SCHEMAID \ AND conS.CONSTRAINTID = KEYS.CONSTRAINTID AND IDX.DESCRIPTOR IS NOT NULL \ AND KEYS.CONGLOMERATEID = IDX.CONGLOMERATEID AND IDX.ISCONSTRAINT \ AND conS.type = 'U' \ AND ((1=1) OR ? IS NOT NULL) \ AND (SCHEMAS.SCHEMANAME LIKE ?) \ AND (TABS.TABLENAME=?) \ ORDER BY NUMCOLS # getBestRowIdentifierUniqueKeyColumns # # Return the columns in the unique constraint # # parameter1 - CONSTRAINTID from SYS.SYSCONSTRAINTS # parameter2 - CONSTRAINTID from SYS.SYSKEYS # parameter3 - null ok # getBestRowIdentifierUniqueKeyColumns=\ SELECT \ CAST (java.sql.DatabaseMetaData::bestRowSession AS SMALLINT) AS SCOPE, \ COLS.COLUMNNAME AS COLUMN_NAME, \ COLS.COLUMNDATATYPE.getJDBCTypeId() AS DATA_TYPE, \ CAST (COLS.COLUMNDATATYPE.getTypeName() AS VARCHAR(128)) AS TYPE_NAME, \ COLS.COLUMNDATATYPE.getMaximumWidth() AS COLUMN_SIZE, \ CAST (NULL AS INT) AS BUFFER_LENGTH, \ CAST ((CASE WHEN (COLS.COLUMNDATATYPE.getJDBCTypeId() IN ( \ java.sql.Types::DECIMAL, java.sql.Types::NUMERIC, \ java.sql.Types::INTEGER, java.sql.Types::SMALLINT, \ java.sql.Types::TINYINT, java.sql.Types::BIGINT, \ java.sql.Types::DATE, java.sql.Types::TIME, \ java.sql.Types::TIMESTAMP)) \ THEN COLS.COLUMNDATATYPE.getPrecision() \ ELSE CAST (NULL AS SMALLINT) END) AS SMALLINT) \ AS DECIMAL_DIGITS, \ CAST (java.sql.DatabaseMetaData::bestRowNotPseudo AS SMALLINT) AS PSEUDO_COLUMN \ FROM SYS.SYSSCHEMAS SCHEMAS, SYS.SYSTABLES TABS, \ SYS.SYSCONSTRAINTS cons, SYS.SYSKEYS keys, \ SYS.SYSCONGLOMERATES CONGLOMS, SYS.SYSCOLUMNS COLS \ WHERE TABS.TABLEID = conS.TABLEID \ AND TABS.TABLEID = COLS.REFERENCEID \ AND SCHEMAS.SCHEMAID = TABS.SCHEMAID \ AND conS.CONSTRAINTID = ? \ AND KEYS.CONSTRAINTID = ? \ AND conS.CONSTRAINTID = KEYS.CONSTRAINTID \ AND TABS.TABLEID = CONGLOMS.TABLEID \ AND KEYS.CONGLOMERATEID = CONGLOMS.CONGLOMERATEID \ AND (CASE WHEN CONGLOMS.DESCRIPTOR IS NOT NULL THEN \ CONGLOMS.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE \ 0 END) > 0 \ AND (?<>0 or not COLS.COLUMNDATATYPE.isNullable() ) # getBestRowIdentifierUniqueIndex # # See if there is a unique index on the given table # # parameter1 - catalog # parameter2 - schema # parameter3 - table # getBestRowIdentifierUniqueIndex=\ SELECT IDX.CONGLOMERATENUMBER, IDX.DESCRIPTOR.numberOfOrderedColumns() AS NUMCOLS \ FROM SYS.SYSSCHEMAS SCHEMAS, SYS.SYSTABLES TABS, SYS.SYSCONGLOMERATES IDX \ WHERE SCHEMAS.SCHEMAID = TABS.SCHEMAID and not IDX.ISCONSTRAINT \ AND TABS.TABLEID = IDX.TABLEID \ AND (CASE WHEN IDX.DESCRIPTOR IS NULL THEN (1=0) ELSE IDX.DESCRIPTOR.isUnique() END) \ AND ((1=1) OR ? IS NOT NULL) \ AND (SCHEMAS.SCHEMANAME LIKE ?) \ AND (TABS.TABLENAME=?) \ ORDER BY NUMCOLS # getBestRowIdentifierUniqueIndexColumns # # Return the index columns for the given indexnumber # # parameter1 - index number from SYS.SYSCONSTRAINTS # parameter2 - null ok # getBestRowIdentifierUniqueIndexColumns=\ SELECT \ CAST (java.sql.DatabaseMetaData::bestRowSession AS SMALLINT) AS SCOPE, \ COLS.COLUMNNAME AS COLUMN_NAME, \ COLS.COLUMNDATATYPE.getJDBCTypeId() AS DATA_TYPE, \ CAST (COLS.COLUMNDATATYPE.getTypeName() AS VARCHAR(128)) AS TYPE_NAME, \ COLS.COLUMNDATATYPE.getMaximumWidth() AS COLUMN_SIZE, \ CAST (NULL AS INT) AS BUFFER_LENGTH, \ CAST ((CASE WHEN (COLS.COLUMNDATATYPE.getJDBCTypeId() IN ( \ java.sql.Types::DECIMAL, java.sql.Types::NUMERIC, \ java.sql.Types::INTEGER, java.sql.Types::SMALLINT, \ java.sql.Types::TINYINT, java.sql.Types::BIGINT, \ java.sql.Types::DATE, java.sql.Types::TIME, \ java.sql.Types::TIMESTAMP)) \ THEN COLS.COLUMNDATATYPE.getPrecision() \ ELSE CAST (NULL AS SMALLINT) END) AS SMALLINT) \ AS DECIMAL_DIGITS, \ CAST (java.sql.DatabaseMetaData::bestRowNotPseudo AS SMALLINT) AS PSEUDO_COLUMN \ FROM SYS.SYSSCHEMAS SCHEMAS, SYS.SYSTABLES TABS, \ SYS.SYSCONGLOMERATES IDX, SYS.SYSCOLUMNS COLS \ WHERE TABS.TABLEID = COLS.REFERENCEID and SCHEMAS.SCHEMAID = TABS.SCHEMAID \ AND TABS.TABLEID = IDX.TABLEID and IDX.CONGLOMERATENUMBER = ? \ AND (CASE WHEN IDX.DESCRIPTOR IS NOT NULL THEN \ IDX.DESCRIPTOR.getKeyColumnPosition(COLS.COLUMNNUMBER) ELSE \ 0 END) > 0 \ AND (?<>0 or not COLS.COLUMNDATATYPE.isNullable() ) # getBestRowIdentifierAllColumns # # Return all columns as the unique identifier for this table. # Used when # # parameter1 - catalog # parameter2 - schema # parameter3 - table # parameter4 - scope # parameter5 - null ok # getBestRowIdentifierAllColumns=\ SELECT \ CAST (java.sql.DatabaseMetaData::bestRowSession AS SMALLINT) AS SCOPE, \ COLS.COLUMNNAME AS COLUMN_NAME, \ COLS.COLUMNDATATYPE.getJDBCTypeId() AS DATA_TYPE, \ CAST (COLS.COLUMNDATATYPE.getTypeName() AS VARCHAR(128)) AS TYPE_NAME, \ COLS.COLUMNDATATYPE.getMaximumWidth() AS COLUMN_SIZE, \ CAST (NULL AS INT) AS BUFFER_LENGTH, \ CAST ((CASE WHEN (COLS.COLUMNDATATYPE.getJDBCTypeId() IN ( \ java.sql.Types::DECIMAL, java.sql.Types::NUMERIC, \ java.sql.Types::INTEGER, java.sql.Types::SMALLINT, \ java.sql.Types::TINYINT, java.sql.Types::BIGINT, \ java.sql.Types::DATE, java.sql.Types::TIME, \ java.sql.Types::TIMESTAMP)) \ THEN COLS.COLUMNDATATYPE.getPrecision() \ ELSE CAST (NULL AS SMALLINT) END) AS SMALLINT) \ AS DECIMAL_DIGITS, \ CAST (java.sql.DatabaseMetaData::bestRowNotPseudo AS SMALLINT) AS PSEUDO_COLUMN \ FROM SYS.SYSSCHEMAS SCHEMAS, SYS.SYSTABLES TABS, \ SYS.SYSCOLUMNS COLS \ WHERE COLS.REFERENCEID = TABS.TABLEID \ AND TABS.SCHEMAID = SCHEMAS.SCHEMAID \ AND ((1=1) OR ? IS NOT NULL) \ AND (SCHEMAS.SCHEMANAME LIKE ?) \ AND (TABS.TABLENAME=?) \ AND ? BETWEEN 0 AND 2 \ AND (?<>0 OR NOT COLS.COLUMNDATATYPE.isNullable()) # # getSuperTypes is not supported, so we return an empty result set of the right # shape # getSuperTypes=SELECT \ CAST(NULL AS VARCHAR(128)) AS TYPE_CAT, \ CAST(NULL AS VARCHAR(128)) AS TYPE_SCHEM, \ VARCHAR('', 128) AS TYPE_NAME, \ CAST(NULL AS VARCHAR(128)) AS SUPERTYPE_CAT, \ CAST(NULL AS VARCHAR(128)) AS SUPERTYPE_SCHEM, \ VARCHAR('', 128) AS SUPERTYPE_NAME \ FROM SYSIBM.SYSDUMMY1 WHERE 1=0 WITH UR getAttributes=SELECT \ CAST(NULL AS VARCHAR(128)) AS TYPE_CAT, \ CAST(NULL AS VARCHAR(128)) AS TYPE_SCHEM, \ VARCHAR('', 128) AS TYPE_NAME, \ CAST(NULL AS VARCHAR(128)) AS ATTR_NAME, \ CAST(NULL AS INT) AS DATA_TYPE, \ CAST(NULL AS VARCHAR(128)) AS ATTR_TYPE_NAME, \ CAST(NULL AS INT) AS ATTR_SIZE, \ CAST(NULL AS INT) AS DECIMAL_DIGITS, \ CAST(NULL AS INT) AS NUM_PREC_RADIX, \ CAST(NULL AS INT) AS NULLABLE, \ CAST(NULL AS VARCHAR(128)) AS REMARKS, \ CAST(NULL AS VARCHAR(128)) AS ATTR_DEF, \ CAST(NULL AS INT) AS SQL_DATA_TYPE, \ CAST(NULL AS INT) AS SQL_DATETIME_SUB, \ CAST(NULL AS INT) AS CHAR_OCTET_LENGTH, \ CAST(NULL AS INT) AS ORDINAL_POSITION, \ CAST(NULL AS VARCHAR(128)) AS IS_NULLABLE, \ CAST(NULL AS VARCHAR(128)) AS SCOPE_CATALOG, \ CAST(NULL AS VARCHAR(128)) AS SCOPE_SCHEMA, \ CAST(NULL AS VARCHAR(128)) AS SCOPE_TABLE, \ CAST(NULL AS SMALLINT) AS SOURCE_DATA_TYPE \ FROM SYSIBM.SYSDUMMY1 WHERE 1=0 WITH UR # # getSuperTables is not supported, so we return an empty result set of the right # shape # getSuperTables=SELECT \ CAST(NULL AS VARCHAR(128)) AS TABLE_CAT, \ CAST(NULL AS VARCHAR(128)) AS TABLE_SCHEM, \ VARCHAR('', 128) AS TABLE_NAME, \ VARCHAR('', 128) AS SUPERTABLE_NAME \ FROM SYSIBM.SYSDUMMY1 WHERE 1=0 WITH UR # # getClientInfoProperties is not supported, so we return an empty result set # of the right shape # getClientInfoProperties=SELECT \ CAST(NULL AS VARCHAR(128)) AS NAME, \ CAST(NULL AS INT) AS MAX_LEN, \ CAST(NULL AS VARCHAR(128)) AS DEFAULT_VALUE, \ CAST(NULL AS VARCHAR(128)) AS DESCRIPTION \ FROM SYSIBM.SYSDUMMY1 WHERE 1=0 WITH UR # # getFunctions - From JDBC 4.0, JDK 1.6. Returns NULL for # catalog. Returns fully qualified method name as REMARKS # Param 1 catalog - dummy parameter that is not used # Param 2 schemaPattern - NULL=>any, "" => no schema (none) # Param 3 functionNamePattern - NULL=>any # Return a result set with the right shape. # getFunctions=SELECT \ CAST(NULL AS VARCHAR(128)) AS FUNCTION_CAT, \ SYS.SYSSCHEMAS.SCHEMANAME AS FUNCTION_SCHEM, \ SYS.SYSALIASES.ALIAS AS FUNCTION_NAME, \ CAST ((SYS.SYSALIASES.JAVACLASSNAME || '.' || \ SYS.SYSALIASES.ALIASINFO->getMethodName()) \ AS VARCHAR(32672)) AS REMARKS, \ CASE WHEN (SYS.SYSALIASES.ALIASINFO->isTableFunction())\ THEN CAST (2 AS SMALLINT)\ ELSE CAST (1 AS SMALLINT) \ END AS FUNCTION_TYPE, \ SYS.SYSALIASES.SPECIFICNAME AS SPECIFIC_NAME \ FROM SYS.SYSSCHEMAS, SYS.SYSALIASES \ WHERE SYS.SYSALIASES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID \ AND SYS.SYSALIASES.ALIASTYPE = 'F' \ AND ((1=1) OR ? IS NOT NULL) \ AND SYS.SYSSCHEMAS.SCHEMANAME LIKE ? \ AND SYS.SYSALIASES.ALIAS LIKE ? \ ORDER BY FUNCTION_SCHEM, FUNCTION_NAME, SPECIFIC_NAME # # getFunctionColumns - From JDBC 4.0, JDK 1.6. Returns # function parameters, including eventually, the shape # of the ResultSet if the function returns a ResultSet. # Param 1 catalog - dummy parameter that is not used # Param 2 schemaPattern - NULL=>any, "" => no schema (none) # Param 3 functionNamePattern - NULL=>any # Param 4 columnNamePattern - NULL=>any # getFunctionColumns=SELECT \ CAST(NULL AS VARCHAR(128)) AS FUNCTION_CAT, \ S.SCHEMANAME AS FUNCTION_SCHEM, \ A.ALIAS AS FUNCTION_NAME, \ V."COLUMN_NAME" AS COLUMN_NAME, \ V."COLUMN_TYPE" AS COLUMN_TYPE, \ V."DATA_TYPE" AS DATA_TYPE, \ V."TYPE_NAME" AS TYPE_NAME, \ V."PRECISION" AS PRECISION, \ V."LENGTH" AS LENGTH, \ CASE WHEN (V."DATA_TYPE" IN (java.sql.Types::DECIMAL, \ java.sql.Types::NUMERIC, java.sql.Types::INTEGER, \ java.sql.Types::SMALLINT, java.sql.Types::TINYINT, \ java.sql.Types::BIGINT, java.sql.Types::DATE, \ java.sql.Types::TIME, java.sql.Types::TIMESTAMP)) \ THEN \ V."SCALE" \ ELSE CAST (NULL AS SMALLINT) END \ AS SCALE, \ CASE WHEN (V."DATA_TYPE" IN (java.sql.Types::DECIMAL, \ java.sql.Types::NUMERIC, java.sql.Types::INTEGER, \ java.sql.Types::SMALLINT, java.sql.Types::TINYINT, \ java.sql.Types::BIGINT, java.sql.Types::DOUBLE, \ java.sql.Types::FLOAT, java.sql.Types::REAL, \ java.sql.Types::DATE, java.sql.Types::TIME, \ java.sql.Types::TIMESTAMP)) \ THEN V."RADIX" \ ELSE CAST (NULL AS SMALLINT) END AS RADIX, \ V."NULLABLE" AS NULLABLE, \ V."REMARKS" AS REMARKS, \ CASE WHEN (V."DATA_TYPE" IN ( \ java.sql.Types::CHAR, \ java.sql.Types::VARCHAR, \ java.sql.Types::BINARY, \ java.sql.Types::VARBINARY)) \ THEN V."LENGTH" \ ELSE CAST(NULL AS INT) \ END AS CHAR_OCTET_LENGTH, \ CASE WHEN (V."COLUMN_TYPE" = 5) \ THEN CAST((V."PARAMETER_ID" + 1 - V."METHOD_ID") AS INT) \ ELSE CAST((V."PARAMETER_ID" + 1) AS INT) \ END AS ORDINAL_POSITION, \ CAST(( \ CASE \ WHEN V."NULLABLE" = java.sql.DatabaseMetaData::procedureNullable \ THEN 'YES' ELSE 'NO' \ END) AS VARCHAR(128)) AS IS_NULLABLE, \ A.SPECIFICNAME AS SPECIFIC_NAME, \ V."METHOD_ID" AS METHOD_ID, \ V."PARAMETER_ID" AS PARAMETER_ID \ FROM \ SYS.SYSALIASES A, SYS.SYSSCHEMAS S, \ NEW org.apache.derby.catalog.GetProcedureColumns(A.ALIASINFO, \ A.ALIASTYPE) V \ WHERE \ A.ALIASTYPE = 'F' \ AND S.SCHEMANAME LIKE ? \ AND A.SCHEMAID = S.SCHEMAID \ AND A.ALIAS LIKE ? \ AND (V."COLUMN_NAME" LIKE ?) \ ORDER BY FUNCTION_SCHEM, FUNCTION_NAME, SPECIFIC_NAME, PARAMETER_ID, ORDINAL_POSITION # parameter 1 = thrown away. preserved here for backward compatibility with pre-10.6 clients. # parameter 2 = catalog name pattern # parameter 3 = schema name pattern (should have like comparison) # parameter 4 = type name pattern (should have like comparison) # parameter 5 = UDT type (JAVA_OBJECT, STRUCT, DISTINCT). In Derby 10.6, there is only one UDT type (JAVA_OBJECT) # # IMPORTANT NOTE: # # When we add a new kind of user defined type (e.g., STRUCT or DISTINCT) to Derby, we should # also add a ? to the in TYPES IN clause. We should also adjust the # values of the DATA_TYPE and BASE_TYPE columns accordingly. In addition, we should # modify EmbedDatabaseMetaData.getUDTs to account for that new ? # getUDTs=\ SELECT CAST (NULL AS VARCHAR(128)) AS TYPE_CAT, \ CASE WHEN (SCHEMANAME IS NULL) THEN CAST (NULL AS VARCHAR(128)) ELSE SCHEMANAME END AS TYPE_SCHEM, \ ALIAS AS TYPE_NAME, \ JAVACLASSNAME AS CLASS_NAME, \ 2000 AS DATA_TYPE, \ CAST (NULL AS VARCHAR(128)) AS REMARKS, \ CAST (NULL AS SMALLINT) AS BASE_TYPE \ FROM \ SYS.SYSALIASES, \ SYS.SYSSCHEMAS \ WHERE (ALIASTYPE='A') \ AND (SYS.SYSALIASES.SCHEMAID = SYS.SYSSCHEMAS.SCHEMAID) \ AND ((1=1) OR ? IS NOT NULL) \ AND ((1=1) OR ? IS NOT NULL) \ AND (SCHEMANAME LIKE ?) \ AND (ALIAS LIKE ?) AND (CAST (java.sql.Types::JAVA_OBJECT AS INTEGER) IN (?)) \ ORDER BY DATA_TYPE, TYPE_SCHEM, TYPE_NAME