16.20 - Example: Debugging a UDF Using a Trace Table - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Product
Advanced SQL Engine
Teradata Database
Release Number
16.20
Release Date
April 2020
Content Type
Programming Reference
Publication ID
B035-1147-162K
Language
English (United States)

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;