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

Example 46 with DatabaseQueryConnectionSettings

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

the class DBReaderDialogPane method loadSettingsFrom.

/**
 * {@inheritDoc}
 */
@Override
protected void loadSettingsFrom(final NodeSettingsRO settings, final PortObjectSpec[] specs) throws NotConfigurableException {
    if (m_showConnectionPanel) {
        m_connectionPane.loadSettingsFrom(settings, specs, getCredentialsProvider());
    }
    DatabaseQueryConnectionSettings s = new DatabaseQueryConnectionSettings();
    try {
        s.loadValidatedConnection(settings, getCredentialsProvider());
    } catch (InvalidSettingsException ex) {
    // use settings as they are
    }
    // statement
    String statement = s.getQuery();
    m_statmnt.setText(statement == null ? "SELECT * FROM " + DatabaseQueryConnectionSettings.TABLE_PLACEHOLDER : statement);
    // select the table placeholder statement for easier replacements
    selectPlaceHolder(m_statmnt, DatabaseQueryConnectionSettings.TABLE_PLACEHOLDER);
    // update list of flow/workflow variables
    m_listModelVars.removeAllElements();
    for (Map.Entry<String, FlowVariable> e : getAvailableFlowVariables().entrySet()) {
        m_listModelVars.addElement(e.getValue());
    }
    // read execute without configure checkbox
    if (runWithoutConfigure()) {
        m_configureBox.setSelected(!s.getValidateQuery());
    }
    m_upstreamConnectionSettings = null;
    for (PortObjectSpec pos : specs) {
        if (pos instanceof DatabaseConnectionPortObjectSpec) {
            try {
                m_upstreamConnectionSettings = ((DatabaseConnectionPortObjectSpec) pos).getConnectionSettings(getCredentialsProvider());
            } catch (InvalidSettingsException ex) {
                LOGGER.warn("Could not load database connection from upstream node: " + ex.getMessage(), ex);
            }
        }
    }
    if (m_upstreamConnectionSettings != null || !m_showConnectionPanel) {
        m_connectionPane.setVisible(false);
    } else {
        m_connectionPane.setVisible(true);
    }
}

Example 47 with DatabaseQueryConnectionSettings

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

the class DBReaderDialogPane method saveSettingsTo.

/**
 * {@inheritDoc}
 */
@Override
protected void saveSettingsTo(final NodeSettingsWO settings) throws InvalidSettingsException {
    DatabaseQueryConnectionSettings s = new DatabaseQueryConnectionSettings();
    s.setQuery(m_statmnt.getText().trim());
    if (runWithoutConfigure()) {
        s.setValidateQuery(!m_configureBox.isSelected());
    }
    s.saveConnection(settings);
    if (m_showConnectionPanel) {
        m_connectionPane.saveSettingsTo(settings, getCredentialsProvider());
    }
}

Example 48 with DatabaseQueryConnectionSettings

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

the class DBReaderImpl method createTable.

/**
 * Called from the database port to read the first n-number of rows.
 *
 * @param useDbRowId <code>true</code> if the KNIME row id should based on the db row id
 * @param cachedNoRows number of rows cached for data preview
 * @param cp {@link CredentialsProvider} providing user/password
 * @return buffered data table read from database
 * @throws SQLException if the connection could not be opened
 */
