SQL - How do you compare a CLOB

Question

in a DB2 trigger, I need to compare the value of a CLOB field. Something like:

IF OLD_ROW.CLOB_FIELD != UPDATED_ROW.CLOB_FIELD

but "!=" does not work for comparing CLOBs.

What is the way to compare it?

Edited to add:

My trigger needs to do some action if the Clob field was changed during an update. This is the reason I need to compare the 2 CLOBs in the trigger code. I'm looking for some detailed information on how this can be done

Answer 1

I believe it's not possible to use these kind of operators on CLOB fields, because of the way they're stored.

Answer 2

Calculate the md5 (or other) hash of the clobs and then compare these. Initial calculation will be slow but comparison is fast and easy. This could be a good method if the bulk of your data doesn't change very often.

One way to calculate md5 is through a java statement in your trigger. Save these in the same table (if possible) or build a simple auxiliary table.

Answer 3

Iglekott's idea is a good one, with a caveat:

Be careful with compare-by-hash if your data is likely to get attacked. It is not currently computationally feasible to generate a hash collision for a specific MD5 value, but it is possible to generate two different inputs that will produce the same MD5 (therefore not triggering your code). It is also possible to generate two different strings with the same prefix that hash to the same value.

If that kind of attack can lead to the integrity of your system being compromised, and that's a concern, you want to explore other options. The easiest would be simply switching the hash functions, SHA-2 does not have currently known vulnerabilities.

If this isn't a concern -- hell, go with CRC. You aren't going for cryptographic security here. Just don't go with a cryptographically weak function if this stuff is getting installed on a smartbomb, 'mkay? :-)

Answer 4

In Oracle 10g you can use DBMS_LOB.compare() API.

Example:

select * from table t where dbms_lob.compare(t.clob1, t.clob2) != 0

Full API:

DBMS_LOB.COMPARE (
   lob_1            IN BLOB,
   lob_2            IN BLOB,
   amount           IN INTEGER := 4294967295,
   offset_1         IN INTEGER := 1,
   offset_2         IN INTEGER := 1)
  RETURN INTEGER;

DBMS_LOB.COMPARE (
   lob_1            IN CLOB  CHARACTER SET ANY_CS,
   lob_2            IN CLOB  CHARACTER SET lob_1%CHARSET,
   amount           IN INTEGER := 4294967295,
   offset_1         IN INTEGER := 1,
   offset_2         IN INTEGER := 1)
  RETURN INTEGER; 

DBMS_LOB.COMPARE (
   lob_1            IN BFILE,
   lob_2            IN BFILE,
   amount           IN INTEGER,
   offset_1         IN INTEGER := 1,
   offset_2         IN INTEGER := 1)
  RETURN INTEGER;

Answer 5

Does DB2 use != for not equals? The ANSI SQL Standard uses <> for not equals.

Answer 6

If the CLOBs are 32K or less, you can cast them as VARCHAR, which allows comparison, LIKE, and various SQL string functions.

Otherwise, you may want to consider adding a column to contain the hash of the CLOB and change the application(s) to keep that hash up to date whenever the CLOB is updated.

Answer 7

The md5 idea is probably the best, but another alternative is to create a special trigger that only fires when your CLOB field is updated.

According to the syntax diagram, you would define the trigger as:

CREATE TRIGGER trig_name AFTER UPDATE OF CLOB_FIELD 
//trigger body goes here

This is assuming that your application (or whoever is updating the table) is smart enough to update the CLOB field ONLY WHEN there has been a change made to the clob field, and not every time your table is updated.