001: package net.sourceforge.squirrel_sql.plugins.mssql.action;
002:
003: /*
004: * Copyright (C) 2004 Ryan Walberg <generalpf@yahoo.com>
005: *
006: * This library is free software; you can redistribute it and/or
007: * modify it under the terms of the GNU Lesser General Public
008: * License as published by the Free Software Foundation; either
009: * version 2.1 of the License, or (at your option) any later version.
010: *
011: * This library is distributed in the hope that it will be useful,
012: * but WITHOUT ANY WARRANTY; without even the implied warranty of
013: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
014: * Lesser General Public License for more details.
015: *
016: * You should have received a copy of the GNU Lesser General Public
017: * License along with this library; if not, write to the Free Software
018: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
019: */
020:
021: import java.sql.CallableStatement;
022: import java.sql.Connection;
023: import java.sql.PreparedStatement;
024: import java.sql.ResultSet;
025:
026: import net.sourceforge.squirrel_sql.client.session.ISession;
027: import net.sourceforge.squirrel_sql.fw.sql.IDatabaseObjectInfo;
028: import net.sourceforge.squirrel_sql.fw.sql.WrappedSQLException;
029: import net.sourceforge.squirrel_sql.fw.util.BaseException;
030: import net.sourceforge.squirrel_sql.fw.util.ICommand;
031:
032: import net.sourceforge.squirrel_sql.plugins.mssql.MssqlPlugin;
033: import net.sourceforge.squirrel_sql.plugins.mssql.util.MssqlIntrospector;
034:
035: public class ScriptProcedureExecCommand implements ICommand {
036: private ISession _session;
037: private final MssqlPlugin _plugin;
038:
039: private final IDatabaseObjectInfo[] _dbObjs;
040:
041: public ScriptProcedureExecCommand(ISession session,
042: MssqlPlugin plugin, IDatabaseObjectInfo[] dbObjs) {
043: super ();
044: if (session == null)
045: throw new IllegalArgumentException("ISession == null");
046: if (dbObjs == null)
047: throw new IllegalArgumentException(
048: "IDatabaseObjectInfo array is null");
049:
050: _session = session;
051: _plugin = plugin;
052: _dbObjs = dbObjs;
053: }
054:
055: public void execute() throws BaseException {
056: try {
057: if (_dbObjs.length > 0) {
058: Connection conn = _session.getSQLConnection()
059: .getConnection();
060: final String sqlSep = _session.getQueryTokenizer()
061: .getSQLStatementSeparator();
062: final StringBuffer buf = new StringBuffer();
063:
064: for (int i = 0; i < _dbObjs.length; i++) {
065: final IDatabaseObjectInfo oi = _dbObjs[i];
066:
067: if (!conn.getCatalog().equals(oi.getCatalogName()))
068: conn.setCatalog(oi.getCatalogName());
069:
070: if (oi.getSimpleName().endsWith(";0")) {
071: // this is a UDF, not a stored procedure.
072: buf
073: .append("/* WILL NOT EXECUTE USER-DEFINED FUNCTION ");
074: buf.append(oi.getQualifiedName());
075: buf.append(" */\n\n");
076: continue;
077: }
078:
079: /* call sp_help on the stored procedure, then look at the second ResultSet for
080: * the parameter information. */
081:
082: String useThisName = MssqlIntrospector
083: .getFixedVersionedObjectName(oi
084: .getSimpleName());
085: CallableStatement stmt = conn
086: .prepareCall("{ call sp_help (?) }");
087: stmt.setString(1, useThisName);
088: ResultSet rs;
089:
090: StringBuffer procExec = new StringBuffer();
091: procExec.append("DECLARE @rc int\nEXECUTE @rc = [");
092: procExec.append(oi.getCatalogName());
093: procExec.append("].[");
094: procExec.append(oi.getSchemaName());
095: procExec.append("].[");
096: procExec.append(useThisName);
097: procExec.append("] ");
098:
099: if (!stmt.execute())
100: return;
101:
102: /* since .execute() returned true, the first result is a ResultSet. */
103: rs = stmt.getResultSet();
104: if (!stmt.getMoreResults())
105: return;
106: rs = stmt.getResultSet();
107:
108: while (rs.next()) {
109: String paramName = rs.getString(1);
110: String paramType = rs.getString(2);
111: short paramLength = rs.getShort(3);
112: int paramPrec = rs.getInt(4);
113: int paramScale = rs.getInt(5);
114:
115: procExec.append(paramName);
116: if (!rs.isLast())
117: procExec.append(", ");
118:
119: buf.append("DECLARE ");
120: buf.append(paramName);
121: buf.append(" ");
122: buf.append(MssqlIntrospector.formatDataType(
123: paramType, paramLength, paramPrec,
124: paramScale));
125: buf.append("\n");
126: buf.append("SET ");
127: buf.append(paramName);
128: buf.append(" = NULL\n");
129: }
130:
131: procExec.append("\nSELECT @rc\n\n");
132:
133: buf.append(procExec);
134: }
135:
136: _session.getSessionInternalFrame().getSQLPanelAPI()
137: .appendSQLScript(buf.toString());
138: _session
139: .selectMainTab(ISession.IMainPanelTabIndexes.SQL_TAB);
140: }
141: } catch (java.sql.SQLException ex) {
142: ex.printStackTrace();
143: throw new WrappedSQLException(ex);
144: }
145: }
146: }
|