DataTable createTable(final boolean useDbRowId, final int cachedNoRows, final CredentialsProvider cp) throws SQLException {
    if (m_blobFactory == null) {
        m_blobFactory = new BinaryObjectCellFactory();
    }
    // retrieve connection
    // final Connection conn = initConnection(cp);
    final DatabaseQueryConnectionSettings dbConn = getQueryConnection();
    // synchronized (dbConn.syncConnection(conn)) {
    return dbConn.execute(cp, conn -> {
        // remember auto-commit flag
        final boolean autoCommit = conn.getAutoCommit();
        final Statement stmt = initStatement(cp, conn);
        try {
            final String[] oQueries = dbConn.getQuery().split(SQL_QUERY_SEPARATOR);
            if (cachedNoRows < 0) {
                int fetchsize = (DatabaseConnectionSettings.FETCH_SIZE != null) ? DatabaseConnectionSettings.FETCH_SIZE : -1;
                dbConn.getUtility().getStatementManipulator().setFetchSize(stmt, fetchsize);
            } else {
                final int hashAlias = System.identityHashCode(this);
                final int selectIdx = oQueries.length - 1;
                // replace last element in statement(s) with wrapped SQL
                oQueries[selectIdx] = "SELECT * FROM (" + oQueries[selectIdx] + ") table_" + hashAlias;
                try {
                    // bugfix 2925: may fail, e.g. on sqlite
                    stmt.setMaxRows(cachedNoRows);
                } catch (SQLException ex) {
                    Throwable cause = ExceptionUtils.getRootCause(ex);
                    if (cause == null) {
                        cause = ex;
                    }
                    LOGGER.warn("Can't set max rows on statement, reason: " + cause.getMessage(), ex);
                }
            }
            // execute all except the last query
            for (int i = 0; i < oQueries.length - 1; i++) {
                LOGGER.debug("Executing SQL statement as execute: " + oQueries[i]);
                stmt.execute(oQueries[i]);
            }
            final String lastQuery = oQueries[oQueries.length - 1];
            LOGGER.debug("Executing SQL statement as executeQuery: " + lastQuery);
            final ResultSet result = stmt.executeQuery(lastQuery);
            LOGGER.debug("Reading meta data from database ResultSet...");
            m_spec = createTableSpec(result.getMetaData());
            LOGGER.debug("Parsing database ResultSet...");
            // final DBRowIterator dbIt = createRowIterator(useDbRowId, result);
            final RowIterator it = createDBRowIterator(m_spec, dbConn, m_blobFactory, useDbRowId, result);
            DataContainer buf = new DataContainer(m_spec);
            while (it.hasNext()) {
                buf.addRowToTable(it.next());
            }
            buf.close();
            return buf.getTable();
        } finally {
            if (stmt != null) {
                if (!conn.getAutoCommit()) {
                    conn.commit();
                }
                DatabaseConnectionSettings.setAutoCommit(conn, autoCommit);
                stmt.close();
            }
        }
    });
}

Example 49 with DatabaseQueryConnectionSettings

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

the class DBReaderImpl method loopTable.

/**
 * @since 3.2
 */
