001: /*
002: * SqlCommand.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;
013:
014: import java.sql.ResultSet;
015: import java.sql.SQLException;
016: import java.sql.Statement;
017: import workbench.WbManager;
018: import workbench.db.DbSettings;
019: import workbench.db.WbConnection;
020: import workbench.interfaces.ParameterPrompter;
021: import workbench.interfaces.StatementRunner;
022: import workbench.util.ArgumentParser;
023: import workbench.util.ExceptionUtil;
024: import workbench.interfaces.ResultLogger;
025: import workbench.log.LogMgr;
026: import workbench.resource.ResourceMgr;
027: import workbench.storage.DataStore;
028: import workbench.storage.RowActionMonitor;
029: import workbench.util.SqlUtil;
030: import workbench.util.StringUtil;
031: import workbench.util.WbFile;
032:
033: /**
034: * A single SQL command. This class is used if no special class was found
035: * for a given SQL verb. The execute method checks if the command
036: * returned a result set or simply an update count.
037: *
038: * An instance of a SQL command should always be executed in a separate Thread
039: * to allow cancelling of the running statement.
040: *
041: * @author support@sql-workbench.net
042: */
043: public class SqlCommand {
044: protected Statement currentStatement;
045: protected WbConnection currentConnection;
046: protected boolean isCancelled = false;
047: private boolean consumerWaiting = false;
048: protected RowActionMonitor rowMonitor;
049: protected boolean isUpdatingCommand = false;
050: protected boolean reportFullStatementOnError = false;
051: protected ResultLogger resultLogger;
052: protected StatementRunner runner;
053: protected int queryTimeout = 0;
054: protected int maxRows = 0;
055: protected DataStore currentRetrievalData;
056: protected ParameterPrompter prompter;
057: protected ArgumentParser cmdLine;
058:
059: public SqlCommand() {
060: }
061:
062: public void setRowMonitor(RowActionMonitor monitor) {
063: this .rowMonitor = monitor;
064: }
065:
066: public void setResultLogger(ResultLogger logger) {
067: this .resultLogger = logger;
068: }
069:
070: public ArgumentParser getArgumentParser() {
071: return this .cmdLine;
072: }
073:
074: public boolean getFullErrorReporting() {
075: return reportFullStatementOnError;
076: }
077:
078: public void setFullErrorReporting(boolean flag) {
079: reportFullStatementOnError = flag;
080: }
081:
082: protected void appendSuccessMessage(StatementRunnerResult result) {
083: result.addMessage(this .getVerb() + " "
084: + ResourceMgr.getString("MsgKnownStatementOK"));
085: }
086:
087: public void setParameterPrompter(ParameterPrompter p) {
088: this .prompter = p;
089: }
090:
091: protected void appendOutput(StatementRunnerResult result) {
092: String s = this .currentConnection.getOutputMessages();
093: if (!StringUtil.isWhitespaceOrEmpty(s)) {
094: if (result.hasMessages()) {
095: result.addMessageNewLine();
096: }
097: result.addMessage(ResourceMgr.getString("TxtServerOutput"));
098: result.addMessage(s);
099: result.addMessageNewLine();
100: }
101: }
102:
103: /**
104: * Append any warnings from the given Statement and Connection to the given
105: * StringBuilder. If the connection is a connection to Oracle
106: * then any messages written with dbms_output are appended as well
107: * This behaviour is then similar to MS SQL Server where any messages
108: * displayed using the PRINT function are returned in the Warnings as well.
109: *
110: * @see workbench.util.SqlUtil#getWarnings(WbConnection, Statement)
111: */
112: protected boolean appendWarnings(StatementRunnerResult result) {
113: CharSequence warn = SqlUtil.getWarnings(this .currentConnection,
114: this .currentStatement);
115: boolean hasWarning = false;
116: if (warn != null && warn.length() > 0) {
117: hasWarning = true;
118: if (result.hasMessages())
119: result.addMessageNewLine();
120: result.addMessage(ResourceMgr.getString("TxtWarnings"));
121: result.addMessageNewLine();
122: result.addMessage(warn);
123: result.setWarning(true);
124: }
125: return hasWarning;
126: }
127:
128: protected void setUnknownMessage(StatementRunnerResult result,
129: ArgumentParser cmdline, String help) {
130: StringBuilder msg = new StringBuilder(ResourceMgr
131: .getString("ErrUnknownParameter"));
132: msg.append(cmdLine.getUnknownArguments());
133: result.addMessage(msg.toString());
134: if (!WbManager.getInstance().isBatchMode()) {
135: result.addMessage(""); // add empty line
136: result.addMessage(help);
137: }
138: result.setFailure();
139: }
140:
141: /**
142: * Cancels this statements execution. Cancelling is done by
143: * calling <tt>cancel</tt> on the current JDBC Statement object. This requires
144: * that the JDBC driver actually supports cancelling of statements <b>and</b>
145: * that this method is called from a differen thread.
146: *
147: * It also sets the internal cancelled flag so that <tt>SqlCommand</tt>s
148: * that process data in a loop can check this flag and exit the loop
149: * (e.g. {@link workbench.sql.wbcommands.WbExport})
150: *
151: */
152: public void cancel() throws SQLException {
153: this .isCancelled = true;
154: if (this .currentRetrievalData != null) {
155: this .currentRetrievalData.cancelRetrieve();
156: } else if (this .currentStatement != null) {
157: try {
158: LogMgr.logDebug("SqlCommand.cancel()",
159: "Cancelling statement execution...");
160: this .currentStatement.cancel();
161: LogMgr.logDebug("SqlCommand.cancel()", "Cancelled.");
162: } catch (Exception th) {
163: LogMgr.logWarning("SqlCommand.cancel()",
164: "Error when cancelling statement", th);
165: }
166: }
167: }
168:
169: /**
170: * This method should be called, once the caller is finished with running
171: * the SQL command. This releases any database resources that were
172: * obtained during the execution of the statement (especially it
173: * closes the JDBC statement object that was used to run this command).
174: *
175: * If this statement has been cancelled a rollback() is sent to the server.
176: */
177: public void done() {
178: if (this .currentStatement != null) {
179: if (!this .isCancelled) {
180: try {
181: this .currentStatement.clearWarnings();
182: } catch (Exception th) {
183: }
184: try {
185: this .currentStatement.clearBatch();
186: } catch (Exception th) {
187: }
188: }
189: try {
190: this .currentStatement.close();
191: } catch (Exception th) {
192: }
193: }
194: if (this .isCancelled) {
195: try {
196: this .currentConnection.rollback();
197: } catch (Exception th) {
198: }
199: }
200: try {
201: currentConnection.clearWarnings();
202: } catch (Exception e) {
203: }
204: this .currentStatement = null;
205: this .isCancelled = false;
206: }
207:
208: public void setStatementRunner(StatementRunner r) {
209: this .runner = r;
210: }
211:
212: protected boolean isConnectionRequired() {
213: return true;
214: }
215:
216: /**
217: * Should be overridden by a specialised SqlCommand.
218: * setConnection should have been called before calling execute()
219: */
220: public StatementRunnerResult execute(String aSql)
221: throws SQLException, Exception {
222: StatementRunnerResult result = new StatementRunnerResult(aSql);
223:
224: this .currentStatement = this .currentConnection
225: .createStatement();
226: this .isCancelled = false;
227:
228: try {
229: boolean hasResult = this .currentStatement.execute(aSql);
230: result.setSuccess();
231: processResults(result, hasResult);
232: } catch (Exception e) {
233: addErrorInfo(result, aSql, e);
234: LogMgr.logDebug("SqlCommand.execute()",
235: "Error executing sql statement: " + aSql
236: + "\nError:" + ExceptionUtil.getDisplay(e),
237: null);
238: } finally {
239: this .done();
240: }
241: return result;
242: }
243:
244: /**
245: * Tries to process any "pending" results from the last statement that was
246: * executed, but only if the current DBMS supports multiple SQL statements
247: * in a single execute() call. In all other cases the current SqlCommand
248: * will process results properly.
249: * If the DBMS does not support "batched" statements, then only possible
250: * warnings are stored in the result object
251: *
252: * @see #processResults(StatementRunnerResult, boolean)
253: * @see #appendWarnings(StatementRunnerResult)
254: */
255: protected void processMoreResults(String sql,
256: StatementRunnerResult result, boolean hasResult)
257: throws SQLException {
258: if (this .isMultiple(sql)) {
259: processResults(result, hasResult);
260: } else {
261: appendWarnings(result);
262: }
263: }
264:
265: protected void processResults(StatementRunnerResult result,
266: boolean hasResult) throws SQLException {
267: processResults(result, hasResult, null);
268: }
269:
270: /**
271: * Process any ResultSets or updatecounts generated by the last statement
272: * execution.
273: */
274: protected void processResults(StatementRunnerResult result,
275: boolean hasResult, ResultSet queryResult)
276: throws SQLException {
277: if (result == null)
278: return;
279:
280: appendOutput(result);
281:
282: // Postgres obviously clears the warnings if the getMoreResults() is called,
283: // so we add the warnings before calling getMoreResults(). This doesn't seem
284: // to do any harm for other DBMS as well.
285: appendWarnings(result);
286:
287: int updateCount = -1;
288: boolean moreResults = false;
289:
290: if (hasResult == false) {
291: // the first "result" is an updateCount
292: try {
293: updateCount = this .currentStatement.getUpdateCount();
294: } catch (Exception e) {
295: LogMgr.logError("SqlCommand.processResults()",
296: "Error when calling getUpdateCount()", e);
297: updateCount = -1;
298: }
299:
300: try {
301: moreResults = this .currentStatement.getMoreResults();
302: } catch (Exception e) {
303: // Some drivers throw errors if no result is available. In this case
304: // simply assume there are no more results.
305: LogMgr.logError("SqlCommand.processResults()",
306: "Error when calling getMoreResults()", e);
307: moreResults = false;
308: }
309: } else {
310: moreResults = true;
311: }
312:
313: ResultSet rs = null;
314: boolean multipleUpdateCounts = (this .currentConnection != null ? this .currentConnection
315: .getDbSettings().allowsMultipleGetUpdateCounts()
316: : false);
317:
318: int counter = 0;
319: while (moreResults || updateCount > -1) {
320: if (updateCount > -1) {
321: result.addUpdateCountMsg(updateCount);
322: }
323:
324: if (moreResults) {
325: if (queryResult != null) {
326: rs = queryResult;
327: queryResult = null;
328: } else {
329: rs = this .currentStatement.getResultSet();
330: }
331:
332: if (this .isConsumerWaiting() && rs != null) {
333: result.addResultSet(rs);
334: // only one resultSet can be exported
335: // if we call getMoreResults() another time, the previous ResultSet will be closed!
336: break;
337: }
338:
339: if (rs != null) {
340: // we have to use an instance variable for the retrieval, otherwise the retrieval
341: // cannot be cancelled!
342: this .currentRetrievalData = new DataStore(rs,
343: false, this .rowMonitor, maxRows,
344: this .currentConnection);
345: try {
346: // Not reading the data in the constructor enables us
347: // to cancel the retrieval of the data from the ResultSet
348: // without using statement.cancel()
349: // The DataStore checks for the cancel flag during processing
350: // of the ResulSet
351: this .currentRetrievalData.initData(rs, maxRows);
352: this .currentRetrievalData
353: .setGeneratingSql(result
354: .getSourceCommand());
355: } catch (SQLException e) {
356: // Some JDBC driver throw an exception when a statement is
357: // cancelled. But in this case, we do not want to throw away the
358: // data that was retrieved until now. We only add a warning
359: if (this .currentRetrievalData != null
360: && this .currentRetrievalData
361: .isCancelled()) {
362: result
363: .addMessage(ResourceMgr
364: .getString("MsgErrorDuringRetrieve"));
365: result.addMessage(ExceptionUtil
366: .getAllExceptions(e));
367: result.setWarning(true);
368: } else {
369: // if the statement was not cancelled, make sure
370: // the error is displayed to the user.
371: throw e;
372: }
373: } finally {
374: SqlUtil.closeResult(rs);
375: }
376: result.addDataStore(this .currentRetrievalData);
377: }
378: }
379:
380: try {
381: moreResults = this .currentStatement.getMoreResults();
382: } catch (Throwable th) {
383: // Some older Postgres drivers throw a NPE when getMoreResults() is called multiple
384: // times. This exception is simply ignored, so that processing can proceed normally
385: LogMgr.logError("SqlCommand.processResults()",
386: "Error when calling getMoreResults()", th);
387: break;
388: }
389:
390: if (multipleUpdateCounts) {
391: try {
392: updateCount = this .currentStatement
393: .getUpdateCount();
394: } catch (Exception e) {
395: LogMgr.logWarning("SqlCommand.processResult()",
396: "Error when calling getUpdateCount(): "
397: + ExceptionUtil.getDisplay(e));
398: updateCount = -1;
399: multipleUpdateCounts = false;
400: }
401: } else {
402: updateCount = -1;
403: }
404:
405: counter++;
406:
407: // some JDBC drivers do not implement getMoreResults() and getUpdateCount()
408: // correctly, so this is a safety to prevent an endless loop
409: if (counter > 50)
410: break;
411: }
412:
413: this .currentRetrievalData = null;
414: }
415:
416: public void setConnection(WbConnection conn) {
417: this .currentConnection = conn;
418: }
419:
420: /**
421: * Should be overridden by a specialised SqlCommand
422: */
423: public String getVerb() {
424: return StringUtil.EMPTY_STRING;
425: }
426:
427: /**
428: * The commands producing a result set need this flag.
429: * If no consumer is waiting, the can directly produce a DataStore
430: * for the result.
431: */
432: public void setConsumerWaiting(boolean flag) {
433: this .consumerWaiting = flag;
434: }
435:
436: public boolean isConsumerWaiting() {
437: return this .consumerWaiting;
438: }
439:
440: public boolean isUpdatingCommand() {
441: return this .isUpdatingCommand;
442: }
443:
444: public void setQueryTimeout(int timeout) {
445: this .queryTimeout = timeout;
446: }
447:
448: public void setMaxRows(int max) {
449: maxRows = max;
450: }
451:
452: public boolean isResultSetConsumer() {
453: return false;
454: }
455:
456: public void consumeResult(StatementRunnerResult aResult) {
457: }
458:
459: /**
460: * Get a "clean" version of the sql with the verb stripped off
461: * and all comments and newlines removed for processing the
462: * parameters to a Workbench command
463: *
464: * @param sql the sql to "clean"
465: * @return the sql with the verb, comments and newlines removed
466: * @see workbench.util.Sqlutil#makeCleanSql(String, boolean, boolean, char)
467: * @see workbench.util.Sqlutil#getVerb(String)
468: */
469: protected String getCommandLine(String sql) {
470: return SqlUtil.stripVerb(SqlUtil.makeCleanSql(sql, false,
471: false, '\''));
472: }
473:
474: protected WbFile evaluateFileArgument(String fileName) {
475: if (StringUtil.isEmptyString(fileName))
476: return null;
477:
478: String fname = StringUtil.trimQuotes(fileName);
479: WbFile f = new WbFile(fname);
480: if (f.isAbsolute())
481: return f;
482:
483: // Use the "current" directory of the StatementRunner
484: // for the path of the file, if no path is specified.
485: if (this .runner != null) {
486: String dir = this .runner.getBaseDir();
487: if (!StringUtil.isEmptyString(dir)) {
488: f = new WbFile(dir, fname);
489: }
490: }
491: return f;
492: }
493:
494: protected void addErrorInfo(StatementRunnerResult result,
495: String sql, Throwable e) {
496: result.clear();
497:
498: StringBuilder msg = new StringBuilder(150);
499: msg.append(ResourceMgr.getString("MsgExecuteError") + "\n");
500: if (reportFullStatementOnError) {
501: msg.append(sql);
502: } else {
503: msg.append(StringUtil.getMaxSubstring(sql.trim(), 150));
504: }
505: result.addMessage(msg);
506: result.addMessageNewLine();
507: result.addMessage(ExceptionUtil.getAllExceptions(e));
508:
509: result.setFailure();
510: }
511:
512: /**
513: * Check if the passed SQL is a "batched" statement.
514: *
515: * Returns true if the passed SQL string could be a "batched"
516: * statement that actually contains more than one statement.
517: * SQL Server supports these kind of "batches". If this is the case
518: * affected rows will always be shown, because we cannot know
519: * if the statement did not update anything or if it actually
520: * updated only 0 rows (for some reason SQL Server seems to
521: * return 0 as the updatecount even if no update was involved).
522: *
523: * Currently this is only checking for newlines in the passed string.
524: *
525: * @param sql the statement/script to check
526: * @return true if the passed sql could contain more than one (independent) statements
527: */
528: protected boolean isMultiple(String sql) {
529: if (this .currentConnection == null)
530: return false;
531: DbSettings settings = currentConnection.getDbSettings();
532: if (settings.supportsBatchedStatements()) {
533: // TODO: analyze the statement properly to find out if it is really a batched statement.
534: return (sql.indexOf('\n') > -1);
535: }
536: return false;
537: }
538:
539: }
|