/** * validates the action. checks to verify inputs are available to execute * * <p>- 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) { error( Messages.getInstance() .getErrorString( "SQLBaseComponent.ERROR_0001_QUERY_NOT_SPECIFIED", actionName)); // $NON-NLS-1$ 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", actionName)); //$NON-NLS-1$ 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", actionName)); //$NON-NLS-1$ result = false; } } else { error( Messages.getInstance() .getErrorString( "ComponentBase.ERROR_0001_UNKNOWN_ACTION_TYPE", actionDefinition.getElement().asXML())); // $NON-NLS-1$ result = false; } return result; }
/** * 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) { error( Messages.getInstance() .getErrorString("SQLBaseComponent.ERROR_0005_INVALID_CONNECTION")); // $NON-NLS-1$ return null; } else { localConnection = defaultConnection; } } return localConnection; } catch (Exception e) { error( Messages.getInstance() .getErrorString("SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName()), e); //$NON-NLS-1$ } return null; }
/** * 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()) { error( Messages.getInstance() .getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION")); // $NON-NLS-1$ 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; warn( Messages.getInstance() .getString("SQLBaseComponent.WARN_FALL_BACK_TO_NONSCROLLABLE")); // $NON-NLS-1$ } 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", getActionName())); //$NON-NLS-1$ 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) { error( Messages.getInstance() .getErrorString("SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName()), e); //$NON-NLS-1$ } return false; }
/** * 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) { error( Messages.getInstance() .getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION")); // $NON-NLS-1$ return null; } if (!connection.initialized()) { error( Messages.getInstance() .getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION")); // $NON-NLS-1$ return null; } if (preparedQuery == null) { error( Messages.getInstance() .getErrorString( "SQLBaseComponent.ERROR_0001_QUERY_NOT_SPECIFIED", getActionName())); //$NON-NLS-1$ 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()) { warn( Messages.getInstance() .getString("SQLBaseComponent.WARN_FALL_BACK_TO_NONSCROLLABLE")); // $NON-NLS-1$ } } 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) { error( Messages.getInstance() .getErrorString("SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName()), e); //$NON-NLS-1$ } return null; }
/** * determines state of component, and executes accordingly. * * <p>various inputs that impact the state include: * * <p>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 * * <p>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", actionDefinition.getDescription())); // $NON-NLS-1$ 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", getActionName())); //$NON-NLS-1$ return false; } else if (conn.getDatasourceType() == IPentahoConnection.SQL_DATASOURCE) { connection = conn; } else { error( Messages.getInstance() .getErrorString( "IPreparedComponent.ERROR_0001_INVALID_CONNECTION_TYPE", getActionName())); //$NON-NLS-1$ return false; } } else { dispose(); connection = getDatasourceConnection(); } if (connection == null) { return false; } // Check if this is a prepared query that will be executed later. If so cache the // 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; } // TODO not sure if this should be allowed without connection ownership? // 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)) { runQuery(baseQuery, false); // The side effect of // 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) { error( Messages.getInstance() .getErrorString("SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName()), e); //$NON-NLS-1$ } return false; }