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");