UDF - 7504

Database

UDF - 7504

Hi,

I am creating a UDF and it throws the above error message.

Bteq:
----
REPLACE FUNCTION ORDERED_CONCAT(
value_txt VARCHAR(2048)
, delimiter_txt VARCHAR(31)
, ordering_key INTEGER
) RETURNS VARCHAR(60000)
CLASS AGGREGATE ( 60000 )
LANGUAGE C
NO SQL
SPECIFIC ORDERED_CONCAT_CCI
DETERMINISTIC
CALLED ON NULL INPUT
PARAMETER STYLE SQL
EXTERNAL NAME
'F!ttext_udf_ordered_concat_cci!CI!ttext_lib! ttext_lib.h!CS!ttext_udf_ordered_concat_cci! ttext_udf_ordered_concat_cci.c'
;

ttext_udf_ordered_concat_cci.c
---------------------------------------

#include "ttext_lib.h"

/***************************
* Preprocessor Definitions
***************************/

#define STR(a) STR2(a)
#define STR2(a) #a
#define MAX_NUMBER_OF_VALUES 4000
#define BUFFER_SIZE 60000 - (sizeof(struct Item) * MAX_NUMBER_OF_VALUES) - \
(sizeof(unsigned short) * 2) - \
MAX_DELIM_SIZE - 1 - \
32
#define MAX_RESULT_SIZE 60000
#define MAX_DELIM_SIZE 31

/******************
* Data Structures
******************/

struct Item /* a structure to contain an ordering key / value pair */
{
INTEGER order;
unsigned short value_offset;
};

struct Storage /* the layout of data stored in shared memory */
{
struct Item list[MAX_NUMBER_OF_VALUES]; /* pp defs ensure this struct */
VARCHAR_LATIN buffer[BUFFER_SIZE]; /* will always be less than */
unsigned short buffer_offset; /* 64000 bytes (32 bytes padd */
short value_count; /* ing added to resolve any */
VARCHAR_LATIN delim[MAX_DELIM_SIZE + 1]; /* byte alignment issues) */
};

/*********************
* Internal Functions
*********************/

/***************************************************** ***********************
* copy_value
* ------------------------------------------------------ -------------------
* A function to copy a single input value and order number into the storage
* buffer.
*
* Return Codes: 0 = success, 2 = ex buffer, 3 = ex max # of values
****************************************************** **********************/

static int copy_value(struct Storage *s1, VARCHAR_LATIN *value, INTEGER order)
{
register unsigned short i;

/* assign the next list item pointer to the current buffer position */
s1->list[s1->value_count].order = order;
s1->list[s1->value_count].value_offset = s1->buffer_offset;

/* copy the value into the buffer */
for (i = 0; value[i] != 0; i++)
{
/* check to see if this next byte breaks the bank */
if (s1->buffer + s1->buffer_offset + i >= s1->buffer + BUFFER_SIZE)
return 2;

/* copy in the next character */
*(s1->buffer + s1->buffer_offset + i) = value[i];
}

/* null terminate the string in the buffer */
*(s1->buffer + s1->buffer_offset + i) = 0;

/* advance the offset value */
s1->buffer_offset = s1->buffer_offset + i + 1;

/* increment the value count */
s1->value_count++;

/* check to see if we have exceeded the number of values we can store */
if (s1->value_count > MAX_NUMBER_OF_VALUES)
return 3;

return 0;
}

/***************************************************** ***********************
* compare
* ------------------------------------------------------ -------------------
* A small function to compare two values based on their ordering key.
*
* Return Codes: 0 = equal, 1 = a > b, -1 = a < b
****************************************************** **********************/

static int compare(const void *a, const void *b)
{
register struct Item *x = (struct Item *)a;
register struct Item *y = (struct Item *)b;

if (x->order > y->order)
return 1;
else if (x->order < y->order)
return -1;
else
return 0;
}

