The following UDF code examples are supplied with DB2.
For information on where to find all the examples supplied, and how to invoke them, see Appendix B, Sample Programs.
For information on compiling and linking UDFs, refer to the Application Building Guide.
Each of the example UDFs is accompanied by the corresponding CREATE FUNCTION statement, and a small scenario showing its use. These scenarios all use the following table TEST, which has been carefully crafted to illustrate certain points being made in the scenarios. Here is the table definition:
CREATE TABLE TEST (INT1 INTEGER,
INT2 INTEGER,
PART CHAR(5),
DESCR CLOB(33K))
After populating the table, issue the following statement using CLP to display its contents:
SELECT INT1, INT2, PART, SUBSTR(DESCR,1,50) FROM TEST
Note the use of the SUBSTR function on the CLOB column to make the output more readable. You receive the following CLP output:
INT1 INT2 PART 4
----------- ----------- ----- --------------------------------------------------
16 1 brain The only part of the body capable of forgetting.
8 2 heart The seat of the emotions?
4 4 elbow That bendy place in mid-arm.
2 0 - -
97 16 xxxxx Unknown.
5 record(s) selected.
Refer to the previous information on table TEST as you read the examples and scenarios which follow.
Suppose you are unhappy with the way integer divide works in DB2 because it returns an error, SQLCODE -802 (SQLSTATE 22003), and terminates the statement when the divisor is zero. (Note that if you enable friendly arithmetic with the DFT_SQLMATHWARN configuration parameter, DB2 returns a NULL instead of an error in this situation.) Instead, you want the integer divide to return a NULL, so you code this UDF:
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <sqludf.h>
#include <sqlca.h>
#include <sqlda.h>
/*************************************************************************
* function divid: performs integer divid, but unlike the / operator
* shipped with the product, gives NULL when the
* denominator is zero.
*
* This function does not use the constructs defined in the
* "sqludf.h" header file.
*
* inputs: INTEGER num numerator
* INTEGER denom denominator
* output: INTEGER out answer
**************************************************************************/
#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN divid (
sqlint32 *num, /* numerator */
sqlint32 *denom, /* denominator */
sqlint32 *out, /* output result */
short *in1null, /* input 1 NULL indicator */
short *in2null, /* input 2 NULL indicator */
short *outnull, /* output NULL indicator */
char *sqlstate, /* SQL STATE */
char *funcname, /* function name */
char *specname, /* specific function name */
char *mesgtext) { /* message text insert */
if (*denom == 0) { /* if denominator is zero, return null result */
*outnull = -1;
} else { /* else, compute the answer */
*out = *num / *denom;
*outnull = 0;
} /* endif */
}
/* end of UDF : divid */
For this UDF, notice that:
#include <sqlsystm.h>
Here is the CREATE FUNCTION statement for this UDF:
CREATE FUNCTION MATH."/"(INT,INT)
RETURNS INT
NOT FENCED
DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
LANGUAGE C
PARAMETER STYLE DB2SQL
EXTERNAL NAME '/u/slick/udfx/div' ;
(This statement is for an AIX version of this UDF. For other platforms, you may need to modify the value specified in the EXTERNAL NAME clause.)
For this statement, observe that:
Now if you run the following pair of statements (CLP input is shown):
SET CURRENT FUNCTION PATH = SYSIBM, SYSFUN, SLICK
SELECT INT1, INT2, INT1/INT2, "/"(INT1,INT2) FROM TEST
You get this output from CLP (if you do not enable friendly arithmetic with the database configuration parameter DFT_SQLMATHWARN):
INT1 INT2 3 4
----------- ----------- ----------- -----------
16 1 16 16
8 2 4 4
4 4 1 1
SQL0802N Arithmetic overflow or other arithmetic exception occurred.
SQLSTATE=22003
The SQL0802N error message occurs because you have set your CURRENT FUNCTION PATH special register to a concatenation of schemas which does not include MATH, the schema in which the "/" UDF is defined. And therefore you are executing DB2's built-in divide operator, whose defined behavior is to give the error when a "divide by zero" condition occurs. The fourth row in the TEST table provides this condition.
However, if you change the function path, putting MATH in front of SYSIBM in the path, and rerun the SELECT statement:
SET CURRENT FUNCTION PATH = MATH, SYSIBM, SYSFUN, SLICK
SELECT INT1, INT2, INT1/INT2, "/"(INT1,INT2) FROM TEST
You then get the desired behavior, as shown by the following CLP output:
INT1 INT2 3 4
----------- ----------- ----------- -----------
16 1 16 16
8 2 4 4
4 4 1 1
2 0 - -
97 16 6 6
5 record(s) selected.
For the above example, observe that:
In this case, for a fully general set of functions you have to CREATE the following three additional functions to completely handle integer divide:
CREATE FUNCTION MATH."/"(SMALLINT,SMALLINT)
RETURNS INT
SOURCE MATH."/"(INT,INT)
CREATE FUNCTION MATH."/"(SMALLINT,INT)
RETURNS INT
SOURCE MATH."/"(INT,INT)
CREATE FUNCTION MATH."/"(INT,SMALLINT)
RETURNS INT
SOURCE MATH."/"(INT,INT)
Even though three UDFs are added, additional code does not have to be written as they are sourced on MATH."/".
And now, with the definition of these four "/" functions, any users who want to take advantage of the new behavior on integer divide need only place MATH ahead of SYSIBM in their function path, and can write their SQL as usual.
While the preceding example does not consider the BIGINT data type, you can easily extend the example to include BIGINT.
Suppose you have coded up two UDFs to help you with your text handling application. The first UDF folds your text string after the nth byte. In this example, fold means to put the part that was originally after the n byte before the part that was originally in front of the n+1 byte. In other words, the UDF moves the first n bytes from the beginning of the string to the end of the string. The second function returns the position of the first vowel in the text string. Both of these functions are coded in the udf.c example file:
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <sqludf.h>
#include <sqlca.h>
#include <sqlda.h>
#include "util.h"
/*************************************************************************
* function fold: input string is folded at the point indicated by the
* second argument.
*
* input: CLOB in1 input string
* INTEGER in2 position to fold on
* CLOB out folded string
**************************************************************************/
#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN fold (
SQLUDF_CLOB *in1, /* input CLOB to fold */
SQLUDF_INTEGER *in2, /* position to fold on */
SQLUDF_CLOB *out, /* output CLOB, folded */
SQLUDF_NULLIND *in1null, /* input 1 NULL indicator */
SQLUDF_NULLIND *in2null, /* input 2 NULL indicator */
SQLUDF_NULLIND *outnull, /* output NULL indicator */
SQLUDF_TRAIL_ARGS) { /* trailing arguments */
SQLUDF_INTEGER len1;
if (SQLUDF_NULL(in1null) || SQLUDF_NULL(in2null)) {
/* one of the arguments is NULL. The result is then "INVALID INPUT" */
strcpy( ( char * ) out->data, "INVALID INPUT" ) ;
out->length = strlen("INVALID INPUT");
} else {
len1 = in1->length; /* length of the CLOB */
/* build the output by folding at position "in2" */
strncpy( ( char * ) out->data, &in1->data[*in2], len1 - *in2 ) ;
strncpy( ( char * ) &out->data[len1 - *in2], in1->data, *in2 ) ;
out->length = in1->length;
} /* endif */
*outnull = 0; /* result is always non-NULL */
}
/* end of UDF : fold */
/*************************************************************************
* function findvwl: returns the position of the first vowel.
* returns an error if no vowel is found
* when the function is created, must be defined as
* NOT NULL CALL.
* inputs: VARCHAR(500) in
* output: INTEGER out
**************************************************************************/
#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN findvwl (
SQLUDF_VARCHAR *in, /* input character string */
SQLUDF_SMALLINT *out, /* output location of vowel */
SQLUDF_NULLIND *innull, /* input NULL indicator */
SQLUDF_NULLIND *outnull, /* output NULL indicator */
SQLUDF_TRAIL_ARGS) { /* trailing arguments */
short i; /* local indexing variable */
for (i=0; (i < (short)strlen(in) && /* find the first vowel */
in[i] != 'a' && in[i] != 'e' && in[i] != 'i' &&
in[i] != 'o' && in[i] != 'u' && in[i] != 'y' &&
in[i] != 'A' && in[i] != 'E' && in[i] != 'I' &&
in[i] != 'O' && in[i] != 'U' && in[i] != 'Y'); i++);
if (i == strlen( ( char * ) in )) { /* no vowels found */
/* error state */
strcpy( ( char * ) sqludf_sqlstate, "38999" ) ;
/* message insert */
strcpy( ( char * ) sqludf_msgtext, "findvwl: No Vowel" ) ;
} else { /* a vowel was found at "i" */
*out = i + 1;
*outnull = 0;
} /* endif */
}
/* end of UDF : findvwl */
For the above UDFs, notice:
Here are the CREATE FUNCTION statements for these UDFs:
CREATE FUNCTION FOLD(CLOB(100K),INT)
RETURNS CLOB(100K)
FENCED
DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
LANGUAGE C
NULL CALL
PARAMETER STYLE DB2SQL
EXTERNAL NAME 'udf!fold' ;
CREATE FUNCTION FINDV(VARCHAR(500))
RETURNS INTEGER
NOT FENCED
DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
LANGUAGE C
NOT NULL CALL
PARAMETER STYLE DB2SQL
EXTERNAL NAME 'udf!findvwl' ;
The above CREATE FUNCTION statements are for UNIX-based platforms. On other platforms, you may need to modify the value specified in the EXTERNAL NAME clause in the above statements. You can find the above CREATE FUNCTION statements in the calludf.sqc example program shipped with DB2.
Referring to these CREATE statements, observe that:
Now you can successfully run the following statement:
SELECT SUBSTR(DESCR,1,30), SUBSTR(FOLD(DESCR,6),1,30) FROM TEST
The output from the CLP for this statement is:
1 2
------------------------------ ------------------------------
The only part of the body capa ly part of the body capable of
The seat of the emotions? at of the emotions?The se
That bendy place in mid-arm. endy place in mid-arm.That b
- INVALID INPUT
Unknown. n.Unknow
5 record(s) selected.
Note the use of the SUBSTR built-in function to make the selected CLOB values display more nicely. It shows how the output is folded (best seen in the second, third and fifth rows, which have a shorter CLOB value than the first row, and thus the folding is more evident even with the use of SUBSTR). And it shows (fourth row) how the INVALID INPUT string is returned by the FOLD UDF when its input text string (column DESCR) is null. This SELECT also shows simple nesting of function references; the reference to FOLD is within an argument of the SUBSTR function reference.
Then if you run the following statement:
SELECT PART, FINDV(PART) FROM TEST
The CLP output is as follows:
PART 2
----- -----------
brain 3
heart 2
elbow 1
- -
SQL0443N User defined function "SLICK.FINDV" (specific name
"SQL950424135144750") has returned an error SQLSTATE with diagnostic
text "findvwl: No Vowel". SQLSTATE=38999
This example shows how the 38999 SQLSTATE value and error message token returned by findvwl() are handled: message SQL0443N returns this information to the user. The PART column in the fifth row contains no vowel, and this is the condition which triggers the error in the UDF.
Observe the argument promotion in this example. The PART column is CHAR(5), and is promoted to VARCHAR to be passed to FINDV.
And finally note how DB2 has generated a null output from FINDV for the fourth row, as a result of the NOT NULL CALL specification in the CREATE statement for FINDV.
The following statement:
SELECT SUBSTR(DESCR,1,25), FINDV(CAST (DESCR AS VARCHAR(60) ) )
FROM TEST
Produces this output when executed in the CLP:
1 2
------------------------- -----------
The only part of the body 3
The seat of the emotions? 3
That bendy place in mid-a 3
- -
Unknown. 1
5 record(s) selected.
This SELECT statement shows FINDV working on a VARCHAR input argument. Observe how we cast column DESCR to VARCHAR to make this happen. Without the cast we would not be able to use FINDV on a CLOB argument, because CLOB is not promotable to VARCHAR. Again, the built-in SUBSTR function is used to make the DESCR column value display better.
And here again note that the fourth row produces a null result from FINDV because of the NOT NULL CALL.
Suppose you want to simply number the rows in your SELECT statement. So you write a UDF which increments and returns a counter. This UDF uses a scratchpad:
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <sqludf.h>
#include <sqlca.h>
#include <sqlda.h>
/* structure scr defines the passed scratchpad for the function "ctr" */
struct scr {
sqlint32 len;
sqlint32 countr;
char not_used[96];
} ;
/*************************************************************************
* function ctr: increments and reports the value from the scratchpad.
*
* This function does not use the constructs defined in the
* "sqludf.h" header file.
*
* input: NONE
* output: INTEGER out the value from the scratchpad
**************************************************************************/
#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN ctr (
sqlint32 *out, /* output answer (counter) */
short *outnull, /* output NULL indicator */
char *sqlstate, /* SQL STATE */
char *funcname, /* function name */
char *specname, /* specific function name */
char *mesgtext, /* message text insert */
struct scr *scratchptr) { /* scratch pad */
*out = ++scratchptr->countr; /* increment counter & copy out */
*outnull = 0;
}
/* end of UDF : ctr */
For this UDF, observe that:
Following is the CREATE FUNCTION statement for this UDF:
CREATE FUNCTION COUNTER()
RETURNS INT
SCRATCHPAD
NOT FENCED
NOT DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
LANGUAGE C
PARAMETER STYLE DB2SQL
EXTERNAL NAME 'udf!ctr'
DISALLOW PARALLELISM;
(This statement is for an AIX version of this UDF. For other platforms, you may need to modify the value specified in the EXTERNAL NAME clause.)
Referring to this statement, observe that:
Now you can successfully run the following statement:
SELECT INT1, COUNTER(), INT1/COUNTER() FROM TEST
When run through the CLP, it produces this output:
INT1 2 3
----------- ----------- -----------
16 1 16
8 2 4
4 3 1
2 4 0
97 5 19
5 record(s) selected.
Observe that the second column shows the straight COUNTER() output. The third column shows that the two separate references to COUNTER() in the SELECT statement each get their own scratchpad; had they not each gotten their own, the output in the second column would have been 1 3 5 7 9, instead of the nice orderly 1 2 3 4 5.
The following is an example table function, tfweather_u, (supplied by DB2 in the programming example tblsrv.c), that returns weather information for various cities in the United States. The weather data for these cities is included in the example program, but could be read in from an external file, as indicated in the comments contained in the example program. The data includes the name of a city followed by its weather information. This pattern is repeated for the other cities. Note that there is a client application (tblcli.sqc) supplied with DB2 that calls this table function and prints out the weather data retrieved using the tfweather_u table function.
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <sql.h>
#include <sqludf.h> /* for use in compiling User Defined Function */
#define SQL_NOTNULL 0 /* Nulls Allowed - Value is not Null */
#define SQL_ISNULL -1 /* Nulls Allowed - Value is Null */
/* Short and long city name structure */
typedef struct {
char * city_short ;
char * city_long ;
} city_area ;
/* Scratchpad data */
/* Preserve information from one function call to the next call */
typedef struct {
/* FILE * file_ptr; if you use weather data text file */
int file_pos ; /* if you use a weather data buffer */
} scratch_area ;
/* Field descriptor structure */
typedef struct {
char fld_field[31] ; /* Field data */
int fld_ind ; /* Field null indicator data */
int fld_type ; /* Field type */
int fld_length ; /* Field length in the weather data */
int fld_offset ; /* Field offset in the weather data */
} fld_desc ;
/* Short and long city name data */
city_area cities[] = {
{ "alb", "Albany, NY" },
{ "atl", "Atlanta, GA" },
.
.
.
{ "wbc", "Washington DC, DC" },
/* You may want to add more cities here */
/* Do not forget a null termination */
{ ( char * ) 0, ( char * ) 0 }
} ;
/* Field descriptor data */
fld_desc fields[] = {
{ "", SQL_ISNULL, SQL_TYP_VARCHAR, 30, 0 }, /* city */
{ "", SQL_ISNULL, SQL_TYP_INTEGER, 3, 2 }, /* temp_in_f */
{ "", SQL_ISNULL, SQL_TYP_INTEGER, 3, 7 }, /* humidity */
{ "", SQL_ISNULL, SQL_TYP_VARCHAR, 5, 13 }, /* wind */
{ "", SQL_ISNULL, SQL_TYP_INTEGER, 3, 19 }, /* wind_velocity */
{ "", SQL_ISNULL, SQL_TYP_FLOAT, 5, 24 }, /* barometer */
{ "", SQL_ISNULL, SQL_TYP_VARCHAR, 25, 30 }, /* forecast */
/* You may want to add more fields here */
/* Do not forget a null termination */
{ ( char ) 0, 0, 0, 0, 0 }
} ;
/* Following is the weather data buffer for this example. You */
/* may want to keep the weather data in a separate text file. */
/* Uncomment the following fopen() statement. Note that you */
/* have to specify the full path name for this file. */
char * weather_data[] = {
"alb.forecast",
" 34 28% wnw 3 30.53 clear",
"atl.forecast",
" 46 89% east 11 30.03 fog",
.
.
.
"wbc.forecast",
" 38 96% ene 16 30.31 light rain",
/* You may want to add more weather data here */
/* Do not forget a null termination */
( char * ) 0
} ;
#ifdef __cplusplus
extern "C"
#endif
/* This is a subroutine. */
/* Find a full city name using a short name */
int get_name( char * short_name, char * long_name ) {
int name_pos = 0 ;
while ( cities[name_pos].city_short != ( char * ) 0 ) {
if (strcmp(short_name, cities[name_pos].city_short) == 0) {
strcpy( long_name, cities[name_pos].city_long ) ;
/* A full city name found */
return( 0 ) ;
}
name_pos++ ;
}
/* Could not find such city in the city data */
strcpy( long_name, "Unknown City" ) ;
return( -1 ) ;
}
#ifdef __cplusplus
extern "C"
#endif
/* This is a subroutine. */
/* Clean all field data and field null indicator data */
int clean_fields( int field_pos ) {
while ( fields[field_pos].fld_length != 0 ) {
memset( fields[field_pos].fld_field, '\0', 31 ) ;
fields[field_pos].fld_ind = SQL_ISNULL ;
field_pos++ ;
}
return( 0 ) ;
}
#ifdef __cplusplus
extern "C"
#endif
/* This is a subroutine. */
/* Fills all field data and field null indicator data ... */
/* ... from text weather data */
int get_value( char * value, int field_pos ) {
fld_desc * field ;
char field_buf[31] ;
double * double_ptr ;
int * int_ptr, buf_pos ;
while ( fields[field_pos].fld_length != 0 ) {
field = &fields[field_pos] ;
memset( field_buf, '\0', 31 ) ;
memcpy( field_buf,
( value + field->fld_offset ),
field->fld_length ) ;
buf_pos = field->fld_length ;
while ( ( buf_pos > 0 ) &&
( field_buf[buf_pos] == ' ' ) )
field_buf[buf_pos--] = '\0' ;
buf_pos = 0 ;
while ( ( buf_pos < field->fld_length ) &&
( field_buf[buf_pos] == ' ' ) )
buf_pos++ ;
if ( strlen( ( char * ) ( field_buf + buf_pos ) ) > 0 ||
strcmp( ( char * ) ( field_buf + buf_pos ), "n/a") != 0 ) {
field->fld_ind = SQL_NOTNULL ;
/* Text to SQL type conversion */
switch( field->fld_type ) {
case SQL_TYP_VARCHAR:
strcpy( field->fld_field,
( char * ) ( field_buf + buf_pos ) ) ;
break ;
case SQL_TYP_INTEGER:
int_ptr = ( int * ) field->fld_field ;
*int_ptr = atoi( ( char * ) ( field_buf + buf_pos ) ) ;
break ;
case SQL_TYP_FLOAT:
double_ptr = ( double * ) field->fld_field ;
*double_ptr = atof( ( char * ) ( field_buf + buf_pos ) ) ;
break ;
/* You may want to add more text to SQL type conversion here */
}
}
field_pos++ ;
}
return( 0 ) ;
}
#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN weather( /* Return row fields */
SQLUDF_VARCHAR * city,
SQLUDF_INTEGER * temp_in_f,
SQLUDF_INTEGER * humidity,
SQLUDF_VARCHAR * wind,
SQLUDF_INTEGER * wind_velocity,
SQLUDF_DOUBLE * barometer,
SQLUDF_VARCHAR * forecast,
/* You may want to add more fields here */
/* Return row field null indicators */
SQLUDF_NULLIND * city_ind,
SQLUDF_NULLIND * temp_in_f_ind,
SQLUDF_NULLIND * humidity_ind,
SQLUDF_NULLIND * wind_ind,
SQLUDF_NULLIND * wind_velocity_ind,
SQLUDF_NULLIND * barometer_ind,
SQLUDF_NULLIND * forecast_ind,
/* You may want to add more field indicators here */
/* UDF always-present (trailing) input arguments */
SQLUDF_TRAIL_ARGS_ALL
) {
scratch_area * save_area ;
char line_buf[81] ;
int line_buf_pos ;
/* SQLUDF_SCRAT is part of SQLUDF_TRAIL_ARGS_ALL */
/* Preserve information from one function call to the next call */
save_area = ( scratch_area * ) ( SQLUDF_SCRAT->data ) ;
/* SQLUDF_CALLT is part of SQLUDF_TRAIL_ARGS_ALL */
switch( SQLUDF_CALLT ) {
/* First call UDF: Open table and fetch first row */
case SQL_TF_OPEN:
/* If you use a weather data text file specify full path */
/* save_area->file_ptr = fopen("/sqllib/samples/c/tblsrv.dat",
"r"); */
save_area->file_pos = 0 ;
break ;
/* Normal call UDF: Fetch next row */
case SQL_TF_FETCH:
/* If you use a weather data text file */
/* memset(line_buf, '\0', 81); */
/* if (fgets(line_buf, 80, save_area->file_ptr) == NULL) { */
if ( weather_data[save_area->file_pos] == ( char * ) 0 ) {
/* SQLUDF_STATE is part of SQLUDF_TRAIL_ARGS_ALL */
strcpy( SQLUDF_STATE, "02000" ) ;
break ;
}
memset( line_buf, '\0', 81 ) ;
strcpy( line_buf, weather_data[save_area->file_pos] ) ;
line_buf[3] = '\0' ;
/* Clean all field data and field null indicator data */
clean_fields( 0 ) ;
/* Fills city field null indicator data */
fields[0].fld_ind = SQL_NOTNULL ;
/* Find a full city name using a short name */
/* Fills city field data */
if ( get_name( line_buf, fields[0].fld_field ) == 0 ) {
save_area->file_pos++ ;
/* If you use a weather data text file */
/* memset(line_buf, '\0', 81); */
/* if (fgets(line_buf, 80, save_area->file_ptr) == NULL) { */
if ( weather_data[save_area->file_pos] == ( char * ) 0 ) {
/* SQLUDF_STATE is part of SQLUDF_TRAIL_ARGS_ALL */
strcpy( SQLUDF_STATE, "02000" ) ;
break ;
}
memset( line_buf, '\0', 81 ) ;
strcpy( line_buf, weather_data[save_area->file_pos] ) ;
line_buf_pos = strlen( line_buf ) ;
while ( line_buf_pos > 0 ) {
if ( line_buf[line_buf_pos] >= ' ' )
line_buf_pos = 0 ;
else {
line_buf[line_buf_pos] = '\0' ;
line_buf_pos-- ;
}
}
}
/* Fills field data and field null indicator data ... */
/* ... for selected city from text weather data */
get_value( line_buf, 1 ) ; /* Skips city field */
/* Builds return row fields */
strcpy( city, fields[0].fld_field ) ;
memcpy( (void *) temp_in_f,
fields[1].fld_field,
sizeof( SQLUDF_INTEGER ) ) ;
memcpy( (void *) humidity,
fields[2].fld_field,
sizeof( SQLUDF_INTEGER ) ) ;
strcpy( wind, fields[3].fld_field ) ;
memcpy( (void *) wind_velocity,
fields[4].fld_field,
sizeof( SQLUDF_INTEGER ) ) ;
memcpy( (void *) barometer,
fields[5].fld_field,
sizeof( SQLUDF_DOUBLE ) ) ;
strcpy( forecast, fields[6].fld_field ) ;
/* Builds return row field null indicators */
memcpy( (void *) city_ind,
&(fields[0].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
memcpy( (void *) temp_in_f_ind,
&(fields[1].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
memcpy( (void *) humidity_ind,
&(fields[2].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
memcpy( (void *) wind_ind,
&(fields[3].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
memcpy( (void *) wind_velocity_ind,
&(fields[4].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
memcpy( (void *) barometer_ind,
&(fields[5].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
memcpy( (void *) forecast_ind,
&(fields[6].fld_ind),
sizeof( SQLUDF_NULLIND ) ) ;
/* Next city weather data */
save_area->file_pos++ ;
break ;
/* Special last call UDF for cleanup (no real args!): Close table */
case SQL_TF_CLOSE:
/* If you use a weather data text file */
/* fclose(save_area->file_ptr); */
/* save_area->file_ptr = NULL; */
save_area->file_pos = 0 ;
break ;
}
}
Referring to this UDF code, observe that:
Following is the CREATE FUNCTION statement for this UDF:
CREATE FUNCTION tfweather_u()
RETURNS TABLE (CITY VARCHAR(25),
TEMP_IN_F INTEGER,
HUMIDITY INTEGER,
WIND VARCHAR(5),
WIND_VELOCITY INTEGER,
BAROMETER FLOAT,
FORECAST VARCHAR(25))
SPECIFIC tfweather_u
DISALLOW PARALLELISM
NOT FENCED
DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
SCRATCHPAD
NO FINAL CALL
LANGUAGE C
PARAMETER STYLE DB2SQL
EXTERNAL NAME 'tf_dml!weather';
The above CREATE FUNCTION statement is for a UNIX version of this UDF. For other platforms, you may need to modify the value specified in the EXTERNAL NAME clause.
Referring to this statement, observe that:
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.
#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <sql.h>
#include <sqlca.h>
#include <sqlda.h>
#include <sqludf.h>
#include "util.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, 100 bytes at a time.
* Depending on the "criteria" passed in, we may decide
* to append the 100 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 100 bytes, and append to result if it meets
* the criteria.
*/
for (input_pos = 0; (input_pos < input_len); input_pos += 100) {
/* Read the next 100 (or less) bytes of the input LOB value */
rc = sqludf_substr(lob_input, input_pos, 100,
(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 condition for retaining the segment is TRUE...
* Write that buffer segment which was last read in
*/
rc = sqludf_append(lob_output,
(unsigned char *) lob_buf, input_rec, &output_rec) ;
/* Error and exit if unable to read the 100 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 100-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:
Following is the CREATE FUNCTION statement for this UDF:
CREATE FUNCTION carve(CLOB(50M), VARCHAR(255) )
RETURNS CLOB(50M)
NOT NULL CALL
NOT FENCED
DETERMINISTIC
NO SQL
NO EXTERNAL ACTION
LANGUAGE C
PARAMETER STYLE DB2SQL
EXTERNAL NAME '/u/wilfred/udfs/lobudfs!lob_subsetter' ;
(This statement is for an AIX version of this UDF. For other platforms, you may need to modify the value specified in the EXTERNAL NAME clause.)
Referring to this statement, observe that:
Now you can successfully run the following statement:
UPDATE tablex
SET col_a = 99,
col_b = carve (:hv_clob, '...criteria...')
WHERE tablex_key = :hv_key;
The UDF is used to subset the CLOB value represented by the host variable :hv_clob and update the row represented by key value in host variable :hv_key.
In this update example by the way, it may be that :hv_clob is defined in the application as a CLOB_LOCATOR. It is not this same locator which will be passed to the "carve" UDF! When :hv_clob is "bound in" to the DB2 engine agent running the statement, it is known only as a CLOB. When it is then passed to the UDF, DB2 generates a new locator for the value. This conversion back and forth between CLOB and locator is not expensive, by the way; it does not involve any extra memory copies or I/O.
The following example implements a counter class using Microsoft Visual BASIC. The class has an instance variable, nbrOfInvoke, that tracks the number of invocations. The constructor of the class initializes the number to 0. The increment method increments nbrOfInvoke by 1 and returns the current state.
Description="Example in SQL Reference"
Name="bert"
Class=bcounter; bcounter.cls
ExeName32="bert_app.exe"
VERSION 1.0 CLASS
BEGIN
SingleUse = -1 'True
END
Attribute VB_Name = "bcounter"
Attribute VB_Creatable = True
Attribute VB_Exposed = True
Option Explicit
Dim nbrOfInvoke As Long
Public Sub increment(output As Long, _
output_ind As Integer, _
sqlstate As String, _
fname As String, _
fspecname As String, _
msg As String, _
scratchpad() As Byte, _
calltype As Long)
nbrOfInvoke = nbrOfInvoke + 1
End Sub
Private Sub Class_Initialize()
nbrOfInvoke = 0
End Sub
Private Sub Class_Terminate()
End Sub
The bcounter class is implemented as an OLE automation object and registered under the progId bert.bcounter. You can compile the automation server either as an in-process or local server; this is transparent to DB2. The following CREATE FUNCTION statement registers a UDF bcounter with the increment method as an external implementation:
CREATE FUNCTION bcounter () RETURNS integer
EXTERNAL NAME 'bert.bcounter!increment'
LANGUAGE OLE
FENCED
SCRATCHPAD
FINAL CALL
NOT DETERMINISTIC
NULL CALL
PARAMETER STYLE DB2SQL
NO SQL
NO EXTERNAL ACTION
DISALLOW PARALLEL;
For the following query:
SELECT INT1, BCOUNTER() AS COUNT, INT1/BCOUNTER() AS DIV FROM TEST
The results are exactly the same as in the previous example:
INT1 COUNT DIV
----------- ----------- -----------
16 1 16
8 2 4
4 3 1
2 4 0
97 5 19
5 record(s) selected.
The following example implements the previous BASIC counter class in C++. Only fragments of the code are shown here, a listing of the entire sample can be found in the /sqllib/samples/ole directory.
The increment method is described in the Object Description Language as part of the counter interface description:
interface ICounter : IDispatch
{
...
HRESULT increment([out] long *out,
[out] short *outnull,
[out] BSTR *sqlstate,
[in] BSTR *fname,
[in] BSTR *fspecname,
[out] BSTR *msgtext,
[in,out] SAFEARRAY (unsigned char) *spad,
[in] long *calltype);
...
}
The COM CCounter class definition in C++ includes the declaration of the increment method as well as nbrOfInvoke:
class FAR CCounter : public ICounter
{
...
STDMETHODIMP CCounter::increment(long *out,
short *outnull,
BSTR *sqlstate,
BSTR *fname,
BSTR *fspecname,
BSTR *msgtext,
SAFEARRAY **spad,
long *calltype );
long nbrOfInvoke;
...
};
The C++ implementation of the method is similar to the BASIC code:
STDMETHODIMP CCounter::increment(long *out,
short *outnull,
BSTR *sqlstate,
BSTR *fname,
BSTR *fspecname,
BSTR *msgtext,
SAFEARRAY **spad,
long *calltype)
{
nbrOfInvoke = nbrOfInvoke + 1;
*out = nbrOfInvoke;
return NOERROR;
};
In the above example, sqlstate and msgtext are [out] parameters of type BSTR*, that is, DB2 passes a pointer to NULL to the UDF. To return values for these parameters, the UDF allocates a string and returns it to DB2 (for example, *sqlstate = SysAllocString (L"01H00")), and DB2 frees the memory. The parameters fname and fspecname are [in] parameters. DB2 allocates the memory and passes in values which are read by the UDF, and then DB2 frees the memory.
The class factory of the CCounter class creates counter objects. You can register the class factory as a single-use or multi-use object (not shown in this example).
STDMETHODIMP CCounterCF::CreateInstance(IUnknown FAR* punkOuter,
REFIID riid,
void FAR* FAR* ppv)
{
CCounter *pObj;
...
// create a new counter object
pObj = new CCounter;
...
};
The CCounter class is implemented as a local server, and it is registered under the progId bert.ccounter. The following CREATE FUNCTION statement registers a UDF ccounter with the increment method as an external implementation:
CREATE FUNCTION ccounter () RETURNS integer
EXTERNAL NAME 'bert.ccounter!increment'
LANGUAGE OLE
FENCED
SCRATCHPAD
FINAL CALL
NOT DETERMINISTIC
NULL CALL
PARAMETER STYLE DB2SQL
NO SQL
NO EXTERNAL ACTION
DISALLOW PARALLEL;
While processing the following query, DB2 creates two different instances of class CCounter. An instance is created for each UDF reference in the query. The two instances are reused for the entire query as the scratchpad option is specified in the ccounter UDF registration.
SELECT INT1, CCOUNTER() AS COUNT, INT1/CCOUNTER() AS DIV FROM TEST
The results are exactly the same as in the previous example:
INT1 COUNT DIV
----------- ----------- -----------
16 1 16
8 2 4
4 3 1
2 4 0
97 5 19
5 record(s) selected.
The following example implements a class using Microsoft Visual BASIC that exposes a public method list to retrieve message header information and the partial message text of messages in Microsoft Exchange. The method implementation employs OLE Messaging which provides an OLE automation interface to MAPI (Messaging API).
Description="Mail OLE Automation Table Function"
Module=MainModule; MainModule.bas
Class=Header; Header.cls
ExeName32="tfmapi.dll"
Name="TFMAIL"
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
END
Attribute VB_Name = "Header"
Attribute VB_Creatable = True
Attribute VB_Exposed = True
Option Explicit
Dim MySession As Object
Dim MyMsgColl As Object
Dim MyMsg As Object
Dim CurrentSender As Object
Dim name As Variant
Const SQL_TF_OPEN = -1
Const SQL_TF_CLOSE = 1
Const SQL_TF_FETCH = 0
Public Sub List(timereceived As Date, subject As String, size As Long, _
text As String, ind1 As Integer, ind2 As Integer, _
ind3 As Integer, ind4 As Integer, sqlstate As String, _
fname As String, fspecname As String, msg As String, _
scratchpad() As Byte, calltype As Long)
If (calltype = SQL_TF_OPEN) Then
Set MySession = CreateObject("MAPI.Session")
MySession.Logon ProfileName:="Profile1"
Set MyMsgColl = MySession.Inbox.Messages
Set MyMsg = MyMsgColl.GetFirst
ElseIf (calltype = SQL_TF_CLOSE) Then
MySession.Logoff
Set MySession = Nothing
Else
If (MyMsg Is Nothing) Then
sqlstate = "02000"
Else
timereceived = MyMsg.timereceived
subject = Left(MyMsg.subject, 15)
size = MyMsg.size
text = Left(MyMsg.text, 30)
Set MyMsg = MyMsgColl.GetNext
End If
End If
End Sub
On the table function OPEN call, the CreateObject statement creates a mail session, and the logon method logs on to the mail system (user name and password issues are neglected). The message collection of the mail inbox is used to retrieve the first message. On the FETCH calls, the message header information and the first 30 characters of the current message are assigned to the table function output parameters. If no messages are left, SQLSTATE 02000 is returned. On the CLOSE call, the example logs off and sets the session object to nothing, which releases all the system and memory resources associated with the previously referenced object when no other variable refers to it.
Following is the CREATE FUNCTION statement for this UDF:
CREATE FUNCTION MAIL()
RETURNS TABLE (TIMERECIEVED DATE,
SUBJECT VARCHAR(15),
SIZE INTEGER,
TEXT VARCHAR(30))
EXTERNAL NAME 'tfmail.header!list'
LANGUAGE OLE
PARAMETER STYLE DB2SQL
NOT DETERMINISTIC
FENCED
NULL CALL
SCRATCHPAD
FINAL CALL
NO SQL
EXTERNAL ACTION
DISALLOW PARALLEL;
Following is a sample query:
SELECT * FROM TABLE (MAIL()) AS M
TIMERECEIVED SUBJECT SIZE TEXT
------------ --------------- ----------- ------------------------------
01/18/1997 Welcome! 3277 Welcome to Windows Messaging!
01/18/1997 Invoice 1382 Please process this invoice. T
01/19/1997 Congratulations 1394 Congratulations to the purchas
3 record(s) selected.