TIMESTAMP in UDF

Connectivity
Fan

TIMESTAMP in UDF

Hello,

I have a problem with writing a UDF in C that accepts a TIMESTAMP argument. Its return value should also be of TIMESTAMP data type.

I have written several UDF in C passing strings and/or numbers back and forth between Teradata and C UDF. But this one seems to be a problem I have not been able to solve yet, that's why this posting.

So I would like to kindly ask, any ideas how to write the C function that would accept a TIMESTAMP argument and also return a TIMESTAMP? I highly appreciate your help.

Thanks in advance.

Peter
4 REPLIES
Teradata Employee

Re: TIMESTAMP in UDF

Did you look at the supplied sqltypes_td.h? TIMESTAMP is a struct rather than a simple type...
Fan

Re: TIMESTAMP in UDF

Yes, I did take a look into it and I noticed TimeStamp is a structure. As a matter of fact, I wrote the following C code:

#include "maxdate.h"

// Returns MAXDATE if its argument's year is greater or equal to THRESHOLDYEAR.
// Returns its argument unchanged otherwise.
TimeStamp maxdate(TimeStamp *indate,
TimeStamp *outdate)
{
if (indate->year < THRESHOLDYEAR)
{
outdate->seconds = indate->seconds;
outdate->year = indate->year;
outdate->month = indate->month;
outdate->day = indate->day;
outdate->hour = indate->hour;
outdate->minute = indate->minute;
}
else
{
outdate->seconds = MAXDATE.seconds;
outdate->year = MAXDATE.year;
outdate->month = MAXDATE.month;
outdate->day = MAXDATE.day;
outdate->hour = MAXDATE.hour;
outdate->minute = MAXDATE.minute;
}

return *outdate;
}

together with the maxdate.h file:

#define SQL_TEXT Latin_Text
#include

struct TimeStamp MAXDATE = {59.000000, 3000, 12, 31, 23, 59};
SMALLINT THRESHOLDYEAR = 2999;

I have also created the UDF in the database by running the following CREATE FUNCTION SQL statement:

REPLACE FUNCTION DP_UTIL.MAXDATE(indate TIMESTAMP(6))
RETURNS TIMESTAMP(6)
LANGUAGE C
NO SQL
PARAMETER STYLE TD_GENERAL
EXTERNAL NAME 'CI!maxdate!M:\DBA\Function\maxdate\maxdate.h!CS!maxdate!M:\DBA\Function\maxdate\maxdate.c!F!maxdate';

COMMENT ON FUNCTION DP_UTIL.MAXDATE IS 'Function to replace its TIMESTAMP argument with MAXDATE if it is greater or equal to the THRESHOLDDATE';

When I SELECT FROM DBC.Functions I can see the function has been created correctly (with correct arguments).
When I try to call the function, though, I am getting the 7502 error ("A system trap was caused by UDF/XSP/UDM DP_UTIL_MAXDATE for SIGSEGV"). So the function basically crashes.

Any ideas what I am doing wrong?

TIA,

Peter
rgs
Enthusiast

Re: TIMESTAMP in UDF

You need to look at the documentation for writing UDFs again. The C prototype is not correct. The UDF C code never returns anything via the C return parameter. It is always defined as returns void. What happens is that the stack frame is not generated correctly. That is why you got the trap. Also note that the definition of DECIMAL4 is an int in (8,6) decimal format. This means you have to multiply the seconds by 1000000 to move the decimal point over in the implied decimal point of the int. Try this correct example. It should work for you:

#define SQL_TEXT Latin_Text
#include

/* create statement :

replace function maxdate(timestamp(6))
returns timestamp(6)
language C
no SQL
PARAMETER STYLE TD_GENERAL
external;

*/

struct TimeStamp MAXDATE = {59*1000000, 3000, 12, 31, 23, 59};
SMALLINT THRESHOLDYEAR = 2999;

void maxdate(TimeStamp *indate,
TimeStamp *outdate,
char sqlstate[6])
{

if (indate->year < THRESHOLDYEAR)
{
outdate->seconds = indate->seconds;
outdate->year = indate->year;
outdate->month = indate->month;
outdate->day = indate->day;
outdate->hour = indate->hour;
outdate->minute = indate->minute;
}
else
{
outdate->seconds = MAXDATE.seconds;
outdate->year = MAXDATE.year;
outdate->month = MAXDATE.month;
outdate->day = MAXDATE.day;
outdate->hour = MAXDATE.hour;
outdate->minute = MAXDATE.minute;
}

}

Here is sample output:

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select maxdate(cast(date as timestamp));

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

maxdate(Date)
--------------------------
2007-05-10 00:00:00.000000

BTEQ -- Enter your DBC/SQL request or BTEQ command:
select maxdate(cast('4000-05-10 11:25.34.000000' as timestamp ));

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

maxdate('4000-05-10 11:25.34.000000')
-------------------------------------
3000-12-31 23:59:59.000000

-- Rolf

Fan

Re: TIMESTAMP in UDF

Thank you very much for your help.

Peter