001: /*
002: * Copyright 2006 Pentaho Corporation. All rights reserved.
003: * This software was developed by Pentaho Corporation and is provided under the terms
004: * of the Mozilla Public License, Version 1.1, or any later version. You may not use
005: * this file except in compliance with the license. If you need a copy of the license,
006: * please go to http://www.mozilla.org/MPL/MPL-1.1.txt. The Original Code is the Pentaho
007: * BI Platform. The Initial Developer is Pentaho Corporation.
008: *
009: * Software distributed under the Mozilla Public License is distributed on an "AS IS"
010: * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. Please refer to
011: * the license for the specific language governing your rights and limitations.
012: *
013: * Contributed May 22, 2006
014: * @author Radek Maciaszek, M3 Media Service Limited
015: *
016: */
017:
018: package org.pentaho.plugin.sql;
019:
020: import java.sql.SQLException;
021: import java.util.StringTokenizer;
022:
023: import org.apache.commons.logging.Log;
024: import org.apache.commons.logging.LogFactory;
025: import org.pentaho.actionsequence.dom.actions.AbstractRelationalDbAction;
026: import org.pentaho.actionsequence.dom.actions.ActionDefinition;
027: import org.pentaho.actionsequence.dom.actions.SqlConnectionAction;
028: import org.pentaho.actionsequence.dom.actions.SqlExecuteAction;
029: import org.pentaho.commons.connection.memory.MemoryMetaData;
030: import org.pentaho.commons.connection.memory.MemoryResultSet;
031: import org.pentaho.data.connection.sql.SQLConnection;
032: import org.pentaho.messages.Messages;
033:
034: public class SQLExecute extends SQLLookupRule {
035:
036: private static final long serialVersionUID = 2480019361917802106L;
037:
038: public Log getLogger() {
039: return LogFactory.getLog(SQLExecute.class);
040: }
041:
042: public boolean validateAction() {
043: boolean result = true;
044: if (!(getActionDefinition() instanceof SqlExecuteAction)) {
045: error(Messages
046: .getErrorString(
047: "ComponentBase.ERROR_0001_UNKNOWN_ACTION_TYPE", getActionDefinition().getElement().asXML())); //$NON-NLS-1$
048: result = false;
049: } else {
050: result = super .validateAction();
051: }
052: return result;
053: }
054:
055: protected boolean runQuery(String rawQuery, boolean live) {
056: SQLConnection conn = (SQLConnection) connection;
057: return runSqlQuery(conn, rawQuery, live);
058: }
059:
060: protected boolean runSqlQuery(SQLConnection conn, String rawQuery,
061: boolean live) {
062: SqlExecuteAction sqlExecuteAction = (SqlExecuteAction) getActionDefinition();
063: boolean executed = false;
064: boolean continueOnException = sqlExecuteAction
065: .getContinueOnException().getBooleanValue(false);
066: String[] columnHeaders = new String[] {
067: Messages
068: .getString("SQLExecute.USER_AFFECTED_ROWS_COLUMN_NAME"), //$NON-NLS-1$
069: Messages
070: .getString("SQLExecute.USER_AFFECTED_ROW_STATUS") //$NON-NLS-1$
071: };
072: MemoryMetaData metaData = new MemoryMetaData(
073: new String[][] { columnHeaders }, null);
074: metaData.setColumnTypes(new String[] { "int", "string" }); //$NON-NLS-1$ //$NON-NLS-2$
075: MemoryResultSet affectedRowsResultSet = new MemoryResultSet(
076: metaData);
077: String successMsg = Messages
078: .getString("SQLExecute.USER_SUCCESS"); //$NON-NLS-1$
079: String failMsg = Messages.getString("SQLExecute.USER_FAILED"); //$NON-NLS-1$
080: try {
081: if (conn == null) {
082: error(Messages
083: .getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION")); //$NON-NLS-1$
084: return false;
085: }
086: if (!conn.initialized()) {
087: error(Messages
088: .getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION")); //$NON-NLS-1$
089: return false;
090: }
091:
092: if (sqlExecuteAction.getForceSingleStatement()
093: .getBooleanValue(false)) {
094: // Forces original execution path.
095: //
096: // This execution path should be used if the query
097: // has a semi-colon in the text of the SQL statement.
098: //
099: // This is a legitimate condition if there is (for example)
100: // a statement with a where-clause that has a semi-colon.
101: //
102: // e.g.: UPDATE sometable SET somecolumn='val1;val2' WHERE somecolumn='val3;val4'
103: //
104: // In this case, using StringTokenizer on semi-colon will result in multiple un-executable
105: // statements - the whole thing will fail.
106: //
107: // This is (arguably) unlikely, but it is possible. That's why I've chosen to make sure
108: // that there is a mechanism for instating the old behavior.
109: //
110: String query = applyInputsToFormat(rawQuery);
111: if (debug) {
112: debug(Messages
113: .getString(
114: "SQLBaseComponent.DEBUG_RUNNING_QUERY", query)); //$NON-NLS-1$
115: }
116: int affectedRows = conn.execute(query);
117: executed = true;
118: affectedRowsResultSet.addRow(new Object[] {
119: new Integer(affectedRows), successMsg });
120: } else {
121: //
122: // Multiple statement execute support provided by contribution from Melanie Crouch
123: //
124: rawQuery = removeLineTerminators(rawQuery.trim())
125: .toString();
126:
127: // tokenize the rawQuery passed into method to find if there are multiple updates to be executed.
128: StringTokenizer st = new StringTokenizer(rawQuery,
129: sqlExecuteAction.getMultiStatementSeparator()
130: .getStringValue(";"));
131:
132: while (st.hasMoreTokens()) {
133: //set rawQuery equal to the nextToken.
134: rawQuery = st.nextToken();
135: String query = applyInputsToFormat(rawQuery.trim());
136: if (debug)
137: debug(Messages
138: .getString(
139: "SQLBaseComponent.DEBUG_RUNNING_QUERY", query)); //$NON-NLS-1$
140: try {
141: int affectedRows = conn.execute(query);
142: // Normally, we'd check to see if the execution resulted in
143: // some updated rows.
144: affectedRowsResultSet
145: .addRow(new Object[] {
146: new Integer(affectedRows),
147: successMsg });
148: executed = true;
149: debug(Messages
150: .getString(
151: "SQLBaseComponent.DEBUG_UPDATED_QUERY", query)); //$NON-NLS-1$
152: } catch (SQLException e) {
153: error(Messages
154: .getErrorString(
155: "SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName() + " : " + e.getLocalizedMessage())); //$NON-NLS-1$ //$NON-NLS-2$
156: executed = continueOnException;
157: if (!continueOnException) {
158: break;
159: }
160: addErrorCode(affectedRowsResultSet, e, failMsg);
161: }
162: } //end while tokenizer
163: }
164: if (getResultOutputName() != null) {
165: setOutputValue(this .getResultOutputName(),
166: affectedRowsResultSet);
167: }
168: } catch (SQLException e) {
169: error(Messages
170: .getErrorString(
171: "SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName() + " : " + e.getLocalizedMessage())); //$NON-NLS-1$ //$NON-NLS-2$
172: executed = continueOnException;
173: addErrorCode(affectedRowsResultSet, e, e
174: .getLocalizedMessage());
175: }
176: //moved finally after last catch so one connection could be used to execute multiple updates.
177: finally {
178:
179: //
180: // No matter what, make sure the connection
181: // gets closed. Otherwise, the connection can
182: // (ok, will) get stranded eating up resources
183: // on the server. This is important.
184: //
185:
186: if (connectionOwner) {
187: conn.close();
188: }
189: }
190: return executed;
191: }
192:
193: public void addErrorCode(MemoryResultSet affectedRowsResultSet,
194: SQLException e, String failMsg) {
195: int eCode = e.getErrorCode();
196: if (eCode > 0) {
197: eCode *= -1; // Make sure that error code results are negative.
198: }
199: affectedRowsResultSet.addRow(new Object[] { new Integer(eCode),
200: e.getLocalizedMessage() });
201: }
202:
203: public static String removeLineTerminators(String inputStr) {
204: char[] rtn = new char[inputStr.length()];
205: char ch;
206: for (int i = 0; i < inputStr.length(); i++) {
207: ch = inputStr.charAt(i);
208: switch (ch) {
209: case '\r':
210: case '\n':
211: rtn[i] = ' ';
212: break;
213: default:
214: rtn[i] = (ch);
215: }
216: }
217: return new String(rtn);
218: }
219: }
|