/*
* This program is free software; you can redistribute it and/or modify it under the
* terms of the GNU Lesser General Public License, version 2.1 as published by the Free Software
* Foundation.
*
* You should have received a copy of the GNU Lesser General Public License along with this
* program; if not, you can obtain a copy at http://www.gnu.org/licenses/old-licenses/lgpl-2.1.html
* or from the Free Software Foundation, Inc.,
* 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
*
* This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
* without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
* See the GNU Lesser General Public License for more details.
*
* Copyright 2006 - 2009 Pentaho Corporation. All rights reserved.
*
*
* Contributed May 22, 2006
* @author Radek Maciaszek, M3 Media Service Limited
*
*/
package org.pentaho.platform.plugin.action.sql;
import java.sql.SQLException;
import java.util.StringTokenizer;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.pentaho.actionsequence.dom.actions.SqlExecuteAction;
import org.pentaho.commons.connection.memory.MemoryMetaData;
import org.pentaho.commons.connection.memory.MemoryResultSet;
import org.pentaho.platform.engine.services.solution.ComponentBase;
import org.pentaho.platform.plugin.action.messages.Messages;
import org.pentaho.platform.plugin.services.connections.sql.SQLConnection;
public class SQLExecute extends SQLLookupRule {
private static final long serialVersionUID = 2480019361917802106L;
@Override
public Log getLogger() {
return LogFactory.getLog(SQLExecute.class);
}
@Override
public boolean validateAction() {
boolean result = true;
if (!(getActionDefinition() instanceof SqlExecuteAction)) {
error(Messages.getInstance().getErrorString(
"ComponentBase.ERROR_0001_UNKNOWN_ACTION_TYPE", getActionDefinition().getElement().asXML())); //$NON-NLS-1$
result = false;
} else {
result = super.validateAction();
}
return result;
}
@Override
protected boolean runQuery(final String rawQuery, final boolean live) {
SQLConnection conn = (SQLConnection) connection;
return runSqlQuery(conn, rawQuery, live);
}
protected boolean runSqlQuery(final SQLConnection conn, String rawQuery, final boolean live) {
SqlExecuteAction sqlExecuteAction = (SqlExecuteAction) getActionDefinition();
boolean executed = false;
boolean continueOnException = sqlExecuteAction.getContinueOnException().getBooleanValue(false);
String[] columnHeaders = new String[] { Messages.getInstance().getString("SQLExecute.USER_AFFECTED_ROWS_COLUMN_NAME"), //$NON-NLS-1$
Messages.getInstance().getString("SQLExecute.USER_AFFECTED_ROW_STATUS") //$NON-NLS-1$
};
MemoryMetaData metaData = new MemoryMetaData(new String[][] { columnHeaders }, null);
metaData.setColumnTypes(new String[] { "int", "string" }); //$NON-NLS-1$ //$NON-NLS-2$
MemoryResultSet affectedRowsResultSet = new MemoryResultSet(metaData);
String successMsg = Messages.getInstance().getString("SQLExecute.USER_SUCCESS"); //$NON-NLS-1$
String failMsg = Messages.getInstance().getString("SQLExecute.USER_FAILED"); //$NON-NLS-1$
try {
if (conn == null) {
error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION")); //$NON-NLS-1$
return false;
}
if (!conn.initialized()) {
error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION")); //$NON-NLS-1$
return false;
}
if (sqlExecuteAction.getForceSingleStatement().getBooleanValue(false)) {
// Forces original execution path.
//
// This execution path should be used if the query
// has a semi-colon in the text of the SQL statement.
//
// This is a legitimate condition if there is (for example)
// a statement with a where-clause that has a semi-colon.
//
// e.g.: UPDATE sometable SET somecolumn='val1;val2' WHERE somecolumn='val3;val4'
//
// In this case, using StringTokenizer on semi-colon will result in multiple un-executable
// statements - the whole thing will fail.
//
// This is (arguably) unlikely, but it is possible. That's why I've chosen to make sure
// that there is a mechanism for instating the old behavior.
//
String query = applyInputsToFormat(rawQuery);
if (ComponentBase.debug) {
debug(Messages.getInstance().getString("SQLBaseComponent.DEBUG_RUNNING_QUERY", query)); //$NON-NLS-1$
}
int affectedRows = conn.execute(query);
executed = true;
affectedRowsResultSet.addRow(new Object[] { new Integer(affectedRows), successMsg });
} else {
//
// Multiple statement execute support provided by contribution from Melanie Crouch
//
rawQuery = SQLExecute.removeLineTerminators(rawQuery.trim()).toString();
// tokenize the rawQuery passed into method to find if there are multiple updates to be executed.
StringTokenizer st = new StringTokenizer(rawQuery, sqlExecuteAction.getMultiStatementSeparator()
.getStringValue(";")); //$NON-NLS-1$
while (st.hasMoreTokens()) {
//set rawQuery equal to the nextToken.
rawQuery = st.nextToken();
String query = applyInputsToFormat(rawQuery.trim());
if (ComponentBase.debug) {
debug(Messages.getInstance().getString("SQLBaseComponent.DEBUG_RUNNING_QUERY", query)); //$NON-NLS-1$
}
try {
int affectedRows = conn.execute(query);
// Normally, we'd check to see if the execution resulted in
// some updated rows.
affectedRowsResultSet.addRow(new Object[] { new Integer(affectedRows), successMsg });
executed = true;
debug(Messages.getInstance().getString("SQLBaseComponent.DEBUG_UPDATED_QUERY", query)); //$NON-NLS-1$
} catch (SQLException e) {
error(Messages.getInstance().getErrorString(
"SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName() + " : " + e.getLocalizedMessage())); //$NON-NLS-1$ //$NON-NLS-2$
executed = continueOnException;
if (!continueOnException) {
break;
}
addErrorCode(affectedRowsResultSet, e, failMsg);
}
} //end while tokenizer
}
if (getResultOutputName() != null) {
setOutputValue(this.getResultOutputName(), affectedRowsResultSet);
}
} catch (SQLException e) {
error(Messages.getInstance().getErrorString(
"SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName() + " : " + e.getLocalizedMessage())); //$NON-NLS-1$ //$NON-NLS-2$
executed = continueOnException;
addErrorCode(affectedRowsResultSet, e, e.getLocalizedMessage());
}
//moved finally after last catch so one connection could be used to execute multiple updates.
finally {
//
// No matter what, make sure the connection
// gets closed. Otherwise, the connection can
// (ok, will) get stranded eating up resources
// on the server. This is important.
//
if (connectionOwner) {
conn.close();
}
}
return executed;
}
public void addErrorCode(final MemoryResultSet affectedRowsResultSet, final SQLException e, final String failMsg) {
int eCode = e.getErrorCode();
if (eCode > 0) {
eCode *= -1; // Make sure that error code results are negative.
}
affectedRowsResultSet.addRow(new Object[] { new Integer(eCode), e.getLocalizedMessage() });
}
public static String removeLineTerminators(final String inputStr) {
char[] rtn = new char[inputStr.length()];
char ch;
for (int i = 0; i < inputStr.length(); i++) {
ch = inputStr.charAt(i);
switch (ch) {
case '\r':
case '\n':
rtn[i] = ' ';
break;
default:
rtn[i] = (ch);
}
}
return new String(rtn);
}
}