DB2 C language external scalar user-defined function

来源：互联网发布：男科网络咨询说话技巧编辑：程序博客网时间：2024/04/20 16:26

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 __cplusplusextern "C"#endifSQL_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/db2inst1mymd5: 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.sqlDB20000I  The SQL command completed successfully.

4. Verify the function

$ db2 "select md5('123') from sysibm.sysdummy1"1                              --------------------------------202cb962ac59075b964b07152d234b70  1 record(s) selected.

0 0