アプリケーション開発の手引き

UDF コードの例

次の UDF コード例が DB2 で提供されます。

例: 整数除算演算子
例: CLOB の折り返し、母音の検出
例: カウンター

提供されるすべての例の記載ページと、それらの呼び出し方法については、 付録 B, サンプル・プログラムを参照してください。

UDF のコンパイルとリンクに関する説明は、 アプリケーション構築の手引き を参照してください。

それぞれの UDF 例には、対応する CREATE FUNCTION ステートメントと、使用法を示す短いシナリオが付いています。このシナリオはすべて、次の TEST という表を使います。 TEST 表は、このシナリオで行われる一定の項目を説明するために慎重に加工されています。以下にこの表の定義を示します。

     CREATE TABLE TEST (INT1 INTEGER,
                        INT2 INTEGER,
                        PART CHAR(5),
                        DESCR CLOB(33K))

この表の作成後、CLP を使って次のステートメントを出してその内容を表示します。

     SELECT INT1, INT2, PART, SUBSTR(DESCR,1,50) FROM TEST

CLOB 列で SUBSTR 関数を使用すると、出力が読みやすくなることに注目してください。次のような CLP 出力が表示されます。

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.

例およびそれに続くシナリオを読む際に、表 TEST での上記のような情報を参照してください。

例: 整数除算演算子

DB2 で整数除算処理をすると、除数がゼロのときにエラー SQLCODE -802 (SQLSTATE 22003) が戻され、ステートメントが終了してしまうため都合が悪いとしましょう。 (DFT_SQLMATHWARN 構成パラメーターで friendly 算術計算 を使用できるようにする場合、 DB2 はこの状況下でエラーを戻すのではなく NULL を戻します。) 整数除算が NULL を戻すようにするには、次の 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 */

この UDF の場合、次の点に注意してください。

以下に、この UDF に対する CREATE FUNCTION ステートメントを示します。

     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' ;

(上のステートメントは、この UDF の AIX バージョン向けです。その他のプラットフォームの場合は、EXTERNAL NAME 文節で指定された値を修正する必要があります。)

このステートメントでは次の点に注意してください。

ここで、次の 1 組のステートメント (CLP 入力を示す) を実行するとします。

 
     SET CURRENT FUNCTION PATH = SYSIBM, SYSFUN, SLICK
     SELECT INT1, INT2, INT1/INT2, "/"(INT1,INT2) FROM TEST

CLP から以下の出力が表示されます(データベース構成パラメーター DFT_SQLMATHWARN で friendly 算術計算 を使用可能にしない場合)。

 
   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

CURRENT FUNCTION PATH の特殊レジスターを、 "/" UDF が定義されている MATH スキーマ以外のスキーマの連結に設定したので、 SQL0802N エラー・メッセージが出されます。したがって、DB2 の組み込み除算演算子を実行しており、その演算子は、「ゼロによる除算」条件が発生した際にエラーとなるよう定義されています。 TEST 表の中の 4 番目の行はこの条件を提供します。

ただし、関数のパスを変更する場合は、そのパスの SYSIBM の前に MATH を入れて SELECT ステートメントを再実行してください。

 
     SET CURRENT FUNCTION PATH = MATH, SYSIBM, SYSFUN, SLICK
     SELECT INT1, INT2, INT1/INT2, "/"(INT1,INT2) FROM TEST

次に、以下の CLP 出力が示すような要求どおりの動作を取得します。

 
   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.

上記の例では、次の点に注意してください。

例: CLOB の折り返し、母音の検出

テキストを処理するアプリケーションに役立つ 2 つの UDF をコーディングしたと仮定します。最初の UDF は、テキスト・ストリングを n 番目のバイト以降で折り返し ます。この例でいう折り返しとは、元来は n バイトの後ろにあった部分を、元来は n+1 バイトの前にあった部分に置くことを意味します。言い換えれば、UDF は最初の n バイトをストリングの先頭から終わりに移動します。 2 番目の関数は、テキスト・ストリングの中の最初の母音の位置を戻します。これらの関数は両方とも udf.c サンプル・ファイルにコード化されています。

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

上記の UDF では次のことに注意してください。

