Hi,

I'm trying to modify sample code in Appendix of "SQL External Routine Programming" to create a constant mode table UDF. The UDF simply take an integer and return its square. Unfortunately I'm having trouble in getting the result back.

When I use Teradata SQL Assistant to run my query I got the following message.

SELECT * FROM TABLE (es_double(1)) AS T1;

[Teradata Database] [TeraJDBC 13.00.00.20] [Error 2507] [SQLState HY000] Out of spool space on disk.

It seems in UDF entry point, under case TBL_BUILD I need to give sqlstate a value. According to the document it should be initialized to "00000", but if I didn't assign value to it (or assign to "00000"), the execution lasts a long time and return error message listed above. If I assign "02000" (no data) to it then the execution can be stopped in a reasonably time but nothing is returned (this seems to be the only way other than warning/error state that can make UDF execution stop).

I'm using 13.0 simulator on Windows. And my scalar UDF works fine.

I'm posting my code below. Any advice/comments are welcome!

/*++++++++ es_double.c ++++++++*/
#define SQL_TEXT Latin_Text
#include
#include

typedef struct
{
INTEGER input;
INTEGER result;
} es_ctx;

static int
Initialize(es_ctx *info, INTEGER *input, int input_i)
{
if (input_i != 0)
return -1;

info->input = *input;
info->result = (*input)*(*input);

return 0;
}

static void unicpy(SQL_TEXT *dest, char *src)
{
while (*src)
*dest++ = *src++;
}

static int
Calculate(es_ctx *info, INTEGER *result, int result_i)
{
if (result_i == 0)
{
*result = info->result;
return 0;
}

return -1;
}

void
es_double (INTEGER *input, INTEGER *result, int *input_i, int *result_i,
char sqlstate[6], SQL_TEXT fncname[129], SQL_TEXT sfncname[129], SQL_TEXT err_msg[257])
{
int status;
FNC_Phase Phase;
es_ctx *info;

switch(FNC_GetPhase(&Phase))
{
case TBL_MODE_CONST:
switch(Phase)
{
case TBL_PRE_INIT:
switch(FNC_TblFirstParticipant())
{
case 1:
return;
case 0:
if (FNC_TblOptOut())
{
strcpy(sqlstate,"U0006");
unicpy(err_msg,"Opt-out failed.");
return;
}
break;
default:
strcpy(sqlstate,"U0007");
unicpy(err_msg,"First participant logic did not work");
return;
}
case TBL_INIT:
info = FNC_TblAllocCtx(sizeof(es_ctx));
status = Initialize(info, input, *input_i);
if (status == -1)
{
status = FNC_TblAbort();
strcpy(sqlstate,"U0008");
unicpy(err_msg,"Failed to initialize in const mode.");
return;
}
break;
case TBL_BUILD:
info = FNC_TblGetCtx();
status = Calculate(info, result, *result_i);
if (status == 0)
{
strcpy(sqlstate,"00000");
}
else if (status == -1)
{
status = FNC_TblAbort();
strcpy(sqlstate,"U0009");
unicpy(err_msg,"failed to calculate in const mode.");
return;
}
break;
case TBL_END:
case TBL_ABORT:
break;
}
break;
}
}

/*++++++++ SQL Definition ++++++++*/
REPLACE FUNCTION es_double(x INTEGER)
RETURNS TABLE (col INTEGER)
LANGUAGE C
NO SQL
PARAMETER STYLE SQL
EXTERNAL NAME 'CS!es_double!c:\udf\es_double.c!F!es_double';

/*++++++++ SQL Query ++++++++*/
SELECT * FROM TABLE (es_double(1)) AS T1;