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

Example 6 with DBReader

use of org.knime.core.node.port.database.reader.DBReader 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 7 with DBReader

use of org.knime.core.node.port.database.reader.DBReader 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 8 with DBReader

use of org.knime.core.node.port.database.reader.DBReader 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 };
}

Example 9 with DBReader

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

the class DBQueryNodeModel2 method configure.

/**
 * {@inheritDoc}
 */
@Override
protected PortObjectSpec[] configure(final PortObjectSpec[] inSpecs) throws InvalidSettingsException {
    DatabasePortObjectSpec spec = (DatabasePortObjectSpec) inSpecs[0];
    DatabaseQueryConnectionSettings conn = spec.getConnectionSettings(getCredentialsProvider());
    String newQuery = parseQuery(conn.getQuery());
    conn = createDBQueryConnection(spec, newQuery);
    if (!conn.getRetrieveMetadataInConfigure()) {
        return new PortObjectSpec[1];
    }
    try {
        DBReader reader = conn.getUtility().getReader(conn);
        DataTableSpec outSpec = reader.getDataTableSpec(getCredentialsProvider());
        DatabasePortObjectSpec dbSpec = new DatabasePortObjectSpec(outSpec, conn.createConnectionModel());
        return new PortObjectSpec[] { dbSpec };
    } catch (Throwable t) {
        throw new InvalidSettingsException(t);
    }
}

Example 10 with DBReader

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

the class DBReaderConnectionNodeModel method configure.

/**
 * {@inheritDoc}
 */
@Override
protected PortObjectSpec[] configure(final PortObjectSpec[] inSpecs) throws InvalidSettingsException {
    try {
        // DatabaseQueryConnectionSettings conn = m_load.getQueryConnection();
        if (m_conn == null) {
            throw new InvalidSettingsException("No database connection available.");
        }
        final DatabaseQueryConnectionSettings conn = new DatabaseQueryConnectionSettings(m_conn, parseQuery(m_conn.getQuery()));
        if (!conn.getRetrieveMetadataInConfigure()) {
            return new PortObjectSpec[1];
        }
        DBReader load = conn.getUtility().getReader(conn);
        DataTableSpec spec = load.getDataTableSpec(getCredentialsProvider());
        if (spec == null) {
            throw new InvalidSettingsException("No database connection available.");
        }
        DatabasePortObjectSpec dbSpec = new DatabasePortObjectSpec(spec, conn.createConnectionModel());
        return new PortObjectSpec[] { dbSpec };
    } catch (InvalidSettingsException ise) {
        throw ise;
    } catch (Throwable t) {
        throw new InvalidSettingsException(t);
    }
}