The following statement creates a trace table that defines one column, Trace_Output, for a UDF to write trace output to:
CREATE GLOBAL TEMPORARY TRACE TABLE UDF_Trace (vproc_ID BYTE(2) ,Sequence INTEGER ,Trace_Output VARCHAR(256)) ON COMMIT DELETE ROWS;
The following code uses the value of the trace string to determine the level of debugging:
#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>
void Find_Text ( VARCHAR_LATIN *searched_string,
VARCHAR_LATIN *pattern,
CHARACTER_LATIN *result,
int *indicator_searched_string,
int *indicator_pattern,
int *indicator_result,
char sqlstate[6],
SQL_TEXT fncname[129],
SQL_TEXT sfncname[129],
SQL_TEXT error_message[257] )
{
SQL_TEXT trace_string[257];
char trace_output[257];
void *argv[1];
int length[1];
char debug_level = '0';
/* Get the trace string specified by SET SESSION FUNCTION */
/* TRACE and use it to determine debug level. */
FNC_Trace_String(trace_string);
if (trace_string[0] != 0)
debug_level = trace_string[0];
...
switch (debug_level)
{
case '1':
/* Debug Level 1: Output the function name */
sprintf(trace_output, "Function: %s", sfncname);
break;
case '2':
/* Debug Level 1: Output all values */
sprintf(trace_output,
"Function: %s, string: %s, pattern: %s",
sfncname, searched_string, pattern);
break;
}
...
/* Output the trace string to the trace table. */
argv[0] = trace_output;
length[0] = strlen(trace_output);
FNC_Trace_Write_DL(1, argv, length);
...
}
The following statement enables trace output for table UDF_Trace and sets the trace string to 2 so that the UDF outputs all values to the trace table:
SET SESSION FUNCTION TRACE USING '2' FOR TABLE UDF_Trace;
The SET SESSION FUNCTION TRACE statement disables any previously enabled trace tables.
The following statement queries the trace table to retrieve the trace output from the UDF:
SELECT * FROM UDF_Trace ORDER BY 1, 2;