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

Example 6 with CharAlphabet

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

the class TriggerTest method testClobInTriggerTable.

/**
 * Create a table with after update trigger on non-lob column.
 * Insert clob of size clobSize into table and perform update
 * on str1 column to fire trigger. Helper method called from
 * testClobInTriggerTable
 * @param clobSize size of clob to test
 * @throws SQLException
 * @throws IOException
 */
private void testClobInTriggerTable(int clobSize) throws SQLException, IOException {
    // Alphabet used when inserting a CLOB.
    CharAlphabet a1 = CharAlphabet.singleChar('a');
    // Alphabet used when updating a CLOB.
    CharAlphabet a2 = CharAlphabet.singleChar('b');
    // --- add a clob
    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), c_lob CLOB(50M))");
    s.executeUpdate("create table t_lob1_log(oldvalue varchar(80), newvalue varchar(80), chng_time timestamp default current_timestamp)");
    s.executeUpdate(trig);
    commit();
    PreparedStatement ps = prepareStatement("INSERT INTO LOB1 VALUES (?, ?)");
    ps.setString(1, clobSize + "");
    // - set the value of the input parameter to the input stream
    ps.setCharacterStream(2, new LoopingAlphabetReader(clobSize, a1), clobSize);
    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 c_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.c_lob, new.c_lob)";
    s.executeUpdate("create table LOB1 (str1 Varchar(80), c_lob CLOB(50M))");
    s.executeUpdate("create table t_lob1_log(oldvalue CLOB(50M), newvalue  CLOB(50M), chng_time timestamp default current_timestamp)");
    s.executeUpdate(trig);
    commit();
    ps = prepareStatement("INSERT INTO LOB1 VALUES (?, ?)");
    ps.setString(1, clobSize + "");
    // - set the value of the input parameter to the input stream
    ps.setCharacterStream(2, new LoopingAlphabetReader(clobSize, a1), clobSize);
    ps.execute();
    closeStatement(ps);
    commit();
    // Now executing update to fire trigger
    ps = prepareStatement("update LOB1 set c_lob = ?");
    ps.setCharacterStream(1, new LoopingAlphabetReader(clobSize, a2), clobSize);
    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 c_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.c_lob, new.c_lob, old.c_lob, new.c_lob)";
    s.executeUpdate("create table LOB1 (str1 Varchar(80), c_lob CLOB(50M))");
    s.executeUpdate("create table t_lob1_log(oldvalue CLOB(50M), newvalue  CLOB(50M), oldvalue_again CLOB(50M), newvalue_again CLOB(50M), chng_time timestamp default current_timestamp)");
    s.executeUpdate(trig);
    commit();
    ps = prepareStatement("INSERT INTO LOB1 VALUES (?, ?)");
    ps.setString(1, clobSize + "");
    // - set the value of the input parameter to the input stream
    ps.setCharacterStream(2, new LoopingAlphabetReader(clobSize, a1), clobSize);
    ps.execute();
    closeStatement(ps);
    commit();
    // Now executing update to fire trigger
    ps = prepareStatement("update LOB1 set c_lob = ?");
    ps.setCharacterStream(1, new LoopingAlphabetReader(clobSize, a2), clobSize);
    ps.execute();
    closeStatement(ps);
    commit();
    // check log table.
    ResultSet rs = s.executeQuery("SELECT * from t_lob1_log");
    rs.next();
    assertEquals(new LoopingAlphabetReader(clobSize, a1), rs.getCharacterStream(1));
    assertEquals(new LoopingAlphabetReader(clobSize, a2), rs.getCharacterStream(2));
    assertEquals(new LoopingAlphabetReader(clobSize, a1), rs.getCharacterStream(3));
    assertEquals(new LoopingAlphabetReader(clobSize, a2), rs.getCharacterStream(4));
    rs.close();
    s.executeUpdate("drop table lob1");
    s.executeUpdate("drop table t_lob1_log");
}