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

Example 21 with DatabaseConnectionSettings

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

the class DatabaseLoopingNodeModel method configure.

/**
 * {@inheritDoc}
 */
@Override
protected PortObjectSpec[] configure(final PortObjectSpec[] inSpecs) throws InvalidSettingsException {
    final DataTableSpec lastSpec = getLastSpec();
    if (lastSpec != null) {
        return new DataTableSpec[] { lastSpec };
    }
    final DataTableSpec tableSpec = (DataTableSpec) inSpecs[0];
    String column = m_columnModel.getStringValue();
    if (column == null) {
        throw new InvalidSettingsException("No column selected.");
    }
    final int colIdx = tableSpec.findColumnIndex(column);
    if (colIdx < 0) {
        throw new InvalidSettingsException("Column '" + column + "' not found in input data.");
    }
    if ((inSpecs.length > 1) && (inSpecs[1] instanceof DatabaseConnectionPortObjectSpec)) {
        DatabaseConnectionSettings connSettings = ((DatabaseConnectionPortObjectSpec) inSpecs[1]).getConnectionSettings(getCredentialsProvider());
        m_settings.setValidateQuery(connSettings.getRetrieveMetadataInConfigure());
    } else {
        m_settings.setValidateQuery(true);
    }
    if (!m_settings.getValidateQuery()) {
        setLastSpec(null);
        return new DataTableSpec[] { null };
    }
    final String oQuery = getQuery();
    PortObjectSpec[] spec = null;
    try {
        final String newQuery;
        newQuery = createDummyValueQuery(tableSpec, colIdx, oQuery);
        setQuery(newQuery);
        spec = new DataTableSpec[] { getResultSpec(inSpecs) };
    } catch (InvalidSettingsException e) {
        setLastSpec(null);
        throw e;
    } catch (SQLException ex) {
        setLastSpec(null);
        Throwable cause = ExceptionUtils.getRootCause(ex);
        if (cause == null) {
            cause = ex;
        }
        throw new InvalidSettingsException("Could not determine table spec from database query: " + cause.getMessage(), ex);
    } finally {
        setQuery(oQuery);
    }
    if (spec[0] == null) {
        return spec;
    } else {
        final DataTableSpec resultSpec = createSpec((DataTableSpec) spec[0], tableSpec.getColumnSpec(column));
        setLastSpec(resultSpec);
        return new DataTableSpec[] { resultSpec };
    }
}

Example 22 with DatabaseConnectionSettings

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

the class DBTableSelectorNodeModel method execute.

/**
 * {@inheritDoc}
 */
