Archives of the TeradataForum

Message Posted: Tue, 12 Apr 2011 @ 13:21:05 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

The requested UDF is attached.

clobcmp.cf.txt:
---------------

     /*----------------------------------------
     UDF to compare two CLOBs. (Can easily be modified to compare BLOBs
     also.) Returns -1 if the first CLOB is lexicographically less than
     the second, 0 if they're equal, 1 if the first is greater.

     If the CLOBs are of different lengths, the function will stop as
     soon as it finishes with the shorter string. This means, for
     instance, that 'string1' and 'string1 augmented' will compare
     as equal.

     The buffer used in the function is 100,000 bytes. Increasing this
     value might make the comparisons a little faster, but keep in mind
     that each AMP uses a copy of the function, so the memory requirements
     can grow fast.

     The familiar disclaimer, that this software comes with NO WARRANTY
     and is used ENTIRELY AT YOUR OWN RISK, is even more applicable
     than usual here. :-)

     G. Rommel, 2009-02-12 (200th birthday of a noted tyrant and a
      noted helminthologist)



     ----------------------------------------*/
     drop function sysdba.clobcmp;

     create function sysdba.clobcmp
      ( CLOB as locator,
        CLOB as locator )
     returns byteint
     language C
     no sql
     parameter style td_general
     deterministic
     returns null on null input
     external name
      'CS!clobcmp!C:\Documents and Settings\Geoffrey\My
     Documents\UDFs\clobcmp.c!F!clobcmp';

clobcmp.c:
----------

     /*--------------------------------------------------------------------
     ** c l o b c m p
     ** User-Defined Function to compare two CLOBs. Returns -1 if the first
     ** CLOB is lexicographically less than the second, 0 if they're equal,
     ** 1 if the first is greater.
     ** Two arguments, both CLOBs.
     **
     ** 2009-02-12  G. Rommel -- initial release
     **
     **------------------------------------------------------------------*/
     #define SQL_TEXT Latin_Text
     #define BUFFER_SIZE  100000
     #include 
     #include 


     void clobcmp (
       LOB_LOCATOR   *in_clob1,
       LOB_LOCATOR   *in_clob2,
       BYTEINT       *result,
       char      sqlstate[6] )
     {
      BYTE    buffer1[BUFFER_SIZE];
      BYTE    buffer2[BUFFER_SIZE];
      BYTE    *p_buf1, *p_buf2;
      FNC_LobLength_t  actlen1, actlen2, shorter, i;
      LOB_CONTEXT_ID   id1, id2;
      int     read_rc;

     /* Establish a read context for the LOB inputs. */
      FNC_LobOpen(*in_clob1, &id1, 0, BUFFER_SIZE);
      FNC_LobOpen(*in_clob2, &id2, 0, BUFFER_SIZE);

      *result = 0;

     /* Read the LOBs and compare them. */
      read_rc =  FNC_LobRead(id1, buffer1, BUFFER_SIZE, &actlen1);
      read_rc += FNC_LobRead(id2, buffer2, BUFFER_SIZE, &actlen2);
      while (read_rc == 0) {
         shorter = (actlen1 < actlen2) ? actlen1 : actlen2;
         for (i = 0; i < shorter; i++) {
            if (buffer1[i] < buffer2[i]) {
               *result = -1;
               break;
            } else if (buffer1[i] > buffer2[i]) {
               *result = 1;
               break;
            } else
               continue;
         }

         read_rc =  FNC_LobRead(id1, buffer1, BUFFER_SIZE, &actlen1);
         read_rc += FNC_LobRead(id2, buffer2, BUFFER_SIZE, &actlen2);
      }
     /* Release the resources associated with the read context */
      FNC_LobClose(id1);
      FNC_LobClose(id2);

      strcpy(sqlstate, "00000");
      return;
     }

	<Prev	Next>		<<First	<Prev	Next>	Last>>