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

Example 1 with AbstractRelationalDbAction

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

the class SQLBaseComponent method executePrepared.

/**
 * executes a prepared method that returns a result set executePrepared looks up any "PREPARELATER" params in the
 * preparedParams map.
 *
 * @param preparedParams
 *          a map of possible parameters.
 * @return result set
 */
public IPentahoResultSet executePrepared(final Map preparedParams) {
    try {
        if (connection == null) {
            // $NON-NLS-1$
            error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION"));
            return null;
        }
        if (!connection.initialized()) {
            // $NON-NLS-1$
            error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION"));
            return null;
        }
        if (preparedQuery == null) {
            error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0001_QUERY_NOT_SPECIFIED", // $NON-NLS-1$
            getActionName()));
            return null;
        }
        // copy the preparedParams list, so it can be used multiple times.
        ArrayList copyOfPreparedParameters = new ArrayList(preparedParameters);
        // parse preparedQuery, replacing any {PREPARELATER:NAME} with appropriate values
        String query = TemplateUtil.applyTemplate(preparedQuery, getRuntimeContext(), new ParamResolver(copyOfPreparedParameters, preparedParams));
        if (ComponentBase.debug) {
            dumpQuery(query);
        }
        // evaluate
        IPentahoResultSet resultSet = null;
        if (preparedParameters.size() > 0) {
            resultSet = connection.prepareAndExecuteQuery(query, copyOfPreparedParameters);
        } else {
            resultSet = connection.executeQuery(query);
        }
        if (connection instanceof SQLConnection) {
            if (((SQLConnection) connection).isForcedForwardOnly()) {
                // $NON-NLS-1$
                warn(Messages.getInstance().getString("SQLBaseComponent.WARN_FALL_BACK_TO_NONSCROLLABLE"));
            }
        }
        boolean live = true;
        IActionDefinition actionDefinition = getActionDefinition();
        if (actionDefinition instanceof AbstractRelationalDbAction) {
            AbstractRelationalDbAction relationalDbAction = (AbstractRelationalDbAction) actionDefinition;
            live = relationalDbAction.getLive().getBooleanValue(false);
        }
        IPentahoResultSet rs = resultSet;
        // BISERVER-5915, BISERVER-5875 - if the live setting is false, return an in memory resultset.
        if (!live) {
            rs = resultSet.memoryCopy();
        }
        rSet = rs;
        return rs;
    } catch (Exception e) {
        // $NON-NLS-1$
        error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName()), e);
    }
    return null;
}

Example 2 with AbstractRelationalDbAction

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

the class SQLBaseComponent method getConnection.

/**
 * This method retrieves a connection based on the components inputs.
 *
 * @param defaultConnection
 *          a default connection to use if no other is available
 * @return new connection object
 */
protected IPentahoConnection getConnection(final IPentahoConnection defaultConnection) {
    IPentahoConnection localConnection = null;
    try {
        String jndiName = null;
        String driver = null;
        String userId = null;
        String password = null;
        String connectionInfo = null;
        if (getActionDefinition() instanceof SqlConnectionAction) {
            SqlConnectionAction sqlConnectionAction = (SqlConnectionAction) getActionDefinition();
            jndiName = sqlConnectionAction.getJndi().getStringValue();
            driver = sqlConnectionAction.getDriver().getStringValue();
            userId = sqlConnectionAction.getUserId().getStringValue();
            password = sqlConnectionAction.getPassword().getStringValue();
            connectionInfo = sqlConnectionAction.getDbUrl().getStringValue();
        } else if (getActionDefinition() instanceof AbstractRelationalDbAction) {
            AbstractRelationalDbAction relationalDbAction = (AbstractRelationalDbAction) getActionDefinition();
            jndiName = relationalDbAction.getJndi().getStringValue();
            driver = relationalDbAction.getDriver().getStringValue();
            userId = relationalDbAction.getUserId().getStringValue();
            password = relationalDbAction.getPassword().getStringValue();
            connectionInfo = relationalDbAction.getDbUrl().getStringValue();
        }
        if (jndiName != null) {
            localConnection = PentahoConnectionFactory.getConnection(IPentahoConnection.SQL_DATASOURCE, jndiName, getSession(), this);
        }
        if (localConnection == null) {
            localConnection = PentahoConnectionFactory.getConnection(IPentahoConnection.SQL_DATASOURCE, driver, connectionInfo, userId, password, getSession(), this);
        }
        if (localConnection == null) {
            if (defaultConnection == null) {
                // $NON-NLS-1$
                error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0005_INVALID_CONNECTION"));
                return null;
            } else {
                localConnection = defaultConnection;
            }
        }
        return localConnection;
    } catch (Exception e) {
        // $NON-NLS-1$
        error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName()), e);
    }
    return null;
}

Example 3 with AbstractRelationalDbAction

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

the class SQLBaseComponent method executeAction.

