16.20 - Forcing an SQL Warning Condition - 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)

You can force an SQL warning condition at the point in the code where the function appears to have problems. To force the warning, set the sqlstate return argument to '01H xx', where you choose the numeric value of xx.

If you use parameter style SQL, you can also set the error_message return argument to return up to 256 SQL_TEXT characters.

The warning is issued as a return state of the function. The warning does not terminate the transaction; therefore, you must set the return value of the function to a valid value that the transaction can use.

Only one warning can be returned per request.

Here is an example of how to set the SQLSTATE result code and error message return argument in a scalar function that uses parameter style SQL:

#define SQL_TEXT Latin_Text
#include <sqltypes_td.h>
#include <string.h>
   
void String_UDF(VARCHAR_LATIN   *inputString,
                CHARACTER_LATIN *result,
                int             *inputStringIsNull,
                int             *resultIsNull,
                char            sqlstate[6],
                SQL_TEXT        extname[129],
                SQL_TEXT        specific_name[129],
                SQL_TEXT        error_message[257])
{
  if (strlen((char *)inputString) == 0) {
    *result = 'F';

    /* Force a warning using the SQLSTATE result code value */
    strcpy(sqlstate, "01H01");
 
    /* Set the error message return value */
    strcpy((char *)error_message, "Zero length input string");
    return;
  }

   ...

}

In a BTEQ session, if String_UDF is passed a zero length string, a warning is reported with the SQLSTATE result code value and the error message return value:

SELECT String_UDF('');
   
   *** Query completed. One row found. One column returned.
   *** Warning: 7505 in UDF dbname.String_UDF: SQLSTATE 01H01:
    Zero length input string
   *** Total elapsed time was 1 second.