Examples with DBCPService org.apache.nifi.dbcp.DBCPService used on opensource projects

Example 71 with DBCPService

use of org.apache.nifi.dbcp.DBCPService in project nifi by apache.

the class QueryDatabaseTableTest method testAddedRowsTwoTables.

@Test
public void testAddedRowsTwoTables() throws ClassNotFoundException, SQLException, InitializationException, IOException {
    // load test data to database
    final Connection con = ((DBCPService) runner.getControllerService("dbcp")).getConnection();
    Statement stmt = con.createStatement();
    try {
        stmt.execute("drop table TEST_QUERY_DB_TABLE");
    } catch (final SQLException sqle) {
    // Ignore this error, probably a "table does not exist" since Derby doesn't yet support DROP IF EXISTS [DERBY-4842]
    }
    try {
        stmt.execute("drop table TEST_QUERY_DB_TABLE2");
    } catch (final SQLException sqle) {
    // Ignore this error, probably a "table does not exist" since Derby doesn't yet support DROP IF EXISTS [DERBY-4842]
    }
    stmt.execute("create table TEST_QUERY_DB_TABLE (id integer not null, name varchar(100), scale float, created_on timestamp, bignum bigint default 0)");
    stmt.execute("insert into TEST_QUERY_DB_TABLE (id, name, scale, created_on) VALUES (0, 'Joe Smith', 1.0, '1962-09-23 03:23:34.234')");
    stmt.execute("insert into TEST_QUERY_DB_TABLE (id, name, scale, created_on) VALUES (1, 'Carrie Jones', 5.0, '2000-01-01 03:23:34.234')");
    stmt.execute("insert into TEST_QUERY_DB_TABLE (id, name, scale, created_on) VALUES (2, NULL, 2.0, '2010-01-01 00:00:00')");
    runner.setProperty(QueryDatabaseTable.TABLE_NAME, "TEST_QUERY_DB_TABLE");
    runner.setIncomingConnection(false);
    runner.setProperty(QueryDatabaseTable.MAX_VALUE_COLUMN_NAMES, "ID");
    runner.setProperty(QueryDatabaseTable.MAX_ROWS_PER_FLOW_FILE, "2");
    runner.run();
    runner.assertAllFlowFilesTransferred(QueryDatabaseTable.REL_SUCCESS, 2);
    MockFlowFile flowFile = runner.getFlowFilesForRelationship(QueryDatabaseTable.REL_SUCCESS).get(0);
    assertEquals("TEST_QUERY_DB_TABLE", flowFile.getAttribute(QueryDatabaseTable.RESULT_TABLENAME));
    assertEquals(flowFile.getAttribute("maxvalue.id"), "2");
    InputStream in = new ByteArrayInputStream(flowFile.toByteArray());
    runner.setProperty(QueryDatabaseTable.FETCH_SIZE, "2");
    assertEquals(2, getNumberOfRecordsFromStream(in));
    flowFile = runner.getFlowFilesForRelationship(QueryDatabaseTable.REL_SUCCESS).get(1);
    assertEquals(flowFile.getAttribute("maxvalue.id"), "2");
    in = new ByteArrayInputStream(flowFile.toByteArray());
    assertEquals(1, getNumberOfRecordsFromStream(in));
    runner.clearTransferState();
    // Populate a second table and set
    stmt.execute("create table TEST_QUERY_DB_TABLE2 (id integer not null, name varchar(100), scale float, created_on timestamp, bignum bigint default 0)");
    stmt.execute("insert into TEST_QUERY_DB_TABLE2 (id, name, scale, created_on) VALUES (0, 'Joe Smith', 1.0, '1962-09-23 03:23:34.234')");
    stmt.execute("insert into TEST_QUERY_DB_TABLE2 (id, name, scale, created_on) VALUES (1, 'Carrie Jones', 5.0, '2000-01-01 03:23:34.234')");
    stmt.execute("insert into TEST_QUERY_DB_TABLE2 (id, name, scale, created_on) VALUES (2, NULL, 2.0, '2010-01-01 00:00:00')");
    runner.setProperty(QueryDatabaseTable.TABLE_NAME, "TEST_QUERY_DB_TABLE2");
    runner.setProperty(QueryDatabaseTable.MAX_ROWS_PER_FLOW_FILE, "0");
    runner.run();
    runner.assertAllFlowFilesTransferred(QueryDatabaseTable.REL_SUCCESS, 1);
    flowFile = runner.getFlowFilesForRelationship(QueryDatabaseTable.REL_SUCCESS).get(0);
    assertEquals("TEST_QUERY_DB_TABLE2", flowFile.getAttribute(QueryDatabaseTable.RESULT_TABLENAME));
    assertEquals(flowFile.getAttribute("maxvalue.id"), "2");
    in = new ByteArrayInputStream(flowFile.toByteArray());
    assertEquals(3, getNumberOfRecordsFromStream(in));
    runner.clearTransferState();
    // Add a new row with a higher ID and run, one flowfile with one new row should be transferred
    stmt.execute("insert into TEST_QUERY_DB_TABLE2 (id, name, scale, created_on) VALUES (3, 'Mary West', 15.0, '2000-01-01 03:23:34.234')");
    runner.run();
    runner.assertAllFlowFilesTransferred(QueryDatabaseTable.REL_SUCCESS, 1);
    flowFile = runner.getFlowFilesForRelationship(QueryDatabaseTable.REL_SUCCESS).get(0);
    assertEquals(flowFile.getAttribute("maxvalue.id"), "3");
    in = new ByteArrayInputStream(flowFile.toByteArray());
    assertEquals(1, getNumberOfRecordsFromStream(in));
    // Sanity check - run again, this time no flowfiles/rows should be transferred
    runner.clearTransferState();
    runner.run();
    runner.assertAllFlowFilesTransferred(QueryDatabaseTable.REL_SUCCESS, 0);
    runner.clearTransferState();
}

