Examples with ByteAlphabet org.apache.derbyTesting.functionTests.util.streams.ByteAlphabet used on opensource projects

Example 1 with ByteAlphabet

use of org.apache.derbyTesting.functionTests.util.streams.ByteAlphabet in project derby by apache.

the class TriggerTest method testBlobInTriggerTable.

/**
 * Create a table with after update trigger on non-lob column.
 * Insert two blobs of size blobSize into table and perform update
 * on str1 column to fire trigger. Helper method called from
 * testBlobInTriggerTable
 *
 * @param blobSize  size of blob to test.
 * @throws SQLException
 * @throws IOException
 */
private void testBlobInTriggerTable(int blobSize) throws SQLException, IOException {
    // Alphabet used when inserting a BLOB.
    ByteAlphabet a1 = ByteAlphabet.singleByte((byte) 8);
    // Alphabet used when updating a BLOB.
    ByteAlphabet a2 = ByteAlphabet.singleByte((byte) 9);
    String trig = " create trigger t_lob1 after update of str1 on lob1 ";
    trig = trig + " REFERENCING OLD AS old NEW AS new FOR EACH ROW MODE DB2SQL ";
    trig = trig + " insert into t_lob1_log(oldvalue, newvalue) values (old.str1, new.str1)";
    Statement s = createStatement();
    s.executeUpdate("create table LOB1 (str1 Varchar(80), b_lob BLOB(50M), b_lob2 BLOB(50M))");
    s.executeUpdate("create table t_lob1_log(oldvalue varchar(80), newvalue varchar(80), chng_time timestamp default current_timestamp)");
    s.executeUpdate(trig);
    commit();
    // --- add a blob
    PreparedStatement ps = prepareStatement("INSERT INTO LOB1 VALUES (?, ?, ?)");
    ps.setString(1, blobSize + "");
    // - set the value of the input parameter to the input stream
    // use a couple blobs so we are sure it works with multiple lobs
    ps.setBinaryStream(2, new LoopingAlphabetStream(blobSize, a1), blobSize);
    ps.setBinaryStream(3, new LoopingAlphabetStream(blobSize, a1), blobSize);
    ps.execute();
    closeStatement(ps);
    commit();
    // Now executing update to fire trigger
    s.executeUpdate("update LOB1 set str1 = str1 || ' '");
    s.executeUpdate("drop table lob1");
    s.executeUpdate("drop table t_lob1_log");
    // now referencing the lob column
    trig = " create trigger t_lob1 after update of b_lob on lob1 ";
    trig = trig + " REFERENCING OLD AS old NEW AS new FOR EACH ROW MODE DB2SQL ";
    trig = trig + " insert into t_lob1_log(oldvalue, newvalue) values (old.b_lob, new.b_lob)";
    s.executeUpdate("create table LOB1 (str1 Varchar(80), b_lob BLOB(50M))");
    s.executeUpdate("create table t_lob1_log(oldvalue BLOB(50M), newvalue  BLOB(50M), chng_time timestamp default current_timestamp)");
    s.executeUpdate(trig);
    commit();
    ps = prepareStatement("INSERT INTO LOB1 VALUES (?, ?)");
    ps.setString(1, blobSize + "");
    // - set the value of the input parameter to the input stream
    ps.setBinaryStream(2, new LoopingAlphabetStream(blobSize, a1), blobSize);
    ps.execute();
    closeStatement(ps);
    commit();
    // Now executing update to fire trigger
    ps = prepareStatement("update LOB1 set b_lob = ?");
    ps.setBinaryStream(1, new LoopingAlphabetStream(blobSize, a2), blobSize);
    ps.execute();
    closeStatement(ps);
    commit();
    s.executeUpdate("drop table lob1");
    s.executeUpdate("drop table t_lob1_log");
    // now referencing the lob column twice
    trig = " create trigger t_lob1 after update of b_lob on lob1 ";
    trig = trig + " REFERENCING OLD AS old NEW AS new FOR EACH ROW MODE DB2SQL ";
    trig = trig + " insert into t_lob1_log(oldvalue, newvalue, oldvalue_again, newvalue_again) values (old.b_lob, new.b_lob, old.b_lob, new.b_lob)";
    s.executeUpdate("create table LOB1 (str1 Varchar(80), b_lob BLOB(50M))");
    s.executeUpdate("create table t_lob1_log(oldvalue BLOB(50M), newvalue  BLOB(50M), oldvalue_again BLOB(50M), newvalue_again BLOB(50M), chng_time timestamp default current_timestamp)");
    s.executeUpdate(trig);
    commit();
    ps = prepareStatement("INSERT INTO LOB1 VALUES (?, ?)");
    ps.setString(1, blobSize + "");
    // - set the value of the input parameter to the input stream
    ps.setBinaryStream(2, new LoopingAlphabetStream(blobSize, a1), blobSize);
    ps.execute();
    closeStatement(ps);
    commit();
    // Now executing update to fire trigger
    ps = prepareStatement("update LOB1 set b_lob = ?");
    ps.setBinaryStream(1, new LoopingAlphabetStream(blobSize, a2), blobSize);
    ps.execute();
    closeStatement(ps);
    commit();
    // check log table.
    ResultSet rs = s.executeQuery("SELECT * from t_lob1_log");
    rs.next();
    assertEquals(new LoopingAlphabetStream(blobSize, a1), rs.getBinaryStream(1));
    assertEquals(new LoopingAlphabetStream(blobSize, a2), rs.getBinaryStream(2));
    assertEquals(new LoopingAlphabetStream(blobSize, a1), rs.getBinaryStream(3));
    assertEquals(new LoopingAlphabetStream(blobSize, a2), rs.getBinaryStream(4));
    rs.close();
    s.executeUpdate("drop table lob1");
    s.executeUpdate("drop table t_lob1_log");
}