I'm trying to write a simple UDF in C to return a string to containing a formatted system time. I am using DB2 LUW v10.1.0.5 Fix Pack 5.


Here is the C code, my function definition, and the result that I always get when I call the function, which seems to be a chunk of text taken out of the middle of the EXTERNAL NAME value! Obviously there is a pointer issue, but I don't see it. It doesn't work when I dumb it down to just return a fixed INTEGER value as a test, either. What is driving me crazy is that when I call the function (with an INTEGER result) from the command line in one directory it returns 0. From a different directory it returns a huge random number. Called in DBArtisan, I get a useless error message and no result at all -> DBMS CMADEV -- [IBM][CLI Driver][DB2/AIX64] SQL0443N Routine "CMS.AIXTIME" (specific name "AIXTIME") has returned an error SQLSTATE with diagnostic text "AIXTIME". Yeah, thanks for the help...

================================================== =============
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <time.h>
#include <sqlca.h>
#include <sqludf.h>
#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN aixtime(SQLUDF_VARCHAR *sysDate, SQLUDF_NULLIND *outRetNullInd, SQLUDF_TRAIL_ARGS)
{
struct timeval tval;
char main_time[20];
char microsec[10];
gettimeofday(&tval, NULL);
strftime(main_time, 15, "%H:%M:%S.", localtime(&tval.tv_sec));
sprintf(microsec, "%06ld", tval.tv_usec);
strcat(main_time, microsec);
strcpy((char *) sysDate, (char *) main_time);
*outRetNullInd = 0;
}
================================================== =============
CREATE OR REPLACE FUNCTION cms.aixtime()
RETURNS VARCHAR(20)
SPECIFIC aixtime
EXTERNAL NAME '/home/cmabatch/db2client/aixtime!aixtime'
LANGUAGE C
PARAMETER STYLE SQL
NO SQL
NOT DETERMINISTIC
NO EXTERNAL ACTION;
================================================== =============
.../> db2 "values cms.aixtime()"
1
--------------------
nt/aixtime!aixti
1 record(s) selected.