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

Example 6 with SQLConnection

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

the class SQLBaseComponentTest method testRunQuery.

// IMPORTANT!!! This test is solely to exercise the SQLConnection's
// "fallBackToNonscrollableOnError" flag. We have set up a mock connection that
// does not support scrollable cursors. The connection should throw a SQLException, then
// we set the cursor to forward-only and attempt the query again.
@Test
public void testRunQuery() {
    java.sql.Connection connection = new MockNativeConnection();
    SQLConnection sqlConnection = new MockSQLConnection(connection);
    MockSQLBaseComponent component = new MockSQLBaseComponent(sqlConnection);
    Assert.assertTrue(component.runQuery());
}

Example 7 with SQLConnection

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

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

the class SQLDdlComponent method doQuery.

@Override
public IPentahoResultSet doQuery(final SQLConnection sqlConnection, final String query, boolean forwardOnlyResultset) throws Exception {
    MemoryResultSet resultSet = null;
    int n = ((SQLConnection) connection).execute(query);
    Object[][] columnHeaders = new Object[1][1];
    // $NON-NLS-1$
    columnHeaders[0][0] = "result";
    IPentahoMetaData metadata = new MemoryMetaData(columnHeaders, null);
    resultSet = new MemoryResultSet(metadata);
    Object[] rowObjects = new Object[1];
    rowObjects[0] = new Integer(n);
    resultSet.addRow(rowObjects);
    return resultSet;
}

Example 9 with SQLConnection

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

the class SqlMetadataQueryExec method executeQuery.

public IPentahoResultSet executeQuery(Query queryObject) {
    // need to get the correct DatabaseMeta
    SqlPhysicalModel sqlModel = (SqlPhysicalModel) queryObject.getLogicalModel().getPhysicalModel();
    DatabaseMeta databaseMeta = ThinModelConverter.convertToLegacy(sqlModel.getId(), sqlModel.getDatasource());
    // this connection needs closed
    boolean closeConnection = true;
    DatabaseMeta activeDatabaseMeta = getActiveDatabaseMeta(databaseMeta);
    SQLConnection sqlConnection = getConnection(activeDatabaseMeta);
    String sql = null;
    try {
        if ((sqlConnection == null) || !sqlConnection.initialized()) {
            // $NON-NLS-1$
            logger.error(Messages.getInstance().getErrorString("SQLBaseComponent.ERROR_0007_NO_CONNECTION"));
            // TODO: throw an exception up the stack.
            return null;
        }
        // Fix for PDB-1753
        for (Parameter param : queryObject.getParameters()) {
            String pName = param.getName();
            if (parameters.containsKey(pName) && parameters.get(pName) != null && !parameters.get(pName).getClass().isArray()) {
                parameters.put(pName, this.convertParameterValue(param, parameters.get(pName)));
            }
        }
        MappedQuery mappedQuery = null;
        try {
            SqlGenerator sqlGenerator = createSqlGenerator();
            mappedQuery = sqlGenerator.generateSql(queryObject, LocaleHelper.getLocale().toString(), getMetadataDomainRepository(), activeDatabaseMeta, parameters, true);
        } catch (Exception e) {
            throw new RuntimeException(e.getLocalizedMessage(), e);
        }
        Integer timeout = getTimeout();
        if (timeout != null && timeout >= 0) {
            sqlConnection.setQueryTimeout(timeout);
        }
        Integer maxRows = getMaxRows();
        if (maxRows != null && maxRows >= 0) {
            sqlConnection.setMaxRows(maxRows);
        }
        Boolean readOnly = isReadOnly();
        if (readOnly != null && readOnly.booleanValue()) {
            sqlConnection.setReadOnly(true);
        }
        IPentahoResultSet localResultSet = null;
        sql = mappedQuery.getQuery();
        if (logger.isDebugEnabled()) {
            // $NON-NLS-1$
            logger.debug("SQL: " + sql);
        }
        if (getDoQueryLog()) {
            // $NON-NLS-1$
            logger.info("SQL: " + sql);
        }
        // populate prepared sql params
        List<Object> sqlParams = null;
        if (mappedQuery.getParamList() != null) {
            sqlParams = new ArrayList<Object>();
            for (String param : mappedQuery.getParamList()) {
                Object sqlParam = parameters.get(param);
                // lets see if the parameter is a multi valued param
                if (sqlParam instanceof Object[]) {
                    Object[] multivaluedParamValues = (Object[]) sqlParam;
                    for (Object p : multivaluedParamValues) {
                        sqlParams.add(p);
                    }
                    if (multivaluedParamValues.length == 0) {
                        sqlParams.add("");
                    }
                } else {
                    sqlParams.add(sqlParam);
                }
            }
        }
        try {
            if (!isForwardOnly()) {
                if (sqlParams != null) {
                    localResultSet = sqlConnection.prepareAndExecuteQuery(sql, sqlParams);
                } else {
                    localResultSet = sqlConnection.executeQuery(sql);
                }
            } else {
                if (sqlParams != null) {
                    localResultSet = sqlConnection.prepareAndExecuteQuery(sql, sqlParams, SQLConnection.RESULTSET_FORWARDONLY, SQLConnection.CONCUR_READONLY);
                } else {
                    localResultSet = sqlConnection.executeQuery(sql, SQLConnection.RESULTSET_FORWARDONLY, SQLConnection.CONCUR_READONLY);
                }
            }
            IPentahoMetaData metadata = mappedQuery.generateMetadata(localResultSet.getMetaData());
            ((SQLResultSet) localResultSet).setMetaData(metadata);
            closeConnection = false;
        } catch (Exception e) {
            logger.error(Messages.getInstance().getErrorString("SqlMetadataQueryExec.ERROR_0002_ERROR_EXECUTING_QUERY", e.getLocalizedMessage(), // $NON-NLS-1$
            sql));
            // $NON-NLS-1$
            logger.debug("error", e);
            return null;
        }
        return localResultSet;
    } finally {
        if (closeConnection && sqlConnection != null) {
            sqlConnection.close();
        }
    }
}