@Override
protected PortObject[] execute(final PortObject[] inObjects, final ExecutionContext exec) throws Exception {
    exec.setMessage("Retrieving metadata from database");
    DatabaseConnectionPortObject incomingConnection = (DatabaseConnectionPortObject) inObjects[0];
    DatabaseConnectionSettings connSettings = incomingConnection.getConnectionSettings(getCredentialsProvider());
    String sql = FlowVariableResolver.parse(m_settings.getQuery(), this);
    DatabaseQueryConnectionSettings querySettings = new DatabaseQueryConnectionSettings(connSettings, sql);
    DBReader conn = querySettings.getUtility().getReader(querySettings);
    try {
        DataTableSpec tableSpec = conn.getDataTableSpec(getCredentialsProvider());
        return new PortObject[] { new DatabasePortObject(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: " + cause.getMessage(), ex);
    }
}

Example 23 with DatabaseConnectionSettings

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

the class JDBCConnectorNodeModel method execute.

/**
 * {@inheritDoc}
 */
@Override
protected PortObject[] execute(final PortObject[] inObjects, final ExecutionContext exec) throws Exception {
    DatabaseConnectionSettings s = new DatabaseConnectionSettings(m_settings);
    // new behavior since 2.10
    s.setRowIdsStartWithZero(true);
    DatabaseConnectionPortObject dbPort = new DatabaseConnectionPortObject(new DatabaseConnectionPortObjectSpec(s));
    try {
        dbPort.getConnectionSettings(getCredentialsProvider()).execute(getCredentialsProvider(), conn -> {
            return conn != null;
        });
    } catch (SQLException ex) {
        Throwable cause = ExceptionUtils.getRootCause(ex);
        if (cause == null) {
            cause = ex;
        }
        if (cause instanceof ClassNotFoundException) {
            cause = new Exception("Could not find driver class: " + cause.getMessage());
        }
        throw new SQLException("Could not create connection to database: " + cause.getMessage(), ex);
    }
    return new PortObject[] { dbPort };
}

Example 24 with DatabaseConnectionSettings

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

the class DBTableCreatorNodeModel method configure.

/**
 * {@inheritDoc}
 */
@Override
protected PortObjectSpec[] configure(final PortObjectSpec[] inSpecs) throws InvalidSettingsException {
    if (inSpecs[0] == null && !(inSpecs[0] instanceof DatabaseConnectionPortObjectSpec)) {
        throw new InvalidSettingsException("No valid database connection available.");
    }
    final DatabaseConnectionPortObjectSpec dbSpec = (DatabaseConnectionPortObjectSpec) inSpecs[0];
    m_config.setTableSpec((DataTableSpec) inSpecs[1]);
    final boolean isColumnsEmpty = m_config.getColumns().isEmpty();
    if (m_config.getTableSpec() != null && (m_config.useDynamicSettings() || isColumnsEmpty)) {
        m_config.loadColumnSettingsFromTableSpec(m_config.getTableSpec());
        m_config.updateKeysWithDynamicSettings();
    }
    if (m_config.getTableSpec() == null && m_config.useDynamicSettings()) {
        throw new InvalidSettingsException("Dynamic settings enabled but no input table available.");
    }
    if (isColumnsEmpty) {
        throw new InvalidSettingsException("At least one column must be defined.");
    }
    final DatabaseConnectionSettings conn = dbSpec.getConnectionSettings(getCredentialsProvider());
    final DBTableCreator tableCreator = conn.getUtility().getTableCreator(m_config.getSchema(), getTableName(), m_config.isTempTable());
    final List<DBColumn> columns = m_config.getColumns();
    final List<DBKey> keys = m_config.getKeys();
    try {
        tableCreator.validateSettings(m_config.ifNotExists(), columns.toArray(new DBColumn[columns.size()]), keys.toArray(new DBKey[keys.size()]), m_config.getAdditionalOptions());
    } catch (Exception e) {
        throw new InvalidSettingsException(e.getMessage());
    }
    pushFlowVariables(tableCreator.getSchema(), tableCreator.getTableName());
    return new PortObjectSpec[] { dbSpec };
}

Example 25 with DatabaseConnectionSettings

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

the class DBTableCreatorNodeModel method execute.

/**
 * {@inheritDoc}
 */
@Override
protected PortObject[] execute(final PortObject[] inData, final ExecutionContext exec) throws Exception {
    exec.setMessage("Creating table");
    final DatabaseConnectionPortObject dbConn = (DatabaseConnectionPortObject) inData[0];
    final DatabaseConnectionSettings conn = dbConn.getConnectionSettings(getCredentialsProvider());
    final DBTableCreator tableCreator = conn.getUtility().getTableCreator(m_config.getSchema(), getTableName(), m_config.isTempTable());
    final List<DBColumn> columns = m_config.getColumns();
    final List<DBKey> keys = m_config.getKeys();
    tableCreator.createTable(conn, getCredentialsProvider(), m_config.ifNotExists(), columns.toArray(new DBColumn[columns.size()]), keys.toArray(new DBKey[keys.size()]), m_config.getAdditionalOptions());
    pushFlowVariables(tableCreator.getSchema(), tableCreator.getTableName());
    final String warning = tableCreator.getWarning();
    if (!StringUtils.isBlank(warning)) {
        setWarningMessage(warning);
    }
    return new PortObject[] { dbConn };
}