001: /*
002: * WbDefineVar.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.sql.wbcommands;
013:
014: import java.sql.ResultSet;
015: import java.sql.SQLException;
016: import workbench.db.WbConnection;
017: import workbench.util.ArgumentParser;
018: import workbench.util.ArgumentType;
019: import workbench.util.ExceptionUtil;
020: import workbench.log.LogMgr;
021: import workbench.resource.ResourceMgr;
022: import workbench.sql.SqlCommand;
023: import workbench.sql.VariablePool;
024: import workbench.sql.StatementRunnerResult;
025: import workbench.util.SqlUtil;
026: import workbench.util.StringUtil;
027: import workbench.util.WbFile;
028: import workbench.util.WbStringTokenizer;
029:
030: /**
031: * SQL Command to define a variable that gets stored in the system
032: * wide parameter pool.
033: *
034: * @see workbench.sql.VariablePool
035: *
036: * @author support@sql-workbench.net
037: */
038: public class WbDefineVar extends SqlCommand {
039: public static final String VERB_DEFINE_LONG = "WBVARDEFINE";
040: public static final String VERB_DEFINE_SHORT = "WBVARDEF";
041: public static final WbDefineVar DEFINE_LONG = new WbDefineVar(
042: VERB_DEFINE_LONG);
043: public static final WbDefineVar DEFINE_SHORT = new WbDefineVar(
044: VERB_DEFINE_SHORT);
045:
046: private String verb = null;
047:
048: private WbDefineVar(String aVerb) {
049: this .verb = aVerb;
050: this .cmdLine = new ArgumentParser();
051: this .cmdLine.addArgument("file", ArgumentType.StringArgument);
052: this .cmdLine.addArgument("encoding",
053: ArgumentType.StringArgument);
054: }
055:
056: public String getVerb() {
057: return verb;
058: }
059:
060: protected boolean isConnectionRequired() {
061: return false;
062: }
063:
064: public StatementRunnerResult execute(String aSql)
065: throws SQLException {
066: StatementRunnerResult result = new StatementRunnerResult();
067: String sql = SqlUtil.stripVerb(aSql);
068:
069: cmdLine.parse(sql);
070: WbFile file = this .evaluateFileArgument(cmdLine
071: .getValue("file"));
072:
073: if (file != null) {
074: // if the file argument has been supplied, no variable definition
075: // can be present, but the encoding parameter might have been passed
076: String encoding = cmdLine.getValue("encoding");
077: try {
078: if (file.exists()) {
079: VariablePool.getInstance().readFromFile(
080: file.getFullPath(), encoding);
081: String msg = ResourceMgr
082: .getString("MsgVarDefFileLoaded");
083: msg = StringUtil.replace(msg, "%file%", file
084: .getFullPath());
085: result.addMessage(msg);
086: result.setSuccess();
087: } else {
088: String msg = ResourceMgr.getFormattedString(
089: "ErrFileNotFound", file.getFullPath());
090: result.addMessage(msg);
091: result.setFailure();
092: }
093: } catch (Exception e) {
094: LogMgr.logError("WbDefineVar.execute()",
095: "Error reading definition file: "
096: + file.getFullPath(), e);
097: String msg = ResourceMgr
098: .getString("ErrReadingVarDefFile");
099: msg = StringUtil.replace(msg, "%file%", file
100: .getAbsolutePath());
101: msg = msg + " " + ExceptionUtil.getDisplay(e);
102: result.addMessage(msg);
103: result.setFailure();
104: }
105: } else {
106: WbStringTokenizer tok = new WbStringTokenizer("=", true,
107: "\"'", false);
108: tok.setSourceString(sql);
109: tok.setKeepQuotes(true);
110: String value = null;
111: String var = null;
112:
113: if (tok.hasMoreTokens())
114: var = tok.nextToken();
115:
116: if (var == null) {
117: result.addMessage(ResourceMgr
118: .getString("ErrVarDefWrongParameter"));
119: result.setFailure();
120: return result;
121: }
122:
123: var = var.trim();
124:
125: if (tok.hasMoreTokens())
126: value = tok.nextToken();
127:
128: String msg = null;
129: result.setSuccess();
130:
131: if (value != null) {
132: if (value.trim().startsWith("@")
133: || StringUtil.trimQuotes(value).startsWith("@")) {
134: String valueSql = null;
135: try {
136: // In case the @ sign was placed inside the quotes, make sure
137: // there are no quotes before removing the @ sign
138: value = StringUtil.trimQuotes(value);
139: valueSql = StringUtil.trimQuotes(value.trim()
140: .substring(1));
141: value = this .evaluateSql(currentConnection,
142: valueSql);
143: } catch (Exception e) {
144: LogMgr.logError("WbDefineVar.execute()",
145: "Error retrieving variable value using SQL: "
146: + valueSql, e);
147: String err = ResourceMgr
148: .getString("ErrReadingVarSql");
149: err = StringUtil
150: .replace(err, "%sql%", valueSql);
151: err = err + "\n\n"
152: + ExceptionUtil.getDisplay(e);
153: result.addMessage(err);
154: result.setFailure();
155: return result;
156: }
157: } else {
158: // WbStringTokenizer returned any quotes that were used, so
159: // we have to remove them again as they should not be part of the variable value
160: value = StringUtil.trimQuotes(value.trim());
161: }
162:
163: msg = ResourceMgr.getString("MsgVarDefVariableDefined");
164: try {
165: VariablePool.getInstance().setParameterValue(var,
166: value);
167: msg = StringUtil.replace(msg, "%var%", var);
168: msg = StringUtil.replace(msg, "%value%", value);
169: msg = StringUtil.replace(msg, "%varname%",
170: VariablePool.getInstance().buildVarName(
171: var, false));
172: } catch (IllegalArgumentException e) {
173: result.setFailure();
174: msg = ResourceMgr.getString("ErrVarDefWrongName");
175: result.setFailure();
176: }
177: } else {
178: VariablePool.getInstance().removeValue(var);
179: msg = ResourceMgr.getString("MsgVarDefVariableRemoved");
180: msg = msg.replaceAll("%var%", var);
181: }
182: result.addMessage(msg);
183: }
184:
185: return result;
186: }
187:
188: /**
189: * Return the result of the given SQL string and return
190: * the value of the first column of the first row
191: * as a string value.
192: *
193: * If the SQL gives an error, an empty string will be returned
194: */
195: private String evaluateSql(WbConnection conn, String sql)
196: throws SQLException {
197: ResultSet rs = null;
198: String result = StringUtil.EMPTY_STRING;
199: if (conn == null) {
200: throw new SQLException(
201: "Cannot evaluate SQL based variable without a connection");
202: }
203:
204: try {
205: this .currentStatement = conn.createStatement();
206:
207: if (sql.endsWith(";")) {
208: sql = sql.substring(0, sql.length() - 1);
209: }
210: rs = this .currentStatement.executeQuery(sql);
211: if (rs.next()) {
212: Object value = rs.getObject(1);
213: if (value != null) {
214: result = value.toString();
215: }
216: }
217: } finally {
218: try {
219: rs.close();
220: } catch (Throwable th) {
221: }
222: }
223:
224: return result;
225: }
226:
227: }
|