jtdsdatabasemetadata.java

第三方的SQL Server and Sybase的jdbc dirver,速度更快

        s.setString(2, schema);
        s.setString(3, catalog);

        JtdsResultSet rs = (JtdsResultSet)s.executeQuery();
        CachedResultSet rsTmp = new CachedResultSet((JtdsStatement)s, colNames, colTypes);
        rsTmp.moveToInsertRow();
        int colCnt = rs.getMetaData().getColumnCount();
        while (rs.next()) {
            for (int i = 1; i <= colCnt; i++) {
                rsTmp.updateObject(i, rs.getObject(i));
            }
            rsTmp.insertRow();
        }
        rs.close();
        rsTmp.moveToCurrentRow();
        rsTmp.setConcurrency(ResultSet.CONCUR_READ_ONLY);

        return rsTmp;
    }

    /**
     * Get a description of a catalog's stored procedure parameters and result
     * columns. <P>
     *
     * 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. <P>
     *
     * Each row in the ResultSet is a parameter description or column
     * description with the following fields:
     * <OL>
     *   <LI> <B>PROCEDURE_CAT</B> String =>procedure catalog (may be null)
     *
     *   <LI> <B>PROCEDURE_SCHEM</B> String =>procedure schema (may be null)
     *
     *   <LI> <B>PROCEDURE_NAME</B> String =>procedure name
     *   <LI> <B>COLUMN_NAME</B> String =>column/parameter name
     *   <LI> <B>COLUMN_TYPE</B> Short =>kind of column/parameter:
     *   <UL>
     *     <LI> procedureColumnUnknown - nobody knows
     *     <LI> procedureColumnIn - IN parameter
     *     <LI> procedureColumnInOut - INOUT parameter
     *     <LI> procedureColumnOut - OUT parameter
     *     <LI> procedureColumnReturn - procedure return value
     *     <LI> procedureColumnResult - result column in ResultSet
     *   </UL>
     *
     *   <LI> <B>DATA_TYPE</B> short =>SQL type from java.sql.Types
     *   <LI> <B>TYPE_NAME</B> String =>SQL type name
     *   <LI> <B>PRECISION</B> int =>precision
     *   <LI> <B>LENGTH</B> int =>length in bytes of data
     *   <LI> <B>SCALE</B> short =>scale
     *   <LI> <B>RADIX</B> short =>radix
     *   <LI> <B>NULLABLE</B> short =>can it contain NULL?
     *   <UL>
     *     <LI> procedureNoNulls - does not allow NULL values
     *     <LI> procedureNullable - allows NULL values
     *     <LI> procedureNullableUnknown - nullability unknown
     *   </UL>
     *
     *   <LI> <B>REMARKS</B> String =>comment describing parameter/column
     * </OL>
     * <P>
     *
     * <B>Note:</B> 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; "" retrieves those without a
     *        <code>null</code> 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
     * @param columnNamePattern a column name pattern
     * @return ResultSet - each row is a stored procedure parameter or column description
     * @throws SQLException if a database-access error occurs.
     * @see #getSearchStringEscape
     */
    public java.sql.ResultSet getProcedureColumns(String catalog,
                                                  String schemaPattern,
                                                  String procedureNamePattern,
                                                  String columnNamePattern)
    throws SQLException {
        String colNames[] = {"PROCEDURE_CAT",   "PROCEDURE_SCHEM",
                             "PROCEDURE_NAME",  "COLUMN_NAME",
                             "COLUMN_TYPE",     "DATA_TYPE",
                             "TYPE_NAME",       "PRECISION",
                             "LENGTH",          "SCALE",
                             "RADIX",           "NULLABLE",
                             "REMARKS"};
        int   colTypes[]  = {Types.VARCHAR,     Types.VARCHAR,
                             Types.VARCHAR,     Types.VARCHAR,
                             Types.SMALLINT,    Types.INTEGER,
                             Types.VARCHAR,     Types.INTEGER,
                             Types.INTEGER,     Types.SMALLINT,
                             Types.SMALLINT,    Types.SMALLINT,
                             Types.VARCHAR};

        String query = "sp_sproc_columns ?, ?, ?, ?, ?";

        CallableStatement s = connection.prepareCall(syscall(catalog,query));

        s.setString(1, processEscapes(procedureNamePattern));
        s.setString(2, processEscapes(schemaPattern));
        s.setString(3, catalog);
        s.setString(4, processEscapes(columnNamePattern));
        s.setInt(5, 3); // ODBC version 3

        JtdsResultSet rs = (JtdsResultSet)s.executeQuery();
        ResultSetMetaData rsmd = rs.getMetaData();
        CachedResultSet rsTmp = new CachedResultSet((JtdsStatement)s, colNames, colTypes);
        rsTmp.moveToInsertRow();
        while (rs.next()) {
            int offset = 0;
            for (int i = 1; i + offset <= colNames.length; i++) {
                if (i == 5 && !"column_type".equalsIgnoreCase(rsmd.getColumnName(i))) {
                    // With Sybase 11.92 despite what the documentation says, the
                    // column_type column is missing!
                    // Set the output value to 0 and shift the rest along by one.
                    String colName = rs.getString(4);
                    if (colName != null && colName.equals("RETURN_VALUE")) {
                        rsTmp.updateInt(i, DatabaseMetaData.procedureColumnReturn);
                    } else {
                        rsTmp.updateInt(i, DatabaseMetaData.procedureColumnUnknown);
                    }
                    offset = 1;
                }
                if (i == 3) {
                    String name = rs.getString(i);
                    if (name != null && name.length() > 0) {
                        int pos = name.lastIndexOf(';');
                        if (pos >= 0) {
                            name = name.substring(0, pos);
                        }
                    }
                    rsTmp.updateString(i + offset, name);
                } else if ("data_type".equalsIgnoreCase(rsmd.getColumnName(i))) {
                    int type = TypeInfo.normalizeDataType(rs.getInt(i));
                    rsTmp.updateInt(i + offset, type);
                } else {
                    rsTmp.updateObject(i + offset, rs.getObject(i));
                }
            }
            if (serverType == Driver.SYBASE && rsmd.getColumnCount() >= 22) {
                //
                // For Sybase 12.5+ we can obtain column in/out status from
                // the mode column.
                //
                String mode = rs.getString(22);
                if (mode != null) {
                    if (mode.equalsIgnoreCase("in")) {
                        rsTmp.updateInt(5, DatabaseMetaData.procedureColumnIn);
                    } else
                    if (mode.equalsIgnoreCase("out")) {
                        rsTmp.updateInt(5, DatabaseMetaData.procedureColumnInOut);
                    }
                }
             }
             if (serverType == Driver.SYBASE || tdsVersion == Driver.TDS42) {
                //
                // Standardise the name of the return_value column as
                // @RETURN_VALUE for Sybase and SQL 6.5
                //
                String colName = rs.getString(4);
                if (colName != null && colName.equals("RETURN_VALUE")) {
                    rsTmp.updateString(4, "@RETURN_VALUE");
                }
            }
            rsTmp.insertRow();
        }
        rs.close();
        rsTmp.moveToCurrentRow();
        rsTmp.setConcurrency(ResultSet.CONCUR_READ_ONLY);
        return rsTmp;
    }

    /**
     * Get a description of stored procedures available in a catalog. <P>
     *
     * Only procedure descriptions matching the schema and procedure name
     * criteria are returned. They are ordered by PROCEDURE_SCHEM, and
     * PROCEDURE_NAME. <P>
     *
     * Each procedure description has the the following columns:
     * <OL>
     *   <LI> <B>PROCEDURE_CAT</B> String =>procedure catalog (may be null)
     *
     *   <LI> <B>PROCEDURE_SCHEM</B> String =>procedure schema (may be null)
     *
     *   <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
     *        <code>null</code> 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
     * @throws SQLException if a database-access error occurs.
     *
     * @see #getSearchStringEscape
     */
    public java.sql.ResultSet getProcedures(String catalog,
                                            String schemaPattern,
                                            String procedureNamePattern)
    throws SQLException {
        String colNames[] = {"PROCEDURE_CAT",   "PROCEDURE_SCHEM",
                             "PROCEDURE_NAME",  "RESERVED_1",
                             "RESERVED_2",      "RESERVED_3",
                             "REMARKS",         "PROCEDURE_TYPE"};
        int colTypes[]    = {Types.VARCHAR,     Types.VARCHAR,
                             Types.VARCHAR,     Types.INTEGER,
                             Types.INTEGER,     Types.INTEGER,
                             Types.VARCHAR,     Types.SMALLINT};

        String query = "sp_stored_procedures ?, ?, ?";

        CallableStatement s = connection.prepareCall(syscall(catalog, query));

        s.setString(1, processEscapes(procedureNamePattern));
        s.setString(2, processEscapes(schemaPattern));
        s.setString(3, catalog);

        JtdsResultSet rs = (JtdsResultSet)s.executeQuery();

        CachedResultSet rsTmp = new CachedResultSet((JtdsStatement)s, colNames, colTypes);
        rsTmp.moveToInsertRow();
        int colCnt = rs.getMetaData().getColumnCount();
        //
        // Copy results to local result set.
        //
        while (rs.next()) {
            rsTmp.updateString(1, rs.getString(1));
            rsTmp.updateString(2, rs.getString(2));
            String name = rs.getString(3);
            if (name != null) {
                // Remove grouping integer
                if (name.endsWith(";1")) {
                    name = name.substring(0, name.length() - 2);
                }
            }
            rsTmp.updateString(3, name);
            // Copy over rest of fields
            for (int i = 4; i <= colCnt; i++) {
                rsTmp.updateObject(i, rs.getObject(i));
            }
            if (colCnt < 8) {
                // Sybase does not return this column so fake it now.
                rsTmp.updateShort(8, (short)DatabaseMetaData.procedureReturnsResult);
            }
            rsTmp.insertRow();
        }
        rsTmp.moveToCurrentRow();
        rsTmp.setConcurrency(ResultSet.CONCUR_READ_ONLY);
        rs.close();
        return rsTmp;
    }

    /**
     * What's the database vendor's preferred term for "procedure"?
     *
     * @return the vendor term
     * @throws SQLException if a database-access error occurs.
     */
    public String getProcedureTerm() throws SQLException {
        // 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
     *   <LI> <B>TABLE_CATALOG</B> String => catalog name (may be <code>null</code>, JDBC 3.0)
     * </OL>
     *
     * @return a <code>ResultSet</code> object in which each row is a schema decription
     * @throws SQLException if a database access error occurs
     */
    public java.sql.ResultSet getSchemas() throws SQLException {
        java.sql.Statement statement = connection.createStatement();

        String sql = Driver.JDBC3
                ? "SELECT name AS TABLE_SCHEM, NULL as TABLE_CATALOG FROM dbo.sysusers"
                : "SELECT name AS TABLE_SCHEM FROM dbo.sysusers";

        //
        // MJH - isLogin column only in MSSQL >= 7.0
        //
        if (tdsVersion >= Driver.TDS70) {
            sql += " WHERE islogin=1";
        } else {
            sql += " WHERE uid>0";
        }

        sql += " ORDER BY TABLE_SCHEM";

        return statement.executeQuery(sql);
    }

    /**
     * What's the database vendor's