/**
 * determines state of component, and executes accordingly.
 *
 * various inputs that impact the state include:
 *
 * live - returns a live result set vs. an in memory copy transform - transform a result set based on additional
 * inputs prepared_component - if available, use existing connection from prepared component max_rows - sets the
 * number of rows that should be returned in result sets
 *
 * The specified output also impacts the state of the execution. If prepared_component is defined as an output, setup
 * the query but delay execution.
 */
@Override
protected boolean executeAction() {
    IActionDefinition actionDefinition = getActionDefinition();
    try {
        if (actionDefinition instanceof AbstractRelationalDbAction) {
            AbstractRelationalDbAction relationalDbAction = (AbstractRelationalDbAction) actionDefinition;
            // Added by Arijit Chatterjee
            IActionInput queryTimeoutInput = relationalDbAction.getQueryTimeout();
            IActionInput maxRowsInput = relationalDbAction.getMaxRows();
            IActionInput readOnlyInput = relationalDbAction.getReadOnly();
            String baseQuery = getQuery();
            if (baseQuery == null) {
                error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0001_QUERY_NOT_SPECIFIED", // $NON-NLS-1$
                actionDefinition.getDescription()));
                return false;
            }
            IPreparedComponent sharedConnection = (IPreparedComponent) relationalDbAction.getSharedConnection().getValue();
            if (readOnlyInput != ActionInputConstant.NULL_INPUT) {
                this.setReadOnly(readOnlyInput.getBooleanValue());
            }
            if (sharedConnection != null) {
                connectionOwner = false;
                IPentahoConnection conn = sharedConnection.shareConnection();
                if (conn == null) {
                    error(Messages.getInstance().getErrorString("IPreparedComponent.ERROR_0002_CONNECTION_NOT_AVAILABLE", // $NON-NLS-1$
                    getActionName()));
                    return false;
                } else if (conn.getDatasourceType() == IPentahoConnection.SQL_DATASOURCE) {
                    connection = conn;
                } else {
                    error(Messages.getInstance().getErrorString("IPreparedComponent.ERROR_0001_INVALID_CONNECTION_TYPE", // $NON-NLS-1$
                    getActionName()));
                    return false;
                }
            } else {
                dispose();
                connection = getDatasourceConnection();
            }
            if (connection == null) {
                return false;
            }
            // query and set this component as the output. This query will be run later from a subreport.
            if (relationalDbAction.getOutputPreparedStatement() != null) {
                prepareQuery(baseQuery);
                IActionOutput actionOutput = relationalDbAction.getOutputPreparedStatement();
                if (actionOutput != null) {
                    actionOutput.setValue(this);
                }
                return true;
            }
            // int maxRows = relationalDbAction.getMaxRows().getIntValue(-1);
            if (maxRowsInput != ActionInputConstant.NULL_INPUT) {
                this.setMaxRows(maxRowsInput.getIntValue());
            }
            // Added by Arijit Chatterjee.Sets the value of timeout. Default is -1, if parameter not found.
            if (queryTimeoutInput != ActionInputConstant.NULL_INPUT) {
                this.setQueryTimeout(queryTimeoutInput.getIntValue());
            }
            if (relationalDbAction.getPerformTransform().getBooleanValue(false)) {
                // The side effect of
                runQuery(baseQuery, false);
                // transform rSet here
                rSet = PentahoDataTransmuter.crossTab(rSet, relationalDbAction.getTransformPivotColumn().getIntValue(-1) - 1, relationalDbAction.getTransformMeasuresColumn().getIntValue(-1) - 1, relationalDbAction.getTransformSortColumn().getIntValue(0) - 1, (Format) relationalDbAction.getTransformPivotDataFormat().getValue(), (Format) relationalDbAction.getTransformSortDataFormat().getValue(), relationalDbAction.getTransformOrderOutputColumns().getBooleanValue(false));
                IActionOutput actionOutput = relationalDbAction.getOutputResultSet();
                if (actionOutput != null) {
                    actionOutput.setValue(rSet);
                }
                return true;
            } else {
                return runQuery(baseQuery, relationalDbAction.getLive().getBooleanValue(false));
            }
        } else if (actionDefinition instanceof SqlConnectionAction) {
            SqlConnectionAction sqlConnectionAction = (SqlConnectionAction) actionDefinition;
            dispose();
            connection = getDatasourceConnection();
            if (connection == null) {
                return false;
            } else {
                IActionOutput actionOutput = sqlConnectionAction.getOutputConnection();
                if (actionOutput != null) {
                    actionOutput.setValue(this);
                    return true;
                } else {
                    return false;
                }
            }
        }
    } catch (Exception e) {
        // $NON-NLS-1$
        error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName()), e);
    }
    return false;
}

Example 4 with AbstractRelationalDbAction

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

the class SQLBaseComponent method validateAction.

/**
 * validates the action. checks to verify inputs are available to execute
 *
 * - verify query is available - verify connection is available, via jndi, connection string, or prepared component -
 * verify output is specified
 */
