|
|
|
Valid in: DBProc
The Raise Error statement notifies the DBMS Server and the application that a database procedure has encountered an error. The raise error statement can only be issued inside a database procedure. This statement is particularly useful when using a rule and its associated database procedure to apply an integrity constraint.
When this statement is issued, the DBMS Server responds as if the database procedure has encountered an error. If the procedure was invoked by a rule, the DBMS Server rolls back any changes made to the database by the original user statement and any made by the database procedure and issues an error to the application. If the raise error statement is issued in a procedure that is executed directly by the execute procedure statement, the error is processed in the same manner as are other errors in a database procedure. (For details, refer Database Procedures.)
When executing a Raise Error statement with associated errortext, both the errornumber and errortext are returned to the application. However, only the errortext is displayed. In embedded SQL and 4GL applications, this can be changed by using inquire_sql to retrieve the error number and text (dbmserror and errortext). Additionally, in embedded SQL, use the whenever statement with the sqlerror condition to inhibit the automatic display of the errortext and provide an alternate error handling mechanism.
The errornumber is considered a local DBMS server error and, by default, is returned to SQLCA variable sqlerrd(1) and to dbmserror, which is accessible using inquire_sql. The generic error number corresponding to a raise error is 41300. This number is returned, by default, to errorno, which is accessible using inquire_sql, and to sqlcode, another SQLCA variable. The number in sqlcode is negative (-41300).
If you have specified that local errors are returned to errorno and sqlcode (by issuing the set_sql(dbmserror) statement), the locations described above for the errornumber and its generic error number are reversed also. In such cases, it is not necessary to provide a negative number for the errornumber; the DBMS Server automatically negates the number when it places the number in sqlcode. For a complete discussion of local and generic error numbers, see the chapter "Working with Transactions and Handling Errors."
In interactive applications that rely on default error messages, such as QBF, the errornumber must be included as part of the errortext to display the number. For example, assume that you are working in QBF and a rule fires and, as a result, the following statement executes:
raise error 123445 'Invalid value inserted';
When the statement is executed, QBF displays a pop-up window with the message:
'Invalid value inserted'
If it is important to display the error number also, it must be included as part of the errortext in addition to specifying it as the errornumber:
raise error 123445
'Error 123445: Invalid value inserted';
To direct the output of the raise error statement to the error log, specify with destination = (error_log). The error number and text are written to the "errlog.log" file with message identifier E_QE0300. To direct output to the session (the default behavior), specify with destination = (session). To both log an error and return it to an application, specify with destination = (session, error_log).
To direct the output of the raise error statement directly to the audit log, specify with destination=(audit_log). Any such messages are regarded as security audit events. The description indicates the source of the event (for example: message, raise error). The message text and error number are available in the detail information for the event.
|
|
|
The Raise Error statement has the following format:
RAISE ERROR errornumber [errortext]
[WITH DESTINATION = ([SESSION] [, ERROR_LOG] [, AUDIT_LOG])];
Can be an integer constant, a local variable, or a parameter in the invoked database procedure. If it is a local variable, it must be either a non-nullable integer or smallint type.
Is an optional text string that describes the error associated with errornumber. It can be a string constant, a local string variable, or a parameter in the invoked database procedure. If errortext is not specified, interactive applications such as QBF display a default error message.
|
|
|
Anyone with permission to create procedures can use this statement.
|
|
|
|
|
|
The following example enforces a relationship (or integrity constraint) between an employee and a manager. When an employee is entered into the database, a check is performed to enforce the existence of the manager of the employee. If the manager is not found, the Raise Error statement returns a message to the user and rolls back the changes made to the database by the statement that fired the rule.
create procedure validate_manager
(mname varchar(30)) as
declare
msg varchar(80) not null;
check_val integer;
begin
select count(*) into :check_val from manager
where name = :mname;
if check_val = 0 then
msg = 'Error 99999: Manager "' + :mname +
'" not found.';
raise error 99999 :msg;
endif;
end;
create rule check_emp after insert into employee
execute procedure validate_manager
(mname = new.manager);