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

Example 1 with DatabaseConnectionPortObject

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

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

Example 3 with DatabaseConnectionPortObject

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

the class DBDeleteRowsNodeModel 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[] 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;
    }
    // DELETE rows
    final int[] deleteStatus = new int[inTable.getRowCount()];
    final DBWriter dbWriter = connSettings.getUtility().getWriter(connSettings);
    final String errMsg = dbWriter.deleteRows(null, m_tableName, inTable, whereIncludes, deleteStatus, exec, getCredentialsProvider(), m_batchSize);
    // set warning message generated during deleting rows
    if (errMsg != null) {
        setWarningMessage(errMsg);
    }
    // create out table with update column
    final ColumnRearranger colre = createColumnRearranger(inTable.getSpec(), deleteStatus);
    final BufferedDataTable outTable = exec.createColumnRearrangeTable(inTable, colre, exec.createSubProgress(1.0));
    return new BufferedDataTable[] { outTable };
}

Example 4 with DatabaseConnectionPortObject

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

the class DBWriterNodeModel method execute.

/**
 * {@inheritDoc}
 */
@Override
protected PortObject[] execute(final PortObject[] inData, final ExecutionContext exec) throws CanceledExecutionException, Exception {
    exec.setProgress("Opening database connection to write data...");
    DatabaseConnectionSettings connSettings;
    if ((inData.length > 1) && (inData[1] instanceof DatabaseConnectionPortObject)) {
        connSettings = ((DatabaseConnectionPortObject) inData[1]).getConnectionSettings(getCredentialsProvider());
    } else {
        connSettings = m_conn;
    }
    DBWriter writer = connSettings.getUtility().getWriter(connSettings);
    BufferedDataTable inputTable = (BufferedDataTable) inData[0];
    DataTableRowInput rowInput = new DataTableRowInput(inputTable);
    // write entire data
    final String error = writer.writeData(m_tableName, rowInput, inputTable.size(), m_append, exec, m_types, getCredentialsProvider(), m_batchSize, m_insertNullForMissingCols, m_failOnError);
    // set error message generated during writing rows
    if (error != null) {
        super.setWarningMessage(error);
    }
    return new BufferedDataTable[0];
}

Example 5 with DatabaseConnectionPortObject

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

the class DBWriterNodeModel method createStreamableOperator.

/**
 * {@inheritDoc}
 */
@Override
public StreamableOperator createStreamableOperator(final PartitionInfo partitionInfo, final PortObjectSpec[] inSpecs) throws InvalidSettingsException {
    return new StreamableOperator() {

        @Override
        public void runFinal(final PortInput[] inputs, final PortOutput[] outputs, final ExecutionContext exec) throws Exception {
            exec.setProgress("Opening database connection to write data...");
            DatabaseConnectionSettings connSettings;
            PortObject portObj = ((PortObjectInput) inputs[1]).getPortObject();
            if (portObj != null && (portObj instanceof DatabaseConnectionPortObject)) {
                connSettings = ((DatabaseConnectionPortObject) portObj).getConnectionSettings(getCredentialsProvider());
            } else {
                connSettings = m_conn;
            }
            DBWriter writer = connSettings.getUtility().getWriter(connSettings);
            // write entire data
            m_errorMessage = writer.writeData(m_tableName, (RowInput) inputs[0], -1, m_append, exec, m_types, getCredentialsProvider(), m_batchSize, m_insertNullForMissingCols);
        }
    };
}