001: /*
002: * SqlServerProcedureReader.java
003: *
004: * This file is part of SQL Workbench/J, http://www.sql-workbench.net
005: *
006: * Copyright 2002-2008, Thomas Kellerer
007: * No part of this code maybe reused without the permission of the author
008: *
009: * To contact the author please send an email to: support@sql-workbench.net
010: *
011: */
012: package workbench.db.mssql;
013:
014: import java.sql.CallableStatement;
015: import java.sql.DatabaseMetaData;
016: import java.sql.ResultSet;
017: import java.sql.SQLException;
018: import workbench.db.JdbcProcedureReader;
019: import workbench.db.ProcedureReader;
020: import workbench.db.WbConnection;
021: import workbench.storage.DataStore;
022: import workbench.util.SqlUtil;
023: import workbench.util.StringUtil;
024:
025: /**
026: *
027: * @author support@sql-workbench.net
028: */
029: public class SqlServerProcedureReader extends JdbcProcedureReader {
030: private final String GET_PROC_SQL = "{call sp_stored_procedures '%', ?}";
031:
032: public SqlServerProcedureReader(WbConnection db) {
033: super (db);
034: }
035:
036: public StringBuilder getProcedureHeader(String catalog,
037: String schema, String procName, int procType) {
038: return StringUtil.emptyBuffer();
039: }
040:
041: /**
042: * The MS JDBC driver does not return the PROCEDURE_TYPE column correctly
043: * so we implement it ourselves (MS always returns RESULT which is
044: * - strictly speaking - true, but as MS still distinguished between
045: * procedures and functions we need to return this correctly
046: */
047: public DataStore getProcedures(String catalog, String owner)
048: throws SQLException {
049: //PreparedStatement stmt = this.dbConn.prepareStatement(GET_PROC_SQL);
050: CallableStatement cstmt = this .connection.getSqlConnection()
051: .prepareCall(GET_PROC_SQL);
052:
053: DataStore ds = null;
054: ResultSet rs = null;
055: try {
056: if (owner == null || "*".equals(owner)) {
057: cstmt.setString(1, "%");
058: } else {
059: cstmt.setString(1, owner);
060: }
061: rs = cstmt.executeQuery();
062: ds = buildProcedureListDataStore(this .connection
063: .getMetadata());
064: while (rs.next()) {
065: String dbname = rs.getString("PROCEDURE_QUALIFIER");
066: String procOwner = rs.getString("PROCEDURE_OWNER");
067: String name = rs.getString("PROCEDURE_NAME");
068: char procType = 0;
069: if (name.indexOf(';') == name.length() - 2) {
070: procType = name.charAt(name.length() - 1);
071: name = name.substring(0, name.length() - 2);
072: }
073: String remark = rs.getString("REMARKS");
074: short type = rs.getShort("PROCEDURE_TYPE");
075: Integer iType = null;
076: if (rs.wasNull()) {
077: iType = new Integer(
078: DatabaseMetaData.procedureResultUnknown);
079: } else {
080: if (procType != 0) {
081: if (procType == '0') {
082: iType = new Integer(
083: DatabaseMetaData.procedureReturnsResult);
084: } else {
085: iType = new Integer(
086: DatabaseMetaData.procedureNoResult);
087: }
088: } else {
089: iType = new Integer(type);
090: }
091: }
092: int row = ds.addRow();
093: ds.setValue(row,
094: ProcedureReader.COLUMN_IDX_PROC_LIST_CATALOG,
095: dbname);
096: ds.setValue(row,
097: ProcedureReader.COLUMN_IDX_PROC_LIST_SCHEMA,
098: procOwner);
099: ds
100: .setValue(
101: row,
102: ProcedureReader.COLUMN_IDX_PROC_LIST_NAME,
103: name);
104: ds.setValue(row,
105: ProcedureReader.COLUMN_IDX_PROC_LIST_TYPE,
106: iType);
107: ds.setValue(row,
108: ProcedureReader.COLUMN_IDX_PROC_LIST_REMARKS,
109: remark);
110: }
111: } finally {
112: SqlUtil.closeAll(rs, cstmt);
113: }
114: return ds;
115: }
116:
117: }
|