16.20 - Returning SQLSTATE Values - 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 parameter list of a UDM includes an output character string for returning the SQLSTATE result code value.

C Data Type

The following table defines the C data type that corresponds to the SQLSTATE result code variable.

Result Code Variable C Data Type
SQLSTATE char sqlstate[6]

SQLSTATE Values

The first five characters of the sqlstate output character string have a format of 'ccsss', where cc is the class and sss is the subclass. The last character of the string is a binary 0, or C string terminator.

For more information on the valid settings that a UDM can return for the SQLSTATE result code, see SQLSTATE Values.

Initial Value

The sqlstate output character string is initialized to '00000' (five zero characters), which corresponds to a success condition. Therefore, you do not have to set the value of the sqlstate output argument for a normal return.

Display Format

If a UDM returns an SQLSTATE value other than success, a BTEQ session displays an error.

IF the SQLSTATE category is … THEN the display format is …
not a warning
*** Failure 7504 in UDF/XSP  databasename.udmname: SQLSTATE  ccsss:
 <text>
where:
  • 7504 is the Teradata designated error code for user-defined functions, user-defined methods, and external stored procedures
  • udmname is the name of the UDM
  • ccsss is the value that the UDM sets the SQLSTATE output argument to, according to the table in SQLSTATE Values.
  • <text> is the value of the error message output argument, if the UDM uses parameter style SQL
warning
*** Warning: 7505 in UDF/XSP databasename.udmname: SQLSTATE 01H xx:
 <text>
where:
  • 7505 is the Teradata designated warning code for user-defined functions, user-defined methods, and external stored procedures
  • udmname is the name of the UDM
  • 01H xx is the value that the UDM sets the SQLSTATE output argument to, according to values for the warning category in the table in SQLSTATE Values.
  • <text> is the value of the error message output argument, if the UDM uses parameter style SQL

Example: Returning the SQLSTATE Result Code Value

Consider the following C function that implements an instance method for a structured UDT that has an attribute called social_security_number:

void encrypt( UDT_HANDLE    *personalUdt,
              VARCHAR_LATIN *result,
              char           sqlstate[6])
{
     ...
}

You can use the sqlstate argument to return the SQLSTATE result code value.

For example, if the social_security_number attribute value of the UDT argument is not a valid value, you can set the value of the sqlstate argument to return a data exception:

strcpy(sqlstate, "U0005");

In a BTEQ session, the exception condition appears in the following format:

*** Failure 7504 in UDF/XSP employee.encrypt: SQLSTATE U0005:

Related Topics

FOR more information on … SEE …
using a warning condition to debug a method "Forcing an SQL Warning" in Debugging a UDM.