7.7. Trapping and Handling Errors

Firebird has a useful lexicon of PSQL statements and resources for trapping errors in modules and for handling them. Internally-implemented exceptions exist for stalling execution when every sort of standard error occurs in DDL, DSQL and the physical environment.

In PSQL code, exceptions are handled by means of the Section 7.7.4, “WHEN … DO” statement. Handling an exception in the code involves either fixing the problem in situ, or stepping past it; either solution allows execution to continue without returning an exception message to the client.

An exception results in execution being terminated in the block. Instead of passing the execution to the END statement, the procedure moves outward through levels of nested blocks, starting from the block where the exception is caught, searching for the code of the handler that knows about this exception. It stops searching when it finds the first WHEN statement that can handle this exception.

7.7.1. System Exceptions

An exception is a message that is generated when an error occurs.

All exceptions handled by Firebird have predefined numeric values for context variables (symbols) and text messages associated with them. Error messages are output in English by default. Localized Firebird builds are available, where error messages are translated into other languages.

Complete listings of the system exceptions can be found in Appendix B: Exception Codes and Messages:

7.7.2. Custom Exceptions

Custom exceptions can be declared in the database as persistent objects and called in the PSQL code to signal specific errors; for instance, to enforce certain business rules. A custom exception consists of an identifier and a default message of approximately 1000 bytes. For details, see CREATE EXCEPTION.

7.7.3. `EXCEPTION`

Used forThrowing a user-defined exception or re-throwing an exception

Available inPSQL

Syntax

  |EXCEPTION [exception_name [custom_message]]

Table 7.17. EXCEPTION Statement Parameters

Argument	Description
exception_name	Exception name
custom_message	Alternative message text to be returned to the caller interface when an exception is thrown. Maximum length of the text message is 1,021 bytes

An EXCEPTION statement throws the user-defined exception with the specified name. An alternative message text of up to 1,021 bytes can optionally override the exception’s default message text.

The exception can be handled in the statement, by just leaving it with no specific WHEN … DO handler and allowing the trigger or stored procedure to terminate and roll back all operations. The calling application gets the alternative message text, if any was specified; otherwise, it receives the message originally defined for that exception.

Within the exception-handling block — and only within it — the caught exception can be re-thrown by executing the EXCEPTION statement without parameters. If located outside the block, the re-thrown EXCEPTION call has no effect.

ⓘ

Note

Custom exceptions are stored in the system table RDB$EXCEPTIONS.

Examples

Throwing an exception with dynamically generated text:

  |…
  |EXCEPTION EX_BAD_TYPE
  |  'Incorrect record type with id ' || new.id;
  |…

Throwing an exception upon a condition in the SHIP_ORDER stored procedure:

   |CREATE OR ALTER PROCEDURE SHIP_ORDER (
   |  PO_NUM CHAR(8))
   |AS
   |  DECLARE VARIABLE ord_stat  CHAR(7);
   |  DECLARE VARIABLE hold_stat CHAR(1);
   |  DECLARE VARIABLE cust_no   INTEGER;
   |  DECLARE VARIABLE any_po    CHAR(8);
   |BEGIN
   |  SELECT
   |    s.order_status,
   |    c.on_hold,
   |    c.cust_no
   |  FROM
   |    sales s, customer c
   |  WHERE
   |    po_number = :po_num AND
   |    s.cust_no = c.cust_no
   |  INTO :ord_stat,
   |       :hold_stat,
   |       :cust_no;
   | 
   |  IF (ord_stat = 'shipped') THEN
   |    EXCEPTION order_already_shipped;
   |  /* Other statements */
   |END

Throwing an exception upon a condition and replacing the original message with an alternative message:

   |CREATE OR ALTER PROCEDURE SHIP_ORDER (
   |  PO_NUM CHAR(8))
   |AS
   |  DECLARE VARIABLE ord_stat  CHAR(7);
   |  DECLARE VARIABLE hold_stat CHAR(1);
   |  DECLARE VARIABLE cust_no   INTEGER;
   |  DECLARE VARIABLE any_po    CHAR(8);
   |BEGIN
   |  SELECT
   |    s.order_status,
   |    c.on_hold,
   |    c.cust_no
   |  FROM
   |    sales s, customer c
   |  WHERE
   |    po_number = :po_num AND
   |    s.cust_no = c.cust_no
   |  INTO :ord_stat,
   |       :hold_stat,
   |       :cust_no;
   | 
   |  IF (ord_stat = 'shipped') THEN
   |    EXCEPTION order_already_shipped
   |      'Order status is "' || ord_stat || '"';
   |  /* Other statements */
   |END

Logging an error and re-throwing it in the WHEN block:

   |CREATE PROCEDURE ADD_COUNTRY (
   |  ACountryName COUNTRYNAME,
   |  ACurrency VARCHAR(10) )
   |AS
   |BEGIN
   |  INSERT INTO country (country,
   |                       currency)
   |  VALUES (:ACountryName,
   |          :ACurrency);
   |  WHEN ANY DO
   |  BEGIN
   |    -- write an error in log
   |    IN AUTONOMOUS TRANSACTION DO
   |      INSERT INTO ERROR_LOG (PSQL_MODULE,
   |                             GDS_CODE,
   |                             SQL_CODE,
   |                             SQL_STATE)
   |      VALUES ('ADD_COUNTRY',
   |              GDSCODE,
   |              SQLCODE,
   |              SQLSTATE);
   |    -- Re-throw exception
   |    EXCEPTION;
   |  END
   |END

7.7.4. `WHEN … DO`