以下に、これらの UDF に対する CREATE FUNCTION ステートメントを示します。

     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' ;

上記の CREATE FUNCTION ステートメントは UNIX ベースのプラットフォーム用です。その他のプラットフォームの場合、上記のステートメントの EXTERNAL NAME 文節内に指定された値を修正する必要があります。 CREATE FUNCTION ステートメントは、 DB2 に添付されている calludf.sqc サンプル・プログラムの中にあります。

これらの CREATE ステートメントに関しては、次の点に注意してください。

以上の結果、次のステートメントを正常に実行することができます。

 
     SELECT SUBSTR(DESCR,1,30), SUBSTR(FOLD(DESCR,6),1,30) FROM TEST

このステートメントに対する CLP からの出力結果は次のとおりです。

     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.

SUBSTR 組み込み関数を使用すると、選択された CLOB 値がより見やすく表示されます。ここでは、出力が折り返される様子が示されます (2、3、および 5 行目によく示されています。これは最初の行より短い CLOB 値を持つためです。折り返しは SUBSTR を使用するとさらに明らかです)。また、入力テキスト・ストリング (列 DESCR) がヌルの場合に INVALID INPUT ストリングが FOLD UDF により戻される様子が (4 行目で) 示されます。この SELECT では、関数参照の単純なネストも示されます。 FOLD への参照は SUBSTR 関数参照の引き数の範囲内で行われます。

次に、以下のステートメントを実行するとします。

     SELECT PART, FINDV(PART) FROM TEST

CLP 出力は次のようになります。

     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

この例では、findvwl() により戻される 38999 SQLSTATE 値およびエラー・メッセージ・トークンが処理される様子を示します。メッセージ SQL0443N は、この情報をユーザーに戻します。 5 行目の PART 列には母音は含まれておらず、これは UDF 内のエラーを引き起こす条件です。

この例では、引き数のプロモーションが行われます。 PART 列は CHAR(5) であり、VARCHAR にプロモートされて FINDV に渡されます。

最後に、FINDV についての CREATE ステートメント内で NOT NULL CALL が指定された結果として、 4 行目で DB2 により FINDV からヌルの出力が生成された様子に注意してください。

次のステートメントをご覧ください。

     SELECT SUBSTR(DESCR,1,25), FINDV(CAST (DESCR AS VARCHAR(60) ) )
     FROM TEST

このステートメントが 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.

この SELECT ステートメントでは、FINDV が VARCHAR 入力引き数で作動する様子が示されます。これを行うために列 DESCR を VARCHAR にキャストする方法に注意してください。キャストを行わないと CLOB が VARCHAR にプロモートされないため、 CLOB 引き数で FINDV を使用することはできません。この場合も、組み込み SUBSTR 関数は DESCR 列の値を見やすく表示させるために使用されます。

ここでもまた、 NOT NULL CALL のために FINDV からヌルの結果が 4 行目で生成されることに注意してください。

例: カウンター

SELECT ステートメント内の行数だけを数えたいと仮定します。このため、カウンターを増分して戻す UDF を定義します。この UDF はスクラッチパッドを使用します。

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

この UDF の場合、次のことに注意してください。

以下に、この UDF に対する CREATE FUNCTION ステートメントを示します。

     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;

(上のステートメントは、この UDF の AIX バージョン向けです。その他のプラットフォームの場合は、EXTERNAL NAME 文節で指定された値を修正する必要があります。)

このステートメントに関して、次の点に注意してください。

以上の結果、次のステートメントを正常に実行することができます。

 
     SELECT INT1, COUNTER(), INT1/COUNTER() FROM TEST

このステートメントを CLP を介して実行すると、以下のように出力されます。

 
INT1        2           3
----------- ----------- ----------- 
         16           1          16
          8           2           4 
          4           3           1 
          2           4           0 
         97           5          19 
  5 record(s) selected.

2 番目の列には、COUNTER() 出力がそのまま示されていることに注目してください。 3 番目の列には、SELECT ステートメント内の COUNTER() に対する 2 つの別個の参照が、それぞれ独自のスクラッチパッドを獲得することが示されます。それぞれが独自のスクラッチパッドを獲得しなかった場合、 2 番目の列の出力結果は 1 2 3 4 5 という正しい順序とはならず、 1 3 5 7 9 となります。