@Override
public boolean validateAction() {
    boolean result = true;
    IActionDefinition actionDefinition = getActionDefinition();
    String actionName = getActionName();
    if (actionDefinition instanceof AbstractRelationalDbAction) {
        AbstractRelationalDbAction relationalDbAction = (AbstractRelationalDbAction) actionDefinition;
        IActionInput query = relationalDbAction.getQuery();
        IActionInput dbUrl = relationalDbAction.getDbUrl();
        IActionInput jndi = relationalDbAction.getJndi();
        IActionInput sharedConnection = relationalDbAction.getSharedConnection();
        if (query == ActionInputConstant.NULL_INPUT) {
            // $NON-NLS-1$
            error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0001_QUERY_NOT_SPECIFIED", actionName));
            result = false;
        }
        if ((jndi == ActionInputConstant.NULL_INPUT) && (dbUrl == ActionInputConstant.NULL_INPUT) && (sharedConnection == ActionInputConstant.NULL_INPUT)) {
            error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0002_CONNECTION_NOT_SPECIFIED", // $NON-NLS-1$
            actionName));
            result = false;
        }
    } else if (actionDefinition instanceof SqlConnectionAction) {
        SqlConnectionAction sqlConnectionAction = (SqlConnectionAction) actionDefinition;
        IActionInput dbUrl = sqlConnectionAction.getDbUrl();
        IActionInput jndi = sqlConnectionAction.getJndi();
        if ((jndi == ActionInputConstant.NULL_INPUT) && (dbUrl == ActionInputConstant.NULL_INPUT)) {
            error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0002_CONNECTION_NOT_SPECIFIED", // $NON-NLS-1$
            actionName));
            result = false;
        }
    } else {
        error(Messages.getInstance().getErrorString("ComponentBase.ERROR_0001_UNKNOWN_ACTION_TYPE", // $NON-NLS-1$
        actionDefinition.getElement().asXML()));
        result = false;
    }
    return result;
}

Example 5 with AbstractRelationalDbAction

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

the class SQLBaseComponent method runQuery.

/**
 * executes the specified query template. The query template is first formatted and then executed. If live, the
 * original result set is made available as an output. If not live, the result set is converted into memory and the
 * connection and live result set are closed.
 *
 * @param rawQuery
 *          query template
 * @param live
 *          returns original result set if true, memory result set if false
 * @return true if successful
 */
protected boolean runQuery(final String rawQuery, boolean live) {
    try {
        if ((connection == null) || !connection.initialized()) {
            // $NON-NLS-1$
            error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION"));
            return false;
        }
        String query = applyInputsToFormat(rawQuery);
        SQLConnection sqlConnection = null;
        if ((connection instanceof SQLConnection)) {
            sqlConnection = (SQLConnection) connection;
        }
        // Some of the following Added by Arijit Chatterjee passing the timeout value to SQLConnection class
        if (sqlConnection != null) {
            if (this.getQueryTimeout() >= 0) {
                sqlConnection.setQueryTimeout(this.getQueryTimeout());
            }
            if (this.getMaxRows() >= 0) {
                sqlConnection.setMaxRows(this.getMaxRows());
            }
            if (this.getReadOnly()) {
                sqlConnection.setReadOnly(true);
            }
        }
        AbstractRelationalDbAction relationalDbAction = (AbstractRelationalDbAction) getActionDefinition();
        IPentahoResultSet resultSet = null;
        boolean isForwardOnly = relationalDbAction.getUseForwardOnlyResultSet().getBooleanValue(false);
        resultSet = doQuery(sqlConnection, query, isForwardOnly);
        if (sqlConnection.isForcedForwardOnly()) {
            isForwardOnly = true;
            live = false;
            // $NON-NLS-1$
            warn(Messages.getInstance().getString("SQLBaseComponent.WARN_FALL_BACK_TO_NONSCROLLABLE"));
        }
        if (live) {
            // set the result set as the output
            rSet = resultSet;
            // After preparation and execution, we need to clear out the
            // prepared parameters.
            preparedParameters.clear();
            if (resultSet != null) {
                getMetadata(resultSet, true);
                IActionOutput actionOutput = relationalDbAction.getOutputResultSet();
                if (actionOutput != null) {
                    actionOutput.setValue(resultSet);
                }
                return true;
            } else {
                // close the connection if owner
                error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", // $NON-NLS-1$
                getActionName()));
                if (connectionOwner) {
                    connection.close();
                }
                return false;
            }
        } else {
            // execute the query, read the results and cache them
            try {
                // After preparation and execution, we need to clear out the
                // prepared parameters.
                preparedParameters.clear();
                IPentahoResultSet cachedResultSet = resultSet.memoryCopy();
                rSet = cachedResultSet;
                IActionOutput actionOutput = relationalDbAction.getOutputResultSet();
                if (actionOutput != null) {
                    actionOutput.setValue(cachedResultSet);
                }
            } finally {
                // close the connection if owner
                if (connectionOwner) {
                    connection.close();
                    connection = null;
                }
            }
        }
        return true;
    } catch (Exception e) {
        // $NON-NLS-1$
        error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName()), e);
    }
    return false;
}