databasemetadata.java

Java写的TDS协议(JDBC/ODBC)实现

     *  name criteria are returned. They are ordered by TABLE_SCHEM, TABLE_NAME
     *  and ORDINAL_POSITION. <P>
     *
     *  Each column description has the following columns:
     *  <OL>
     *    <LI> <B>TABLE_CAT</B> String =>table catalog (may be null)
     *    <LI> <B>TABLE_SCHEM</B> String =>table schema (may be null)
     *    <LI> <B>TABLE_NAME</B> String =>table name
     *    <LI> <B>COLUMN_NAME</B> String =>column name
     *    <LI> <B>DATA_TYPE</B> short =>SQL type from java.sql.Types
     *    <LI> <B>TYPE_NAME</B> String =>Data source dependent type name
     *    <LI> <B>COLUMN_SIZE</B> int =>column size. For char or date types this
     *    is the maximum number of characters, for numeric or decimal types this
     *    is precision.
     *    <LI> <B>BUFFER_LENGTH</B> is not used.
     *    <LI> <B>DECIMAL_DIGITS</B> int =>the number of fractional digits
     *    <LI> <B>NUM_PREC_RADIX</B> int =>Radix (typically either 10 or 2)
     *    <LI> <B>NULLABLE</B> int =>is NULL allowed?
     *    <UL>
     *      <LI> columnNoNulls - might not allow NULL values
     *      <LI> columnNullable - definitely allows NULL values
     *      <LI> columnNullableUnknown - nullability unknown
     *    </UL>
     *
     *    <LI> <B>REMARKS</B> String =>comment describing column (may be null)
     *
     *    <LI> <B>COLUMN_DEF</B> String =>default value (may be null)
     *    <LI> <B>SQL_DATA_TYPE</B> int =>unused
     *    <LI> <B>SQL_DATETIME_SUB</B> int =>unused
     *    <LI> <B>CHAR_OCTET_LENGTH</B> int =>for char types the maximum number
     *    of bytes in the column
     *    <LI> <B>ORDINAL_POSITION</B> int =>index of column in table (starting
     *    at 1)
     *    <LI> <B>IS_NULLABLE</B> String =>"NO" means column definitely does not
     *    allow NULL values; "YES" means the column might allow NULL values. An
     *    empty string means nobody knows.
     *  </OL>
     *
     *
     *@param  catalog            a catalog name; "" retrieves those without a
     *      catalog; null means drop catalog name from the selection criteria
     *@param  schemaPattern      a schema name pattern; "" retrieves those
     *      without a schema
     *@param  tableNamePattern   a table name pattern
     *@param  columnNamePattern  a column name pattern
     *@return                    ResultSet - each row is a column description
     *@exception  SQLException   if a database-access error occurs.
     *@see                       #getSearchStringEscape
     */
    public java.sql.ResultSet getColumns( String catalog, String schemaPattern, String tableNamePattern,
        String columnNamePattern ) throws SQLException
    {
        String query = "exec sp_columns ?, ?, ?, ?, ?";
        if( catalog != null )
            query = "exec "+catalog+"..sp_columns ?, ?, ?, ?, ?";

        CallableStatement s = connection.prepareCall(query);
        s.setString(1, tableNamePattern);
        s.setString(2, schemaPattern);
        s.setString(3, catalog);
        s.setString(4, columnNamePattern);
        s.setInt(5, 3); // ODBC version 3

        TdsResultSet rs = (TdsResultSet)s.executeQuery();
        Columns col = rs.getContext().getColumnInfo();

        col.setName(1, "TABLE_CAT");
        col.setLabel(1, "TABLE_CAT");
        col.setName(2, "TABLE_SCHEM");
        col.setLabel(2, "TABLE_SCHEM");
        col.setName(7, "COLUMN_SIZE");
        col.setLabel(7, "COLUMN_SIZE");
        col.setName(8, "BUFFER_LENGTH");
        col.setLabel(8, "BUFFER_LENGTH");
        col.setName(9, "DECIMAL_DIGITS");
        col.setLabel(9, "DECIMAL_DIGITS");
        col.setName(10, "NUM_PREC_RADIX");
        col.setLabel(10, "NUM_PREC_RADIX");
        col.setFakeColumnCount(18);

        return rs;
    }

    /**
     *  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. <P>
     *
     *  Each foreign key column description has the following columns:
     *  <OL>
     *    <LI> <B>PKTABLE_CAT</B> String =>primary key table catalog (may be
     *    null)
     *    <LI> <B>PKTABLE_SCHEM</B> String =>primary key table schema (may be
     *    null)
     *    <LI> <B>PKTABLE_NAME</B> String =>primary key table name
     *    <LI> <B>PKCOLUMN_NAME</B> String =>primary key column name
     *    <LI> <B>FKTABLE_CAT</B> String =>foreign key table catalog (may be
     *    null) being exported (may be null)
     *    <LI> <B>FKTABLE_SCHEM</B> String =>foreign key table schema (may be
     *    null) being exported (may be null)
     *    <LI> <B>FKTABLE_NAME</B> String =>foreign key table name being
     *    exported
     *    <LI> <B>FKCOLUMN_NAME</B> String =>foreign key column name being
     *    exported
     *    <LI> <B>KEY_SEQ</B> short =>sequence number within foreign key
     *    <LI> <B>UPDATE_RULE</B> short =>What happens to foreign key when
     *    primary is updated:
     *    <UL>
     *      <LI> importedNoAction - do not allow update of primary key if it has
     *      been imported
     *      <LI> importedKeyCascade - change imported key to agree with primary
     *      key update
     *      <LI> importedKeySetNull - change imported key to NULL if its primary
     *      key has been updated
     *      <LI> importedKeySetDefault - change imported key to default values
     *      if its primary key has been updated
     *      <LI> importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x
     *      compatibility)
     *    </UL>
     *
     *    <LI> <B>DELETE_RULE</B> short =>What happens to the foreign key when
     *    primary is deleted.
     *    <UL>
     *      <LI> importedKeyNoAction - do not allow delete of primary key if it
     *      has been imported
     *      <LI> importedKeyCascade - delete rows that import a deleted key
     *      <LI> importedKeySetNull - change imported key to NULL if its primary
     *      key has been deleted
     *      <LI> importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x
     *      compatibility)
     *      <LI> importedKeySetDefault - change imported key to default if its
     *      primary key has been deleted
     *    </UL>
     *
     *    <LI> <B>FK_NAME</B> String =>foreign key name (may be null)
     *    <LI> <B>PK_NAME</B> String =>primary key name (may be null)
     *    <LI> <B>DEFERRABILITY</B> short =>can the evaluation of foreign key
     *    constraints be deferred until commit
     *    <UL>
     *      <LI> importedKeyInitiallyDeferred - see SQL92 for definition
     *      <LI> importedKeyInitiallyImmediate - see SQL92 for definition
     *      <LI> importedKeyNotDeferrable - see SQL92 for definition
     *    </UL>
     *
     *  </OL>
     *
     *
     *@param  primaryCatalog    a catalog name; "" retrieves those without a
     *      catalog; null means drop catalog name from the selection criteria
     *@param  primarySchema     a schema name pattern; "" retrieves those
     *      without a schema
     *@param  primaryTable      the table name that exports the key
     *@param  foreignCatalog    a catalog name; "" retrieves those without a
     *      catalog; null means drop catalog name from the selection criteria
     *@param  foreignSchema     a schema name pattern; "" retrieves those
     *      without a schema
     *@param  foreignTable      the table name that imports the key
     *@return                   ResultSet - each row is a foreign key column
     *      description
     *@exception  SQLException  if a database-access error occurs.
     *@see                      #getImportedKeys
     */
    public java.sql.ResultSet getCrossReference(
            String primaryCatalog, String primarySchema, String primaryTable,
            String foreignCatalog, String foreignSchema, String foreignTable
             ) throws SQLException
    {
        String query = "exec sp_fkeys ?, ?, ?, ?, ?, ?";
        if( primaryCatalog != null )
            query = "exec "+primaryCatalog+"..sp_fkeys ?, ?, ?, ?, ?, ?";
        else if( foreignCatalog != null )
            query = "exec "+foreignCatalog+"..sp_fkeys ?, ?, ?, ?, ?, ?";

        CallableStatement s = connection.prepareCall(query);
        s.setString(1, primaryTable);
        s.setString(2, primarySchema);
        s.setString(3, primaryCatalog);
        s.setString(4, foreignTable);
        s.setString(5, foreignSchema);
        s.setString(6, foreignCatalog);

        TdsResultSet rs = (TdsResultSet)s.executeQuery();
        Columns col = rs.getContext().getColumnInfo();

        col.setName(1, "PKTABLE_CAT");
        col.setLabel(1, "PKTABLE_CAT");
        col.setName(2, "PKTABLE_SCHEM");
        col.setLabel(2, "PKTABLE_SCHEM");
        col.setName(5, "FKTABLE_CAT");
        col.setLabel(5, "FKTABLE_CAT");
        col.setName(6, "FKTABLE_SCHEM");
        col.setLabel(6, "FKTABLE_SCHEM");

        return rs;
    }

    /**
     *  What's the name of this database product?
     *
     *@return                   database product name
     *@exception  SQLException  if a database-access error occurs.
     */
    public String getDatabaseProductName() throws SQLException
    {
        return tds.getDatabaseProductName();
    }

    /**
     *  What's the version of this database product?
     *
     *@return                   database version
     *@exception  SQLException  if a database-access error occurs.
     */
    public String getDatabaseProductVersion() throws SQLException
    {
        return tds.getDatabaseProductVersion();
    }

    //----------------------------------------------------------------------

    /**
     *  What's the database's default transaction isolation level? The values
     *  are defined in java.sql.Connection.
     *
     *@return                   the default isolation level
     *@exception  SQLException  if a database-access error occurs.
     *@see                      Connection
     */
    public int getDefaultTransactionIsolation() throws SQLException
    {
        // XXX need to check this for Sybase
        return Connection.TRANSACTION_READ_COMMITTED;
    }

    /**
     *  What's this JDBC driver's major version number?
     *
     *@return    JDBC driver major version
     */
    public int getDriverMajorVersion()
    {
        return DriverVersion.getDriverMajorVersion();
    }

    /**
     *  What's this JDBC driver's minor version number?
     *
     *@return    JDBC driver minor version number
     */
    public int getDriverMinorVersion()
    {
        return DriverVersion.getDriverMinorVersion();
    }

    /**
     *  What's the name of this JDBC driver?
     *
     *@return                   JDBC driver name
     *@exception  SQLException  if a database-access error occurs.
     */
    public String getDriverName() throws SQLException
    {
        return "jTDS Type 4 JDBC Driver for MS SQL Server";
    }

    /**
     *  What's the version of this JDBC driver?
     *
     *@return                   JDBC driver version
     *@exception  SQLException  if a database-access error occurs.
     */
    public String getDriverVersion() throws SQLException
    {
        return getDriverMajorVersion() + "." + getDriverMinorVersion();
    }

    /**
     *  Get a description of the 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. <P>
     *
     *  Each foreign key column description has the following columns:
     *  <OL>
     *    <LI> <B>PKTABLE_CAT</B> String =>primary key table catalog (may be
     *    null)
     *    <LI> <B>PKTABLE_SCHEM</B> String =>primary key table schema (may be
     *    null)
     *    <LI> <B>PKTABLE_NAME</B> String =>primary key table name
     *    <LI> <B>PKCOLUMN_NAME</B> String =>primary key column name
     *    <LI> <B>FKTABLE_CAT</B> String =>foreign key table catalog (may be
     *    null) being exported (may be null)
     *    <LI> <B>FKTABLE_SCHEM</B> String =>foreign key table schema (may be
     *    null) being exported (may be null)
     *    <LI> <B>FKTABLE_NAME</B> String =>foreign key table name being
     *    exported
     *    <LI> <B>FKCOLUMN_NAME</B> String =>foreign key column name being
     *    exported
     *    <LI> <B>KEY_SEQ</B> short =>sequence number within foreign key
     *    <LI> <B>UPDATE_RULE</B> short =>What happens to foreign key when
     *    primary is updated:
     *    <UL>
     *      <LI> importedNoAction - do not allow update of primary key if it has
     *      been imported
     *      <LI> importedKeyCascade - change imported key to agree with primary
     *      key update
     *      <LI> importedKeySetNull - change imported key to NULL if its primary
     *      key has been updated
     *      <LI> importedKeySetDefault - change imported key to default values
     *      if its primary key has been updated
     *      <LI> importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x
     *      compatibility)
     *    </UL>
     *
     *    <LI> <B>DELETE_RULE</B> short =>What happens to the foreign key when
     *    primary is deleted.
     *    <UL>
     *      <LI> importedKeyNoAction - do not allow delete of primary key if it
     *      has been imported
     *      <LI> importedKeyCascade - delete rows that import a deleted key
     *      <LI> importedKeySetNull - change imported key to NULL if its primary
     *      key has been deleted
     *      <LI> importedKeyRestrict - same as importedKeyNoAction (for ODBC 2.x
     *      compatibility)
     *      <LI> importedKeySetDefault - change imported key to default if its
     *      primary key has been deleted
     *    </UL>
     *
     *    <LI> <B>FK_NAME</B> String =>foreign key name (may be null)
     *    <LI> <B>PK_NAME</B> String =>primary key name (may be null)
     *    <LI> <B>DEFERRABILITY</B> short =>can the evaluation of foreign key
     *    constraints be deferred until commit
     *    <UL>