#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)
Service Program Name: QSYS/QSQAPISThe sqludf_substr() function returns a substring of the LOB data the locator represents.
No authorization is required.
returns an integer. Possible values are:
| 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. |
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:
SQL_TYP_CLOB used in the sqludf_create_locator() call is
defined, and sqludf.h, where the type udf_locator
is defined.sqludf_locator,
that is, they represent CREATE FUNCTION specifications of AS LOCATOR.
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.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:
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.