Used forCatching an exception and handling the error

Available inPSQL

Syntax

   |<block> ::=
   |  BEGIN
   |    [<compound_statement> ...]
   |    [<when_do> ...]
   |  END
   | 
   |<compound_statement> ::= {<block> | <statement>}
   | 
   |<<when_do>> ::=
   |  WHEN {<error> [, <error> …] | ANY}
   |  DO <compound_statement>
   | 
   |<error> ::=
   |  { EXCEPTION exception_name
   |  | SQLCODE number
   |  | GDSCODE errcode }

Table 7.18. WHEN … DO Statement Parameters

Argument	Description
exception_name	Exception name
number	SQLCODE error code
errcode	Symbolic GDSCODE error name
compound_statement	A statement or a block of statements

The WHEN … DO statement is used to handle errors and user-defined exceptions. The statement catches all errors and user-defined exceptions listed after the keyword WHEN keyword. If WHEN is followed by the keyword ANY, the statement catches any error or user-defined exception, even if they have already been handled in a WHEN block located higher up.

The WHEN … DO statements must be located at the end of a block of statements, before the block’s END keyword, and after any other statement.

The keyword DO is followed by a statement, or a block of statements inside a BEGIN … END block, that handle the exception. The SQLCODE, GDSCODE, and SQLSTATE context variables are available in the context of this statement or block. The EXCEPTION statement, without parameters, can also be used in this context to re-throw the error or exception.

The WHEN … DO statement or block is never executed unless one of the events targeted by its conditions occurs in run-time. If the statement is executed, even if it actually does nothing, execution will continue as if no error occurred: the error or user-defined exception neither terminates nor rolls back the operations of the trigger or stored procedure.

However, if the WHEN … DO statement or block does nothing to handle or resolve the error, the DML statement (SELECT, INSERT, UPDATE, DELETE, MERGE) that caused the error will be rolled back and none of the statements below it in the same block of statements are executed.

☝

Important

If the error is not caused by one of the DML statements (SELECT, INSERT, UPDATE, DELETE, MERGE), the entire block of statements will be rolled back, not just the one that caused an error. Any operations in the WHEN … DO statement will be rolled back as well. The same limitation applies to the EXECUTE PROCEDURE statement. Read an interesting discussion of the phenomenon in Firebird Tracker ticket CORE-4483.
In selectable stored procedures, output rows that were already passed to the client in previous iterations of a FOR SELECT … DO … SUSPEND loop remain available to the client if an exception is thrown subsequently in the process of retrieving rows.

7.7.4.1. Scope of a `WHEN … DO` Statement

A WHEN … DO statement catches errors and exceptions in the current block of statements. It also catches similar exceptions in nested blocks, if those exceptions have not been handled in them.

All changes made before the statement that caused the error are visible to a WHEN … DO statement. However, if you try to log them in an autonomous transaction, those changes are unavailable, because the transaction where the changes took place is not committed at the point when the autonomous transaction is started. Example 4, below, demonstrates this behaviour.

☞

Tip

When handling exceptions, it is sometimes desirable to handle the exception by writing a log message to mark the fault and having execution continue past the faulty record. Logs can be written to regular tables but there is a problem with that: the log records will disappear if an unhandled error causes the module to stop executing and a rollback ensues. Use of external tables can be useful here, as data written to them is transaction-independent. The linked external file will still be there, regardless of whether the overall process succeeds or not.

7.7.4.2. Examples using `WHEN…DO`

Replacing the standard error with a custom one:

   |CREATE EXCEPTION COUNTRY_EXIST '';
   |SET TERM ^;
   |CREATE PROCEDURE ADD_COUNTRY (
   |  ACountryName COUNTRYNAME,
   |  ACurrency VARCHAR(10) )
   |AS
   |BEGIN
   |  INSERT INTO country (country, currency)
   |  VALUES (:ACountryName, :ACurrency);
   | 
   |  WHEN SQLCODE -803 DO
   |    EXCEPTION COUNTRY_EXIST 'Country already exists!';
   |END^
   |SET TERM ^;

Logging an error and re-throwing it in the WHEN block:

   |CREATE PROCEDURE ADD_COUNTRY (
   |  ACountryName COUNTRYNAME,
   |  ACurrency VARCHAR(10) )
   |AS
   |BEGIN
   |  INSERT INTO country (country,
   |                       currency)
   |  VALUES (:ACountryName,
   |          :ACurrency);
   |  WHEN ANY DO
   |  BEGIN
   |    -- write an error in log
   |    IN AUTONOMOUS TRANSACTION DO
   |      INSERT INTO ERROR_LOG (PSQL_MODULE,
   |                             GDS_CODE,
   |                             SQL_CODE,
   |                             SQL_STATE)
   |      VALUES ('ADD_COUNTRY',
   |              GDSCODE,
   |              SQLCODE,
   |              SQLSTATE);
   |    -- Re-throw exception
   |    EXCEPTION;
   |  END
   |END

Handling several errors in one WHEN block

   |...
   |WHEN GDSCODE GRANT_OBJ_NOTFOUND,
   |	 GDSCODE GRANT_FLD_NOTFOUND,
   |	 GDSCODE GRANT_NOPRIV,
   |	 GDSCODE GRANT_NOPRIV_ON_BASE
   |DO
   |BEGIN
   |  EXECUTE PROCEDURE LOG_GRANT_ERROR(GDSCODE);
   |  EXIT;
   |END
   |...

Prev	Up	Next
7.6. Writing the Body Code	Home	Chapter 8. Built-in Functions