Notes
SQLSTATE values: Any valid SQLSTATE value can be
used in the RESIGNAL 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 book in the iSeries Information Center.
Assignment: When the RESIGNAL statement is executed,
the value of each of the specified string-constants and variables is assigned (using storage assignment
rules) to the corresponding condition-information-item. 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 RESIGNAL statement:
- If the RESIGNAL statement is specified without a SQLSTATE clause
or a condition-name, the SQL function, SQL procedure,
or SQL trigger resignals the identical condition that invoked the handler
and the SQLCODE is not changed.
- When a RESIGNAL statement is issued and an SQLSTATE or condition-name is specified, 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 returned and the SQLCODE is set to -438.
If the SQLSTATE or condition indicates that an exception is signalled (SQLSTATE
class other than '01' or '02'):,
- If a handler exists in the same compound statement as the RESIGNAL statement,
and the compound-statement contains a handler for
SQLEXCEPTION or the specified SQLSTATE or condition; the exception is handled
and control is transferred to that handler.
- If the compound-statement is nested and an outer
level compound-statement has a handler for SQLEXCEPTION
or the specified SQLSTATE or condition; the exception is handled and control
is transferred to that handler.
- Otherwise, the exception is not handled and control is immediately returned
to the end of the compound statement.
If the SQLSTATE or condition indicates that a warning (SQLSTATE class '01')
or not found (SQLSTATE class '02') is signalled:
- If a handler exists in the same compound statement as the RESIGNAL statement,
and the compound-statement contains a handler for
SQLWARNING (if the SQLSTATE class is '01'), NOT FOUND (if the SQLSTATE class
is '02'), or the specified SQLSTATE or condition; the warning or not found
condition is handled and control is transferred to that handler.
- If the compound-statement is nested and an outer
level compound statement contains a handler for SQLWARNING (if the SQLSTATE
class is '01'), NOT FOUND (if the SQLSTATE class is '02'), or the specified
SQLSTATE or condition; the warning or not found condition is handled and the
exception is handled and control is returned to that handler.
- Otherwise, the warning is not handled and processing continues with the
next statement.
Affects on the diagnostic area: The RESIGNAL
statement may modify the contents of the current diagnostics area. If an SQLSTATE
or signal-information is specified as part
of the RESIGNAL statement, the diagnostics area is updated with the specified
information.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.