sqludf_create_locator()--SQL LOB Create Locator
Syntax
#include <sql.h> #include <sqludf.h> extern int SQL_API_FN sqludf_create_locator( int loc_type, udf_locator ** loc_p)Service Program Name: QSYS/QSQAPIS
Default Public Authority: *USE
Threadsafe: Yes
The sqludf_create_locator() function creates a LOB locator.
Parameters
- loc_type
- (Input) Type of LOB the locator represents. Valid locator types can be any of the types from sql.h representing LOBs. For example:
Type Name Type Value Description SQL_TYP_BLOB 404 BLOB locator SQL_TYP_NBLOB 405 BLOB locator that allows a null value SQL_TYP_CLOB 408 CLOB locator SQL_TYP_NCLOB 409 CLOB locator that allows a null value SQL_TYP_DBCLOB 412 DBCLOB locator SQL_TYP_NDBCLOB 413 DBCLOB locator that allows a null value
- loc_p
- (Input/Output) Pointer to a pointer where the locator value is to be returned.
Authorities
No authorization is required.
Return Value
sqludf_create_locator()returns an integer. Possible values are:
- 0
- sqludf_create_locator() was successful. The
information is returned in the buffer pointed to by return_len_p.
- -3
- sqludf_create_locator() was not successful. An invalid parameter was passed into the function.
- -429
- sqludf_create_locator() was not successful. The maximum number of concurrent LOB locators has been reached.
- -901
- sqludf_create_locator() was not successful. An SQL system error has occurred.
- -7034
- sqludf_create_locator() 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 create a locator. A LOB locator is only a mechanism used to refer to a LOB value during a transaction; it does not persist beyond the transaction in which it was created unless it is used with the HOLD LOCATOR statement.
Related Information
- sqludf_append()--SQL LOB append 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