UDF - Error 7504

Extensibility
Enthusiast

UDF - Error 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??
2 REPLIES
Enthusiast

Re: UDF - Error 7504

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
rgs
Enthusiast

Re: UDF - Error 7504

This is a UDF generated error. See message manual for explanation. Here is what it says:

7504 in UDF/XSP/UDM %DBID.%TVMID: SQLSTATE %VSTR
Explanation: This is a UDF/XSP/UDM generated error. The meaning depends on the UDF as defined by the developer.

Generated By: AMP subsystem.

For Whom: UDF/XSP/UDM user.

Notes: The variable string will contain the SQLSTATE code followed by an optional text string supplied by the UDF/XSP/UDM.
Remedy: Look up the documentation for the UDF/XSP/UDM and take the necessary action to use the function in the correct context.

Look in the C code for U0003 and U0004. there will be a text associated with it. The text should have been printed along with the error code. If it did not then there might be another bug in the C code.

According to the C code U0003 means "Function can be performed on a maximum of " STR(MAX_NUMBER_OF_VALUES) " values."

and U0004 means "Delimiter cannot be NULL."

If your user account is defaulting to UNICODE for text you are going to have to specify the LATIN character set clause on on the replace function statement for all text variables because that is what the UDF is setup to work with. That is probably the reason you are getting the errors. Set up your user account to default to the LATIN character set is the other option.