Hi all,
I'm trying to complete the samples from the excellent 2003
developerWorks article "Bringing the Power of Regular Expression
Matching to SQL" by Knut Stolze:
http://tinyurl.com/3bhrnn
I've managed to compile and link the C code from "Listing 5", the
regexpSimple() wrapper (see link). The build options are pretty much
the result of trial-and-error using the samples in
/home/db2inst1/sqllib/samples/c/. Any advice on how to successfully
call the C wrapper from a DB2 UDF is appreciated.
# Environment
DB2/LINUX 9.1.0
gcc (GCC) 3.4.6 20060404 (Red Hat 3.4.6-3)
# Flags
$ EXTRA_LFLAG="-Wl,-rpath,$DB2PATH/lib"
$ DB2PATH=/home/db2inst1/sqllib
$ DB2V9LIBS=/opt/ibm/db2/V9.1
$ SHARED_LIB_NAME=regexUdf
# Compile the program.
$ gcc $EXTRA_C_FLAGS -fpic -I$DB2PATH/include -I$DB2V9LIBS/include -c
utilapi.c -D_REENTRANT
$ gcc $EXTRA_C_FLAGS -fpic -I$DB2PATH/include -I$DB2V9LIBS/include -c
regex1.c -D_REENTRANT
# Link the program and create a shared library
$ gcc $EXTRA_C_FLAGS -shared -o $SHARED_LIB_NAME regex1.o utilapi.o
$EXTRA_LFLAG -L$DB2PATH/lib -ldb2 -lpthread
# Copy and chown shared library
$ cp $SHARED_LIB_NAME $DB2PATH/function
$ chown db2inst1: $DB2PATH/function/$SHARED_LIB_NAME
$ ls -l /home/db2inst1/sqllib/function/regexUdf
-rwxr-xr-x 1 db2inst1 db2grp1 10624 Feb 20 10:16 /home/db2inst1/
sqllib/function/regexUdf
However, calling this function from DB2 fails with "SQL0444N":
$ sudo su - db2inst1
$ db2 "connect to sample user db2inst1"
Enter current password for db2inst1:
Database Connection Information
Database server = DB2/LINUX 9.1.0
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
$ db2 -t -f /home/db2inst1/test/db2/regex/regex1.sql
DB20000I The SQL command completed successfully.
$ db2 "select FIRSTNME, LASTNAME from employee where regex1('\w+',
FIRSTNME) = 1"
FIRSTNME LASTNAME
------------ ---------------
SQL0444N Routine "REGEX1" (specific name "REGEXSIMPLE") is
implemented with
code in library or path ".../sqllib/function/regexUdf", function
"regexpSimple" which cannot be accessed. Reason code: "5".
SQLSTATE=42724
$ cat /home/db2inst1/test/db2/regex/regex1.sql
CREATE FUNCTION regex1(pattern VARCHAR(2048), string CLOB(10M))
RETURNS INTEGER
SPECIFIC regexSimple
EXTERNAL NAME 'regexUdf!regexpSimple'
LANGUAGE C
PARAMETER STYLE DB2SQL
DETERMINISTIC
NOT FENCED
RETURNS NULL ON NULL INPUT
NO SQL
NO EXTERNAL ACTION
ALLOW PARALLEL;
$ cat /home/db2inst1/test/db2/regex/regex1.c
/*
http://www-128.ibm.com/developerwork...301stolze.html
listing5
A small chunk of C code, which implements the UDF entry point. This
entry point is called by DB2 during the query execution for each
row to be matched against the pattern. Listing 5 is an example what
this code could look like. For the description of the pcre_*
function and macros, refer to the documentation of the PCRE
library. For compiling the C code and building the shared
libraries, refer to the DB2 Application Development Guide.
*/
#include <pcre/pcre.h>
#include <sqludf.h>
void regexpSimple(
// input parameters
SQLUDF_VARCHAR *pattern, SQLUDF_CLOB *str,
// output
SQLUDF_INTEGER *match,
// null indicators
SQLUDF_NULLIND *pattern_ind, SQLUDF_NULLIND *str_ind,
SQLUDF_NULLIND *match_ind,
SQLUDF_TRAIL_ARGS)
{
pcre *re = NULL;
const char *error = NULL;
int errOffset = 0;
int rc = 0;
// we assume successful return
*match_ind = 0;
// compile the pattern to its internal representation
re = pcre_compile(pattern, 0 /* default options */, &error,
&errOffset, NULL);
if (re == NULL) {
snprintf(SQLUDF_MSGTX, 70, "Regexp compilation failed at "
"offset %d: %s\n", errOffset, error);
strcpy(SQLUDF_STATE, "38900");
(*pcre_free)(re);
return;
}
// match the string againts the pattern
rc = pcre_exec(re, NULL, str->data, str->length, 0,
0 /* default options */, NULL, 0);
switch (rc) {
case PCRE_ERROR_NOMATCH:
*match = 0;
break;
case PCRE_ERROR_BADOPTION:
snprintf(SQLUDF_MSGTX, 70, "An unrecognized bit was set in the
"
"options argument");
strcpy(SQLUDF_STATE, "38901");
break;
case PCRE_ERROR_NOMEMORY:
snprintf(SQLUDF_MSGTX, 70, "Not enough memory available.");
strcpy(SQLUDF_STATE, "38902");
break;
default:
if (rc < 0) {
snprintf(SQLUDF_MSGTX, 70, "A regexp match error "
"occured: %d", rc);
strcpy(SQLUDF_STATE, "38903");
}
else {
*match = 1;
}
break;
}
// cleanup
(*pcre_free)(re);
return;
}
--
Serman D.
