Returning SQLSTATE Values | C/C++ UDMs | Teradata Vantage - Returning SQLSTATE Values - Advanced SQL Engine - Teradata Database

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.

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.

*** Failure 7504 in UDF/XSP  databasename.udmname: SQLSTATE  ccsss:
 <text>

*** Warning: 7505 in UDF/XSP databasename.udmname: SQLSTATE 01H xx:
 <text>

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