Examples with SQLConnection org.pentaho.platform.plugin.services.connections.sql.SQLConnection used on opensource projects

Example 11 with SQLConnection

use of org.pentaho.platform.plugin.services.connections.sql.SQLConnection in project data-access by pentaho.

the class DSWDatasourceServiceImpl method executeQuery.

IPentahoResultSet executeQuery(String connectionName, String query, String previewLimit) throws QueryValidationException, SqlQueriesNotSupportedException {
    SQLConnection sqlConnection = null;
    try {
        checkSqlQueriesSupported(connectionName);
        int limit = (previewLimit != null && previewLimit.length() > 0) ? Integer.parseInt(previewLimit) : -1;
        sqlConnection = (SQLConnection) PentahoConnectionFactory.getConnection(IPentahoConnection.SQL_DATASOURCE, connectionName, PentahoSessionHolder.getSession(), new SimpleLogger(DatasourceServiceHelper.class.getName()));
        sqlConnection.setMaxRows(limit);
        sqlConnection.setReadOnly(true);
        return sqlConnection.executeQuery(BEFORE_QUERY + query + AFTER_QUERY);
    } catch (SqlQueriesNotSupportedException e) {
        logger.error(e.getLocalizedMessage());
        throw e;
    } catch (SQLException e) {
        String error = "DatasourceServiceImpl.ERROR_0009_QUERY_VALIDATION_FAILED";
        if (e.getSQLState().equals("S0021")) {
            // Column already exists
            error = "DatasourceServiceImpl.ERROR_0021_DUPLICATE_COLUMN_NAMES";
        }
        logger.error(Messages.getErrorString(error, e.getLocalizedMessage()));
        throw new QueryValidationException(Messages.getString(error, e.getLocalizedMessage()));
    } catch (Exception e) {
        logger.error(Messages.getErrorString(// $NON-NLS-1$
        "DatasourceServiceImpl.ERROR_0009_QUERY_VALIDATION_FAILED", e.getLocalizedMessage()), e);
        throw new QueryValidationException(e.getLocalizedMessage(), e);
    } finally {
        if (sqlConnection != null) {
            sqlConnection.close();
        }
    }
}

Example 12 with SQLConnection

use of org.pentaho.platform.plugin.services.connections.sql.SQLConnection in project pentaho-platform by pentaho.

the class SqlMetadataQueryExec method getActiveDatabaseMeta.

protected DatabaseMeta getActiveDatabaseMeta(DatabaseMeta databaseMeta) {
    if (getForceDbDialect() || driverClassesToForceMeta.contains(databaseMeta.getDriverClass())) {
        return databaseMeta;
    }
    // retrieve a temporary connection to determine if a dialect change is necessary
    // for generating the MQL Query.
    SQLConnection tempConnection = getConnection(databaseMeta);
    try {
        // if the connection type is not of the current dialect, regenerate the query
        DatabaseInterface di = getDatabaseInterface(tempConnection);
        if ((di != null) && (!databaseMeta.getPluginId().equals(di.getPluginId()))) {
            // we need to reinitialize our mqlQuery object and reset the query.
            // note that using this di object wipes out connection info
            DatabaseMeta meta = (DatabaseMeta) databaseMeta.clone();
            DatabaseInterface di2 = (DatabaseInterface) di.clone();
            di2.setAccessType(databaseMeta.getAccessType());
            di2.setDatabaseName(databaseMeta.getDatabaseName());
            di2.setAttributes(databaseMeta.getAttributes());
            di2.setUsername(databaseMeta.getUsername());
            di2.setPassword(databaseMeta.getPassword());
            di2.setHostname(databaseMeta.getHostname());
            meta.setDatabaseInterface(di2);
            return meta;
        } else {
            return databaseMeta;
        }
    } finally {
        if (tempConnection != null) {
            tempConnection.close();
        }
    }
}

Example 13 with SQLConnection

use of org.pentaho.platform.plugin.services.connections.sql.SQLConnection 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;
}