/* Copyright (c) 2005, 2014, Oracle and/or its affiliates. All rights reserved. The MySQL Connector/J is licensed under the terms of the GPLv2 , like most MySQL Connectors. There are special exceptions to the terms and conditions of the GPLv2 as it is applied to this software, see the FLOSS License Exception . This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; version 2 of the License. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA */ package com.mysql.jdbc; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.util.List; /** * DatabaseMetaData implementation that uses INFORMATION_SCHEMA available in * MySQL-5.0 and newer. * * The majority of the queries in this code were built for Connector/OO.org by * Georg Richter (georg_at_mysql.com). */ public class DatabaseMetaDataUsingInfoSchema extends DatabaseMetaData { protected enum JDBC4FunctionConstant { // COLUMN_TYPE values FUNCTION_COLUMN_UNKNOWN, FUNCTION_COLUMN_IN, FUNCTION_COLUMN_INOUT, FUNCTION_COLUMN_OUT, FUNCTION_COLUMN_RETURN, FUNCTION_COLUMN_RESULT, // NULLABLE values FUNCTION_NO_NULLS, FUNCTION_NULLABLE, FUNCTION_NULLABLE_UNKNOWN; } private boolean hasReferentialConstraintsView; private final boolean hasParametersView; protected DatabaseMetaDataUsingInfoSchema(MySQLConnection connToSet, String databaseToSet) throws SQLException { super(connToSet, databaseToSet); this.hasReferentialConstraintsView = this.conn.versionMeetsMinimum(5, 1, 10); ResultSet rs = null; try { rs = super.getTables("INFORMATION_SCHEMA", null, "PARAMETERS", new String[0]); this.hasParametersView = rs.next(); } finally { if (rs != null) { rs.close(); } } } protected ResultSet executeMetadataQuery(java.sql.PreparedStatement pStmt) throws SQLException { ResultSet rs = pStmt.executeQuery(); ((com.mysql.jdbc.ResultSetInternalMethods) rs).setOwningStatement(null); return rs; } /** * Get a description of the access rights for a table's columns. *

* Only privileges matching the column name criteria are returned. They are * ordered by COLUMN_NAME and PRIVILEGE. *

*

* Each privilige description has the following columns: *

    *
  1. TABLE_CAT String => table catalog (may be null)
    2. *
  2. TABLE_SCHEM String => table schema (may be null)
    3. *
  3. TABLE_NAME String => table name
    4. *
  4. COLUMN_NAME String => column name
    5. *
  5. GRANTOR => grantor of access (may be null)
    6. *
  6. GRANTEE String => grantee of access
    7. *
  7. PRIVILEGE String => name of access (SELECT, INSERT, UPDATE, * REFRENCES, ...)
    8. *
  8. IS_GRANTABLE String => "YES" if grantee is permitted to * grant to others; "NO" if not; null if unknown
    9. *
*

* * @param catalog * a catalog name; "" retrieves those without a catalog * @param schema * a schema name; "" retrieves those without a schema * @param table * a table name * @param columnNamePattern * a column name pattern * @return ResultSet each row is a column privilege description * @throws SQLException * if a database access error occurs * @see #getSearchStringEscape */ public java.sql.ResultSet getColumnPrivileges(String catalog, String schema, String table, String columnNamePattern) throws SQLException { if (columnNamePattern == null) { if (this.conn.getNullNamePatternMatchesAll()) { columnNamePattern = "%"; } else { throw SQLError.createSQLException( "Column name pattern can not be NULL or empty.", SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); } } if (catalog == null) { if (this.conn.getNullCatalogMeansCurrent()) { catalog = this.database; } } String sql = "SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME," +"COLUMN_NAME, NULL AS GRANTOR, GRANTEE, PRIVILEGE_TYPE AS PRIVILEGE, IS_GRANTABLE FROM " + "INFORMATION_SCHEMA.COLUMN_PRIVILEGES WHERE " + "TABLE_SCHEMA LIKE ? AND " + "TABLE_NAME =? AND COLUMN_NAME LIKE ? ORDER BY " + "COLUMN_NAME, PRIVILEGE_TYPE"; java.sql.PreparedStatement pStmt = null; try { pStmt = prepareMetaDataSafeStatement(sql); if (catalog != null) { pStmt.setString(1, catalog); } else { pStmt.setString(1, "%"); } pStmt.setString(2, table); pStmt.setString(3, columnNamePattern); ResultSet rs = executeMetadataQuery(pStmt); ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(new Field[] { new Field("", "TABLE_CAT", Types.CHAR, 64), new Field("", "TABLE_SCHEM", Types.CHAR, 1), new Field("", "TABLE_NAME", Types.CHAR, 64), new Field("", "COLUMN_NAME", Types.CHAR, 64), new Field("", "GRANTOR", Types.CHAR, 77), new Field("", "GRANTEE", Types.CHAR, 77), new Field("", "PRIVILEGE", Types.CHAR, 64), new Field("", "IS_GRANTABLE", Types.CHAR, 3)}); return rs; } finally { if (pStmt != null) { pStmt.close(); } } } /** * Get a description of table columns available in a catalog. *

* Only column descriptions matching the catalog, schema, table and column * name criteria are returned. They are ordered by TABLE_SCHEM, TABLE_NAME * and ORDINAL_POSITION. *

*

* Each column description has the following columns: *

    *
  1. TABLE_CAT String => table catalog (may be null)
    2. *
  2. TABLE_SCHEM String => table schema (may be null)
    3. *
  3. TABLE_NAME String => table name
    4. *
  4. COLUMN_NAME String => column name
    5. *
  5. DATA_TYPE short => SQL type from java.sql.Types
    6. *
  6. TYPE_NAME String => Data source dependent type name
    7. *
  7. COLUMN_SIZE int => column size. For char or date types this * is the maximum number of characters, for numeric or decimal types this is * precision.
    8. *
  8. BUFFER_LENGTH is not used.
    9. *
  9. DECIMAL_DIGITS int => the number of fractional digits
    10. *
  10. NUM_PREC_RADIX int => Radix (typically either 10 or 2)
    11. *
  11. NULLABLE int => is NULL allowed? *
      *
    • columnNoNulls - might not allow NULL values
      • *
    • columnNullable - definitely allows NULL values
      • *
    • columnNullableUnknown - nullability unknown
      • *
    *
    12. *
  12. REMARKS String => comment describing column (may be null) *
    13. *
  13. COLUMN_DEF String => default value (may be null)
    14. *
  14. SQL_DATA_TYPE int => unused
    15. *
  15. SQL_DATETIME_SUB int => unused
    16. *
  16. CHAR_OCTET_LENGTH int => for char types the maximum number * of bytes in the column
    17. *
  17. ORDINAL_POSITION int => index of column in table (starting * at 1)
    18. *
  18. IS_NULLABLE String => "NO" means column definitely does not * allow NULL values; "YES" means the column might allow NULL values. An * empty string means nobody knows.
    19. *
*

*/ public ResultSet getColumns(String catalog, String schemaPattern, String tableName, String columnNamePattern) throws SQLException { if (columnNamePattern == null) { if (this.conn.getNullNamePatternMatchesAll()) { columnNamePattern = "%"; } else { throw SQLError.createSQLException( "Column name pattern can not be NULL or empty.", SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); } } if (catalog == null) { if (this.conn.getNullCatalogMeansCurrent()) { catalog = this.database; } } StringBuffer sqlBuf = new StringBuffer("SELECT " + "TABLE_SCHEMA AS TABLE_CAT, " + "NULL AS TABLE_SCHEM," + "TABLE_NAME," + "COLUMN_NAME,"); MysqlDefs.appendJdbcTypeMappingQuery(sqlBuf, "DATA_TYPE"); sqlBuf.append(" AS DATA_TYPE, "); if (conn.getCapitalizeTypeNames()) { sqlBuf.append("UPPER(CASE WHEN LOCATE('unsigned', COLUMN_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 AND LOCATE('set', DATA_TYPE) <> 1 AND LOCATE('enum', DATA_TYPE) <> 1 THEN CONCAT(DATA_TYPE, ' unsigned') ELSE DATA_TYPE END) AS TYPE_NAME,"); } else { sqlBuf.append("CASE WHEN LOCATE('unsigned', COLUMN_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 AND LOCATE('set', DATA_TYPE) <> 1 AND LOCATE('enum', DATA_TYPE) <> 1 THEN CONCAT(DATA_TYPE, ' unsigned') ELSE DATA_TYPE END AS TYPE_NAME,"); } sqlBuf .append("CASE WHEN LCASE(DATA_TYPE)='date' THEN 10 WHEN LCASE(DATA_TYPE)='time' THEN 8 WHEN LCASE(DATA_TYPE)='datetime' THEN 19 WHEN LCASE(DATA_TYPE)='timestamp' THEN 19 WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION WHEN CHARACTER_MAXIMUM_LENGTH > " + Integer.MAX_VALUE + " THEN " + Integer.MAX_VALUE + " ELSE CHARACTER_MAXIMUM_LENGTH END AS COLUMN_SIZE, " + MysqlIO.getMaxBuf() + " AS BUFFER_LENGTH," + "NUMERIC_SCALE AS DECIMAL_DIGITS," + "10 AS NUM_PREC_RADIX," + "CASE WHEN IS_NULLABLE='NO' THEN " + columnNoNulls + " ELSE CASE WHEN IS_NULLABLE='YES' THEN " + columnNullable + " ELSE " + columnNullableUnknown + " END END AS NULLABLE," + "COLUMN_COMMENT AS REMARKS," + "COLUMN_DEFAULT AS COLUMN_DEF," + "0 AS SQL_DATA_TYPE," + "0 AS SQL_DATETIME_SUB," + "CASE WHEN CHARACTER_OCTET_LENGTH > " + Integer.MAX_VALUE + " THEN " + Integer.MAX_VALUE + " ELSE CHARACTER_OCTET_LENGTH END AS CHAR_OCTET_LENGTH," + "ORDINAL_POSITION," + "IS_NULLABLE," + "NULL AS SCOPE_CATALOG," + "NULL AS SCOPE_SCHEMA," + "NULL AS SCOPE_TABLE," + "NULL AS SOURCE_DATA_TYPE," + "IF (EXTRA LIKE '%auto_increment%','YES','NO') AS IS_AUTOINCREMENT " + "FROM INFORMATION_SCHEMA.COLUMNS WHERE "); final boolean operatingOnInformationSchema = "information_schema".equalsIgnoreCase(catalog); if (catalog != null) { if ((operatingOnInformationSchema) || ((StringUtils.indexOfIgnoreCase(0, catalog, "%") == -1) && (StringUtils.indexOfIgnoreCase(0, catalog, "_") == -1))) { sqlBuf.append("TABLE_SCHEMA = ? AND "); } else { sqlBuf.append("TABLE_SCHEMA LIKE ? AND "); } } else { sqlBuf.append("TABLE_SCHEMA LIKE ? AND "); } if (tableName != null) { if ((StringUtils.indexOfIgnoreCase(0, tableName, "%") == -1) && (StringUtils.indexOfIgnoreCase(0, tableName, "_") == -1)) { sqlBuf.append("TABLE_NAME = ? AND "); } else { sqlBuf.append("TABLE_NAME LIKE ? AND "); } } else { sqlBuf.append("TABLE_NAME LIKE ? AND "); } if ((StringUtils.indexOfIgnoreCase(0, columnNamePattern, "%") == -1) && (StringUtils.indexOfIgnoreCase(0, columnNamePattern, "_") == -1)) { sqlBuf.append("COLUMN_NAME = ? "); } else { sqlBuf.append("COLUMN_NAME LIKE ? "); } sqlBuf.append("ORDER BY TABLE_SCHEMA, TABLE_NAME, ORDINAL_POSITION"); java.sql.PreparedStatement pStmt = null; try { pStmt = prepareMetaDataSafeStatement(sqlBuf.toString()); if (catalog != null) { pStmt.setString(1, catalog); } else { pStmt.setString(1, "%"); } pStmt.setString(2, tableName); pStmt.setString(3, columnNamePattern); ResultSet rs = executeMetadataQuery(pStmt); ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(createColumnsFields()); return rs; } finally { if (pStmt != null) { pStmt.close(); } } } /** * Get a description of the foreign key columns in the foreign key table * that reference the primary key columns of the primary key table (describe * how one table imports another's key.) This should normally return a * single foreign key/primary key pair (most tables only import a foreign * key from a table once.) They are ordered by FKTABLE_CAT, FKTABLE_SCHEM, * FKTABLE_NAME, and KEY_SEQ. *

* Each foreign key column description has the following columns: *

    *
  1. PKTABLE_CAT String => primary key table catalog (may be * null)
    2. *
  2. PKTABLE_SCHEM String => primary key table schema (may be * null)
    3. *
  3. PKTABLE_NAME String => primary key table name
    4. *
  4. PKCOLUMN_NAME String => primary key column name
    5. *
  5. FKTABLE_CAT String => foreign key table catalog (may be * null) being exported (may be null)
    6. *
  6. FKTABLE_SCHEM String => foreign key table schema (may be * null) being exported (may be null)
    7. *
  7. FKTABLE_NAME String => foreign key table name being exported *
    8. *
  8. FKCOLUMN_NAME String => foreign key column name being * exported
    9. *
  9. KEY_SEQ short => sequence number within foreign key
    10. *
  10. UPDATE_RULE short => What happens to foreign key when * primary is updated: *
      *
    • importedKeyCascade - change imported key to agree with primary key * update
      • *
    • importedKeyRestrict - do not allow update of primary key if it has * been imported
      • *
    • importedKeySetNull - change imported key to NULL if its primary key * has been updated
      • *
    *
    11. *
  11. DELETE_RULE short => What happens to the foreign key when * primary is deleted. *
      *
    • importedKeyCascade - delete rows that import a deleted key
      • *
    • importedKeyRestrict - do not allow delete of primary key if it has * been imported
      • *
    • importedKeySetNull - change imported key to NULL if its primary key * has been deleted
      • *
    *
    12. *
  12. FK_NAME String => foreign key identifier (may be null)
    13. *
  13. PK_NAME String => primary key identifier (may be null)
    14. *
*

* * @param primaryCatalog * a catalog name; "" retrieves those without a catalog * @param primarySchema * a schema name pattern; "" retrieves those without a schema * @param primaryTable * a table name * @param foreignCatalog * a catalog name; "" retrieves those without a catalog * @param foreignSchema * a schema name pattern; "" retrieves those without a schema * @param foreignTable * a table name * @return ResultSet each row is a foreign key column description * @throws SQLException * if a database access error occurs */ public java.sql.ResultSet getCrossReference(String primaryCatalog, String primarySchema, String primaryTable, String foreignCatalog, String foreignSchema, String foreignTable) throws SQLException { if (primaryTable == null) { throw SQLError.createSQLException("Table not specified.", SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); } if (primaryCatalog == null) { if (this.conn.getNullCatalogMeansCurrent()) { primaryCatalog = this.database; } } if (foreignCatalog == null) { if (this.conn.getNullCatalogMeansCurrent()) { foreignCatalog = this.database; } } String sql = "SELECT " + "A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT," + "NULL AS PKTABLE_SCHEM," + "A.REFERENCED_TABLE_NAME AS PKTABLE_NAME," + "A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME," + "A.TABLE_SCHEMA AS FKTABLE_CAT," + "NULL AS FKTABLE_SCHEM," + "A.TABLE_NAME AS FKTABLE_NAME, " + "A.COLUMN_NAME AS FKCOLUMN_NAME, " + "A.ORDINAL_POSITION AS KEY_SEQ," + generateUpdateRuleClause() + " AS UPDATE_RULE," + generateDeleteRuleClause() + " AS DELETE_RULE," + "A.CONSTRAINT_NAME AS FK_NAME," + "(SELECT CONSTRAINT_NAME FROM" + " INFORMATION_SCHEMA.TABLE_CONSTRAINTS" + " WHERE TABLE_SCHEMA = A.REFERENCED_TABLE_SCHEMA AND" + " TABLE_NAME = A.REFERENCED_TABLE_NAME AND" + " CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1)" + " AS PK_NAME," + importedKeyNotDeferrable + " AS DEFERRABILITY " + "FROM " + "INFORMATION_SCHEMA.KEY_COLUMN_USAGE A JOIN " + "INFORMATION_SCHEMA.TABLE_CONSTRAINTS B " + "USING (TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME) " + generateOptionalRefContraintsJoin() + "WHERE " + "B.CONSTRAINT_TYPE = 'FOREIGN KEY' " + "AND A.REFERENCED_TABLE_SCHEMA LIKE ? AND A.REFERENCED_TABLE_NAME=? " + "AND A.TABLE_SCHEMA LIKE ? AND A.TABLE_NAME=? " + "ORDER BY " + "A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION"; java.sql.PreparedStatement pStmt = null; try { pStmt = prepareMetaDataSafeStatement(sql); if (primaryCatalog != null) { pStmt.setString(1, primaryCatalog); } else { pStmt.setString(1, "%"); } pStmt.setString(2, primaryTable); if (foreignCatalog != null) { pStmt.setString(3, foreignCatalog); } else { pStmt.setString(3, "%"); } pStmt.setString(4, foreignTable); ResultSet rs = executeMetadataQuery(pStmt); ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(createFkMetadataFields()); return rs; } finally { if (pStmt != null) { pStmt.close(); } } } /** * Get a description of a foreign key columns that reference a table's * primary key columns (the foreign keys exported by a table). They are * ordered by FKTABLE_CAT, FKTABLE_SCHEM, FKTABLE_NAME, and KEY_SEQ. *

* Each foreign key column description has the following columns: *

    *
  1. PKTABLE_CAT String => primary key table catalog (may be * null)
    2. *
  2. PKTABLE_SCHEM String => primary key table schema (may be * null)
    3. *
  3. PKTABLE_NAME String => primary key table name
    4. *
  4. PKCOLUMN_NAME String => primary key column name
    5. *
  5. FKTABLE_CAT String => foreign key table catalog (may be * null) being exported (may be null)
    6. *
  6. FKTABLE_SCHEM String => foreign key table schema (may be * null) being exported (may be null)
    7. *
  7. FKTABLE_NAME String => foreign key table name being exported *
    8. *
  8. FKCOLUMN_NAME String => foreign key column name being * exported
    9. *
  9. KEY_SEQ short => sequence number within foreign key
    10. *
  10. UPDATE_RULE short => What happens to foreign key when * primary is updated: *
      *
    • importedKeyCascade - change imported key to agree with primary key * update
      • *
    • importedKeyRestrict - do not allow update of primary key if it has * been imported
      • *
    • importedKeySetNull - change imported key to NULL if its primary key * has been updated
      • *
    *
    11. *
  11. DELETE_RULE short => What happens to the foreign key when * primary is deleted. *
      *
    • importedKeyCascade - delete rows that import a deleted key
      • *
    • importedKeyRestrict - do not allow delete of primary key if it has * been imported
      • *
    • importedKeySetNull - change imported key to NULL if its primary key * has been deleted
      • *
    *
    12. *
  12. FK_NAME String => foreign key identifier (may be null)
    13. *
  13. PK_NAME String => primary key identifier (may be null)
    14. *
*

* * @param catalog * a catalog name; "" retrieves those without a catalog * @param schema * a schema name pattern; "" retrieves those without a schema * @param table * a table name * @return ResultSet each row is a foreign key column description * @throws SQLException * if a database access error occurs * @see #getImportedKeys */ public java.sql.ResultSet getExportedKeys(String catalog, String schema, String table) throws SQLException { // TODO: Can't determine actions using INFORMATION_SCHEMA yet... if (table == null) { throw SQLError.createSQLException("Table not specified.", SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); } if (catalog == null) { if (this.conn.getNullCatalogMeansCurrent()) { catalog = this.database; } } //CASCADE, SET NULL, SET DEFAULT, RESTRICT, NO ACTION String sql = "SELECT " + "A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT," + "NULL AS PKTABLE_SCHEM," + "A.REFERENCED_TABLE_NAME AS PKTABLE_NAME, " + "A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME, " + "A.TABLE_SCHEMA AS FKTABLE_CAT," + "NULL AS FKTABLE_SCHEM," + "A.TABLE_NAME AS FKTABLE_NAME," + "A.COLUMN_NAME AS FKCOLUMN_NAME, " + "A.ORDINAL_POSITION AS KEY_SEQ," + generateUpdateRuleClause() + " AS UPDATE_RULE," + generateDeleteRuleClause() + " AS DELETE_RULE," + "A.CONSTRAINT_NAME AS FK_NAME," + "(SELECT CONSTRAINT_NAME FROM" + " INFORMATION_SCHEMA.TABLE_CONSTRAINTS" + " WHERE TABLE_SCHEMA = A.REFERENCED_TABLE_SCHEMA AND" + " TABLE_NAME = A.REFERENCED_TABLE_NAME AND" + " CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1)" + " AS PK_NAME," + importedKeyNotDeferrable + " AS DEFERRABILITY " + "FROM " + "INFORMATION_SCHEMA.KEY_COLUMN_USAGE A JOIN " + "INFORMATION_SCHEMA.TABLE_CONSTRAINTS B " + "USING (TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME) " + generateOptionalRefContraintsJoin() + "WHERE " + "B.CONSTRAINT_TYPE = 'FOREIGN KEY' " + "AND A.REFERENCED_TABLE_SCHEMA LIKE ? AND A.REFERENCED_TABLE_NAME=? " + "ORDER BY A.TABLE_SCHEMA, A.TABLE_NAME, A.ORDINAL_POSITION"; java.sql.PreparedStatement pStmt = null; try { pStmt = prepareMetaDataSafeStatement(sql); if (catalog != null) { pStmt.setString(1, catalog); } else { pStmt.setString(1, "%"); } pStmt.setString(2, table); ResultSet rs = executeMetadataQuery(pStmt); ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(createFkMetadataFields()); return rs; } finally { if (pStmt != null) { pStmt.close(); } } } private String generateOptionalRefContraintsJoin() { return ((this.hasReferentialConstraintsView) ? "JOIN " + "INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R " + "ON (R.CONSTRAINT_NAME = B.CONSTRAINT_NAME " + "AND R.TABLE_NAME = B.TABLE_NAME AND " + "R.CONSTRAINT_SCHEMA = B.TABLE_SCHEMA) " : ""); } private String generateDeleteRuleClause() { return ((this.hasReferentialConstraintsView) ? "CASE WHEN R.DELETE_RULE='CASCADE' THEN " + String.valueOf(importedKeyCascade) + " WHEN R.DELETE_RULE='SET NULL' THEN " + String.valueOf(importedKeySetNull) + " WHEN R.DELETE_RULE='SET DEFAULT' THEN " + String.valueOf(importedKeySetDefault) + " WHEN R.DELETE_RULE='RESTRICT' THEN " + String.valueOf(importedKeyRestrict) + " WHEN R.DELETE_RULE='NO ACTION' THEN " + String.valueOf(importedKeyNoAction) + " ELSE " + String.valueOf(importedKeyNoAction) + " END " : String.valueOf(importedKeyRestrict)); } private String generateUpdateRuleClause() { return ((this.hasReferentialConstraintsView) ? "CASE WHEN R.UPDATE_RULE='CASCADE' THEN " + String.valueOf(importedKeyCascade) + " WHEN R.UPDATE_RULE='SET NULL' THEN " + String.valueOf(importedKeySetNull) + " WHEN R.UPDATE_RULE='SET DEFAULT' THEN " + String.valueOf(importedKeySetDefault) + " WHEN R.UPDATE_RULE='RESTRICT' THEN " + String.valueOf(importedKeyRestrict) + " WHEN R.UPDATE_RULE='NO ACTION' THEN " + String.valueOf(importedKeyNoAction) + " ELSE " + String.valueOf(importedKeyNoAction) + " END " : String.valueOf(importedKeyRestrict)); } /** * Get a description of the primary key columns that are referenced by a * table's foreign key columns (the primary keys imported by a table). They * are ordered by PKTABLE_CAT, PKTABLE_SCHEM, PKTABLE_NAME, and KEY_SEQ. *

* Each primary key column description has the following columns: *

    *
  1. PKTABLE_CAT String => primary key table catalog being * imported (may be null)
    2. *
  2. PKTABLE_SCHEM String => primary key table schema being * imported (may be null)
    3. *
  3. PKTABLE_NAME String => primary key table name being imported *
    4. *
  4. PKCOLUMN_NAME String => primary key column name being * imported
    5. *
  5. FKTABLE_CAT String => foreign key table catalog (may be * null)
    6. *
  6. FKTABLE_SCHEM String => foreign key table schema (may be * null)
    7. *
  7. FKTABLE_NAME String => foreign key table name
    8. *
  8. FKCOLUMN_NAME String => foreign key column name
    9. *
  9. KEY_SEQ short => sequence number within foreign key
    10. *
  10. UPDATE_RULE short => What happens to foreign key when * primary is updated: *
      *
    • importedKeyCascade - change imported key to agree with primary key * update
      • *
    • importedKeyRestrict - do not allow update of primary key if it has * been imported
      • *
    • importedKeySetNull - change imported key to NULL if its primary key * has been updated
      • *
    *
    11. *
  11. DELETE_RULE short => What happens to the foreign key when * primary is deleted. *
      *
    • importedKeyCascade - delete rows that import a deleted key
      • *
    • importedKeyRestrict - do not allow delete of primary key if it has * been imported
      • *
    • importedKeySetNull - change imported key to NULL if its primary key * has been deleted
      • *
    *
    12. *
  12. FK_NAME String => foreign key name (may be null)
    13. *
  13. PK_NAME String => primary key name (may be null)
    14. *
*

* * @param catalog * a catalog name; "" retrieves those without a catalog * @param schema * a schema name pattern; "" retrieves those without a schema * @param table * a table name * @return ResultSet each row is a primary key column description * @throws SQLException * if a database access error occurs * @see #getExportedKeys */ public java.sql.ResultSet getImportedKeys(String catalog, String schema, String table) throws SQLException { if (table == null) { throw SQLError.createSQLException("Table not specified.", SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); } if (catalog == null) { if (this.conn.getNullCatalogMeansCurrent()) { catalog = this.database; } } String sql = "SELECT " + "A.REFERENCED_TABLE_SCHEMA AS PKTABLE_CAT," + "NULL AS PKTABLE_SCHEM," + "A.REFERENCED_TABLE_NAME AS PKTABLE_NAME," + "A.REFERENCED_COLUMN_NAME AS PKCOLUMN_NAME," + "A.TABLE_SCHEMA AS FKTABLE_CAT," + "NULL AS FKTABLE_SCHEM," + "A.TABLE_NAME AS FKTABLE_NAME, " + "A.COLUMN_NAME AS FKCOLUMN_NAME, " + "A.ORDINAL_POSITION AS KEY_SEQ," + generateUpdateRuleClause() + " AS UPDATE_RULE," + generateDeleteRuleClause() + " AS DELETE_RULE," + "A.CONSTRAINT_NAME AS FK_NAME," + "(SELECT CONSTRAINT_NAME FROM" + " INFORMATION_SCHEMA.TABLE_CONSTRAINTS" + " WHERE TABLE_SCHEMA = A.REFERENCED_TABLE_SCHEMA AND" + " TABLE_NAME = A.REFERENCED_TABLE_NAME AND" + " CONSTRAINT_TYPE IN ('UNIQUE','PRIMARY KEY') LIMIT 1)" + " AS PK_NAME," + importedKeyNotDeferrable + " AS DEFERRABILITY " + "FROM " + "INFORMATION_SCHEMA.KEY_COLUMN_USAGE A " + "JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS B USING " + "(CONSTRAINT_NAME, TABLE_NAME) " + generateOptionalRefContraintsJoin() + "WHERE " + "B.CONSTRAINT_TYPE = 'FOREIGN KEY' " + "AND A.TABLE_SCHEMA LIKE ? " + "AND A.TABLE_NAME=? " + "AND A.REFERENCED_TABLE_SCHEMA IS NOT NULL " + "ORDER BY " + "A.REFERENCED_TABLE_SCHEMA, A.REFERENCED_TABLE_NAME, " + "A.ORDINAL_POSITION"; java.sql.PreparedStatement pStmt = null; try { pStmt = prepareMetaDataSafeStatement(sql); if (catalog != null) { pStmt.setString(1, catalog); } else { pStmt.setString(1, "%"); } pStmt.setString(2, table); ResultSet rs = executeMetadataQuery(pStmt); ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(createFkMetadataFields()); return rs; } finally { if (pStmt != null) { pStmt.close(); } } } /** * Get a description of a table's indices and statistics. They are ordered * by NON_UNIQUE, TYPE, INDEX_NAME, and ORDINAL_POSITION. *

* Each index column description has the following columns: *

    *
  1. TABLE_CAT String => table catalog (may be null)
    2. *
  2. TABLE_SCHEM String => table schema (may be null)
    3. *
  3. TABLE_NAME String => table name
    4. *
  4. NON_UNIQUE boolean => Can index values be non-unique? false * when TYPE is tableIndexStatistic
    5. *
  5. INDEX_QUALIFIER String => index catalog (may be null); null * when TYPE is tableIndexStatistic
    6. *
  6. INDEX_NAME String => index name; null when TYPE is * tableIndexStatistic
    7. *
  7. TYPE short => index type: *
      *
    • tableIndexStatistic - this identifies table statistics that are * returned in conjuction with a table's index descriptions
      • *
    • tableIndexClustered - this is a clustered index
      • *
    • tableIndexHashed - this is a hashed index
      • *
    • tableIndexOther - this is some other style of index
      • *
    *
    8. *
  8. ORDINAL_POSITION short => column sequence number within * index; zero when TYPE is tableIndexStatistic
    9. *
  9. COLUMN_NAME String => column name; null when TYPE is * tableIndexStatistic
    10. *
  10. ASC_OR_DESC String => column sort sequence, "A" => * ascending, "D" => descending, may be null if sort sequence is not * supported; null when TYPE is tableIndexStatistic
    11. *
  11. CARDINALITY int => When TYPE is tableIndexStatisic then this * is the number of rows in the table; otherwise it is the number of unique * values in the index.
    12. *
  12. PAGES int => When TYPE is tableIndexStatisic then this is * the number of pages used for the table, otherwise it is the number of * pages used for the current index.
    13. *
  13. FILTER_CONDITION String => Filter condition, if any. (may be * null)
    14. *
*

* * @param catalog * a catalog name; "" retrieves those without a catalog * @param schema * a schema name pattern; "" retrieves those without a schema * @param table * a table name * @param unique * when true, return only indices for unique values; when false, * return indices regardless of whether unique or not * @param approximate * when true, result is allowed to reflect approximate or out of * data values; when false, results are requested to be accurate * @return ResultSet each row is an index column description * @throws SQLException * DOCUMENT ME! */ public ResultSet getIndexInfo(String catalog, String schema, String table, boolean unique, boolean approximate) throws SQLException { StringBuffer sqlBuf = new StringBuffer("SELECT " + "TABLE_SCHEMA AS TABLE_CAT, " + "NULL AS TABLE_SCHEM," + "TABLE_NAME," + "NON_UNIQUE," + "TABLE_SCHEMA AS INDEX_QUALIFIER," + "INDEX_NAME," + tableIndexOther + " AS TYPE," + "SEQ_IN_INDEX AS ORDINAL_POSITION," + "COLUMN_NAME," + "COLLATION AS ASC_OR_DESC," + "CARDINALITY," + "NULL AS PAGES," + "NULL AS FILTER_CONDITION " + "FROM INFORMATION_SCHEMA.STATISTICS WHERE " + "TABLE_SCHEMA LIKE ? AND " + "TABLE_NAME LIKE ?"); if (unique) { sqlBuf.append(" AND NON_UNIQUE=0 "); } sqlBuf.append("ORDER BY NON_UNIQUE, INDEX_NAME, SEQ_IN_INDEX"); java.sql.PreparedStatement pStmt = null; try { if (catalog == null) { if (this.conn.getNullCatalogMeansCurrent()) { catalog = this.database; } } pStmt = prepareMetaDataSafeStatement(sqlBuf.toString()); if (catalog != null) { pStmt.setString(1, catalog); } else { pStmt.setString(1, "%"); } pStmt.setString(2, table); ResultSet rs = executeMetadataQuery(pStmt); ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(createIndexInfoFields()); return rs; } finally { if (pStmt != null) { pStmt.close(); } } } /** * Get a description of a table's primary key columns. They are ordered by * COLUMN_NAME. *

* Each column description has the following columns: *

    *
  1. TABLE_CAT String => table catalog (may be null)
    2. *
  2. TABLE_SCHEM String => table schema (may be null)
    3. *
  3. TABLE_NAME String => table name
    4. *
  4. COLUMN_NAME String => column name
    5. *
  5. KEY_SEQ short => sequence number within primary key
    6. *
  6. PK_NAME String => primary key name (may be null)
    7. *
*

* * @param catalog * a catalog name; "" retrieves those without a catalog * @param schema * a schema name pattern; "" retrieves those without a schema * @param table * a table name * @return ResultSet each row is a primary key column description * @throws SQLException * DOCUMENT ME! */ public java.sql.ResultSet getPrimaryKeys(String catalog, String schema, String table) throws SQLException { if (catalog == null) { if (this.conn.getNullCatalogMeansCurrent()) { catalog = this.database; } } if (table == null) { throw SQLError.createSQLException("Table not specified.", SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); } String sql = "SELECT TABLE_SCHEMA AS TABLE_CAT, NULL AS TABLE_SCHEM, TABLE_NAME, " + "COLUMN_NAME, SEQ_IN_INDEX AS KEY_SEQ, 'PRIMARY' AS PK_NAME FROM INFORMATION_SCHEMA.STATISTICS " + "WHERE TABLE_SCHEMA LIKE ? AND TABLE_NAME LIKE ? AND " + "INDEX_NAME='PRIMARY' ORDER BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX"; java.sql.PreparedStatement pStmt = null; try { pStmt = prepareMetaDataSafeStatement(sql); if (catalog != null) { pStmt.setString(1, catalog); } else { pStmt.setString(1, "%"); } pStmt.setString(2, table); ResultSet rs = executeMetadataQuery(pStmt); ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(new Field[] { new Field("", "TABLE_CAT", Types.CHAR, 255), new Field("", "TABLE_SCHEM", Types.CHAR, 0), new Field("", "TABLE_NAME", Types.CHAR, 255), new Field("", "COLUMN_NAME", Types.CHAR, 32), new Field("", "KEY_SEQ", Types.SMALLINT, 5), new Field("", "PK_NAME", Types.CHAR, 32) }); return rs; } finally { if (pStmt != null) { pStmt.close(); } } } /** * Get a description of stored procedures available in a catalog. *

* Only procedure descriptions matching the schema and procedure name * criteria are returned. They are ordered by PROCEDURE_SCHEM, and * PROCEDURE_NAME. *

*

* Each procedure description has the the following columns: *

    *
  1. PROCEDURE_CAT String => procedure catalog (may be null) *
    2. *
  2. PROCEDURE_SCHEM String => procedure schema (may be null) *
    3. *
  3. PROCEDURE_NAME String => procedure name
    4. *
  4. reserved for future use
    5. *
  5. reserved for future use
    6. *
  6. reserved for future use
    7. *
  7. REMARKS String => explanatory comment on the procedure
    8. *
  8. PROCEDURE_TYPE short => kind of procedure: *
      *
    • procedureResultUnknown - May return a result
      • *
    • procedureNoResult - Does not return a result
      • *
    • procedureReturnsResult - Returns a result
      • *
    *
    9. *
*

* * @param catalog * a catalog name; "" retrieves those without a catalog * @param schemaPattern * a schema name pattern; "" retrieves those without a schema * @param procedureNamePattern * a procedure name pattern * @return ResultSet each row is a procedure description * @throws SQLException * if a database access error occurs * @see #getSearchStringEscape */ public ResultSet getProcedures(String catalog, String schemaPattern, String procedureNamePattern) throws SQLException { if ((procedureNamePattern == null) || (procedureNamePattern.length() == 0)) { if (this.conn.getNullNamePatternMatchesAll()) { procedureNamePattern = "%"; } else { throw SQLError.createSQLException( "Procedure name pattern can not be NULL or empty.", SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); } } String db = null; if (catalog == null) { if (this.conn.getNullCatalogMeansCurrent()) { db = this.database; } } else { db = catalog; } String sql = "SELECT ROUTINE_SCHEMA AS PROCEDURE_CAT, " + "NULL AS PROCEDURE_SCHEM, " + "ROUTINE_NAME AS PROCEDURE_NAME, " + "NULL AS RESERVED_1, " + "NULL AS RESERVED_2, " + "NULL AS RESERVED_3, " + "ROUTINE_COMMENT AS REMARKS, " + "CASE WHEN ROUTINE_TYPE = 'PROCEDURE' THEN " + procedureNoResult + " WHEN ROUTINE_TYPE='FUNCTION' THEN " + procedureReturnsResult + " ELSE " + procedureResultUnknown + " END AS PROCEDURE_TYPE, " + "ROUTINE_NAME AS SPECIFIC_NAME " + "FROM INFORMATION_SCHEMA.ROUTINES WHERE " + getRoutineTypeConditionForGetProcedures() + "ROUTINE_SCHEMA LIKE ? AND ROUTINE_NAME LIKE ? " + "ORDER BY ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE"; java.sql.PreparedStatement pStmt = null; try { pStmt = prepareMetaDataSafeStatement(sql); if (db != null) { pStmt.setString(1, db); } else { pStmt.setString(1, "%"); } pStmt.setString(2, procedureNamePattern); ResultSet rs = executeMetadataQuery(pStmt); ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(createFieldMetadataForGetProcedures()); return rs; } finally { if (pStmt != null) { pStmt.close(); } } } /** * Returns a condition to be injected in the query that returns metadata for procedures only. Overridden by * subclasses when needed. When not empty must end with "AND ". * * @return String with the condition to be injected. */ protected String getRoutineTypeConditionForGetProcedures() { return ""; } /** * Retrieves a description of the given catalog's stored procedure parameter * and result columns. * *

Only descriptions matching the schema, procedure and * parameter name criteria are returned. They are ordered by * PROCEDURE_SCHEM and PROCEDURE_NAME. Within this, the return value, * if any, is first. Next are the parameter descriptions in call * order. The column descriptions follow in column number order. * *

Each row in the ResultSet is a parameter description or * column description with the following fields: *

    *
  1. PROCEDURE_CAT String => procedure catalog (may be null) *
  2. PROCEDURE_SCHEM String => procedure schema (may be null) *
  3. PROCEDURE_NAME String => procedure name *
  4. COLUMN_NAME String => column/parameter name *
  5. COLUMN_TYPE Short => kind of column/parameter: *
      *
    • procedureColumnUnknown - nobody knows *
    • procedureColumnIn - IN parameter *
    • procedureColumnInOut - INOUT parameter *
    • procedureColumnOut - OUT parameter *
    • procedureColumnReturn - procedure return value *
    • procedureColumnResult - result column in ResultSet *
    *
  6. DATA_TYPE int => SQL type from java.sql.Types *
  7. TYPE_NAME String => SQL type name, for a UDT type the * type name is fully qualified *
  8. PRECISION int => precision *
  9. LENGTH int => length in bytes of data *
  10. SCALE short => scale *
  11. RADIX short => radix *
  12. NULLABLE short => can it contain NULL. *
      *
    • procedureNoNulls - does not allow NULL values *
    • procedureNullable - allows NULL values *
    • procedureNullableUnknown - nullability unknown *
    *
  13. REMARKS String => comment describing parameter/column *
* *

Note: Some databases may not return the column * descriptions for a procedure. Additional columns beyond * REMARKS can be defined by the database. * * @param catalog a catalog name; must match the catalog name as it * is stored in the database; "" retrieves those without a catalog; * null means that the catalog name should not be used to narrow * the search * @param schemaPattern a schema name pattern; must match the schema name * as it is stored in the database; "" retrieves those without a schema; * null means that the schema name should not be used to narrow * the search * @param procedureNamePattern a procedure name pattern; must match the * procedure name as it is stored in the database * @param columnNamePattern a column name pattern; must match the column name * as it is stored in the database * @return ResultSet - each row describes a stored procedure parameter or * column * @exception SQLException if a database access error occurs * @see #getSearchStringEscape */ public ResultSet getProcedureColumns(String catalog, String schemaPattern, String procedureNamePattern, String columnNamePattern) throws SQLException { if (!this.hasParametersView) { return getProcedureColumnsNoISParametersView(catalog, schemaPattern, procedureNamePattern, columnNamePattern); } if ((procedureNamePattern == null) || (procedureNamePattern.length() == 0)) { if (this.conn.getNullNamePatternMatchesAll()) { procedureNamePattern = "%"; } else { throw SQLError.createSQLException( "Procedure name pattern can not be NULL or empty.", SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); } } String db = null; if (catalog == null) { if (this.conn.getNullCatalogMeansCurrent()) { db = this.database; } } else { db = catalog; } // Here's what we get from MySQL ... // SPECIFIC_CATALOG NULL // SPECIFIC_SCHEMA db17 // SPECIFIC_NAME p // ORDINAL_POSITION 1 // PARAMETER_MODE OUT // PARAMETER_NAME a // DATA_TYPE int // CHARACTER_MAXIMUM_LENGTH NULL // CHARACTER_OCTET_LENGTH NULL // CHARACTER_SET_NAME NULL // COLLATION_NAME NULL // NUMERIC_PRECISION 10 // NUMERIC_SCALE 0 // DTD_IDENTIFIER int(11) StringBuffer sqlBuf = new StringBuffer("SELECT SPECIFIC_SCHEMA AS PROCEDURE_CAT, " + "NULL AS `PROCEDURE_SCHEM`, " + "SPECIFIC_NAME AS `PROCEDURE_NAME`, " + "IFNULL(PARAMETER_NAME, '') AS `COLUMN_NAME`, " + "CASE WHEN PARAMETER_MODE = 'IN' THEN " + procedureColumnIn + " WHEN PARAMETER_MODE = 'OUT' THEN " + procedureColumnOut + " WHEN PARAMETER_MODE = 'INOUT' THEN " + procedureColumnInOut + " WHEN ORDINAL_POSITION = 0 THEN " + procedureColumnReturn + " ELSE " + procedureColumnUnknown + " END AS `COLUMN_TYPE`, "); //DATA_TYPE MysqlDefs.appendJdbcTypeMappingQuery(sqlBuf, "DATA_TYPE"); sqlBuf.append(" AS `DATA_TYPE`, "); // TYPE_NAME if (conn.getCapitalizeTypeNames()) { sqlBuf.append("UPPER(CASE WHEN LOCATE('unsigned', DATA_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 THEN CONCAT(DATA_TYPE, ' unsigned') ELSE DATA_TYPE END) AS `TYPE_NAME`,"); } else { sqlBuf.append("CASE WHEN LOCATE('unsigned', DATA_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 THEN CONCAT(DATA_TYPE, ' unsigned') ELSE DATA_TYPE END AS `TYPE_NAME`,"); } // PRECISION int => precision sqlBuf.append("NUMERIC_PRECISION AS `PRECISION`, "); // LENGTH int => length in bytes of data sqlBuf .append("CASE WHEN LCASE(DATA_TYPE)='date' THEN 10 WHEN LCASE(DATA_TYPE)='time' THEN 8 WHEN LCASE(DATA_TYPE)='datetime' THEN 19 WHEN LCASE(DATA_TYPE)='timestamp' THEN 19 WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION WHEN CHARACTER_MAXIMUM_LENGTH > " + Integer.MAX_VALUE + " THEN " + Integer.MAX_VALUE + " ELSE CHARACTER_MAXIMUM_LENGTH END AS LENGTH, "); // SCALE short => scale sqlBuf.append("NUMERIC_SCALE AS `SCALE`, "); // RADIX short => radix sqlBuf.append("10 AS RADIX,"); sqlBuf.append(procedureNullable + " AS `NULLABLE`, " + "NULL AS `REMARKS`, " + "NULL AS `COLUMN_DEF`, " + "NULL AS `SQL_DATA_TYPE`, " + "NULL AS `SQL_DATETIME_SUB`, " + "CHARACTER_OCTET_LENGTH AS `CHAR_OCTET_LENGTH`, " + "ORDINAL_POSITION, " + "'YES' AS `IS_NULLABLE`, " + "SPECIFIC_NAME " + "FROM INFORMATION_SCHEMA.PARAMETERS WHERE " + getRoutineTypeConditionForGetProcedureColumns() + "SPECIFIC_SCHEMA LIKE ? AND SPECIFIC_NAME LIKE ? AND (PARAMETER_NAME LIKE ? OR PARAMETER_NAME IS NULL) " + "ORDER BY SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_TYPE, ORDINAL_POSITION"); java.sql.PreparedStatement pStmt = null; try { pStmt = prepareMetaDataSafeStatement(sqlBuf.toString()); if (db != null) { pStmt.setString(1, db); } else { pStmt.setString(1, "%"); } pStmt.setString(2, procedureNamePattern); pStmt.setString(3, columnNamePattern); ResultSet rs = executeMetadataQuery(pStmt); ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(createProcedureColumnsFields()); return rs; } finally { if (pStmt != null) { pStmt.close(); } } } /** * Redirects to another implementation of #getProcedureColumns. Subclasses may need to override this method. * * @see getProcedureColumns */ protected ResultSet getProcedureColumnsNoISParametersView(String catalog, String schemaPattern, String procedureNamePattern, String columnNamePattern) throws SQLException { return super.getProcedureColumns(catalog, schemaPattern, procedureNamePattern, columnNamePattern); } /** * Returns a condition to be injected in the query that returns metadata for procedure columns only. Overridden by * subclasses when needed. When not empty must end with "AND ". * * @return String with the condition to be injected. */ protected String getRoutineTypeConditionForGetProcedureColumns() { return ""; } /** * Get a description of tables available in a catalog. *

* Only table descriptions matching the catalog, schema, table name and type * criteria are returned. They are ordered by TABLE_TYPE, TABLE_SCHEM and * TABLE_NAME. *

*

* Each table description has the following columns: *

    *
  1. TABLE_CAT String => table catalog (may be null)
    2. *
  2. TABLE_SCHEM String => table schema (may be null)
    3. *
  3. TABLE_NAME String => table name
    4. *
  4. TABLE_TYPE String => table type. Typical types are "TABLE", * "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", * "SYNONYM".
    5. *
  5. REMARKS String => explanatory comment on the table
    6. *
*

*

* Note: Some databases may not return information for all tables. *

* * @param catalog * a catalog name; "" retrieves those without a catalog * @param schemaPattern * a schema name pattern; "" retrieves those without a schema * @param tableNamePattern * a table name pattern * @param types * a list of table types to include; null returns all types * @return ResultSet each row is a table description * @throws SQLException * DOCUMENT ME! * @see #getSearchStringEscape */ public ResultSet getTables(String catalog, String schemaPattern, String tableNamePattern, String[] types) throws SQLException { if (catalog == null) { if (this.conn.getNullCatalogMeansCurrent()) { catalog = this.database; } } if (tableNamePattern == null) { if (this.conn.getNullNamePatternMatchesAll()) { tableNamePattern = "%"; } else { throw SQLError.createSQLException( "Table name pattern can not be NULL or empty.", SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); } } final String tableNamePat; String tmpCat = ""; if ((catalog == null) || (catalog.length() == 0)) { if (this.conn.getNullCatalogMeansCurrent()) { tmpCat = this.database; } } else { tmpCat = catalog; } List parseList = StringUtils.splitDBdotName(tableNamePattern, tmpCat, quotedId , conn.isNoBackslashEscapesSet()); //There *should* be 2 rows, if any. if (parseList.size() == 2) { tableNamePat = parseList.get(1); } else { tableNamePat = tableNamePattern; } java.sql.PreparedStatement pStmt = null; String sql = "SELECT TABLE_SCHEMA AS TABLE_CAT, " + "NULL AS TABLE_SCHEM, TABLE_NAME, " + "CASE WHEN TABLE_TYPE='BASE TABLE' THEN CASE WHEN TABLE_SCHEMA = 'mysql' OR TABLE_SCHEMA = 'performance_schema' THEN 'SYSTEM TABLE' ELSE 'TABLE' END " + "WHEN TABLE_TYPE='TEMPORARY' THEN 'LOCAL_TEMPORARY' ELSE TABLE_TYPE END AS TABLE_TYPE, " + "TABLE_COMMENT AS REMARKS, NULL AS TYPE_CAT, NULL AS TYPE_SCHEM, NULL AS TYPE_NAME, NULL AS SELF_REFERENCING_COL_NAME, NULL AS REF_GENERATION " + "FROM INFORMATION_SCHEMA.TABLES WHERE "; final boolean operatingOnInformationSchema = "information_schema".equalsIgnoreCase(catalog); if (catalog != null) { if ((operatingOnInformationSchema) || ((StringUtils.indexOfIgnoreCase(0, catalog, "%") == -1) && (StringUtils.indexOfIgnoreCase(0, catalog, "_") == -1))) { sql += "TABLE_SCHEMA = ? "; } else { sql += "TABLE_SCHEMA LIKE ? "; } } else { sql += "TABLE_SCHEMA LIKE ? "; } if (tableNamePat != null) { if ((StringUtils.indexOfIgnoreCase(0, tableNamePat, "%") == -1) && (StringUtils.indexOfIgnoreCase(0, tableNamePat, "_") == -1)) { sql += "AND TABLE_NAME = ? "; } else { sql += "AND TABLE_NAME LIKE ? "; } } else { sql += "AND TABLE_NAME LIKE ? "; } sql = sql + "HAVING TABLE_TYPE IN (?,?,?,?,?) "; sql = sql + "ORDER BY TABLE_TYPE, TABLE_SCHEMA, TABLE_NAME"; try { pStmt = prepareMetaDataSafeStatement(sql); if (catalog != null) { pStmt.setString(1, catalog); } else { pStmt.setString(1, "%"); } pStmt.setString(2, tableNamePat); // This overloading of IN (...) allows us to cache this // prepared statement if (types == null || types.length == 0) { TableType[] tableTypes = TableType.values(); for (int i = 0; i < 5; i++) { pStmt.setString(3 + i, tableTypes[i].getName()); } } else { for (int i = 0; i < 5; i++) { pStmt.setNull(3 + i, Types.VARCHAR); } int idx = 3; for (int i = 0; i < types.length; i++) { TableType tableType = TableType.getTableTypeEqualTo(types[i]); if (tableType != TableType.UNKNOWN) { pStmt.setString(idx++, tableType.getName()); } } } ResultSet rs = executeMetadataQuery(pStmt); ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(createTablesFields()); return rs; } finally { if (pStmt != null) { pStmt.close(); } } } public boolean gethasParametersView() { return this.hasParametersView; } @Override public ResultSet getVersionColumns(String catalog, String schema, String table) throws SQLException { if (catalog == null) { if (this.conn.getNullCatalogMeansCurrent()) { catalog = this.database; } } if (table == null) { throw SQLError.createSQLException("Table not specified.", SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); } StringBuffer sqlBuf = new StringBuffer("SELECT NULL AS SCOPE, COLUMN_NAME, "); MysqlDefs.appendJdbcTypeMappingQuery(sqlBuf, "DATA_TYPE"); sqlBuf.append(" AS DATA_TYPE, "); sqlBuf.append("COLUMN_TYPE AS TYPE_NAME, "); sqlBuf.append("CASE WHEN LCASE(DATA_TYPE)='date' THEN 10 WHEN LCASE(DATA_TYPE)='time' THEN 8 " + "WHEN LCASE(DATA_TYPE)='datetime' THEN 19 WHEN LCASE(DATA_TYPE)='timestamp' THEN 19 " + "WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION WHEN CHARACTER_MAXIMUM_LENGTH > " + Integer.MAX_VALUE + " THEN " + Integer.MAX_VALUE + " ELSE CHARACTER_MAXIMUM_LENGTH END AS COLUMN_SIZE, "); sqlBuf.append( MysqlIO.getMaxBuf() + " AS BUFFER_LENGTH," + "NUMERIC_SCALE AS DECIMAL_DIGITS, " + Integer.toString(java.sql.DatabaseMetaData.versionColumnNotPseudo) + " AS PSEUDO_COLUMN " + "FROM INFORMATION_SCHEMA.COLUMNS " + "WHERE TABLE_SCHEMA LIKE ? AND TABLE_NAME LIKE ?" + " AND EXTRA LIKE '%on update CURRENT_TIMESTAMP%'"); java.sql.PreparedStatement pStmt = null; try { pStmt = prepareMetaDataSafeStatement(sqlBuf.toString()); if (catalog != null) { pStmt.setString(1, catalog); } else { pStmt.setString(1, "%"); } pStmt.setString(2, table); ResultSet rs = executeMetadataQuery(pStmt); ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(new Field[] { new Field("", "SCOPE", Types.SMALLINT, 5), new Field("", "COLUMN_NAME", Types.CHAR, 32), new Field("", "DATA_TYPE", Types.INTEGER, 5), new Field("", "TYPE_NAME", Types.CHAR, 16), new Field("", "COLUMN_SIZE", Types.INTEGER, 16), new Field("", "BUFFER_LENGTH", Types.INTEGER, 16), new Field("", "DECIMAL_DIGITS", Types.SMALLINT, 16), new Field("", "PSEUDO_COLUMN", Types.SMALLINT, 5) }); return rs; } finally { if (pStmt != null) { pStmt.close(); } } } // // JDBC-4.0 functions that aren't reliant on Java6 // /** * Retrieves a description of the given catalog's system or user * function parameters and return type. * *

Only descriptions matching the schema, function and * parameter name criteria are returned. They are ordered by * FUNCTION_CAT, FUNCTION_SCHEM, * FUNCTION_NAME and * SPECIFIC_ NAME. Within this, the return value, * if any, is first. Next are the parameter descriptions in call * order. The column descriptions follow in column number order. * *

Each row in the ResultSet * is a parameter description, column description or * return type description with the following fields: *

    *
  1. FUNCTION_CAT String => function catalog (may be null) *
  2. FUNCTION_SCHEM String => function schema (may be null) *
  3. FUNCTION_NAME String => function name. This is the name * used to invoke the function *
  4. COLUMN_NAME String => column/parameter name *
  5. COLUMN_TYPE Short => kind of column/parameter: *
      *
    • functionColumnUnknown - nobody knows *
    • functionColumnIn - IN parameter *
    • functionColumnInOut - INOUT parameter *
    • functionColumnOut - OUT parameter *
    • functionColumnReturn - function return value *
    • functionColumnResult - Indicates that the parameter or column * is a column in the ResultSet *
    *
  6. DATA_TYPE int => SQL type from java.sql.Types *
  7. TYPE_NAME String => SQL type name, for a UDT type the * type name is fully qualified *
  8. PRECISION int => precision *
  9. LENGTH int => length in bytes of data *
  10. SCALE short => scale - null is returned for data types where * SCALE is not applicable. *
  11. RADIX short => radix *
  12. NULLABLE short => can it contain NULL. *
      *
    • functionNoNulls - does not allow NULL values *
    • functionNullable - allows NULL values *
    • functionNullableUnknown - nullability unknown *
    *
  13. REMARKS String => comment describing column/parameter *
  14. CHAR_OCTET_LENGTH int => the maximum length of binary * and character based parameters or columns. For any other datatype the returned value * is a NULL *
  15. ORDINAL_POSITION int => the ordinal position, starting * from 1, for the input and output parameters. A value of 0 * is returned if this row describes the function's return value. * For result set columns, it is the * ordinal position of the column in the result set starting from 1. *
  16. IS_NULLABLE String => ISO rules are used to determine * the nullability for a parameter or column. *
      *
    • YES --- if the parameter or column can include NULLs *
    • NO --- if the parameter or column cannot include NULLs *
    • empty string --- if the nullability for the * parameter or column is unknown *
    *
  17. SPECIFIC_NAME String => the name which uniquely identifies * this function within its schema. This is a user specified, or DBMS * generated, name that may be different then the FUNCTION_NAME * for example with overload functions *
* *

The PRECISION column represents the specified column size for the given * parameter or column. * For numeric data, this is the maximum precision. For character data, this is the length in characters. * For datetime datatypes, this is the length in characters of the String representation (assuming the * maximum allowed precision of the fractional seconds component). For binary data, this is the length in bytes. For the ROWID datatype, * this is the length in bytes. Null is returned for data types where the * column size is not applicable. * @param catalog a catalog name; must match the catalog name as it * is stored in the database; "" retrieves those without a catalog; * null means that the catalog name should not be used to narrow * the search * @param schemaPattern a schema name pattern; must match the schema name * as it is stored in the database; "" retrieves those without a schema; * null means that the schema name should not be used to narrow * the search * @param functionNamePattern a procedure name pattern; must match the * function name as it is stored in the database * @param columnNamePattern a parameter name pattern; must match the * parameter or column name as it is stored in the database * @return ResultSet - each row describes a * user function parameter, column or return type * * @exception SQLException if a database access error occurs * @see #getSearchStringEscape * @since 1.6 */ public ResultSet getFunctionColumns(String catalog, String schemaPattern, String functionNamePattern, String columnNamePattern) throws SQLException { if (!this.hasParametersView) { return super.getFunctionColumns(catalog, schemaPattern, functionNamePattern, columnNamePattern); } if ((functionNamePattern == null) || (functionNamePattern.length() == 0)) { if (this.conn.getNullNamePatternMatchesAll()) { functionNamePattern = "%"; } else { throw SQLError.createSQLException( "Procedure name pattern can not be NULL or empty.", SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); } } String db = null; if (catalog == null) { if (this.conn.getNullCatalogMeansCurrent()) { db = this.database; } } else { db = catalog; } // FUNCTION_CAT // FUNCTION_SCHEM // FUNCTION_NAME // COLUMN_NAME // COLUMN_TYPE StringBuffer sqlBuf = new StringBuffer("SELECT SPECIFIC_SCHEMA AS FUNCTION_CAT, " + "NULL AS `FUNCTION_SCHEM`, " + "SPECIFIC_NAME AS `FUNCTION_NAME`, " + "IFNULL(PARAMETER_NAME, '') AS `COLUMN_NAME`, " + "CASE WHEN PARAMETER_MODE = 'IN' THEN " + getJDBC4FunctionConstant(JDBC4FunctionConstant.FUNCTION_COLUMN_IN) + " WHEN PARAMETER_MODE = 'OUT' THEN " + getJDBC4FunctionConstant(JDBC4FunctionConstant.FUNCTION_COLUMN_OUT) + " WHEN PARAMETER_MODE = 'INOUT' THEN " + getJDBC4FunctionConstant(JDBC4FunctionConstant.FUNCTION_COLUMN_INOUT) + " WHEN ORDINAL_POSITION = 0 THEN " + getJDBC4FunctionConstant(JDBC4FunctionConstant.FUNCTION_COLUMN_RETURN) + " ELSE " + getJDBC4FunctionConstant(JDBC4FunctionConstant.FUNCTION_COLUMN_UNKNOWN) + " END AS `COLUMN_TYPE`, "); //DATA_TYPE MysqlDefs.appendJdbcTypeMappingQuery(sqlBuf, "DATA_TYPE"); sqlBuf.append(" AS `DATA_TYPE`, "); // TYPE_NAME if (conn.getCapitalizeTypeNames()) { sqlBuf.append("UPPER(CASE WHEN LOCATE('unsigned', DATA_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 THEN CONCAT(DATA_TYPE, ' unsigned') ELSE DATA_TYPE END) AS `TYPE_NAME`,"); } else { sqlBuf.append("CASE WHEN LOCATE('unsigned', DATA_TYPE) != 0 AND LOCATE('unsigned', DATA_TYPE) = 0 THEN CONCAT(DATA_TYPE, ' unsigned') ELSE DATA_TYPE END AS `TYPE_NAME`,"); } // PRECISION int => precision sqlBuf.append("NUMERIC_PRECISION AS `PRECISION`, "); // LENGTH int => length in bytes of data sqlBuf .append("CASE WHEN LCASE(DATA_TYPE)='date' THEN 10 WHEN LCASE(DATA_TYPE)='time' THEN 8 WHEN LCASE(DATA_TYPE)='datetime' THEN 19 WHEN LCASE(DATA_TYPE)='timestamp' THEN 19 WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION WHEN CHARACTER_MAXIMUM_LENGTH > " + Integer.MAX_VALUE + " THEN " + Integer.MAX_VALUE + " ELSE CHARACTER_MAXIMUM_LENGTH END AS LENGTH, "); // SCALE short => scale sqlBuf.append("NUMERIC_SCALE AS `SCALE`, "); // RADIX short => radix sqlBuf.append("10 AS RADIX,"); // NULLABLE // REMARKS // CHAR_OCTET_LENGTH * // ORDINAL_POSITION * // IS_NULLABLE * // SPECIFIC_NAME * sqlBuf.append(getJDBC4FunctionConstant(JDBC4FunctionConstant.FUNCTION_NULLABLE) + " AS `NULLABLE`, " + " NULL AS `REMARKS`, " + "CHARACTER_OCTET_LENGTH AS `CHAR_OCTET_LENGTH`, " + " ORDINAL_POSITION, " + "'YES' AS `IS_NULLABLE`, " + "SPECIFIC_NAME " + "FROM INFORMATION_SCHEMA.PARAMETERS WHERE " + "SPECIFIC_SCHEMA LIKE ? AND SPECIFIC_NAME LIKE ? AND (PARAMETER_NAME LIKE ? OR PARAMETER_NAME IS NULL) " + "AND ROUTINE_TYPE='FUNCTION' ORDER BY SPECIFIC_SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION"); java.sql.PreparedStatement pStmt = null; try { pStmt = prepareMetaDataSafeStatement(sqlBuf.toString()); if (db != null) { pStmt.setString(1, db); } else { pStmt.setString(1, "%"); } pStmt.setString(2, functionNamePattern); pStmt.setString(3, columnNamePattern); ResultSet rs = executeMetadataQuery(pStmt); ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(createFunctionColumnsFields()); return rs; } finally { if (pStmt != null) { pStmt.close(); } } } /** * Getter to JDBC4 DatabaseMetaData.function* constants. * This method must be overridden by JDBC4 subclasses. this implementation should never be called. * * @param constant * the constant id from DatabaseMetaData fields to return. * * @return 0 */ protected int getJDBC4FunctionConstant(JDBC4FunctionConstant constant) { return 0; } /** * Retrieves a description of the system and user functions available * in the given catalog. *

* Only system and user function descriptions matching the schema and * function name criteria are returned. They are ordered by * FUNCTION_CAT, FUNCTION_SCHEM, * FUNCTION_NAME and * SPECIFIC_ NAME. * *

Each function description has the the following columns: *

    *
  1. FUNCTION_CAT String => function catalog (may be null) *
  2. FUNCTION_SCHEM String => function schema (may be null) *
  3. FUNCTION_NAME String => function name. This is the name * used to invoke the function *
  4. REMARKS String => explanatory comment on the function *
  5. FUNCTION_TYPE short => kind of function: *
      *
    • functionResultUnknown - Cannot determine if a return value * or table will be returned *
    • functionNoTable- Does not return a table *
    • functionReturnsTable - Returns a table *
    *
  6. SPECIFIC_NAME String => the name which uniquely identifies * this function within its schema. This is a user specified, or DBMS * generated, name that may be different then the FUNCTION_NAME * for example with overload functions *
*

* A user may not have permission to execute any of the functions that are * returned by getFunctions * * @param catalog a catalog name; must match the catalog name as it * is stored in the database; "" retrieves those without a catalog; * null means that the catalog name should not be used to narrow * the search * @param schemaPattern a schema name pattern; must match the schema name * as it is stored in the database; "" retrieves those without a schema; * null means that the schema name should not be used to narrow * the search * @param functionNamePattern a function name pattern; must match the * function name as it is stored in the database * @return ResultSet - each row is a function description * @exception SQLException if a database access error occurs * @see #getSearchStringEscape * @since 1.6 */ public java.sql.ResultSet getFunctions(String catalog, String schemaPattern, String functionNamePattern) throws SQLException { if ((functionNamePattern == null) || (functionNamePattern.length() == 0)) { if (this.conn.getNullNamePatternMatchesAll()) { functionNamePattern = "%"; } else { throw SQLError.createSQLException( "Function name pattern can not be NULL or empty.", SQLError.SQL_STATE_ILLEGAL_ARGUMENT, getExceptionInterceptor()); } } String db = null; if (catalog == null) { if (this.conn.getNullCatalogMeansCurrent()) { db = this.database; } } else { db = catalog; } String sql = "SELECT ROUTINE_SCHEMA AS FUNCTION_CAT, NULL AS FUNCTION_SCHEM, " + "ROUTINE_NAME AS FUNCTION_NAME, ROUTINE_COMMENT AS REMARKS, " + getJDBC4FunctionNoTableConstant() + " AS FUNCTION_TYPE, ROUTINE_NAME AS SPECIFIC_NAME FROM INFORMATION_SCHEMA.ROUTINES " + "WHERE ROUTINE_TYPE LIKE 'FUNCTION' AND ROUTINE_SCHEMA LIKE ? AND " + "ROUTINE_NAME LIKE ? ORDER BY FUNCTION_CAT, FUNCTION_SCHEM, FUNCTION_NAME, SPECIFIC_NAME"; java.sql.PreparedStatement pStmt = null; try { pStmt = prepareMetaDataSafeStatement(sql); pStmt.setString(1, db != null ? db : "%"); pStmt.setString(2, functionNamePattern); ResultSet rs = executeMetadataQuery(pStmt); ((com.mysql.jdbc.ResultSetInternalMethods) rs).redefineFieldsForDBMD(new Field[] { new Field("", "FUNCTION_CAT", Types.CHAR, 255), new Field("", "FUNCTION_SCHEM", Types.CHAR, 255), new Field("", "FUNCTION_NAME", Types.CHAR, 255), new Field("", "REMARKS", Types.CHAR, 255), new Field("", "FUNCTION_TYPE", Types.SMALLINT, 6), new Field("", "SPECIFIC_NAME", Types.CHAR, 255) }); return rs; } finally { if (pStmt != null) { pStmt.close(); } } } /** * Getter to JDBC4 DatabaseMetaData.functionNoTable constant. * This method must be overridden by JDBC4 subclasses. this implementation should never be called. * * @return 0 */ protected int getJDBC4FunctionNoTableConstant() { return 0; } }