public IPentahoResultSet getResultSet(final ReportSpec reportSpec) throws Exception {
String jndiName = reportSpec.getReportSpecChoice().getJndiSource();
IPentahoConnection connection = null;
if (reportSpec.getIsMDX()) {
// did this ever work??
String connectStr = ""; // $NON-NLS-1$
IDBDatasourceService datasourceService =
PentahoSystem.getObjectFactory().get(IDBDatasourceService.class, null);
String dsName = datasourceService.getDSBoundName(jndiName);
if (dsName != null) {
connectStr = "dataSource=" + dsName + "; Catalog=mondrian"; // $NON-NLS-1$ //$NON-NLS-2$
} else {
error(
Messages.getInstance()
.getErrorString("MDXBaseComponent.ERROR_0005_INVALID_CONNECTION")); // $NON-NLS-1$
return null;
}
Properties props = new Properties();
props.setProperty(IPentahoConnection.CONNECTION, connectStr);
props.setProperty(IPentahoConnection.PROVIDER, reportSpec.getMondrianCubeDefinitionPath());
connection =
PentahoConnectionFactory.getConnection(
IPentahoConnection.MDX_DATASOURCE, props, getSession(), this);
} else {
connection =
PentahoConnectionFactory.getConnection(
IPentahoConnection.SQL_DATASOURCE, jndiName, getSession(), this);
}
String query = ReportParameterUtility.setupParametersForActionSequence(reportSpec.getQuery());
query = setupQueryParameters(query);
IPentahoResultSet res = connection.executeQuery(query);
return res;
}
/**
* attempt to aquire a connection. if connection isn't available, wait a certain period of time
* before trying again.
*
* @return connection
*/
public IPentahoConnection getDatasourceConnection() {
IPentahoConnection con;
int[] timeouts = {200, 500, 2000};
for (int element : timeouts) {
try {
con = getConnection();
try {
con.clearWarnings();
} catch (Exception ex) {
// ignore
}
return con;
} catch (Exception ex) {
// ignore
}
waitFor(element);
}
con = getConnection();
try {
con.clearWarnings();
} catch (Exception ex) {
// ignore
}
return con;
}
public IPentahoResultSet doQuery(
final SQLConnection sqlConnection, final String query, boolean forwardOnlyResultset)
throws Exception {
//
// At this point, 'connection' and 'sqlConnection' should be pointers to
// the same object iff the 'connection' is a subclass of pentaho's SQLConnection.
// It is possible that the sqlConnection will be null, but the connection
// won't be if someone is using their own implementation of the SQLConnection from
// the factory.
//
IPentahoResultSet resultSet = null;
if (ComponentBase.debug) {
dumpQuery(query);
}
if (preparedParameters.size() > 0) {
if (!forwardOnlyResultset) {
resultSet = connection.prepareAndExecuteQuery(query, preparedParameters);
} else {
if (sqlConnection != null) {
resultSet =
sqlConnection.prepareAndExecuteQuery(
query,
preparedParameters,
SQLConnection.RESULTSET_FORWARDONLY,
SQLConnection.CONCUR_READONLY);
} else {
throw new IllegalStateException(
Messages.getInstance()
.getErrorString(
"SQLBaseComponent.ERROR_0008_UNSUPPORTED_CURSOR_TYPE")); //$NON-NLS-1$
}
}
} else {
if (!forwardOnlyResultset) {
resultSet = connection.executeQuery(query);
} else {
if (sqlConnection != null) {
resultSet =
sqlConnection.executeQuery(
query, SQLConnection.RESULTSET_FORWARDONLY, SQLConnection.CONCUR_READONLY);
} else {
throw new IllegalStateException(
Messages.getInstance()
.getErrorString(
"SQLBaseComponent.ERROR_0008_UNSUPPORTED_CURSOR_TYPE")); //$NON-NLS-1$
}
}
}
return resultSet;
}
/**
* called when in prepared-component mode, this method populates the preparedQuery string and
* preparedParameters object.
*
* @param rawQuery
* @return
*/
protected boolean prepareQuery(final String rawQuery) {
try {
if (connection == null) {
error(
Messages.getInstance()
.getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION")); // $NON-NLS-1$
return false;
}
if (!connection.initialized()) {
error(
Messages.getInstance()
.getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION")); // $NON-NLS-1$
return false;
}
preparedQuery = rawQuery;
return true;
} catch (Exception e) {
error(
Messages.getInstance()
.getErrorString("SQLBaseComponent.ERROR_0006_EXECUTE_FAILED", getActionName()),
e); //$NON-NLS-1$
}
return false;
}
/** dispose of the resultset, and if the owner, dispose of the connection. */
public void dispose() {
rSet = null;
// close connection if owner
if (connectionOwner) {
if (connection != null) {
connection.close();
}
connection = 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;
}