Example 10 with SQLConnection

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

the class SqlMetadataQueryExec method getConnection.

protected SQLConnection getConnection(DatabaseMeta databaseMeta) {
    // use the connection specified in the query
    SQLConnection localConnection = null;
    try {
        IPentahoSession session = PentahoSessionHolder.getSession();
        if (databaseMeta.getAccessType() == DatabaseMeta.TYPE_ACCESS_JNDI) {
            String jndiName = databaseMeta.getDatabaseName();
            if (jndiName != null) {
                SimpleLogger simpleLogger = new SimpleLogger(this);
                localConnection = (SQLConnection) PentahoConnectionFactory.getConnection(IPentahoConnection.SQL_DATASOURCE, jndiName, session, simpleLogger);
            }
        }
        if (localConnection == null) {
            String driver = databaseMeta.getDriverClass();
            String userId = databaseMeta.getUsername();
            String password = databaseMeta.getPassword();
            String connectionInfo = databaseMeta.getURL();
            if (StringUtils.isEmpty(databaseMeta.getDatabaseName())) {
                String genericDBMetaDriver = databaseMeta.getAttributes().getProperty(GenericDatabaseMeta.ATRRIBUTE_CUSTOM_DRIVER_CLASS, "");
                if (!StringUtils.isEmpty(genericDBMetaDriver)) {
                    driver = genericDBMetaDriver;
                }
                String genericDBMetaURL = databaseMeta.getAttributes().getProperty(GenericDatabaseMeta.ATRRIBUTE_CUSTOM_URL, "");
                if (!StringUtils.isEmpty(genericDBMetaURL)) {
                    connectionInfo = genericDBMetaURL;
                }
            }
            SimpleLogger simpleLogger = new SimpleLogger(this);
            localConnection = (SQLConnection) PentahoConnectionFactory.getConnection(IPentahoConnection.SQL_DATASOURCE, driver, connectionInfo, userId, password, session, simpleLogger);
        }
        // localConnection = getConnection(localConnection);
        return localConnection;
    } catch (Exception e) {
        // $NON-NLS-1$
        logger.error(Messages.getInstance().getErrorString("MetadataQueryComponent.ERROR_0006_EXECUTE_FAILED"), e);
    }
    return null;
}