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

Example 1 with StatementManipulator

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

the class DBSamplingNodeModel method createDbOutSpec.

/**
 * @param inSpec Spec of the input database object
 * @param exec The {@link ExecutionMonitor}
 * @param checkRetrieveMetadata true if the retrieveMetadataInConfigure settings should be respected,
 *            <code>false</code> if the metadata should be retrieved in any case (for execute)
 * @return Spec of the output database object
 * @throws InvalidSettingsException if the current settings are invalid
 * @throws CanceledExecutionException if execution is canceled
 */
private DatabasePortObjectSpec createDbOutSpec(final DatabasePortObjectSpec inSpec, final ExecutionMonitor exec, final boolean checkRetrieveMetadata) throws InvalidSettingsException, CanceledExecutionException {
    final DatabaseQueryConnectionSettings connectionSettings = inSpec.getConnectionSettings(getCredentialsProvider());
    final StatementManipulator statementManipulator = connectionSettings.getUtility().getStatementManipulator();
    try {
        // Connection connection = connectionSettings.createConnection(getCredentialsProvider());
        final String newQuery = connectionSettings.execute(getCredentialsProvider(), conn -> {
            return createQuery(conn, connectionSettings.getQuery(), statementManipulator, inSpec.getDataTableSpec(), exec);
        });
        DatabaseQueryConnectionSettings resultSettings = createDBQueryConnection(inSpec, newQuery);
        DatabaseQueryConnectionSettings querySettings = new DatabaseQueryConnectionSettings(resultSettings, newQuery);
        DatabaseReaderConnection conn = new DatabaseReaderConnection(querySettings);
        DataTableSpec tableSpec;
        exec.setMessage("Retrieving result specification.");
        tableSpec = conn.getDataTableSpec(getCredentialsProvider());
        return new DatabasePortObjectSpec(tableSpec, resultSettings.createConnectionModel());
    } catch (SQLException e1) {
        throw new InvalidSettingsException("Failure during query generation. Error: " + e1.getMessage(), e1);
    }
}

Example 2 with StatementManipulator

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

the class DBPivotNodeModel method createDbOutSpec.

/**
 * @param inSpec Spec of the input database object
 * @param exec The {@link ExecutionMonitor}
 * @return Spec of the output database object
 * @throws InvalidSettingsException if the current settings are invalid
 * @throws CanceledExecutionException if execution is canceled
 */
private DatabasePortObjectSpec createDbOutSpec(final DatabasePortObjectSpec inSpec, final ExecutionMonitor exec) throws InvalidSettingsException, CanceledExecutionException {
    DatabaseQueryConnectionSettings connectionSettings = inSpec.getConnectionSettings(getCredentialsProvider());
    final StatementManipulator statementManipulator = connectionSettings.getUtility().getStatementManipulator();
    try {
        // Connection connection = connectionSettings.createConnection(getCredentialsProvider());
        String newQuery = createQuery(connectionSettings, inSpec.getDataTableSpec(), exec);
        connectionSettings = createDBQueryConnection(inSpec, newQuery);
        DatabaseQueryConnectionSettings querySettings = new DatabaseQueryConnectionSettings(connectionSettings, newQuery);
        DBReader reader = querySettings.getUtility().getReader(querySettings);
        DataTableSpec tableSpec;
        exec.setMessage("Retrieving result specification.");
        tableSpec = reader.getDataTableSpec(getCredentialsProvider());
        return new DatabasePortObjectSpec(tableSpec, connectionSettings.createConnectionModel());
    } catch (SQLException e1) {
        throw new InvalidSettingsException("Failure during query generation. Error: " + e1.getMessage(), e1);
    }
}

Example 3 with StatementManipulator

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

the class DBAutoBinnerNodeModel 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);
    DatabaseReaderConnection conn = new DatabaseReaderConnection(querySettings);
    DatabasePortObjectSpec databasePortObjectSpec = null;
    try {
        databasePortObjectSpec = new DatabasePortObjectSpec(conn.getDataTableSpec(getCredentialsProvider()), connectionSettings.createConnectionModel());
    } catch (SQLException e) {
        throw new InvalidSettingsException("Failure during query generation. Error: " + e.getMessage());
    }
    DatabasePortObject databasePortObject = new DatabasePortObject(databasePortObjectSpec);
    return databasePortObject;
}

Example 4 with StatementManipulator

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

the class DBNumericBinnerNodeModel method createDatabasePortObjectSpec.

private DatabasePortObjectSpec createDatabasePortObjectSpec(DatabaseQueryConnectionSettings connectionSettings, final DatabasePortObjectSpec inDatabasePortObjectSpec, final DBBinnerMaps binnerMaps) throws InvalidSettingsException, SQLException, BadPaddingException, IllegalBlockSizeException, InvalidKeyException, IOException {
    final StatementManipulator statementManipulator = connectionSettings.getUtility().getStatementManipulator();
    final String newQuery = createQuery(connectionSettings.getQuery(), statementManipulator, inDatabasePortObjectSpec.getDataTableSpec(), binnerMaps);
    connectionSettings = createDBQueryConnection(inDatabasePortObjectSpec, newQuery);
    final DatabaseQueryConnectionSettings querySettings = new DatabaseQueryConnectionSettings(connectionSettings, newQuery);
    DBReader reader = querySettings.getUtility().getReader(querySettings);
    DataTableSpec outDataTableSpec = reader.getDataTableSpec(getCredentialsProvider());
    DatabasePortObjectSpec outDatabasePortObjectSpec = new DatabasePortObjectSpec(outDataTableSpec, connectionSettings.createConnectionModel());
    return outDatabasePortObjectSpec;
}

Example 5 with StatementManipulator

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