/*******************************************************************************
**
** Source File Name = udf.c 1.2
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 1999
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
**
**
** PURPOSE :
** This is a library of UDFs which are made to be used with the tables in
** the SAMPLE database.
**
** To make the library:
** Drop any instances of the functions you are about to create that
** previously existed.
** Compile without linking.
** Link with the DB2 libraries.
** Copy the compiled library to the "function" directory of the DB2INSTANCE
** that the library will used with.
**
** There is an example program called "calludf" which creates the
** functions from the "udf" library runs some of the UDFs.
**
** EXTERNAL DEPENDENCIES :
** - Ensure existence of database for precompile purposes.
** - Compile and link with the IBM Cset++ compiler (AIX and OS/2)
** or the Microsoft Visual C++ compiler (Windows)
** or the compiler supported on your platform.
**
** For more information about these samples see the README file.
**
** For more information on programming in C, see the:
** - "Programming in C and C++" section of the Application Development Guide
** For more information on Building C Applications, see the:
** - "Building C Applications" section of the Application Building Guide.
**
** For more information on the SQL language see the SQL Reference.
**
*******************************************************************************/
#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 {
long len;
long countr;
char not_used[96];
} ;
/*************************************************************************
* function increase: increases the salary by a certain set amount that
* is passed in.
*
* inputs : DOUBLE salary original salary
* DOUBLE increase monetary increase to salary
* output : DOUBLE newSalary
**************************************************************************/
#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN increase (
SQLUDF_DOUBLE *salary,
SQLUDF_DOUBLE *increase,
SQLUDF_DOUBLE *newSalary,
SQLUDF_SMALLINT *salaryNullInd,
SQLUDF_SMALLINT *increaseNullInd,
SQLUDF_SMALLINT *newSalaryNullInd,
SQLUDF_TRAIL_ARGS) {
if (*increaseNullInd == -1) {
*newSalaryNullInd = -1;
} else {
*newSalary = *salary + *increase;
*newSalaryNullInd = 0;
} /* endif */
}
/* end of UDF : increase */
/*************************************************************************
* function raisesal: raises the salary by a percentage factor. 1 is 100%
*
* inputs : DOUBLE salary original salary
* DOUBLE percentage percentage to increase by [.99]
* output : DOUBLE newsalary
**************************************************************************/
#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN raisesal (
SQLUDF_DOUBLE *salary,
SQLUDF_DOUBLE *percentage,
SQLUDF_DOUBLE *newSalary,
SQLUDF_SMALLINT *salaryNullInd,
SQLUDF_SMALLINT *percentageNullInd,
SQLUDF_SMALLINT *newSalaryNullInd,
SQLUDF_TRAIL_ARGS) {
if (*percentageNullInd == -1) {
*newSalaryNullInd = -1;
} else {
*newSalary = *salary * (1 + *percentage);
*newSalaryNullInd = 0;
} /* endif */
}
/* end of UDF : raisesal */
/*************************************************************************
* function promote : promotes the "job" field of "staff" table to a
* higher ranking position.
*
* inputs : VARCHAR job
* output : VARCHAR newJob
**************************************************************************/
#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN promote (
SQLUDF_CHAR *job,
SQLUDF_CHAR *newJob,
SQLUDF_SMALLINT *jobNullInd,
SQLUDF_SMALLINT *newJobNullInd,
SQLUDF_TRAIL_ARGS) {
SQLUDF_CHAR Job[10];
if (strcmp( ( char * ) job, "Mgr" ) == 0 ) {
strcpy( ( char * ) Job, "SUPER" ) ;
} else if (strcmp( ( char * ) job, "Sales" ) == 0) {
strcpy( ( char * ) Job, "Mgr" ) ;
} else if (strcmp( ( char * ) job, "Clerk" ) == 0) {
strcpy( ( char * ) Job, "Sales" ) ;
} else {
strcpy( ( char * ) Job, "New" ) ;
} /* endif */
strcpy( ( char * ) newJob, ( char * ) Job ) ;
*newJobNullInd = 0 ;
}
/* end of UDF : promote */
/*************************************************************************
* function wordcount : counts the number of words in the CLOB that is
* passed in.
*
* inputs: SQLUDF_CLOB in1 CLOB data
* output: SQLUDF_SMALLINT out the number of words
* (number of blank spaces found).
**************************************************************************/
#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN wordcount(
SQLUDF_CLOB *in1,
SQLUDF_INTEGER *out,
SQLUDF_NULLIND *in1null,
SQLUDF_NULLIND *outnull,
SQLUDF_TRAIL_ARGS) {
SQLUDF_INTEGER count = 0;
SQLUDF_INTEGER ind;
SQLUDF_SMALLINT blank = 0;
for (ind = 0; ind < in1->length; ind++) {
if (blank == 0 && in1->data[ind] != ' ') {
blank = 1;
count++;
} else if (blank == 1 && in1->data[ind] == ' ') {
blank = 0;
} /* endif */
} /* endfor */
*out = count;
*outnull = 0;
}
/* end of UDF : wordcount */
/*************************************************************************
* 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 (
long *num, /* numerator */
long *denom, /* denominator */
long *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 */
/*************************************************************************
* 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 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 (
long *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 */
/*************************************************************************
* 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 */
strcpy( ( char * ) sqludf_sqlstate, "38999" ) ; /* error state */
strcpy( ( char * ) sqludf_msgtext, "findvwl: No Vowel" ) ; /* message insert */
} else { /* a vowel was found at "i" */
*out = i + 1;
*outnull = 0;
} /* endif */
}
/* end of UDF : findvwl */
/*************************************************************************
* function ilob: output = position of string represented by
* 2nd argument in LOB represented by 1st arg.
* returns null if no match
* inputs: CLOB, input lob
* VARCHAR string to match
* output: INTEGER position
**************************************************************************/
#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN ilob(
SQLUDF_CLOB *in1, /* input lob */
SQLUDF_CHAR *in2, /* input string to match */
SQLUDF_INTEGER *out, /* output SQLUDF_CHAR */
SQLUDF_SMALLINT *in1null, /* input null ind */
SQLUDF_SMALLINT *in2null, /* input null ind */
SQLUDF_SMALLINT *outnull, /* output null ind */
SQLUDF_TRAIL_ARGS ) {
SQLUDF_INTEGER len1, len2, i;
len2 = strlen(in2); /* first calc len of 2nd arg */
len1 = in1->length; /* grab length of 1st arg */
*out = 0; /* initialize output */
/* make sure lob is SQLUDF_INTEGERer than string */
if (len1 >= len2)
{
/* keep looking for match until find it or done */
for (i = 0; (i <= len1 - len2 && *out == 0); i++)
{
/* see if this position is the match */
if (0 == strncmp(in2, &in1->data[i], len2))
{
/* yup it is */
*out = i+1;
} /* endif */
} /* endfor */
} /* endif */
/* if have answer make result not null */
if (*out != 0) {
/* indicate non-null result */
*outnull = 0;
} else {
/* indicate null result */
*outnull = -1;
} /* endif */
}
/* end of UDF: ilob */
/*************************************************************************
* function leni: output = input * 2
* input : INTEGER, nullable
* output: INTEGER
**************************************************************************/
#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN leni(
SQLUDF_INTEGER *in1, /* input val */
SQLUDF_INTEGER *out, /* output val */
SQLUDF_SMALLINT *in1null, /* input null ind */
SQLUDF_SMALLINT *outnull, /* output null ind */
SQLUDF_TRAIL_ARGS) {
SQLUDF_INTEGER result; /* local var for result */
if (*in1null == -1) {
*outnull = -1;
} else {
/* input is not null, try the doubling */
result = *in1 * 2;
/* see if result is expressible in INTEGER */
if (result > 2000000000 ||
result < -2000000000) {
/* not expressible, set null result */
*outnull = -1;
} else {
/* result is OK, set it and zero output null ind */
*out = result;
*outnull = 0;
} /* endif */
} /* endif */
}
/* end of UDF: leni */
/*
***************************************************************************
* Thai UDFs: thai_in, thai_out
***************************************************************************
*/
/*--------------------------------------------------*/
/* function thai_in: Prepares the data to be sorted */
/* output = thai_encoded_sequence */
/* input : varchar */
/* output: varchar */
/*--------------------------------------------------*/
void SQL_API_FN thai_in(
SQLUDF_CHAR *in1,
SQLUDF_CHAR *out1,
SQLUDF_SMALLINT *in1null,
SQLUDF_SMALLINT *out1null,
SQLUDF_TRAIL_ARGS) {
short i,j;
SQLUDF_SMALLINT c1;
i=j=0;
for (i=0; (i < strlen(in1)); i++,j++) {
c1=(unsigned char)in1[i];
if ((c1>0xdf)&&(c1<0xe5)) /* swap leading vowel? */
{
i++;
out1[j]=in1[i];
j++;
}
out1[j]=c1;
} /* endfor */
out1[j]='\0';
*out1null = 0;
return;
}
/* end of UDF: thai_in */
/*-----------------------------------------------------*/
/* function thai_out: Prepares the data to be displayed*/
/* output = thai_decoded_sequence */
/* input : char string, nullable */
/* output: char string */
/*-----------------------------------------------------*/
void SQL_API_FN thai_out(
SQLUDF_CHAR *in2,
SQLUDF_CHAR *out2,
SQLUDF_SMALLINT *in2null,
SQLUDF_SMALLINT *out2null,
SQLUDF_TRAIL_ARGS) {
short i,j;
SQLUDF_SMALLINT c1,c2;
i=j=0;
out2[j]=in2[i];
c1=(unsigned char)out2[j];
for (i=j=1; (i < strlen(in2)); i++,j++) {
c2=(unsigned char)in2[i];
if ((c2>0xdf)&&(c2<0xe5)) /* swap BACK leading vowel? */
{
out2[j]=c1;
j--;
out2[j]=c2;
j++;
}
else
{
out2[j]=c2;
}
/* endif */
c1=c2;
} /* endfor */
out2[j]='\0';
*out2null = 0;
return;
}
/* end of UDF: thai_out */