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

Example 6 with DatabaseConnectionPortObject

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

the class DBReaderNodeModel method loadConnectionSettings.

/**
 * @param dbPortObject
 * @return
 * @throws InvalidSettingsException
 */
protected DBReader loadConnectionSettings(final PortObject dbPortObject) throws InvalidSettingsException {
    String query = parseQuery(m_settings.getQuery());
    DatabaseQueryConnectionSettings connSettings;
    if ((dbPortObject instanceof DatabaseConnectionPortObject)) {
        DatabaseConnectionPortObject dbObj = (DatabaseConnectionPortObject) dbPortObject;
        connSettings = new DatabaseQueryConnectionSettings(dbObj.getConnectionSettings(getCredentialsProvider()), query);
    } else {
        connSettings = new DatabaseQueryConnectionSettings(m_settings, query);
    }
    final DBReader load = connSettings.getUtility().getReader(new DatabaseQueryConnectionSettings(connSettings, query));
    return load;
}

Example 7 with DatabaseConnectionPortObject

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

the class DBSQLExecutorNodeModel method execute.

/**
 * {@inheritDoc}
 */
@Override
protected final PortObject[] execute(final PortObject[] inData, final ExecutionContext exec) throws CanceledExecutionException, Exception {
    DatabaseConnectionPortObject dbObj = (DatabaseConnectionPortObject) inData[0];
    DatabaseConnectionSettings conn = dbObj.getConnectionSettings(getCredentialsProvider());
    String[] statements = parseStatementAndReplaceVariables().split(DBReader.SQL_QUERY_SEPARATOR);
    final double max = statements.length;
    int i = 0;
    for (String statement : statements) {
        exec.checkCanceled();
        statement = statement.trim();
        exec.setProgress(i++ / max, "Executing '" + statement + "'");
        if (!statement.isEmpty()) {
            conn.execute(statement, getCredentialsProvider());
        }
    }
    return inData;
}

Example 8 with DatabaseConnectionPortObject

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

the class DBUpdateNodeModel method execute.

/**
 * {@inheritDoc}
 */
@Override
protected PortObject[] execute(final PortObject[] inData, final ExecutionContext exec) throws Exception {
    final BufferedDataTable inTable = (BufferedDataTable) inData[0];
    final DataTableSpec inSpec = inTable.getSpec();
    final String[] setIncludes = m_configSET.applyTo(inSpec).getIncludes();
    final String[] whereIncludes = m_configWHERE.applyTo(inSpec).getIncludes();
    DatabaseConnectionSettings connSettings;
    if ((inData.length > 1) && (inData[1] instanceof DatabaseConnectionPortObject)) {
        connSettings = ((DatabaseConnectionPortObject) inData[1]).getConnectionSettings(getCredentialsProvider());
    } else {
        connSettings = m_loginConfig;
    }
    // UPDATE table
    final int[] updateStatus = new int[inTable.getRowCount()];
    final DBWriter dbWriter = connSettings.getUtility().getWriter(connSettings);
    final String errMsg = dbWriter.updateTable(null, m_tableName, inTable, setIncludes, whereIncludes, updateStatus, exec, getCredentialsProvider(), m_batchSize);
    // set warning message generated during updating rows
    if (errMsg != null) {
        setWarningMessage(errMsg);
    }
    // create out table with update column
    final ColumnRearranger colre = createColumnRearranger(inTable.getSpec(), updateStatus);
    final BufferedDataTable outTable = exec.createColumnRearrangeTable(inTable, colre, exec.createSubProgress(1.0));
    return new BufferedDataTable[] { outTable };
}

Example 9 with DatabaseConnectionPortObject

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

the class DatabaseLoopingNodeModel method execute.

/**
 * {@inheritDoc}
 */
@Override
protected PortObject[] execute(final PortObject[] inData, final ExecutionContext exec) throws Exception {
    final BufferedDataTable inputTable = (BufferedDataTable) inData[0];
    final long rowCount = inputTable.size();
    final String column = m_columnModel.getStringValue();
    final DataTableSpec spec = inputTable.getDataTableSpec();
    final int colIdx = spec.findColumnIndex(column);
    if (colIdx < 0) {
        throw new InvalidSettingsException("Column " + column + " not found in input table.");
    }
    final Set<DataCell> values = new HashSet<>();
    BufferedDataContainer buf = null;
    final String oQuery = getQuery();
    final Collection<DataCell> curSet = new LinkedHashSet<>();
    final DBReader load = loadConnectionSettings(inData[getNrInPorts() - 1]);
    try {
        final int noValues = m_noValues.getIntValue();
        MutableInteger rowCnt = new MutableInteger(0);
        for (Iterator<DataRow> it = inputTable.iterator(); it.hasNext(); ) {
            exec.checkCanceled();
            DataCell cell = it.next().getCell(colIdx);
            if (values.contains(cell) && !it.hasNext() && curSet.isEmpty()) {
                continue;
            }
            values.add(cell);
            curSet.add(cell);
            if (curSet.size() == noValues || !it.hasNext()) {
                StringBuilder queryValues = new StringBuilder();
                for (DataCell v : curSet) {
                    if (queryValues.length() > 0) {
                        queryValues.append("','");
                    }
                    queryValues.append(v.toString());
                }
                String newQuery = parseQuery(oQuery.replaceAll(IN_PLACE_HOLDER, queryValues.toString()));
                load.updateQuery(newQuery);
                exec.setProgress(values.size() * (double) noValues / rowCount, "Selecting all values \"" + queryValues + "\"...");
                final BufferedDataTable table = getResultTable(exec, inData, load);
                if (buf == null) {
                    DataTableSpec resSpec = table.getDataTableSpec();
                    buf = exec.createDataContainer(createSpec(resSpec, spec.getColumnSpec(column)));
                }
                if (m_aggByRow.getBooleanValue()) {
                    aggregate(table, rowCnt, buf, CollectionCellFactory.createListCell(curSet));
                } else {
                    notAggregate(table, rowCnt, buf, CollectionCellFactory.createListCell(curSet));
                }
                curSet.clear();
            }
        }
        if (buf == null) {
            // create empty dummy container with spec generated during #configure
            final PortObjectSpec[] inSpec;
            if ((inData.length > 1) && (inData[1] instanceof DatabaseConnectionPortObject)) {
                DatabaseConnectionPortObject dbPort = (DatabaseConnectionPortObject) inData[1];
                inSpec = new PortObjectSpec[] { inputTable.getSpec(), dbPort.getSpec() };
            } else {
                inSpec = new PortObjectSpec[] { inputTable.getSpec() };
            }
            final String newQuery = createDummyValueQuery(spec, colIdx, oQuery);
            setQuery(newQuery);
            final DataTableSpec resultSpec = getResultSpec(inSpec);
            final DataTableSpec outSpec = createSpec(resultSpec, spec.getColumnSpec(column));
            buf = exec.createDataContainer(outSpec);
        }
        buf.close();
    } catch (CanceledExecutionException cee) {
        throw cee;
    } catch (Exception e) {
        setLastSpec(null);
        throw e;
    } finally {
        // reset query to original
        setQuery(oQuery);
    }
    final BufferedDataTable resultTable = buf.getTable();
    setLastSpec(resultTable.getDataTableSpec());
    return new BufferedDataTable[] { resultTable };
}

Example 10 with DatabaseConnectionPortObject

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