Examples with DatabaseQueryConnectionSettings org.knime.core.node.port.database.DatabaseQueryConnectionSettings used on opensource projects

Example 16 with DatabaseQueryConnectionSettings

use of org.knime.core.node.port.database.DatabaseQueryConnectionSettings in project knime-core by knime.

the class DBTableSelectorNodeModel method configure.

/**
 * {@inheritDoc}
 */
@Override
protected PortObjectSpec[] configure(final PortObjectSpec[] inSpecs) throws InvalidSettingsException {
    if ((m_settings.getQuery() == null) || m_settings.getQuery().isEmpty()) {
        throw new InvalidSettingsException("No query configured");
    }
    DatabaseConnectionPortObjectSpec incomingConnection = (DatabaseConnectionPortObjectSpec) inSpecs[0];
    DatabaseConnectionSettings connSettings = incomingConnection.getConnectionSettings(getCredentialsProvider());
    String sql = FlowVariableResolver.parse(m_settings.getQuery(), this);
    DatabaseQueryConnectionSettings querySettings = new DatabaseQueryConnectionSettings(connSettings, sql);
    final DBReader conn = connSettings.getUtility().getReader(querySettings);
    if (!connSettings.getRetrieveMetadataInConfigure()) {
        return new PortObjectSpec[1];
    }
    try {
        DataTableSpec tableSpec = conn.getDataTableSpec(getCredentialsProvider());
        return new PortObjectSpec[] { new DatabasePortObjectSpec(tableSpec, querySettings) };
    } catch (SQLException ex) {
        Throwable cause = ExceptionUtils.getRootCause(ex);
        if (cause == null) {
            cause = ex;
        }
        throw new InvalidSettingsException("Error while validating SQL query '" + sql + "' : " + cause.getMessage(), ex);
    }
}

Example 17 with DatabaseQueryConnectionSettings

use of org.knime.core.node.port.database.DatabaseQueryConnectionSettings in project knime-core by knime.

the class DBColumnRenameNodeModel method createDBOutSpec.

private DatabasePortObjectSpec createDBOutSpec(final DatabasePortObjectSpec inSpec) throws InvalidSettingsException {
    if (m_config == null) {
        throw new InvalidSettingsException("No configuration available");
    }
    final DataTableSpec tableSpec = inSpec.getDataTableSpec();
    final DataTableSpec outSpec = m_config.getNewSpec(tableSpec);
    final List<String> missingColumnNames = m_config.getMissingColumnNames();
    if (missingColumnNames != null && !missingColumnNames.isEmpty()) {
        setWarningMessage("The following columns are configured but no longer exist: " + ConvenienceMethods.getShortStringFrom(missingColumnNames, 5));
    }
    DatabaseQueryConnectionSettings conn = inSpec.getConnectionSettings(getCredentialsProvider());
    final StatementManipulator statementManipulator = conn.getUtility().getStatementManipulator();
    String newQuery = createQuery(conn.getQuery(), tableSpec, statementManipulator);
    conn = createDBQueryConnection(inSpec, newQuery);
    return new DatabasePortObjectSpec(outSpec, conn.createConnectionModel());
}

Example 18 with DatabaseQueryConnectionSettings

use of org.knime.core.node.port.database.DatabaseQueryConnectionSettings in project knime-core by knime.

the class DBGroupByNodeModel2 method createOutSpec.

/**
 * @param inSpec Spec of the input table
 * @param manipulator
 * @param checkRetrieveMetadata
 * @return Spec of the output table
 * @throws InvalidSettingsException if settings do not match the input specification
 */
