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

Example 26 with DatabaseQueryConnectionSettings

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

the class DBColumnFilterNodeModel 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());
    ColumnRearranger colre = new ColumnRearranger(dbObj.getSpec().getDataTableSpec());
    colre.keepOnly(m_filter.getIncludeList().toArray(new String[0]));
    final DataTableSpec resultSpec = colre.createSpec();
    final String newQuery = createQuery(conn, resultSpec);
    conn = createDBQueryConnection(dbObj.getSpec(), newQuery);
    DatabasePortObjectSpec outSpec = new DatabasePortObjectSpec(resultSpec, conn.createConnectionModel());
    DatabasePortObject outObj = new DatabasePortObject(outSpec);
    return new PortObject[] { outObj };
}

Example 27 with DatabaseQueryConnectionSettings

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

the class DBConnectionWriterNodeModel method configure.

/**
 * {@inheritDoc}
 */
@Override
protected PortObjectSpec[] configure(final PortObjectSpec[] inSpecs) throws InvalidSettingsException {
    if (inSpecs == null || inSpecs.length < 1 || inSpecs[0] == null) {
        throw new InvalidSettingsException("No input available");
    }
    // check table name
    final String table = m_tableName.getStringValue();
    if (table == null || table.trim().isEmpty()) {
        throw new InvalidSettingsException("Configure node and enter a valid table name.");
    }
    DatabasePortObjectSpec spec = (DatabasePortObjectSpec) inSpecs[0];
    try {
        final DatabaseQueryConnectionSettings conn = spec.getConnectionSettings(getCredentialsProvider());
        if (conn.getRetrieveMetadataInConfigure()) {
            conn.execute(getCredentialsProvider(), c -> {
                return c != null;
            });
        }
    } catch (InvalidSettingsException ise) {
        throw ise;
    } catch (Throwable t) {
        throw new InvalidSettingsException(t);
    }
    setWarningMessage("Existing table \"" + table + "\" will be dropped!");
    return new DataTableSpec[0];
}

Example 28 with DatabaseQueryConnectionSettings

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

the class DBConnectionWriterNodeModel method execute.

/**
 * {@inheritDoc}
 */
@Override
protected PortObject[] execute(final PortObject[] inData, final ExecutionContext exec) throws CanceledExecutionException, Exception {
    DatabasePortObject dbObj = (DatabasePortObject) inData[0];
    exec.setProgress("Opening database connection...");
    String tableName = m_tableName.getStringValue();
    DatabaseQueryConnectionSettings conn = dbObj.getConnectionSettings(getCredentialsProvider());
    CredentialsProvider cp = getCredentialsProvider();
    final StatementManipulator statementManipulator = conn.getUtility().getStatementManipulator();
    try {
        // use the statement manipulator to create the drop table statement
        conn.execute(statementManipulator.dropTable(tableName, false), cp);
    } catch (Exception e) {
    // suppress exception thrown when table does not exist in database
    }
    String[] stmts = statementManipulator.createTableAsSelect(tableName, conn.getQuery());
    for (final String stmt : stmts) {
        conn.execute(stmt, cp);
    }
    return new BufferedDataTable[0];
}

Example 29 with DatabaseQueryConnectionSettings

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

the class DBConnectionNodeModel method execute.

/**
 * {@inheritDoc}
 */
@Override
protected PortObject[] execute(final PortObject[] inData, final ExecutionContext exec) throws CanceledExecutionException, Exception {
    exec.setProgress("Opening database connection...");
    DatabasePortObject dbObj = (DatabasePortObject) inData[0];
    DatabaseQueryConnectionSettings conn = dbObj.getConnectionSettings(getCredentialsProvider());
    final DBReader reader = conn.getUtility().getReader(conn);
    // final DatabaseReaderConnection load = new DatabaseReaderConnection(conn);
    exec.setProgress("Reading data from database...");
    CredentialsProvider cp = getCredentialsProvider();
    return new BufferedDataTable[] { reader.createTable(exec, cp, m_useDbRowId.getBooleanValue()) };
}

Example 30 with DatabaseQueryConnectionSettings

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

the class DBJoinerNodeModel method createDbOutSpec.

/**
 * @param inSpecLeft Spec of the left database object
 * @param inSpecRight Spec of the right database object
 * @return Spec of the output database object
 * @throws InvalidSettingsException If the current settings are invalid
 */
private DatabasePortObjectSpec createDbOutSpec(final DatabasePortObjectSpec inSpecLeft, final DatabasePortObjectSpec inSpecRight) throws InvalidSettingsException {
    DatabaseQueryConnectionSettings connectionLeft = inSpecLeft.getConnectionSettings(getCredentialsProvider());
    DatabaseQueryConnectionSettings connectionRight = inSpecRight.getConnectionSettings(getCredentialsProvider());
    StatementManipulator statementManipulator = connectionLeft.getUtility().getStatementManipulator();
    // Check first if connections are equal, otherwise abort
    checkIfDBConnectionsAreEqual(connectionLeft, connectionRight);
    // Calculate output columns
    Pair<Map<String, String>, Map<String, String>> columnMaps = createOutputColumnMaps(inSpecLeft.getDataTableSpec(), inSpecRight.getDataTableSpec(), statementManipulator);
    if (columnMaps.getFirst().size() + columnMaps.getSecond().size() < 1) {
        throw new InvalidSettingsException("No column selected");
    }
    // Create output query
    String newQuery = createQuery(connectionLeft.getQuery(), connectionRight.getQuery(), statementManipulator, columnMaps);
    // Create output spec
    DataTableSpec tableSpec = createOutSpec(inSpecLeft.getDataTableSpec(), inSpecRight.getDataTableSpec(), columnMaps, statementManipulator);
    DatabaseQueryConnectionSettings connection = createDBQueryConnection(inSpecLeft, newQuery);
    return new DatabasePortObjectSpec(tableSpec, connection.createConnectionModel());
}