Example 72 with DBCPService

use of org.apache.nifi.dbcp.DBCPService in project nifi by apache.

the class QueryDatabaseTableTest method testWithSqlException.

@Test
public void testWithSqlException() throws SQLException {
    // load test data to database
    final Connection con = ((DBCPService) runner.getControllerService("dbcp")).getConnection();
    Statement stmt = con.createStatement();
    try {
        stmt.execute("drop table TEST_NO_ROWS");
    } catch (final SQLException sqle) {
    // Ignore, usually due to Derby not having DROP TABLE IF EXISTS
    }
    stmt.execute("create table TEST_NO_ROWS (id integer)");
    runner.setIncomingConnection(false);
    // Try a valid SQL statement that will generate an error (val1 does not exist, e.g.)
    runner.setProperty(QueryDatabaseTable.TABLE_NAME, "TEST_NO_ROWS");
    runner.setProperty(QueryDatabaseTable.COLUMN_NAMES, "val1");
    runner.run();
    assertTrue(runner.getFlowFilesForRelationship(QueryDatabaseTable.REL_SUCCESS).isEmpty());
}

Example 73 with DBCPService

use of org.apache.nifi.dbcp.DBCPService in project nifi by apache.

the class ExecuteGroovyScript method onInitSQL.

/**
 * init SQL variables from DBCP services
 */
@SuppressWarnings("unchecked")
private void onInitSQL(HashMap SQL) throws SQLException {
    for (Map.Entry e : (Set<Map.Entry>) SQL.entrySet()) {
        DBCPService s = (DBCPService) e.getValue();
        OSql sql = new OSql(s.getConnection());
        // try to set autocommit to false
        try {
            if (sql.getConnection().getAutoCommit()) {
                sql.getConnection().setAutoCommit(false);
            }
        } catch (Throwable ei) {
            getLogger().warn("Failed to set autocommit=false for `" + e.getKey() + "`", ei);
        }
        e.setValue(sql);
    }
}