例: 天気表関数

次に示す例は、表関数 tfweather_u (DB2 のサンプル・プログラム tblsrv.c に収録) で、この関数は米国の各都市の気象情報を戻します。各都市の天気データがサンプル・プログラムに組み込まれていますが、プログラム中の注釈が示すように、天気データを外部ファイルから読み込むことも可能です。このデータには、都市の名前とそれに続いてその都市の気象情報が入っています。この順番で、その他の都市についても繰り返されます。 DB2 には、この表関数を呼び出し、取り出された天気データを tfweather_u 表関数を使用して印刷するクライアント・アプリケーション (tblcli.sqc) が付属しています。

#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 ;
 
  }
}

この UDF コードでは、次のことに注意してください。

以下に、この UDF に対する CREATE FUNCTION ステートメントを示します。

     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';

上の CREATE FUNCTION ステートメントは、この UDF の UNIX バージョン用です。その他のプラットフォームの場合は、EXTERNAL NAME 文節で指定された値を修正する必要があります。

このステートメントに関して、次の点に注意してください。

例: LOB ロケーターを使用する関数

この UDF は、入力 LOB にロケーターを取り、この入力 LOB のサブセットである別の LOB にロケーターを戻します。 2 番目の入力値として一定の基準が渡され、それにより正確に入力 LOB を分ける方法を UDF に伝えます。

#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;
	}

この UDF コードでは、次のことに注意してください。

以下に、この UDF に対する CREATE FUNCTION ステートメントを示します。

     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' ;

(上のステートメントは、この UDF の AIX バージョン向けです。その他のプラットフォームの場合は、EXTERNAL NAME 文節で指定された値を修正する必要があります。)

このステートメントに関して、次の点に注意してください。

以上の結果、次のステートメントを正常に実行することができます。

 
     UPDATE tablex
       SET col_a = 99,
           col_b = carve (:hv_clob, '...criteria...')
       WHERE tablex_key = :hv_key;

この UDF を使用すると、ホスト変数 :hv_clob によって表される CLOB 値をサブセット化し、ホスト変数 :hv_key によって表される行を更新できます。

この更新例では、:hv_clob をアプリケーション内で CLOB_LOCATOR として定義することができます。 "carve" UDF に渡されるのは、このロケーターではありません。 :hv_clob は、ステートメントを実行している DB2 中心エージェントに「バインド」されている場合、 CLOB としてのみ認識されます。これが UDF に渡されると、DB2 はその値の新しいロケーターを生成します。 CLOB とロケーターの間でのこの変換の繰り返しは、資源を消費するものではありません。これには、余分のメモリー複写や入出力は関係しません。

例: BASIC でのカウンター OLE オートメーション UDF

次の例では、Microsoft Visual BASIC を使用してカウンター・クラスをインプリメントします。カウンター・クラスにはインスタンス変数 nbrOfInvoke があり、呼び出しの数を記録します。このクラスのコンストラクターはその数を 0 に初期化します。増分メソッドは nbrOfInvoke を 1 ずつ増分し、現在の状態を戻します。

 
      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

bcounter クラスは OLE オートメーション・オブジェクトとしてインプリメントされ、 progId bert.bcounter の下で登録されています。オートメーション・サーバーは、プロセス内サーバーまたはローカル・サーバーとしてコンパイルできます。これは DB2 に対して透過です。次の CREATE FUNCTION ステートメントは、 UDF bcounterincrement メソッドに外部インプリメンテーションとして登録します。

     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;

以下の照会を実行するとします。

     SELECT INT1, BCOUNTER() AS COUNT, INT1/BCOUNTER() AS DIV FROM TEST

結果は、前の例とまったく同じです。

     INT1        COUNT       DIV
     ----------- ----------- -----------                               
              16           1          16                               
               8           2           4
               4           3           1                               
               2           4           0                               
              97           5          19                               
                                                                       
       5 record(s) selected.                                           

例: C++ でのカウンター OLE オートメーション UDF