private DataTableSpec createOutSpec(final DataTableSpec inSpec, final DatabaseConnectionSettings settings, final String query, final StatementManipulator manipulator, final boolean ignoreExceptions) throws InvalidSettingsException {
    // Try get spec from database
    try {
        DatabaseQueryConnectionSettings querySettings = new DatabaseQueryConnectionSettings(settings, query);
        DBReader conn = querySettings.getUtility().getReader(querySettings);
        return conn.getDataTableSpec(getCredentialsProvider());
    } catch (SQLException e) {
        NodeLogger.getLogger(getClass()).info("Could not determine table spec from database, trying to guess now", e);
        if (!ignoreExceptions) {
            throw new InvalidSettingsException("Error in automatically build sql statement: " + e.getMessage());
        }
    // Otherwise guess spec
    }
    final List<DataColumnSpec> colSpecs = new ArrayList<>();
    // Add all group by columns
    for (String col : m_groupByCols.getIncludeList()) {
        final DataColumnSpec columnSpec = inSpec.getColumnSpec(col);
        if (columnSpec == null) {
            throw new InvalidSettingsException("Group column '" + col + "' not found in input table");
        }
        colSpecs.add(columnSpec);
    }
    if (m_addCountStar.getBooleanValue()) {
        colSpecs.add(new DataColumnSpecCreator(manipulator.getValidColumnName(m_countStarColName.getStringValue()), LongCell.TYPE).createSpec());
    }
    final ColumnNamePolicy columnNamePolicy = ColumnNamePolicy.getPolicy4Label(m_columnNamePolicy.getStringValue());
    // Add aggregated columns
    for (int i = 0; i < m_aggregationFunction2Use.size(); i++) {
        final DBColumnAggregationFunctionRow row = m_aggregationFunction2Use.get(i);
        final String col = row.getColumnSpec().getName();
        final String methodId = row.getFunction().getId();
        if (inSpec.getColumnSpec(col) == null) {
            throw new InvalidSettingsException("Column '" + col + "' for aggregation function " + row.getFunction().getLabel() + " does not exist");
        }
        final DatabaseUtility databaseUtility = settings.getUtility();
        final DBAggregationFunction function = databaseUtility.getAggregationFunction(methodId);
        // Get type of column after aggregation
        final DataType type = function.getType(inSpec.getColumnSpec(col).getType());
        colSpecs.add(new DataColumnSpecCreator(manipulator.getValidColumnName(generateColumnName(columnNamePolicy, row)), type).createSpec());
    }
    return new DataTableSpec(colSpecs.toArray(new DataColumnSpec[colSpecs.size()]));
}

Example 19 with DatabaseQueryConnectionSettings

use of org.knime.core.node.port.database.DatabaseQueryConnectionSettings in project knime-core by knime.

the class DBQueryNodeModel method execute.

/**
 * {@inheritDoc}
 */
@Override
protected final PortObject[] execute(final PortObject[] inData, final ExecutionContext exec) throws CanceledExecutionException, Exception {
    DatabasePortObject dbObj = (DatabasePortObject) inData[0];
    CredentialsProvider cp = getCredentialsProvider();
    DatabaseQueryConnectionSettings conn = dbObj.getConnectionSettings(cp);
    String newQuery = createQuery(conn.getQuery());
    conn = createDBQueryConnection(dbObj.getSpec(), newQuery);
    DBReader load = conn.getUtility().getReader(conn);
    DataTableSpec outSpec = load.getDataTableSpec(cp);
    DatabasePortObjectSpec dbSpec = new DatabasePortObjectSpec(outSpec, conn.createConnectionModel());
    DatabasePortObject outObj = new DatabasePortObject(dbSpec);
    return new PortObject[] { outObj };
}

Example 20 with DatabaseQueryConnectionSettings

use of org.knime.core.node.port.database.DatabaseQueryConnectionSettings in project knime-core by knime.

the class DBQueryNodeModel2 method execute.

/**
 * {@inheritDoc}
 */
@Override
protected final PortObject[] execute(final PortObject[] inData, final ExecutionContext exec) throws CanceledExecutionException, Exception {
    DatabasePortObject dbObj = (DatabasePortObject) inData[0];
    DatabaseQueryConnectionSettings conn = dbObj.getConnectionSettings(getCredentialsProvider());
    String newQuery = parseQuery(conn.getQuery());
    conn = createDBQueryConnection(dbObj.getSpec(), newQuery);
    DBReader load = conn.getUtility().getReader(conn);
    DataTableSpec outSpec = load.getDataTableSpec(getCredentialsProvider());
    DatabasePortObjectSpec dbSpec = new DatabasePortObjectSpec(outSpec, conn.createConnectionModel());
    DatabasePortObject outObj = new DatabasePortObject(dbSpec);
    return new PortObject[] { outObj };
}