/*******************************************************************************
**
** Source File Name = udf.c
**
** Licensed Materials - Property of IBM
**
** (C) COPYRIGHT International Business Machines Corp. 1995, 2000
** 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 {
   sqlint32 len;
   sqlint32 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    *pincrease,
   SQLUDF_DOUBLE    *newSalary,
   SQLUDF_SMALLINT  *salaryNullInd,
   SQLUDF_SMALLINT  *increaseNullInd,
   SQLUDF_SMALLINT  *newSalaryNullInd,
   SQLUDF_TRAIL_ARGS) {

   if (*increaseNullInd == -1) {
      *newSalaryNullInd = -1;
   } else {
      *newSalary = *salary + *pincrease;
      *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 (
   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 */

 /*************************************************************************
*  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 (
   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 */

 /*************************************************************************
*  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                           */
/*--------------------------------------------------*/

#ifdef __cplusplus
extern "C"
#endif
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                          */
/*-----------------------------------------------------*/

#ifdef __cplusplus
   extern "C"
#endif
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 */