sqludf_append()--SQL LOB Append to Locator
Syntax
#include <sqludf.h> extern int SQL_API_FN sqludf_append( udf_locator * udfloc_p, unsigned char * buffer_p, long length, long * return_len_p)Service Program Name: QSYS/QSQAPIS
Default Public Authority: *USE
Threadsafe: Yes
The sqludf_append() function appends data to the end of the LOB data the locator represents.
Parameters
- udfloc_p
- (Input) Pointer to the LOB locator value.
- buffer_p
- (Input) Pointer to the buffer containing the data to append to the end of the LOB data represented by the locator.
- length
- (Input) The number of bytes to append.
- return_len_p
- (Input/Output) Pointer to the number of bytes actually appended.
Authorities
No authorization is required.
Return Value
sqludf_append()returns an integer. Possible values are:
- 0
- sqludf_append() was successful. The
information is returned in the buffer pointed to by return_len_p.
- -3
- sqludf_append() was not successful. An invalid parameter was passed into the function.
- -137
- sqludf_append() was not successful. The resulting length of the append exceeds the maximum allowed. The maximum length is 2147483647 bytes.
- -423
- sqludf_append() was not successful. The udfloc_p parameter
points to an invalid locator value.
- -901
- sqludf_append() was not successful. An SQL system error has occurred.
- -7034
- sqludf_append() was not successful. LOB locators are not allowed with COMMIT(*NONE).
Error Messages
Message ID | Error Message Text |
---|---|
SQL7034 D | LOB locators are not allowed with COMMIT(*NONE). |
SQL0901 D | SQL system error. |
SQL0952 D | Processing of the SQL statement ended. |
CPF9872 E | Program or service program &1 in library &2 ended. Reason code &3. |
Usage Notes
- This API is used to append data to the end of the LOB data represented by the locator.
Related Information
- 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
- sqludf_substr()--SQL LOB substring locator
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 thesqludf_create_locator()
call is defined, andsqludf.h
, where the typeudf_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.
API introduced: V5R3