SQL - 如何比较CLOB

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

在DB2触发器中,我需要比较CLOB字段的值。就像是:

IF OLD_ROW.CLOB_FIELD != UPDATED_ROW.CLOB_FIELD

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

但“!=”不适用于比较CLOB。

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

如果在更新期间更改了Clob字段,我的触发器需要执行一些操作。这就是我需要比较触发器代码中的2个CLOB的原因。我正在寻找有关如何做到这一点的一些详细信息

9 个解决方案

#1

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

在Oracle 10g中,您可以使用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;

#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.

计算clob的md5(或其他)哈希值,然后比较这些哈希值。初步计算会很慢,但比较快速而简单。如果您的大部分数据不经常更改,这可能是一个很好的方法。

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.

计算md5的一种方法是通过触发器中的java语句。将它们保存在同一个表中(如果可能)或构建一个简单的辅助表。

#3

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

Iglekott的想法很好,但有一点需要注意:

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.

如果您的数据可能受到攻击,请小心使用hash-by-hash。目前在计算上不可能为特定的MD5值生成哈希冲突,但是可以生成两个不同的输入,这些输入将生成相同的MD5(因此不会触发您的代码)。也可以生成两个具有相同前缀的不同字符串,这些字符串散列到相同的值。

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.

如果这种攻击可能导致系统的完整性受到损害,而这是一个问题,那么您需要探索其他选项。最简单的方法是简单地切换哈希函数,SHA-2目前没有已知的漏洞。

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? :-)

如果这不是一个问题 - 地狱,请使用CRC。您不会在这里寻求加密安全性。如果这些东西安装在智能炸弹上,那么就不要使用加密功能弱的功能,'mkay? :-)

#4

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

如果CLOB为32K或更小,则可以将它们转换为VARCHAR,这允许比较,LIKE和各种SQL字符串函数。

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.

否则,您可能需要考虑添加一个列来包含CLOB的哈希值,并在更新CLOB时更改应用程序以使该哈希值保持最新。

#5

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.

md5的想法可能是最好的,但另一种选择是创建一个特殊的触发器,只在更新CLOB字段时触发。

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.

这假设您的应用程序(或更新表的任何人)足够聪明,只有在对clob字段进行更改时才更新CLOB字段,而不是每次更新表时都是如此。

#6

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

我认为在CLOB字段上使用这些类型的运算符是不可能的,因为它们的存储方式。

#7

Just declare the trigger to fire if that particular column is updated.

如果更新了特定列,只需声明触发器即可触发。

create trigger T_TRIG on T 
before update of CLOB_COL
...

#8

Generating a hash value and comparing them is the best way IMHO.

生成哈希值并比较它们是恕我直言的最佳方式。

Here is the untested code:

这是未经测试的代码:

...
declare leftClobHash integer;
declare rightClobHash integer;
set leftClobHash = (
    SELECT DBMS_UTILITY.GET_HASH_VALUE(OLD_ROW.CLOB_FIELD,100,1024) AS HASH_VALUE 
    FROM SYSIBM.SYSDUMMY1);
set rightClobHash = (
    SELECT DBMS_UTILITY.GET_HASH_VALUE(UPDATED_ROW.CLOB_FIELD,100,1024) AS HASH_VALUE 
    FROM SYSIBM.SYSDUMMY1);

IF leftClobHash != rightClobHash
...

Note that you need EXECUTE privilege on the DBMS_UTILITY module. You can find more information about the provided SQL PL code in the following links.

请注意,您需要DBMS_UTILITY模块的EXECUTE特权。您可以在以下链接中找到有关提供的SQL PL代码的更多信息。

Declaring variables: http://www.sqlpl-guide.com/DECLARE

声明变量:http://www.sqlpl-guide.com/DECLARE

Setting values for variables: http://www.sqlpl-guide.com/SET

设置变量值:http://www.sqlpl-guide.com/SET

Generating Hash: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.rtn.doc%2Fdoc%2Fr0055167.html

生成哈希:http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.rtn.doc%2Fdoc%2Fr0055167.html

More on DBMS_UTILITY module: http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic=%2Fcom.ibm.db2.luw.sql.rtn.doc%2Fdoc%2Fr0055155.html

有关DBMS_UTILITY模块的更多信息:http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp?topic =%2Fcom.ibm.db2.luw.sql.rtn.doc%2Fdoc%2Fr0055155.html

#9

-1

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

DB2是否使用!= for not equals? ANSI SQL标准使用<>表示不等于。

#1