001: package net.sourceforge.squirrel_sql.client.session;
002:
003: /*
004: * Copyright (C) 2001-2004 Johan Companger
005: * jcompagner@j-com.nl
006: *
007: * Modifications Copyright (C) 2003-2004 Jason Height
008: * jmheight@users.sourceforge.net
009: *
010: * Modifications copyright (C) 2001-2004 Colin Bell
011: * colbell@users.sourceforge.net
012: *
013: * Modifications copyright (C) 2001-2005 Glenn Griffin
014: * gwghome@users.sourceforge.net
015: *
016: * This library is free software; you can redistribute it and/or
017: * modify it under the terdims of the GNU Lesser General Public
018: * License as published by the Free Software Foundation; either
019: * version 2.1 of the License, or (at your option) any later version.
020: *
021: * This library is distributed in the hope that it will be useful,
022: * but WITHOUT ANY WARRANTY; without even the implied warranty of
023: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
024: * Lesser General Public License for more details.
025: *
026: * You should have received a copy of the GNU Lesser General Public
027: * License along with this library; if not, write to the Free Software
028: * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
029: */
030:
031: import java.sql.ResultSet;
032: import java.sql.SQLException;
033: import java.sql.SQLWarning;
034: import java.sql.Statement;
035:
036: import javax.swing.SwingUtilities;
037:
038: import net.sourceforge.squirrel_sql.client.session.event.ISQLExecutionListener;
039: import net.sourceforge.squirrel_sql.client.session.properties.SessionProperties;
040: import net.sourceforge.squirrel_sql.client.session.schemainfo.SchemaInfoUpdateCheck;
041: import net.sourceforge.squirrel_sql.fw.datasetviewer.ColumnDisplayDefinition;
042: import net.sourceforge.squirrel_sql.fw.datasetviewer.DataSetException;
043: import net.sourceforge.squirrel_sql.fw.datasetviewer.DataSetUpdateableTableModelListener;
044: import net.sourceforge.squirrel_sql.fw.datasetviewer.IDataSetUpdateableTableModel;
045: import net.sourceforge.squirrel_sql.fw.sql.IQueryTokenizer;
046: import net.sourceforge.squirrel_sql.fw.sql.ISQLConnection;
047: import net.sourceforge.squirrel_sql.fw.sql.ITableInfo;
048: import net.sourceforge.squirrel_sql.fw.sql.SQLUtilities;
049: import net.sourceforge.squirrel_sql.fw.sql.TableInfo;
050: import net.sourceforge.squirrel_sql.fw.util.StringManager;
051: import net.sourceforge.squirrel_sql.fw.util.StringManagerFactory;
052: import net.sourceforge.squirrel_sql.fw.util.log.ILogger;
053: import net.sourceforge.squirrel_sql.fw.util.log.LoggerController;
054:
055: /**
056: * This class can be used to execute SQL.
057: * <p/>It implements Runnable so it can be executed as a thread
058: * (asynchronus execution)
059: * or standalone in the main Swing thread (synchronus execution).
060: */
061: public class SQLExecuterTask implements Runnable,
062: IDataSetUpdateableTableModel {
063:
064: /** Logger for this class. */
065: private static final ILogger s_log = LoggerController
066: .createLogger(SQLExecuterTask.class);
067:
068: private static final StringManager s_stringMgr = StringManagerFactory
069: .getStringManager(SQLExecuterTask.class);
070:
071: /** The call back object*/
072: private ISQLExecuterHandler _handler;
073:
074: /** Current session. */
075: private ISession _session;
076:
077: /** SQL passed in to be executed. */
078: private String _sql;
079: private Statement _stmt;
080: private boolean _stopExecution = false;
081:
082: private int _currentQueryIndex = 0;
083: private ISQLExecutionListener[] _executionListeners;
084: private DataSetUpdateableTableModelImpl _dataSetUpdateableTableModel;
085: private SchemaInfoUpdateCheck _schemaInfoUpdateCheck;
086: private IQueryTokenizer _tokenizer = null;
087: /** Whether or not to check if the schema should be updated */
088: private boolean schemaCheck = true;
089:
090: public SQLExecuterTask(ISession session, String sql,
091: ISQLExecuterHandler handler) {
092: this (session, sql, handler, new ISQLExecutionListener[0]);
093: }
094:
095: public SQLExecuterTask(ISession session, String sql,
096: ISQLExecuterHandler handler,
097: ISQLExecutionListener[] executionListeners) {
098: if (sql == null) {
099: if (s_log.isDebugEnabled()) {
100: s_log.debug("init(): expected non-null sql");
101: return;
102: }
103: }
104: _session = session;
105: _schemaInfoUpdateCheck = new SchemaInfoUpdateCheck(_session);
106: _sql = sql;
107: _tokenizer = _session.getQueryTokenizer();
108: _tokenizer.setScriptToTokenize(_sql);
109: _handler = handler;
110: if (_handler == null) {
111: _handler = new DefaultSQLExecuterHandler(session);
112: }
113: _executionListeners = executionListeners;
114: _dataSetUpdateableTableModel = new DataSetUpdateableTableModelImpl();
115: _dataSetUpdateableTableModel.setSession(_session);
116: }
117:
118: public void setExecutionListeners(
119: ISQLExecutionListener[] executionListeners) {
120: _executionListeners = executionListeners;
121: }
122:
123: /**
124: * Returns the number of queries that the tokenizer found in _sql.
125: * @return
126: */
127: public int getQueryCount() {
128: return _tokenizer.getQueryCount();
129: }
130:
131: public void setSchemaCheck(boolean aBoolean) {
132: schemaCheck = aBoolean;
133: }
134:
135: public void run() {
136: if (_sql == null) {
137: if (s_log.isDebugEnabled()) {
138: s_log
139: .debug("init(): expected non-null sql. Skipping execution");
140: }
141: return;
142: }
143:
144: String lastExecutedStatement = null;
145: int statementCount = 0;
146: final SessionProperties props = _session.getProperties();
147: try {
148: final ISQLConnection conn = _session.getSQLConnection();
149: _stmt = conn.createStatement();
150:
151: try {
152: final boolean correctlySupportsMaxRows = conn
153: .getSQLMetaData().correctlySupportsSetMaxRows();
154: if (correctlySupportsMaxRows && props.getSQLLimitRows()) {
155: try {
156: _stmt.setMaxRows(props.getSQLNbrRowsToShow());
157: } catch (Exception e) {
158: s_log.error("Can't Set MaxRows", e);
159: }
160: }
161:
162: if (_tokenizer.getQueryCount() == 0) {
163: throw new IllegalArgumentException(
164: "No SQL selected for execution.");
165: }
166:
167: _currentQueryIndex = 0;
168:
169: // Process each individual query.
170: boolean maxRowsHasBeenSet = correctlySupportsMaxRows;
171: int processedStatementCount = 0;
172: statementCount = _tokenizer.getQueryCount();
173:
174: _handler.sqlStatementCount(statementCount);
175:
176: while (_tokenizer.hasQuery() && !_stopExecution) {
177: String querySql = _tokenizer.nextQuery();
178: if (querySql != null) {
179: ++processedStatementCount;
180: if (_handler != null) {
181: _handler.sqlToBeExecuted(querySql);
182: }
183:
184: // Some driver don't correctly support setMaxRows. In
185: // these cases use setMaxRows only if this is a
186: // SELECT.
187: if (!correctlySupportsMaxRows
188: && props.getSQLLimitRows()) {
189: if ("SELECT".length() < querySql.trim()
190: .length()
191: && "SELECT"
192: .equalsIgnoreCase(querySql
193: .trim()
194: .substring(
195: 0,
196: "SELECT"
197: .length()))) {
198: if (!maxRowsHasBeenSet) {
199: try {
200: _stmt.setMaxRows(props
201: .getSQLNbrRowsToShow());
202: } catch (Exception e) {
203: s_log.error(
204: "Can't Set MaxRows", e);
205: }
206: maxRowsHasBeenSet = true;
207: }
208: } else if (maxRowsHasBeenSet) {
209: _stmt.close();
210: _stmt = conn.createStatement();
211: maxRowsHasBeenSet = false;
212: }
213: }
214: try {
215: lastExecutedStatement = querySql;
216:
217: if (!processQuery(querySql,
218: processedStatementCount,
219: statementCount)) {
220: break;
221: }
222: } catch (SQLException ex) {
223: // If the user has cancelled the query, don't bother logging
224: // an error message. It is likely that the cancel request
225: // interfered with the attempt to fetch results from the
226: // ResultSet, which is to be expected when the Statement is
227: // closed. So, let's not bug the user with obvious error
228: // messages that we can do nothing about.
229: if (_stopExecution) {
230: break;
231: } else {
232: if (props.getAbortOnError()) {
233: throw ex;
234: } else {
235: if (1 < statementCount) {
236: handleError(
237: ex,
238: "Error occured in:\n"
239: + lastExecutedStatement);
240: } else {
241: handleError(ex, null);
242: }
243: }
244: }
245: }
246: }
247: }
248:
249: } finally {
250: try {
251: _stmt.close();
252: } finally {
253: _stmt = null;
254: }
255: }
256: } catch (Throwable ex) {
257: if (props.getAbortOnError() && 1 < statementCount) {
258: handleError(ex, "Error occured in:\n"
259: + lastExecutedStatement);
260: } else {
261: handleError(ex, null);
262: }
263:
264: if (false == ex instanceof SQLException) {
265: s_log.error("Unexpected exception when executing SQL: "
266: + ex, ex);
267: }
268:
269: } finally {
270: if (_stopExecution) {
271: if (_handler != null) {
272: _handler.sqlExecutionCancelled();
273: }
274: try {
275: if (_stmt != null) {
276: _stmt.cancel();
277: }
278: } catch (Throwable th) {
279: s_log.error("Error occured cancelling SQL", th);
280: }
281: }
282: if (_handler != null) {
283: _handler.sqlCloseExecutionHandler();
284: }
285:
286: if (schemaCheck) {
287: try {
288: _schemaInfoUpdateCheck.flush();
289: } catch (Throwable t) {
290: s_log.error("Could not update cache ", t);
291: }
292: }
293: }
294: }
295:
296: public void cancel() {
297: if (_stopExecution) {
298: return;
299: }
300: _handler.sqlExecutionCancelled();
301: // i18n[SQLResultExecuterPanel.canceleRequested=Query execution cancel requested by user.]
302: String msg = s_stringMgr
303: .getString("SQLResultExecuterPanel.canceleRequested");
304: _session.getApplication().getMessageHandler().showMessage(msg);
305:
306: _stopExecution = true;
307: if (_stmt != null) {
308: CancelStatementThread cst = new CancelStatementThread(
309: _stmt, _session.getApplication()
310: .getMessageHandler());
311: cst.tryCancel();
312: }
313: }
314:
315: private boolean processQuery(String sql,
316: int processedStatementCount, int statementCount)
317: throws SQLException {
318: ++_currentQueryIndex;
319:
320: final SQLExecutionInfo exInfo = new SQLExecutionInfo(
321: _currentQueryIndex, sql, _stmt.getMaxRows());
322: boolean firstResultIsResultSet = _stmt.execute(sql);
323: exInfo.sqlExecutionComplete();
324:
325: // Display any warnings generated by the SQL execution.
326: handleAllWarnings(_session.getSQLConnection(), _stmt);
327:
328: boolean supportsMultipleResultSets = _session
329: .getSQLConnection().getSQLMetaData()
330: .supportsMultipleResultSets();
331: boolean inFirstLoop = true;
332:
333: // Loop while we either have a ResultSet to process or rows have
334: // been updated/inserted/deleted.
335: while (true) {
336: // User has cancelled the query execution.
337: if (_stopExecution) {
338: return false;
339: }
340:
341: int updateCount = _stmt.getUpdateCount();
342:
343: ResultSet res = null;
344: if (inFirstLoop && firstResultIsResultSet) {
345: res = _stmt.getResultSet();
346: } else if (false == inFirstLoop) {
347: res = _stmt.getResultSet();
348: }
349:
350: if (-1 != updateCount) {
351: if (_handler != null) {
352: _handler.sqlDataUpdated(updateCount);
353: }
354: }
355: if (null != res) {
356: if (!processResultSet(res, exInfo)) {
357: return false;
358: }
359: }
360:
361: if (false == supportsMultipleResultSets) {
362: // This is (a logically not sufficent) try to cope with the problem that there are the following
363: // contradictory rules in the JDBC API Doc:
364: // Statement.getResultSet():
365: // This method should be called only once per result.
366: // Statement.getUpdateCount():
367: // This method should be called only once per result.
368: // Statement.getMoreResults():
369: // There are no more results when the following is true: (!getMoreResults() && (getUpdateCount() == -1)
370: //
371: // If getMoreResults() returns false, we don't know if we have more results, we only know that it isn't
372: // a result set. Since we called getUpdateCount() before getMoreResults() because we would like to know
373: // the update count of the first result, we might not be allowed to call getUpdateCount() again.
374: //
375: // The Intersystems Cache Driver for example always returns the same updateCount on simple
376: // INSERT, UPDATE, DELETE statements not matter if getMoreResults() was called. So updateCount never
377: // gets -1 and this will loop forever. When I discussed the issue with the Intersystems people they
378: // just told me not to call getUpdateCount() twice. That simple. My hope is that this will cure
379: // problems with DBs that just don't care for multiple result sets.
380: break;
381: }
382:
383: if (!_stmt.getMoreResults() && -1 == updateCount) {
384: // There is no need to close result sets if we call _stmt.getMoreResults() because it
385: // implicitly closes any current ResultSet.
386: // ON DB2 version 7.1 it is even harmful to close a ResultSet explicitly.
387: // _stmt.getMoreResults() will never return true anymore if you do.
388: break;
389: }
390: inFirstLoop = false;
391: }
392:
393: fireExecutionListeners(sql);
394:
395: if (_handler != null) {
396: _handler.sqlExecutionComplete(exInfo,
397: processedStatementCount, statementCount);
398: }
399:
400: EditableSqlCheck edittableCheck = new EditableSqlCheck(exInfo);
401:
402: if (edittableCheck.allowsEditing()) {
403: TableInfo ti = getTableName(edittableCheck
404: .getTableNameFromSQL());
405: _dataSetUpdateableTableModel.setTableInfo(ti);
406: } else {
407: _dataSetUpdateableTableModel.setTableInfo(null);
408: }
409: if (schemaCheck) {
410: _schemaInfoUpdateCheck.addExecutionInfo(exInfo);
411: }
412:
413: return true;
414: }
415:
416: private void fireExecutionListeners(final String sql) {
417: // This method is called from a thread.
418: // In case listeners update Swing controls we invoke later here.
419: SwingUtilities.invokeLater(new Runnable() {
420: public void run() {
421: for (int i = 0; i < _executionListeners.length; i++) {
422: _executionListeners[i].statementExecuted(sql);
423: }
424: }
425: });
426: }
427:
428: private boolean processResultSet(final ResultSet rs,
429: final SQLExecutionInfo exInfo) {
430: if (_stopExecution) {
431: return false;
432: }
433:
434: if (_handler != null) {
435: try {
436: _handler.sqlResultSetAvailable(rs, exInfo, this );
437: } catch (DataSetException ex) {
438: if (_stopExecution) {
439: return false;
440: } else {
441: _session.showMessage(ex);
442: s_log.error("Error reading ResultSet for SQL: "
443: + exInfo.getSQL(), ex);
444: }
445: }
446: }
447:
448: handleResultSetWarnings(rs);
449: SQLUtilities.closeResultSet(rs);
450: return true;
451: }
452:
453: private void handleAllWarnings(ISQLConnection conn, Statement stmt) {
454: // If SQL executing produced warnings then write them out to the session
455: // message handler. TODO: This is a pain. PostgreSQL sends "raise
456: // notice" messages to the connection, not to the statment so they will
457: // be mixed up with warnings from other statements.
458: synchronized (conn) {
459: try {
460: handleWarnings(conn.getWarnings());
461: conn.getConnection().clearWarnings();
462: } catch (Throwable th) {
463: s_log.debug("Driver doesn't handle "
464: + "Connection.getWarnings()/clearWarnings()",
465: th);
466: }
467: }
468:
469: try {
470: handleWarnings(stmt.getWarnings());
471: stmt.clearWarnings();
472: } catch (Throwable th) {
473: s_log.debug("Driver doesn't handle "
474: + "Statement.getWarnings()/clearWarnings()", th);
475: }
476: }
477:
478: private void handleResultSetWarnings(ResultSet rs) {
479: try {
480: handleWarnings(rs.getWarnings());
481: } catch (Throwable th) {
482: s_log.error("Can't get warnings from ResultSet", th);
483: _session.showMessage(th);
484: }
485: }
486:
487: private void handleWarnings(SQLWarning sw) {
488: if (_handler != null) {
489: try {
490: while (sw != null) {
491: _handler.sqlExecutionWarning(sw);
492: sw = sw.getNextWarning();
493: }
494: } catch (Throwable th) {
495: s_log.debug("Driver/DBMS can't handle SQLWarnings", th);
496: }
497: }
498: }
499:
500: private void handleError(Throwable th, String postErrorString) {
501: if (_handler != null)
502: _handler.sqlExecutionException(th, postErrorString);
503: }
504:
505: /*
506: *
507: *
508: * Implement IDataSetUpdateableModel interface
509: * and IDataSetUpdateableTableModel interface
510: *
511: * TODO: THIS CODE WAS COPIED FROM ContentsTab. IT SHOULD PROBABLY
512: * BE PUT INTO A COMMON LOCATION AND SHARED BY BOTH THIS
513: * CLASS AND ContentsTab.
514: *
515: *
516: */
517:
518: /**
519: * Get the full name info for the table that is being referred to in the
520: * SQL query.
521: * Since we do not know the catalog, schema, or the actual name used in
522: * this DB to refer to "table" types, we cannot filter the initial query on any of
523: * those criteria. Thus the only thing we can do is get all of the names
524: * of everything in the DB, then scan for things matching the name of the
525: * table as entered by the user in the SQL query. If there are no objects
526: * with that name or multiple objects with that name, we do not allow editing.
527: * This method was originally copied from TableTypeExpander.createChildren
528: * and heavilly modified.
529: *
530: * @param tableNameInSQL Name of the table as typed by the user in the SQL query.
531: *
532: * @return A <TT>TableInfo</TT> object for the only DB object
533: * with the given name, or null if there is none or more than one with that name.
534: */
535: public TableInfo getTableName(String tableNameFromSQL) {
536: ITableInfo[] tables = _session.getSchemaInfo().getITableInfos();
537:
538: // filter the list of all DB objects looking for things with the given name
539: for (int i = 0; i < tables.length; ++i) {
540: String simpleName = tables[i].getSimpleName().toUpperCase();
541: String nameWithSchema = simpleName;
542: String nameWithSchemaAndCatalog = simpleName;
543:
544: if (null != tables[i].getSchemaName()
545: && 0 < tables[i].getSchemaName().length()) {
546: nameWithSchema = tables[i].getSchemaName()
547: .toUpperCase()
548: + "." + nameWithSchema;
549: nameWithSchemaAndCatalog = nameWithSchema;
550: }
551:
552: if (null != tables[i].getCatalogName()
553: && 0 < tables[i].getCatalogName().length()) {
554: nameWithSchemaAndCatalog = tables[i].getCatalogName()
555: .toUpperCase()
556: + "." + nameWithSchema;
557: }
558:
559: if (simpleName.equals(tableNameFromSQL)
560: || nameWithSchema.equals(tableNameFromSQL)
561: || nameWithSchemaAndCatalog
562: .equals(tableNameFromSQL)) {
563: return (TableInfo) tables[i];
564: }
565: }
566: // ok, that didn't work - let's see if the table looks fully qualified.
567: // if so, we'll split the name from the schema/catalog and try that.
568: String[] parts = tableNameFromSQL.split("\\.");
569: if (parts.length == 2) {
570: String catalog = parts[0];
571: String simpleName = parts[1];
572: tables = _session.getSchemaInfo().getITableInfos(catalog,
573: null, simpleName);
574: if (tables != null && tables.length > 0) {
575: return (TableInfo) tables[0];
576: }
577: // Ok, maybe catalog was really a schema instead.
578: tables = _session.getSchemaInfo().getITableInfos(null,
579: catalog, simpleName);
580: if (tables != null && tables.length > 0) {
581: return (TableInfo) tables[0];
582: }
583: }
584: return null;
585:
586: }
587:
588: ////////////////////////////////////////////////////////
589: // Implementataion of IDataSetUpdateableTableModel:
590: // Delegation to _dataSetUpdateableTableModel
591: public String getWarningOnCurrentData(Object[] values,
592: ColumnDisplayDefinition[] colDefs, int col, Object oldValue) {
593: return _dataSetUpdateableTableModel.getWarningOnCurrentData(
594: values, colDefs, col, oldValue);
595: }
596:
597: public String getWarningOnProjectedUpdate(Object[] values,
598: ColumnDisplayDefinition[] colDefs, int col, Object newValue) {
599: return _dataSetUpdateableTableModel
600: .getWarningOnProjectedUpdate(values, colDefs, col,
601: newValue);
602: }
603:
604: public Object reReadDatum(Object[] values,
605: ColumnDisplayDefinition[] colDefs, int col,
606: StringBuffer message) {
607: return _dataSetUpdateableTableModel.reReadDatum(values,
608: colDefs, col, message);
609: }
610:
611: public String updateTableComponent(Object[] values,
612: ColumnDisplayDefinition[] colDefs, int col,
613: Object oldValue, Object newValue) {
614: return _dataSetUpdateableTableModel.updateTableComponent(
615: values, colDefs, col, oldValue, newValue);
616: }
617:
618: public int getRowidCol() {
619: return _dataSetUpdateableTableModel.getRowidCol();
620: }
621:
622: public String deleteRows(Object[][] rowData,
623: ColumnDisplayDefinition[] colDefs) {
624: return _dataSetUpdateableTableModel
625: .deleteRows(rowData, colDefs);
626: }
627:
628: public String[] getDefaultValues(ColumnDisplayDefinition[] colDefs) {
629: return _dataSetUpdateableTableModel.getDefaultValues(colDefs);
630: }
631:
632: public String insertRow(Object[] values,
633: ColumnDisplayDefinition[] colDefs) {
634: return _dataSetUpdateableTableModel.insertRow(values, colDefs);
635: }
636:
637: public void addListener(DataSetUpdateableTableModelListener l) {
638: _dataSetUpdateableTableModel.addListener(l);
639: }
640:
641: public void removeListener(DataSetUpdateableTableModelListener l) {
642: _dataSetUpdateableTableModel.removeListener(l);
643: }
644:
645: public void forceEditMode(boolean mode) {
646: _dataSetUpdateableTableModel.forceEditMode(mode);
647: }
648:
649: public boolean editModeIsForced() {
650: return _dataSetUpdateableTableModel.editModeIsForced();
651: }
652: //
653: //////////////////////////////////////////////////////////////////////////////////
654:
655: }
|