Returning SQLSTATE Values | C/C++ UDMs | Teradata Vantage - Returning SQLSTATE Values - Analytics Database - Teradata Vantage

SQL External Routine Programming

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
iiv1628111441820.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1147
lifecycle
latest
Product Category
Teradata Vantage™

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 Information

FOR more information on … SEE …
using a warning condition to debug a method Debugging a UDM.