用C创建DB2自定义函数遇到的问题
我想在DB2中用C创建一个自定义的TRIM函数TrimChr.C代码是在C/C++版上搜到的:
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sys/stat.h>
#include <sys/types.h>
#include <sqludf.h>
//Macro for iendity the blanks
#define isSpaces(ch) (ch == ' ' || ch == '\t' || ch == '\r' || ch == '\b' || ch == '\f' || ch == '\n')
char *TrimChr(char *pszSource)
{
//pointer of the first char of string
char *pszHead = pszSource;
//pointer of the last non-blank char
char *pszLast = &pszSource;
//search the location of the last non-blank char
while (isSpaces(*pszLast))
--pszLast;
*(++pszLast) = '\0';
//search the first non-blank char for left shift the string
for ( ; (*pszHead != '\0') && (pszHead != pszLast); ++pszHead)
{
if (! isSpaces(*pszHead))
{
strcpy(pszSource, pszHead);
break;
}
}
return pszSource;
}
int main(int argc,char *argv[])
{
printf("\"");
printf(argv);
printf("\"\n");
printf("\"");
printf(TrimChr(argv));
printf("\"\n");
}
代码可以编译成功并在LINUX环境下运行的.
但是我想用它变成DB2的一个自定义函数时,老是出问题:
values TrimChr(' aaaa ');
SQL0444NRoutine "TRIMCHR" (specific name "SQL080529112348600") is implemented with code in library or path ".../sqllib/function/TrimChr", function "TrimChr" which cannot be accessed.Reason code: "5".SQLSTATE=42724
自定义函数是如此写的:
CREATE FUNCTION TrimChr (VARCHAR(400))
RETURNS VARCHAR(400)
EXTERNAL NAME '/home/db2inst1/sqllib/function/TrimChr!TrimChr'
LANGUAGE C
NULL CALL
PARAMETER STYLE DB2SQL
NO SQL
DETERMINISTIC
NO EXTERNAL ACTION
NOT FENCED;
GRANT EXECUTE ON FUNCTION TRIMCHR(VARCHAR(400)) TO PUBLIC;
请问是什么原因?是编译的问题?还是C函数的问题? Reason code 5 :
There was insufficient memory to load the library containing
The function or one or more symbols could not be resolved.
Contact the routine creator or your database administrator to
Make sure that the library was correctly linked. All required
Libraries to resolve referenced symbols such as external
Functions must be available. If a lack of memory is determined
Then the system configuration may need to be changed to make more
Memory available to DB2. 问题解决了.
C代码如下:#include <string.h>
#include <memory.h>
#include <sqludf.h>
#define ISSPACE(x) ((x)==0x00 ||(x)==0x01 ||(x)==0x02 ||(x)==0x03 ||(x)==0x04 ||(x)==0x05 ||(x)==0x06 ||(x)==0x07 ||(x)==0x08 ||(x)==0x09 ||(x)==0x0A ||(x)==0x0B ||(x)==0x0C ||(x)==0x0D ||(x)==0x0E ||(x)==0x0F ||(x)==0x10 ||(x)==0x11 ||(x)==0x12 ||(x)==0x13 ||(x)==0x14 ||(x)==0x15 ||(x)==0x16 ||(x)==0x17 ||(x)==0x18 ||(x)==0x19 ||(x)==0x1A ||(x)==0x1B ||(x)==0x1C ||(x)==0x1D ||(x)==0x1E ||(x)==0x1F ||(x)==0x20 )
void TrimChr( char *String, char *TrimedString )
{
char *Tail, *Head;
for ( Tail = String + strlen( String ) - 1; Tail >= String; Tail -- )
if ( !ISSPACE( *Tail ) )
break;
Tail = 0;
for ( Head = String; Head <= Tail; Head ++ )
if ( !ISSPACE( *Head ) )
break;
if ( Head != String )
memcpy( String, Head, ( Tail - Head + 2 ) * sizeof( char ) );
strcpy(TrimedString, String);
return;
}
这段代码是借鉴FH的这个贴子的:
http://bbs.chinaunix.net/viewthread.php?tid=277036
编译命令如下:cc -c -o TrimChr.o -I ~/sqllib/include TrimChr.c -D_REENTRANT
cc -o TrimChr -shared -fpic TrimChr.o请用db2inst1用户编译,编译成功后,将可执行文件COPY到 ~/sqllib/function/下,即/home/db2inst1/sqllib/function/下.cp TrimChr ~/sqllib/function/最后到数据库中创建自定义函数:--DROP FUNCTION TrimChr;
CREATE FUNCTION TrimChr (VARCHAR(400))
RETURNS VARCHAR(400)
EXTERNAL NAME '/home/db2inst1/sqllib/function/TrimChr!TrimChr'
LANGUAGE C
NULL CALL
PARAMETER STYLE DB2SQL
NO SQL
DETERMINISTIC
NO EXTERNAL ACTION
NOT FENCED;
GRANT EXECUTE ON FUNCTION TRIMCHR(VARCHAR(400)) TO PUBLIC;然后就可以直接使用此函数了:values TrimChr(' aaa bbb ');
本人不懂C,主要是不断的查资料,不断的做实验而成功的.
页:
[1]