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

Example 6 with DatabaseConnectionSettings

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

the class DBUpdateNodeModel method configure.

/**
 * {@inheritDoc}
 */
@Override
protected PortObjectSpec[] configure(final PortObjectSpec[] inSpecs) throws InvalidSettingsException {
    DataTableSpec tableSpec = (DataTableSpec) inSpecs[0];
    // check optional incoming connection
    if ((inSpecs.length > 1) && (inSpecs[1] instanceof DatabaseConnectionPortObjectSpec)) {
        DatabaseConnectionSettings connSettings = ((DatabaseConnectionPortObjectSpec) inSpecs[1]).getConnectionSettings(getCredentialsProvider());
        if ((connSettings.getJDBCUrl() == null) || connSettings.getJDBCUrl().isEmpty() || (connSettings.getDriver() == null) || connSettings.getDriver().isEmpty()) {
            throw new InvalidSettingsException("No valid database connection provided via second input port");
        }
        if (!connSettings.getUtility().supportsUpdate()) {
            throw new InvalidSettingsException("Connected database does not support update operations");
        }
    } else {
        if (!m_loginConfig.getUtility().supportsUpdate()) {
            throw new InvalidSettingsException("Selected database does not support update operations");
        }
    }
    // check table name
    if ((m_tableName == null) || m_tableName.trim().isEmpty()) {
        throw new InvalidSettingsException("Configure node and enter a valid table name.");
    }
    // check SET and WHERE for overlapping columns
    final List<String> setIncludes = new ArrayList<>(Arrays.asList(m_configSET.applyTo(tableSpec).getIncludes()));
    if (setIncludes.isEmpty()) {
        throw new InvalidSettingsException("No SET column selected.");
    }
    final List<String> whereIncludes = new ArrayList<>(Arrays.asList(m_configWHERE.applyTo(tableSpec).getIncludes()));
    if (whereIncludes.isEmpty()) {
        throw new InvalidSettingsException("No WHERE column selected.");
    }
    if (!setIncludes.retainAll(whereIncludes)) {
        throw new InvalidSettingsException("SET and WHERE column list contain duplicate columns.");
    }
    // forward input spec with one additional update (=int) column
    final ColumnRearranger colre = createColumnRearranger(tableSpec, new int[] {});
    return new DataTableSpec[] { colre.createSpec() };
}

Example 7 with DatabaseConnectionSettings

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

the class DBUpdateNodeModel method validateSettings.

/**
 * {@inheritDoc}
 */
@Override
protected void validateSettings(final NodeSettingsRO settings) throws InvalidSettingsException {
    // validate login settings
    new DatabaseConnectionSettings(settings, getCredentialsProvider());
    // validate table name
    final String tableName = settings.getString(KEY_TABLE_NAME);
    if (tableName == null || tableName.trim().isEmpty()) {
        throw new InvalidSettingsException("Configure node and enter a valid table name.");
    }
    // validate SET columns
    final DataColumnSpecFilterConfiguration confSET = new DataColumnSpecFilterConfiguration(KEY_SET_FILTER_COLUMN);
    confSET.loadConfigurationInModel(settings);
    // validate WHERE columns
    final DataColumnSpecFilterConfiguration confWHERE = new DataColumnSpecFilterConfiguration(KEY_WHERE_FILTER_COLUMN);
    confWHERE.loadConfigurationInModel(settings);
    // validate batch size
    final int batchSize = settings.getInt(KEY_BATCH_SIZE);
    if (batchSize <= 0) {
        throw new InvalidSettingsException("Batch size must be greater than 0, is " + batchSize);
    }
}

Example 8 with DatabaseConnectionSettings

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

the class SQLInjectNodeModel method execute.

/**
 * {@inheritDoc}
 */
@Override
protected PortObject[] execute(final PortObject[] inData, final ExecutionContext exec) throws Exception {
    exec.setMessage("Retrieving metadata from database");
    final String varName = m_flowVariableName.getStringValue();
    if (varName == null) {
        throw new InvalidSettingsException("No flow variable for the SQL statement selected");
    }
    final String sql = peekFlowVariableString(varName);
    if (sql == null) {
        throw new InvalidSettingsException("The selected flow variable is not assigned");
    }
    DatabaseConnectionPortObject dbIn = (DatabaseConnectionPortObject) inData[0];
    DatabaseConnectionSettings inSettings = dbIn.getConnectionSettings(getCredentialsProvider());
    // Attach the SQL query
    DatabaseQueryConnectionSettings outSettings = new DatabaseQueryConnectionSettings(inSettings, sql);
    // Probe the database to see how the result table looks like
    DBReader load = outSettings.getUtility().getReader(outSettings);
    DataTableSpec tableSpec = load.getDataTableSpec(getCredentialsProvider());
    DatabasePortObjectSpec outSpec = new DatabasePortObjectSpec(tableSpec, outSettings.createConnectionModel());
    return new PortObject[] { new DatabasePortObject(outSpec) };
}

Example 9 with DatabaseConnectionSettings

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

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

the class DBDropTableNodeModel method execute.

/**
 * {@inheritDoc}
 */
@Override
protected PortObject[] execute(final PortObject[] inObjects, final ExecutionContext exec) throws Exception {
    exec.setMessage("Droping table");
    final DatabaseConnectionPortObject incomingConnection = (DatabaseConnectionPortObject) inObjects[0];
    final CredentialsProvider cp = getCredentialsProvider();
    final DatabaseConnectionSettings connSettings = incomingConnection.getConnectionSettings(cp);
    final DatabaseUtility dbUtility = connSettings.getUtility();
    final StatementManipulator manipulator = dbUtility.getStatementManipulator();
    final String table2Drop = m_tableName.getStringValue();
    try {
        if (m_failIfNotExists.getBooleanValue() || connSettings.execute(cp, conn -> {
            return dbUtility.tableExists(conn, table2Drop);
        })) {
            connSettings.execute(manipulator.dropTable(table2Drop, m_cascade.getBooleanValue()), cp);
            exec.setMessage("Table " + table2Drop + " sucessful droped");
        } else {
            exec.setMessage("Table " + table2Drop + " does not exist in db");
        }
    } catch (SQLException ex) {
        Throwable cause = ExceptionUtils.getRootCause(ex);
        if (cause == null) {
            cause = ex;
        }
        throw new InvalidSettingsException("Error while validating drop statement: " + cause.getMessage(), ex);
    }
    return inObjects;
}