DB2 C language external scalar user-defined function

Sample step of define a C language external scalar user-defined function.


1. Define function implementation using C language

$ cat mymd5.c

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <time.h>
#include <sqlca.h>
#include <sqludf.h>

#include <openssl/md5.h>

#ifdef __cplusplus
extern "C"
#endif
SQL_API_RC SQL_API_FN md5(SQLUDF_CHAR *input,
                          SQLUDF_CHAR *output,
                          SQLUDF_NULLIND *inputInd,
                          SQLUDF_NULLIND *outputInd,
                          SQLUDF_TRAIL_ARGS)
{

  if (*inputInd == -1) {
    *outputInd = -1;
  }
  else {
    *outputInd = 0;

    MD5_CTX ctx;
    unsigned char md[16];
    char tmp[3]={'\0'};
    int i;
    MD5_Init(&ctx);
    MD5_Update(&ctx, input, strlen(input));
    MD5_Final(md,&ctx);
    for( i=0; i<16; i++ ){
        sprintf(tmp,"%02x",md[i]);
        strcat(output,tmp);
    }
  }
  return;
}


About function declaration definition, please refer to http://www.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.apdv.routines.doc/doc/c0023763.html


2. Compile function into a shared library

$ cat makefile

DB2INSTDIR=/home/db2inst1

mymd5: mymd5.c
    gcc -I${DB2INSTDIR}/sqllib/include -L${DB2INSTDIR}/sqllib/lib -o $@ -Wl,-rpath,${DB2INSTDIR}/sqllib/lib -ldb2 -ffloat-store -fPIC -shared $< -lcrypto

3. Define DB2 function prototype


$ cat mymd5.sql

CREATE FUNCTION md5(VARCHAR(20))
  RETURNS CHAR(32)
  LANGUAGE C
  PARAMETER STYLE SQL
  NO SQL
  NOT FENCED
  DETERMINISTIC
  RETURNS NULL ON NULL INPUT
  NO EXTERNAL ACTION
  EXTERNAL NAME '/full/path/to/mymd5!md5';

You can also put the shared library into folder /home/db2inst1/sqllib/function, so that the "EXTERNAL NAME" should be written as EXTERNAL NAME 'mymd5!md5'

$ db2 -tf mymd5.sql
DB20000I  The SQL command completed successfully.


4. Verify the function

$ db2 "select md5('123') from sysibm.sysdummy1"

1                              
--------------------------------
202cb962ac59075b964b07152d234b70

  1 record(s) selected.


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值