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

Example 16 with DBReader

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

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

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

the class DBApplyBinnerNodeModel method createDatabasePortObject.

private DatabasePortObject createDatabasePortObject(final DatabasePortObjectSpec inSpec, DatabaseQueryConnectionSettings connectionSettings, final PMMLPortObject pmmlPortObject) throws InvalidSettingsException {
    final StatementManipulator statementManipulator = connectionSettings.getUtility().getStatementManipulator();
    String newQuery = createQuery(connectionSettings.getQuery(), statementManipulator, inSpec.getDataTableSpec(), pmmlPortObject);
    connectionSettings = createDBQueryConnection(inSpec, newQuery);
    DatabaseQueryConnectionSettings querySettings = new DatabaseQueryConnectionSettings(connectionSettings, newQuery);
    DBReader reader = querySettings.getUtility().getReader(querySettings);
    try {
        DatabasePortObjectSpec databasePortObjectSpec = new DatabasePortObjectSpec(reader.getDataTableSpec(getCredentialsProvider()), connectionSettings.createConnectionModel());
        DatabasePortObject databasePortObject = new DatabasePortObject(databasePortObjectSpec);
        return databasePortObject;
    } catch (SQLException e) {
        throw new InvalidSettingsException("Failure during query generation. Error: " + e.getMessage());
    }
}

Example 19 with DBReader

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

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

the class ParameterizedDBQueryNodeModel method execute.

/**
 * {@inheritDoc}
 */
@Override
protected PortObject[] execute(final PortObject[] inData, final ExecutionContext exec) throws Exception {
    final BufferedDataTable inTable = (BufferedDataTable) inData[0];
    final DatabasePortObject dbObject = (DatabasePortObject) inData[1];
    DatabaseQueryConnectionSettings conn = dbObject.getConnectionSettings(getCredentialsProvider());
    final String newQuery = parseSQLStatement(inTable.getDataTableSpec(), conn.getQuery());
    conn = createDBQueryConnection(dbObject.getSpec(), newQuery);
    final DBReader reader = conn.getUtility().getReader(conn);
    final DataTableRowInput data = new DataTableRowInput(inTable);
    final BufferedDataTable outTable = reader.loopTable(exec, getCredentialsProvider(), data, inTable.size(), m_failIfExceptionModel.getBooleanValue(), m_appendInputColumnsModel.getBooleanValue(), m_includeEmptyResultsModel.getBooleanValue(), m_retainAllColumnsModel.getBooleanValue(), m_dataColumns.toArray(new String[m_dataColumns.size()])).getDataTable();
    final BufferedDataTable errorTable = reader.getErrorDataTable();
    return new BufferedDataTable[] { outTable, errorTable };
}