/*********************************
* Teradata User Defined Function
*********************************/

void ttext_udf_ordered_concat_cci(
FNC_Phase phase
, FNC_Context_t *context
, VARCHAR_LATIN *value
, VARCHAR_LATIN *delim
, INTEGER *order
, VARCHAR_LATIN *result
, BOOLEAN *valueIsNull
, BOOLEAN *delimIsNull
, BOOLEAN *orderIsNull
, BOOLEAN *resultIsNull
, char sqlstate[6]
, SQL_TEXT function_name[129]
, SQL_TEXT specific_function_name[129]
, SQL_TEXT error_message[257]
)
{
int rc;
int i, j, k, l;
VARCHAR_LATIN c;

/* pointers to intermediate storage areas */
struct Storage *s1 = context->interim1;
struct Storage *s2 = context->interim2;

/* by default, return success */
TTEXT_set_error_message("00000", "");

switch (phase)
{
/***********************************************
* In the inital phase, allocate shared memory.
***********************************************/
case AGR_INIT:

TTEXT_trace("entering initial phase ...");

if (*delimIsNull)
{
TTEXT_set_error_message("U0004", "Delimiter cannot be NULL.");
return;
}

/* allocate shared memory */
s1 = FNC_DefMem(sizeof(struct Storage));

if (s1 == NULL)
{
TTEXT_set_error_message("U0001", "Internal Error: Unable to acquire shared memory from the database.");
return;
}

/* the delimiter won't be available in future phases - copy it now */
strncpy((char *)s1->delim, (const char *)delim, MAX_DELIM_SIZE);

#if defined(TTEXT_OPTION_TRACE)
memset(s1->buffer, 'z', sizeof(s1->buffer) - 1);
#endif

s1->buffer_offset = 0;
s1->value_count = 0;

/* fall through to AGR_DETAIL */

/*****************************************************
* For each record, copy the input value and ordering
* key to the shared memory storage buffer list.
*****************************************************/
case AGR_DETAIL:

if (*valueIsNull)
return;

if (*orderIsNull)
return;

TTEXT_trace("entering detail phase ...");
TTEXT_trace("Input = order: %4d | value: %s", *order, value);

rc = copy_value(s1, value, *order);

switch (rc)
{
case 2: TTEXT_set_error_message("U0002", "All string input values could not be stored in temporary memory; function can only be used on fewer values or shorter strings."); return;
case 3: TTEXT_set_error_message("U0003", "Function can be performed on a maximum of " STR(MAX_NUMBER_OF_VALUES) " values."); return;
}

TTEXT_trace("leaving detail phase ...");

break;

/***************************************************** *****************
* Combine lists of values from different AMPs by copying each item in
* the second list over to the first list.
****************************************************** ****************/
case AGR_COMBINE:

TTEXT_trace("entering combine phase ...");

/* if no values exist in the second list, return */
if (s2->value_count < 1)
return;

s2->value_count--;

for (; s2->value_count >= 0; s2->value_count--)
{
TTEXT_trace("Incoming List = index: %4d | order: %4d | value: %s", s2->value_count, s1->list[s2->value_count].order, s1->buffer + s1->list[s2->value_count].value_offset);

rc = copy_value(s1, s2->buffer + s2->list[s2->value_count].value_offset, s2->list[s2->value_count].order);

switch (rc)
{
case 2: TTEXT_set_error_message("U0002", "All string input values could not be stored in temporary memory; function can only be used on fewer values or shorter strings."); return;
case 3: TTEXT_set_error_message("U0003", "Function can be performed on a maximum of " STR(MAX_NUMBER_OF_VALUES) " values."); return;
}
}

TTEXT_trace("leaving combine phase ...");

break;

/***************************************************** ***************
* In the final step, sort the compiled value list, then concatenate
* the sorted values into a single string. Include the delimiter.
****************************************************** **************/
case AGR_FINAL:

TTEXT_trace("entering final phase ...");

/* if the final list contains no values, return */
if (s1->value_count < 1)
return;

TTEXT_trace("sorting final output ...");

/* sort list */
qsort(s1->list, s1->value_count, sizeof(struct Item), compare);

/* copy values into a buffer */
j = 0; l = (int)strlen((const char *)s1->delim);

for (i = 0; i < s1->value_count; i++)
{
TTEXT_trace("Final List = index: %4d | order: %4d | value: %s", i, s1->list[i].order, s1->buffer + s1->list[i].value_offset);

c = *(s1->buffer + s1->list[i].value_offset);

for (k = 1; c != 0; j++, k++)
{
if (j >= MAX_RESULT_SIZE)
{
TTEXT_set_error_message("U0004", "Result string longer than " STR(MAX_RESULT_SIZE) " characters.");
return;
}

result[j] = c;

c = *(s1->buffer + s1->list[i].value_offset + k);
}

if (j + l >= MAX_RESULT_SIZE)
{
TTEXT_set_error_message("U0004", "Result string longer than " STR(MAX_RESULT_SIZE) " characters.");
return;
}

/* do not add a delimiter after the last value */
if (i + 1 < s1->value_count)
{
strcpy((char *)(result + j), (const char *)s1->delim);

j += l;
}
}

result[j] = 0;

TTEXT_trace("leaving final phase ...");

break;

/***********************************
* If there is no data, do nothing.
***********************************/
case AGR_NODATA:
break;
}

return;
}

