Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2003
    Posts
    127

    Unanswered: UDF to convert BLOB to CLOB

    Does anyone have a UDF to convert a BLOB to a CLOB?

    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Assuming that you just want to re-interpret the binary data as string and don't care about code page conversions, etc... I just typed in this sample code:
    Code:
    #include <stdio.h>
    #include <string.h>
    
    #include <sqludf.h>
    
    /*
      CREATE FUNCTION blob2clob(blob BLOB(10M))
         RETURNS CLOB(10M)
         EXTERNAL NAME 'lobudfs!blob2clob'
         SPECIFIC blob2clob
         LANGUAGE C
         PARAMETER STYLE SQL
         DETERMINISTIC
         NOT FENCED  THREADSAFE
         NO SQL
    */
    #if defined(__cplusplus)
    extern "C"
    #endif
    void SQL_API_FN blob2clob(
    	SQLUDF_BLOB    *blob,
    	SQLUDF_CLOB    *clob,
    	SQLUDF_NULLIND *blob_ind,
    	SQLUDF_NULLIND *clob_ind,
    	SQLUDF_TRAIL_ARGS)
    {
        if (SQLUDF_NULL(blob_ind)) {
    	*clob_ind = -1;
    	return;
        }
        if (clob->length < blob->length) {
    	memcpy(SQLUDF_STATE, "38XXX", SQLUDF_SQLSTATE_LEN);
    	snprintf(SQLUDF_MSGTX, SQLUDF_MSGTEXT_LEN,
    		"The BLOB is %d bytes long and does not fit into %d sized CLOB",
    		blob->length, clob->length);
    	return;
        }
        *clob_ind = 0;
        clob->length = blob->length;
        memcpy(clob->data, blob->data, blob->length);
    }
    And this is how it works:
    Code:
    $ db2 "describe values blob2clob(blob(x'41452069'))"
    
     Column Information
    
     Number of columns: 1
    
                                                                        Name                Data type  Schema                      Name
     SQL type              Type length  Column name                     length  Lob length  schema     length  Data type name      length
     --------------------  -----------  ------------------------------  ------  ----------  ---------  ------  ------------------  --------
     409   CLOB                      0  1                                    1    10485760  SYSIBM          6  CLOB                       4
    
    $ db2 "values varchar(blob2clob(blob(x'41452069')), 40)"
    
    1
    ----------------------------------------
    AE i
    
      1 record(s) selected.
    You can choose a more suitable size for your maximum LOB size. But be aware that the 10M for the output parameter are always allocated by DB2. That means, if your BLOB is only 100 bytes long, DB2 will nevertheless allocate 10M for the CLOB.parameter - because DB2 can't know what the function will return. So if you declare the function with BLOB/CLOB(2G), DB2 would have to allocate at least 2GB of main memory, which is not a good idea. If you have such huge LOBs, you have no work with LOB locators instead, which is a little bit more involved to get the LOB data from DB2 or to pass it back.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jun 2003
    Posts
    127
    Thanks Knut. This is what I'm looking for.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I'll send you my bill later ;-)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jun 2003
    Posts
    127
    Sure, will ask company to reimburse

  6. #6
    Join Date
    Jul 2003
    Location
    Mumbai, India
    Posts
    3

    Red face Run time error while running the UDF

    Stolze,

    This utility is very interesting. We have a similar requirement in DB2 UDB and are getting the following error when executing the UDF -

    SQL0444N Routine "BLOB2CLOB" (specific name "BLOB2CLOB") is implemented with code in library or path "...m Files\IBM\SQLLIB\FUNCTION\blob2clob_c.dll",
    function "*" which cannot be accessed. Reason code: "4". SQLSTATE=42724

    Can you please help. Appreciate your time.
    Thanks!
    Sunil N. Shah

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Put blob2clob_c.dll, which I am sure you have successfully compiled and linked, into c:\Program Files\IBM\SQLLIB\FUNCTION\
    ---
    "It does not work" is not a valid problem statement.

  8. #8
    Join Date
    Jul 2003
    Location
    Mumbai, India
    Posts
    3

    Same error

    n_i: We have put the dll in c:\Program Files\IBM\SQLLIB\FUNCTION\ dir on the server. Still getting the same error.
    Any help appreciated.
    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •