databasemetadata.java

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

     *
     *    <LI> <B>PROCEDURE_NAME</B> String =>procedure name
     *    <LI> reserved for future use
     *    <LI> reserved for future use
     *    <LI> reserved for future use
     *    <LI> <B>REMARKS</B> String =>explanatory comment on the procedure
     *    <LI> <B>PROCEDURE_TYPE</B> short =>kind of procedure:
     *    <UL>
     *      <LI> procedureResultUnknown - May return a result
     *      <LI> procedureNoResult - Does not return a result
     *      <LI> procedureReturnsResult - Returns a result
     *    </UL>
     *
     *  </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  procedureNamePattern  a procedure name pattern
     *@return                       ResultSet - each row is a procedure
     *      description
     *@exception  SQLException      if a database-access error occurs.
     *@see                          #getSearchStringEscape
     */
    public java.sql.ResultSet getProcedures( String catalog,
            String schemaPattern,
            String procedureNamePattern )
             throws SQLException
    {
        String query = "exec sp_stored_procedures ?, ?, ?";
        if( catalog != null )
            query = "exec "+catalog+"..sp_stored_procedures ?, ?, ?";

        CallableStatement s = connection.prepareCall(query);
        s.setString(1, procedureNamePattern);
        s.setString(2, schemaPattern);
        s.setString(3, catalog);

        TdsResultSet rs = (TdsResultSet)s.executeQuery();
        return rs;
    }

    /**
     *  What's the database vendor's preferred term for "procedure"?
     *
     *@return                   the vendor term
     *@exception  SQLException  if a database-access error occurs.
     */
    public String getProcedureTerm() throws SQLException
    {
        // XXX Need to check for Sybase

        // per "Programming ODBC for SQLServer" Appendix A
        return "stored procedure";
    }

    /**
     *  Get the schema names available in this database. The results are ordered
     *  by schema name. <P>
     *
     *  The schema column is:
     *  <OL>
     *    <LI> <B>TABLE_SCHEM</B> String =>schema name
     *  </OL>
     *
     *
     *@return                   ResultSet - each row has a single String column
     *      that is a schema name
     *@exception  SQLException  if a database-access error occurs.
     */
    public java.sql.ResultSet getSchemas() throws SQLException
    {
        // XXX We should really clean up all these temporary tables.
        java.sql.Statement statement = connection.createStatement();

        String sql = "SELECT name AS TABLE_SCHEM FROM dbo.sysusers WHERE issqlrole=0";

        return statement.executeQuery(sql);
    }

    /**
     *  What's the database vendor's preferred term for "schema"?
     *
     *@return                   the vendor term
     *@exception  SQLException  if a database-access error occurs.
     */
    public String getSchemaTerm() throws SQLException
    {
        // need to check this for Sybase
        return "owner";
    }

    /**
     *  This is the string that can be used to escape '_' or '%' in the string
     *  pattern style catalog search parameters. <P>
     *
     *  The '_' character represents any single character. <P>
     *
     *  The '%' character represents any sequence of zero or more characters.
     *
     *@return                   the string used to escape wildcard characters
     *@exception  SQLException  if a database-access error occurs.
     */
    public String getSearchStringEscape() throws SQLException
    {
        // XXX Need to check for Sybase

        return "\\";
        // per "Programming ODBC for SQLServer" Appendix A
    }

    /**
     *  Get a comma separated list of all a database's SQL keywords that are NOT
     *  also SQL92 keywords.
     *
     *@return                   the list
     *@exception  SQLException  if a database-access error occurs.
     */
    public String getSQLKeywords() throws SQLException
    {
        return "BREAK,BROWSE,BULK,CHECKPOINT,CLUSTERED,COMMITTED,COMPUTE,"
            +"CONFIRM,CONTROLROW,DATABASE,DBCC,DISK,DISTRIBUTED,DUMMY,DUMP,"
            +"ERRLVL,ERROREXIT,EXIT,FILE,FILLFACTOR,FLOPPY,HOLDLOCK,"
            +"IDENTITY_INSERT,IDENTITYCOL,IF,KILL,LINENO,LOAD,MIRROREXIT,"
            +"NONCLUSTERED,OFF,OFFSETS,ONCE,OVER,PERCENT,PERM,PERMANENT,PLAN,"
            +"PRINT,PROC,PROCESSEXIT,RAISERROR,READ,READTEXT,RECONFIGURE,"
            +"REPEATABLE,RETURN,ROWCOUNT,RULE,SAVE,SERIALIZABLE,SETUSER,"
            +"SHUTDOWN,STATISTICS,TAPE,TEMP,TEXTSIZE,TOP,TRAN,TRIGGER,"
            +"TRUNCATE,TSEQUEL,UNCOMMITTED,UPDATETEXT,USE,WAITFOR,WHILE,"
            +"WRITETEXT";
    }

    /**
     *  Get a comma separated list of string functions.
     *
     *@return                   the list
     *@exception  SQLException  if a database-access error occurs.
     */
    public String getStringFunctions() throws SQLException
    {
        // @todo Implement CONCAT(a,b) as (a)+(b)
        return "ASCII,CHAR,DIFFERENCE,INSERT,LCASE,LEFT,LENGTH,LOCATE,LTRIM,REPEAT,REPLACE,RIGHT,RTRIM,SOUNDEX,SPACE,SUBSTRING,UCASE";
    }

    /**
     *  Get a comma separated list of system functions.
     *
     *@return                   the list
     *@exception  SQLException  if a database-access error occurs.
     */
    public String getSystemFunctions() throws SQLException
    {
        return "DATABASE,IFNULL,USER";
    }

    /**
     *  Get a description of the access rights for each table available in a
     *  catalog. Note that a table privilege applies to one or more columns in
     *  the table. It would be wrong to assume that this priviledge applies to
     *  all columns (this may be true for some systems but is not true for all.)
     *  <P>
     *
     *  Only privileges matching the schema and table name criteria are
     *  returned. They are ordered by TABLE_SCHEM, TABLE_NAME, and PRIVILEGE.
     *  <P>
     *
     *  Each privilige 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>GRANTOR</B> =>grantor of access (may be null)
     *    <LI> <B>GRANTEE</B> String =>grantee of access
     *    <LI> <B>PRIVILEGE</B> String =>name of access (SELECT, INSERT, UPDATE,
     *    REFRENCES, ...)
     *    <LI> <B>IS_GRANTABLE</B> String =>"YES" if grantee is permitted to
     *    grant to others; "NO" if not; null if unknown
     *  </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
     *@return                   ResultSet - each row is a table privilege
     *      description
     *@exception  SQLException  if a database-access error occurs.
     *@see                      #getSearchStringEscape
     */
    public java.sql.ResultSet getTablePrivileges(
            String catalog,
            String schemaPattern,
            String tableNamePattern ) throws SQLException
    {
        String query = "exec sp_table_privileges ?, ?, ?";
        if( catalog != null )
            query = "exec "+catalog+"..sp_table_privileges ?, ?, ?";

        CallableStatement s = connection.prepareCall(query);
        s.setString(1, tableNamePattern);
        s.setString(2, schemaPattern);
        s.setString(3, catalog);

        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");

        return rs;
    }

    /**
     *  Get a description of tables available in a catalog. <P>
     *
     *  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. <P>
     *
     *  Each table 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>TABLE_TYPE</B> String =>table type. Typical types are "TABLE",
     *    "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY",
     *    "ALIAS", "SYNONYM".
     *    <LI> <B>REMARKS</B> String =>explanatory comment on the table
     *  </OL>
     *  <P>
     *
     *  <B>Note:</B> Some databases may not return information for all tables.
     *
     *@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  types             a list of table types to include; null returns
     *      all types
     *@return                   ResultSet - each row is a table description
     *@exception  SQLException  if a database-access error occurs.
     *@see                      #getSearchStringEscape
     */
    public java.sql.ResultSet getTables(
            String catalog,
            String schemaPattern,
            String tableNamePattern,
            String types[] )
            throws SQLException
    {
        String query = "exec sp_tables ?, ?, ?, ?";
        if( catalog != null )
            query = "exec "+catalog+"..sp_tables ?, ?, ?, ?";

        CallableStatement s = connection.prepareCall(query);
        s.setString(1, tableNamePattern);
        s.setString(2, schemaPattern);
        s.setString(3, catalog);
        if( types == null )
            s.setString(4, null);
        else
        {
            StringBuffer buf = new StringBuffer(64);
            buf.append('"');
            for( int i=0; i<types.length; i++ )
                buf.append('\'').append(types[i]).append("',");
            if( buf.length() > 1 )
                buf.setLength(buf.length()-1);
            buf.append('"');
            s.setString(4, buf.toString());
        }

        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");

        return rs;
    }

    /**
     *  Get the table types available in this database. The results are ordered
     *  by table type. <P>
     *
     *  The table type is:
     *  <OL>
     *    <LI> <B>TABLE_TYPE</B> String =>table type. Typical types are "TABLE",
     *    "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY",
     *    "ALIAS", "SYNONYM".
     *  </OL>
     *
     *
     *@return                   ResultSet - each row has a single String column
     *      that is a table type
     *@exception  SQLException  if a database-access error occurs.
     */
    public java.sql.ResultSet getTableTypes() throws SQLException
    {
        // XXX see if this is still true for Sybase
        String sql =
                "select 'TABLE' TABLE_TYPE              " +
                "union select 'VIEW' TABLE_TYPE         " +
                "union select 'SYSTEM TABLE' TABLE_TYPE ";
        java.sql.Statement stmt = connection.createStatement();
        java.sql.ResultSet rs = stmt.executeQuery( sql );

        return rs;
    }

    /**
     *  Get a comma separated list of time and date functions.
     *
     *@return                   the list
     *@exception  SQLException  if a database-access error occurs.
     */
    public String getTimeDateFunctions() throws SQLException
    {
        // @todo Implement this method correctly (and add dependencies to EscapeProcessor)!
        return "GETDATE,DATEPART,DATENAME,DATEDIFF,DATEADD";
    }

    /**
     *  Get a description of all the standard