sqludf_substr()--SQL LOB Substring Locator

 #include <sqludf.h>

 extern int SQL_API_FN sqludf_substr(
    udf_locator * udfloc_p,                                       
    long          start,                                       
    long          length,                                       
    unsigned char * buffer_p,                                       
    long *        return_len_p)

The sqludf_substr() function returns a substring of the LOB data the locator represents.

Parameters

udfloc_p

(Input) Pointer to the LOB locator value.

start

(Input) The starting position of the substring. The first byte is byte 1.

length

(Input) The number of bytes to return.

buffer_p

(Input) Pointer to the buffer where the substring is to be placed.

return_len_p

(Input/Output) Pointer to the number of bytes actually returned. This can be smaller then the length requested.

Authorities

No authorization is required.

Return Value

returns an integer. Possible values are:

0

sqludf_substr() was successful. The information is returned in the buffer pointed to by return_len_p.

-3

sqludf_substr() was not successful. An invalid parameter was passed into the function.

-404

sqludf_substr() was not successful. The length of the substring is longer than the return buffer specified in buffer_p.

-423

sqludf_substr() was not successful. The udfloc_p parameter points to an invalid locator value.

-901

sqludf_substr() was not successful. An SQL system error has occurred.

-7034

sqludf_substr() was not successful. LOB locators are not allowed with COMMIT(*NONE).

Error Messages

Usage Notes

1. This API is used to see the bytes of the LOB value, when it has a locator.

Related Information

• sqludf_append()--SQL LOB append to locator
• sqludf_create_locator()--SQL LOB create locator
• sqludf_create_locator_with_ccsid()--SQL LOB create locator with ccsid
• sqludf_free_locator()--SQL LOB free locator
• sqludf_length()--SQL LOB locator length

Example

This UDF takes a locator for an input LOB, and returns a locator for another LOB which is a subset of the input LOB. There are some criteria passed as a second input value, which tell the UDF how exactly to break up the input LOB.

Note: By using the code examples, you agree to the terms of the Code license and disclaimer information.

#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <sql.h>
#include <sqludf.h>
 
void SQL_API_FN lob_subsetter(
          udf_locator * lob_input,   /* locator of LOB value to carve up */
          char        * criteria,    /* criteria for carving */
          udf_locator * lob_output,  /* locator of result LOB value */
          sqlint16 * inp_nul,
          sqlint16 * cri_nul,
          sqlint16 * out_nul,
          char     * sqlstate,
          char     * funcname,
          char     * specname,
          char     * msgtext ) {
 
       /* local vars */
       short j;              /* local indexing var */
       int   rc;             /* return code variable for API calls */
       sqlint32  input_len;  /* receiver for input LOB length */
       sqlint32  input_pos;  /* current position for scanning input LOB */
       char lob_buf[100];    /* data buffer */
       sqlint32  input_rec;  /* number of bytes read by sqludf_substr */
       sqlint32  output_rec; /* number of bytes written by sqludf_append */
 
       /*---------------------------------------------
        * UDF Program Logic Starts Here
        *---------------------------------------------
        * What we do is create an output handle, and then
        * loop over the input, 50 bytes at a time.
        * Depending on the "criteria" passed in, we may decide
        * to append the 50 byte input lob segment to the output, or not.
        *---------------------------------------------
        * Create the output locator, right in the return buffer.
        */
 
       rc = sqludf_create_locator(SQL_TYP_CLOB, &lob_output);
       /* Error and exit if unable to create locator */
       if (rc) {
          memcpy (sqlstate, "38901", 5); 
          /* special sqlstate for this condition */
          goto exit;
       }
       /* Find out the size of the input LOB value */
       rc = sqludf_length(lob_input, &input_len) ;
       /* Error and exit if unable to find out length */
       if (rc) {
          memcpy (sqlstate, "38902", 5); 
          /* special sqlstate for this condition */
          goto exit;
       }
       /* Loop to read next 50 bytes, and append to result if it meets
        * the criteria.
        */
       for (input_pos = 1; (input_pos < input_len); input_pos += 50) {
         /* Read the next 50 (or less) bytes of the input LOB value */
         rc = sqludf_substr(lob_input, input_pos, 50,
                            (unsigned char *) lob_buf, &input_rec) ;
         /* Error and exit if unable to read the segment */
         if (rc) {
            memcpy (sqlstate, "38903", 5); 
            /* special sqlstate for this condition */
            goto exit;
         }
         /* apply the criteria for appending this segment to result
	  * if (...predicate involving buffer and criteria...) {
	  * The example shows if the segment matches the first 6
          * characters with the criteria it is appended.
	  */
         if (memcmp(lob_buf,criteria,6) == 0) {         
            rc = sqludf_append(lob_output,
                       (unsigned char *) lob_buf, input_rec, &output_rec) ;
            /* Error and exit if unable to read the 50 byte segment */
            if (rc) {
               memcpy (sqlstate, "38904", 5); 
               /* special sqlstate for this condition */
               goto exit;
	    }
	 }
         /* } end if criteria for inclusion met */
       } /* end of for loop, processing 50-byte chunks of input LOB
          * if we fall out of for loop, we are successful, and done.
	  */
       *out_nul = 0;
       exit: /* used for errors, which will override null-ness of output. */
       return;
       }

Referring to this UDF code, observe that:

• There are includes for sql.h, where the type SQL_TYP_CLOB used in the sqludf_create_locator() call is defined, and sqludf.h, where the type udf_locator is defined.
• The first input argument, and the third input argument (which represents the function output) are defined as pointers to sqludf_locator, that is, they represent CREATE FUNCTION specifications of AS LOCATOR.
• The UDF does not test whether either input argument is null, as NOT NULL CALL is specified in the CREATE FUNCTION statement.
• In the event of error, the UDF exits with sqlstate set to 38xxx. This is sufficient to stop the execution of the statement referencing the UDF. The actual 38xxx SQLSTATE values you choose are not important to DB2^®, but can serve to differentiate the exception conditions which your UDF may encounter.
• By using the input_rec variable as the length of the data appended, the UDF takes care of any partial buffer condition.

Following is the CREATE FUNCTION statement for this UDF:

     CREATE FUNCTION carve(CLOB(50M) AS LOCATOR, VARCHAR(255) ) 
       RETURNS CLOB(50M) AS LOCATOR 
       NOT NULL CALL 
       DETERMINISTIC
       NO SQL 
       NO EXTERNAL ACTION 
       LANGUAGE C 
       PARAMETER STYLE DB2SQL 
       EXTERNAL NAME 'MYLIB/LOBUDFS(lob_subsetter)' ;

Referring to this statement, observe that:

• NOT NULL CALL is specified, so the UDF will not be called if any of its input SQL arguments are NULL, and does not have to check for this condition.
• The function is specified as DETERMINISTIC, meaning that with a given input CLOB value and a given set of criteria, the result will be the same every time.

Now you can successfully run the following statement:

    strcpy(hvchar,"return this text 1                                "         
                  "remove 1                                          "         
                  "return this text 2                                "         
                  "remove 2                                          ");       
    exec sql set :hvloc = clob(:hvchar);                                       
    exec sql set :hvloc2 = carve(:hvloc,'return');                             
    strcpy(hvchar,"");                                                         
    exec sql set :hvchar = char(:hvloc2);                                      

The UDF is used to subset the value represented by the host variable :hvchar. The first and third 50 byte character segments are returned from the UDF.

[ Back to top | Database and File APIs | APIs by category ]