SIGNAL

The SIGNAL statement signals an error or warning condition. It causes an error or warning to be returned with the specified SQLSTATE and optional condition-information-items.

Invocation

This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared. It must not be specified in REXX.

Authorization

None required.

Syntax

signal-information

Description

SQLSTATE VALUE

Specifies the SQLSTATE that will be signalled. The specified value must not be null and must follow the rules for SQLSTATEs:

Each character must be from the set of digits ('0' through '9') or non-accented upper case letters ('A' through 'Z').
The SQLSTATE class (first two characters) cannot be '00' since this represents successful completion.

If the SQLSTATE does not conform to these rules, an error is returned.

sqlstate-string-constant: The sqlstate-string-constant must be a character string constant with exactly 5 characters.
sqlstate-string-variable: The sqlstate-string-variable must be a character or Unicode graphic variable. It cannot be a global variable. The actual length of the contents of the variable must be 5.

SET

Introduces the assignment of values to condition-information-items. The condition-information-item values can be accessed using the GET DIAGNOSTICS statement. The only condition-information-item that can be accessed in the SQLCA is MESSAGE_TEXT.

MESSAGE_TEXT

Specifies a string that describes the error or warning.

If an SQLCA is used,

the string is returned in the SQLERRMC field of the SQLCA
if the actual length of the string is longer than 1000 bytes, it is truncated without a warning.

CONSTRAINT_CATALOG

Specifies a string that indicates the name of the database that contains a constraint related to the signalled error or warning.

CONSTRAINT_SCHEMA

Specifies a string that indicates the name of the schema that contains a constraint related to the signalled error or warning.

CONSTRAINT_NAME

Specifies a string that indicates the name of a constraint related to the signalled error or warning.

CATALOG_NAME

Specifies a string that indicates the name of the database that contains a table or view related to the signalled error or warning.

SCHEMA_NAME

Specifies a string that indicates the name of the schema that contains a table or view related to the signalled error or warning.

TABLE_NAME

Specifies a string that indicates the name of a table or view related to the signalled error or warning.

COLUMN_NAME

Specifies a string that indicates the name of a column in the table or view related to the signalled error or warning.

CURSOR_NAME

Specifies a string that indicates the name of a cursor related to the signalled error or warning.

CLASS_ORIGIN

Specifies a string that indicates the origin of the SQLSTATE class related to the signalled error or warning.

SUBCLASS_ORIGIN

Specifies a string that indicates the origin of the SQLSTATE subclass related to the signalled error or warning.

variable

Identifies a variable that must be declared in accordance with the rules for declaring variables (see References to host variables). The variable contains the value to be assigned to the condition-information-item. The variable must be defined as CHAR, VARCHAR, Unicode GRAPHIC, or Unicode VARGRAPHIC variable. It cannot be a global variable.

diagnostic-string-constant

Specifies a character string constant that contains the value to be assigned to the condition-information-item.

Notes

SQLSTATE values: Any valid SQLSTATE value can be used in the SIGNAL statement. However, it is recommended that programmers define new SQLSTATEs based on ranges reserved for applications. This prevents the unintentional use of an SQLSTATE value that might be defined by the database manager in a future release.

SQLSTATE values are comprised of a two-character class code value, followed by a three-character subclass code value. Class code values represent classes of successful and unsuccessful execution conditions.

SQLSTATE classes that begin with the characters '7' through '9' or 'I' through 'Z' may be defined. Within these classes, any subclass may be defined.
SQLSTATE classes that begin with the characters '0' through '6' or 'A' through 'H' are reserved for the database manager. Within these classes, subclasses that begin with the characters '0' through 'H' are reserved for the database manager. Subclasses that begin with the characters 'I' through 'Z' may be defined.

For more information about SQLSTATEs, see the SQL Messages and Codes topic collection.

Assignment: When the SIGNAL statement is executed, the value of each of the specified string-constants and variables is assigned to the corresponding condition-information-item. However, if the length of a string-constant or variable is longer than the maximum length of the corresponding condition-information-item, it is truncated without a warning. For details on the assignment rules, see Assignments and comparisons. For details on the maximum length of specific condition-information-items, see GET DIAGNOSTICS.

Processing a SIGNAL statement: When a SIGNAL statement is issued, the SQLCODE is based on the SQLSTATE value as follows:

If the specified SQLSTATE class is either '01' or '02', a warning or not found is signalled and the SQLCODE is set to +438.
Otherwise, an exception is signalled and the SQLCODE is set to –438.

Examples

Example 1: Signal SQLSTATE '75002' with a descriptive message text.

   EXEC SQL SIGNAL SQLSTATE '75002'
               SET MESSAGE_TEXT = 'Customer number is not known';

Example 2: Signal SQLSTATE '75002' with a descriptive message text and associate a specific table with the error.

   EXEC SQL SIGNAL SQLSTATE '75002'
               SET MESSAGE_TEXT = 'Customer number is not known',
                   SCHEMA_NAME = 'CORPDATA',
                   TABLE_NAME = 'CUSTOMER';