Examples with SqlExecuteAction org.pentaho.actionsequence.dom.actions.SqlExecuteAction used on opensource projects

Example 1 with SqlExecuteAction

use of org.pentaho.actionsequence.dom.actions.SqlExecuteAction in project pentaho-platform by pentaho.

the class SQLExecute method runSqlQuery.

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[] { // $NON-NLS-1$
    Messages.getInstance().getString("SQLExecute.USER_AFFECTED_ROWS_COLUMN_NAME"), // $NON-NLS-1$
    Messages.getInstance().getString("SQLExecute.USER_AFFECTED_ROW_STATUS") };
    MemoryMetaData metaData = new MemoryMetaData(new String[][] { columnHeaders }, null);
    // $NON-NLS-1$ //$NON-NLS-2$
    metaData.setColumnTypes(new String[] { "int", "string" });
    MemoryResultSet affectedRowsResultSet = new MemoryResultSet(metaData);
    // $NON-NLS-1$
    String successMsg = Messages.getInstance().getString("SQLExecute.USER_SUCCESS");
    // $NON-NLS-1$
    String failMsg = Messages.getInstance().getString("SQLExecute.USER_FAILED");
    try {
        if (conn == null) {
            // $NON-NLS-1$
            error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION"));
            return false;
        }
        if (!conn.initialized()) {
            // $NON-NLS-1$
            error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION"));
            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) {
                // $NON-NLS-1$
                debug(Messages.getInstance().getString("SQLBaseComponent.DEBUG_RUNNING_QUERY", query));
            }
            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 = // $NON-NLS-1$
            new StringTokenizer(rawQuery, sqlExecuteAction.getMultiStatementSeparator().getStringValue(";"));
            while (st.hasMoreTokens()) {
                // set rawQuery equal to the nextToken.
                rawQuery = st.nextToken();
                String query = applyInputsToFormat(rawQuery.trim());
                if (ComponentBase.debug) {
                    // $NON-NLS-1$
                    debug(Messages.getInstance().getString("SQLBaseComponent.DEBUG_RUNNING_QUERY", query));
                }
                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;
                    // $NON-NLS-1$
                    debug(Messages.getInstance().getString("SQLBaseComponent.DEBUG_UPDATED_QUERY", query));
                } catch (SQLException e) {
                    error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", // $NON-NLS-1$ //$NON-NLS-2$
                    getActionName() + " : " + e.getLocalizedMessage()));
                    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", // $NON-NLS-1$ //$NON-NLS-2$
        getActionName() + " : " + e.getLocalizedMessage()));
        executed = continueOnException;
        addErrorCode(affectedRowsResultSet, e, e.getLocalizedMessage());
    } finally {
        if (connectionOwner) {
            conn.close();
        }
    }
    return executed;
}