ttext_lib.h
--------------
#ifndef TTEXT_LIB_H
#define TTEXT_LIB_H

#define SQL_TEXT Latin_Text
#include
#include
#include
#include
#include
#include "sqltypes_td.h"

#undef TTEXT_OPTION_TRACE

#if defined(_WIN32)
#pragma warning(disable: 4100 4702 4996)
#endif

typedef int BOOLEAN;

#define TRUE -1
#define FALSE 0

#define TTEXT_set_error_message(A, B) \
*resultIsNull = FALSE; \
strncpy((char *)sqlstate, (const char *)(A), 5); \
strncpy((char *)error_message, (const char*)(B), 256)

#if defined(TTEXT_OPTION_TRACE)

/***************************************************
CREATE GLOBAL TEMPORARY TRACE TABLE SYSLIB.UDF_TRC (
VPROC_ID BYTE(2)
, SEQ_NBR INTEGER
, MSG_TXT VARCHAR(1000)
) ON COMMIT PRESERVE ROWS;

SET SESSION FUNCTION TRACE USING 'parameter' FOR TRACE TABLE SYSLIB.UDF_TRC;

SELECT * FROM SYSLIB.UDF_TRC ORDER BY VPROC_ID, SEQ_NBR;

DELETE SYSLIB.UDF_TRC ALL;
***************************************************/

static void TTEXT_trace(char *format, ...)
{
static char message[1000];
void *argv[1];
va_list args;

va_start(args, format);

_vsnprintf(message, 999, format, args);

argv[0] = (void *)message;

FNC_Trace_Write(1, argv);

va_end(args);

return;
}

#else

static void TTEXT_trace(char *format, ...) { }

#endif

#endif TTEXT_LIB_H

Can you help please??

The error that i am getting is:

*** Failure 7504 in UDF/XSP/UDM IGAIKW.ORDERED_CONCAT_CCI: SQLSTATE U0003:
Statement# 1, Info =0

*** Failure 7504 in UDF/XSP/UDM IGAIKW.ORDERED_CONCAT_CCI: SQLSTATE U0003:
Statement# 1, Info =0

*** Failure 7504 in UDF/XSP/UDM IGAIKW.ORDERED_CONCAT_CCI: SQLSTATE U0004:
Statement# 1, Info =0

Although if I check Show Function ORDERED_CONCAT ;
I am able to see the defintion but I cant use the udf