次の例は、前に挙げた BASIC のカウンター・クラスを C++ でインプリメントします。ここでは、コードの一部分のみを示します。サンプル全体のリストは、/sqllib/samples/ole ディレクトリーにあります。

増分メソッドは、カウンター・インターフェースの記述の一部として、オブジェクト記述言語で記述されます。

     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);                
         ...                                                        
     }                                                              

C++ での COM CCounter クラス定義には、 nbrOfInvoke と同様に increment メソッドの宣言が含まれます。

     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;                                    
             ...                                                  
     };                                                           

このメソッドの C++ インプリメンテーションは、BASIC コードと似ています。

     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;                             
     };                                            

上の例では、sqlstatemsgtext は、タイプ BSTR* の [out] パラメーターです。つまり、DB2 はヌルへのポインターをこの UDF に渡します。これらのパラメーターの値を戻すのに、 UDF はストリングを割り振ってそれを DB2 に戻し (たとえば、*sqlstate = SysAllocString (L"01H00") とする)、 DB2 はメモリーを解放します。パラメーター fname および fspecname は、[in] パラメーターです。 DB2 は、メモリーを割り振り、その中に UDF によって読み取られる値を渡し、それからそのメモリーを解放します。

クラス CCounter のクラス factory はカウンター・オブジェクトを作成します。クラス factory は、単独使用または複数使用のオブジェクトとして登録できます (この例では示されません)。

     STDMETHODIMP CCounterCF::CreateInstance(IUnknown FAR* punkOuter,
                                             REFIID riid,
                                             void FAR* FAR* ppv)
     {                                                               
                                                                     
       CCounter *pObj;
             ...                                                     
       // create a new counter object
       pObj = new CCounter;
             ...                                                     
     };                                                              

CCounter クラスは、ローカル・サーバーとしてインプリメントされ、 progId bert.ccounter の下に登録されます。次の CREATE FUNCTION ステートメントは、 UDF ccounter を外部インプリメンテーションとして increment メソッドに登録します。

    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;

次の照会を処理している間、 DB2 はクラス CCounter の 2 つの異なるインスタンスを作成します。照会で UDF を参照するごとに 1 つのインスタンスが作成されます。 ccounter UDF 登録で scratchpad オプションが指定されているので、この 2 つのインスタンスは照会全体で再利用できます。

     SELECT INT1, CCOUNTER() AS COUNT, INT1/CCOUNTER() AS DIV FROM TEST

結果は、前の例とまったく同じです。

     INT1        COUNT       DIV
     ----------- ----------- -----------                               
              16           1          16                               
               8           2           4                               
               4           3           1                               
               2           4           0                               
              97           5          19                               
                                                                       
       5 record(s) selected.                                           

例: BASIC でのメール OLE オートメーション表関数

次の例は、Microsoft Visual BASIC を使用してクラスをインプリメントします。このクラスには、メッセージ・ヘッダー情報とメッセージの部分メッセージ・テキストを Microsoft Exchange で取得するパブリック・メソッド・リストがあります。このメソッドのインプリメンテーションは、 MAPI (メッセージング API) に対する OLE オートメーション・インターフェースを提供する OLE メッセージングを利用します。

 
 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                                                                         

表関数 OPEN を呼び出すと、CreateObject ステートメントがメール・セッションを作成し、 logon メソッドがメール・システムにログオンします (ユーザー名とパスワードの発行は省略されます)。メール・インボックスのメッセージ収集により、最初のメッセージが取得されます。 FETCH 呼び出しがあると、メッセージ・ヘッダー情報と、現在のメッセージの最初の 30 文字が、表関数出力パラメーターに割り当てられます。メッセージが残っていない場合は、SQLSTATE 02000 が戻されます。 CLOSE 呼び出しがあると、この例はログオフしてセッション・オブジェクトを何もない状態に設定します。これにより、前の参照オブジェクトに関連したシステムおよびメモリー資源は、他の変数からオブジェクトが参照されない場合に解放されます。

以下に、この UDF に対する CREATE FUNCTION ステートメントを示します。

     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;

照会の例を次に示します。

     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.


[ ページのトップ | 前ページ | 次ページ | 目次 | 索引 ]