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