@SuppressWarnings("resource")
@Override
public BufferedDataTableRowOutput loopTable(final ExecutionContext exec, final CredentialsProvider cp, final RowInput data, final long rowCount, final boolean failIfException, final boolean appendInputColumns, final boolean includeEmptyResults, final boolean retainAllColumns, final String... columns) throws Exception {
    if (m_blobFactory == null) {
        m_blobFactory = new BinaryObjectCellFactory();
    }
    final DatabaseQueryConnectionSettings dbConn = getQueryConnection();
    return getQueryConnection().execute(cp, conn -> {
        /* Get the selected timezone */
        final TimeZone timezone = dbConn.getTimeZone();
        /* Get the input table spec */
        final DataTableSpec inSpec = data.getDataTableSpec();
        /* Create PreparedStatement */
        final String query = dbConn.getQuery();
        LOGGER.debug("Executing SQL preparedStatement as execute: " + query);
        /* Initialize the error table */
        final UniqueNameGenerator errorGenerator = new UniqueNameGenerator(inSpec);
        final DataColumnSpec errorColSpec = errorGenerator.newColumn(DEF_ERROR_COL_NAME, StringCell.TYPE);
        final DataTableSpec errorSpec = new DataTableSpec(inSpec, new DataTableSpec(errorColSpec));
        m_errorContainer = exec.createDataContainer(errorSpec);
        DataTableSpec dbSpec = new DataTableSpec();
        BufferedDataTableRowOutput output = null;
        exec.setMessage("Start reading rows from database...");
        try (final PreparedStatement stmt = conn.prepareStatement(query)) {
            long inDataCounter = 1;
            long rowIdCounter = 0;
            DataRow row;
            while ((row = data.poll()) != null) {
                exec.checkCanceled();
                if (rowCount > 0) {
                    exec.setProgress(1.0 * inDataCounter / rowCount, "Row " + "#" + inDataCounter + " of " + rowCount);
                } else {
                    exec.setProgress("Writing Row " + "#" + inDataCounter);
                }
                final DataCell[] inCells = new DataCell[columns.length];
                for (int i = 0; i < columns.length; i++) {
                    final int dbIdx = i + 1;
                    final int colIdx = inSpec.findColumnIndex(columns[i]);
                    final DataColumnSpec colSpec = inSpec.getColumnSpec(colIdx);
                    inCells[i] = row.getCell(colIdx);
                    fillStatement(stmt, dbIdx, colSpec, inCells[i], timezone, null);
                }
                try (final ResultSet result = stmt.executeQuery()) {
                    /* In the first iteration, create the out DataTableSpec and BufferedDataTableRowOutput */
                    if (output == null) {
                        dbSpec = createTableSpec(result.getMetaData());
                        if (appendInputColumns) {
                            // Create out DataTableSpec for input table
                            final DataTableSpec newInSpec;
                            if (retainAllColumns) {
                                newInSpec = inSpec;
                            } else {
                                final DataColumnSpec[] inColSpecs = new DataColumnSpec[columns.length];
                                for (int i = 0; i < inColSpecs.length; i++) {
                                    inColSpecs[i] = inSpec.getColumnSpec(columns[i]);
                                }
                                newInSpec = new DataTableSpec(inColSpecs);
                            }
                            // Create DataTableSpec for database columns, rename if necessary
                            final UniqueNameGenerator generator = new UniqueNameGenerator(newInSpec);
                            final DataColumnSpec[] dbColSpecs = new DataColumnSpec[dbSpec.getNumColumns()];
                            for (int i = 0; i < dbColSpecs.length; i++) {
                                final DataColumnSpec colSpec = dbSpec.getColumnSpec(i);
                                dbColSpecs[i] = generator.newColumn(colSpec.getName(), colSpec.getType());
                            }
                            dbSpec = new DataTableSpec(dbColSpecs);
                            m_spec = new DataTableSpec(newInSpec, dbSpec);
                        } else {
                            m_spec = dbSpec;
                        }
                        output = new BufferedDataTableRowOutput(exec.createDataContainer(m_spec));
                    }
                    /* Iterate over the result of the database query and put it into the output table*/
                    final RowIterator dbRowIterator = createDBRowIterator(dbSpec, dbConn, m_blobFactory, false, result, rowIdCounter);
                    boolean hasDbRow = false;
                    while (dbRowIterator.hasNext()) {
                        hasDbRow = true;
                        final DataRow dbRow = dbRowIterator.next();
                        if (appendInputColumns) {
                            final DataRow inRow;
                            if (retainAllColumns) {
                                inRow = new DefaultRow(dbRow.getKey(), row);
                            } else {
                                inRow = new DefaultRow(dbRow.getKey(), inCells);
                            }
                            final JoinedRow joinedRow = new JoinedRow(inRow, dbRow);
                            output.push(joinedRow);
                        } else {
                            output.push(dbRow);
                        }
                        rowIdCounter++;
                    }
                    /* Append columns using MissingCell if no result is returned */
                    if (!hasDbRow && appendInputColumns && includeEmptyResults) {
                        final DataCell[] cells = new DataCell[dbSpec.getNumColumns()];
                        Arrays.fill(cells, DataType.getMissingCell());
                        final RowKey rowKey = RowKey.createRowKey(rowIdCounter);
                        final DataRow emptyDbRows = new DefaultRow(rowKey, cells);
                        final DataRow inRow;
                        if (retainAllColumns) {
                            inRow = new DefaultRow(rowKey, row);
                        } else {
                            inRow = new DefaultRow(rowKey, inCells);
                        }
                        final JoinedRow joinedRow = new JoinedRow(inRow, emptyDbRows);
                        output.push(joinedRow);
                        rowIdCounter++;
                    }
                    inDataCounter++;
                } catch (SQLException ex) {
                    LOGGER.debug("SQLException: " + ex.getMessage());
                    if (!failIfException) {
                        if (output == null) {
                            throw new SQLException(ex);
                        }
                        final AppendedColumnRow appendedRow = new AppendedColumnRow(row, new StringCell(ex.getMessage()));
                        m_errorContainer.addRowToTable(appendedRow);
                    } else {
                        throw new SQLException(ex);
                    }
                }
            }
        } finally {
            data.close();
            if (output == null) {
                output = new BufferedDataTableRowOutput(exec.createDataContainer(inSpec));
            }
            output.close();
            if (m_errorContainer != null) {
                m_errorContainer.close();
            }
        }
        return output;
    });
}