Data Manipulation (DML) StatementsBuilt-in Scalar Functions

Custom Exceptions

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

`EXCEPTION`

Throws a user-defined exception or rethrows an exception

Syntax

EXCEPTION [
    exception_name
    [ custom_message
    | USING (<value_list>)]
  ]

<value_list> ::= <val> [, <val> ...]

Table 1. `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
val	Value expression that replaces parameter slots in the exception message text

The EXCEPTION statement with exception_name 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 default exception message can contain slots for parameters that can be filled when throwing an exception.To pass parameter values to an exception, use the USING clause.Considering, in left-to-right order, each parameter passed in the exception-raising statement as “the Nth”, with N starting at 1:

If the Nth parameter is not passed, its slot is not replaced
If a NULL parameter is passed, the slot will be replaced with the string “*** null ***”
If more parameters are passed than are defined in the exception message, the surplus ones are ignored
The maximum number of parameters is 9
The maximum message length, including parameter values, is 1053 bytes

Note

The status vector is generated this code combination isc_except, <exception number>, isc_formatted_exception, <formatted exception message>, <exception parameters>.

The error code used (isc_formatted_exception) was introduced in Firebird 3.0, so the client must be at least version 3.0, or at least use the firebird.msg from version 3.0 or higher, to translate the status vector to a string.

Warning

If the message contains a parameter slot number that is greater than 9, the second and subsequent digits will be treated as literal text.For example @10 will be interpreted as slot 1 followed by a literal ‘0’.

As an example:

CREATE EXCEPTION ex1
  'something wrong in @1@2@3@4@5@6@7@8@9@10@11';
SET TERM ^;
EXECUTE BLOCK AS
BEGIN
  EXCEPTION ex1 USING ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i');
END^

This will produce the following output

Statement failed, SQLSTATE = HY000
exception 1
-EX1
-something wrong in abcdefghia0a1

Exceptions can be handled in a [fblangref50-psql-when] statement.If an exception is not handled in a module, then the effects of the actions executed inside this module are cancelled, and the caller program receives the exception (either the default text, or the custom text).

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.

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

`EXCEPTION` Examples

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

Using a parameterized exception:

CREATE EXCEPTION EX_BAD_SP_NAME
  'Name of procedures must start with' '@ 1' ':' '@ 2' '' ;
...
CREATE TRIGGER TRG_SP_CREATE BEFORE CREATE PROCEDURE
AS
  DECLARE SP_NAME VARCHAR(255);
BEGIN
  SP_NAME = RDB$GET_CONTEXT ('DDL_TRIGGER' , 'OBJECT_NAME');
  IF (SP_NAME NOT STARTING 'SP_') THEN
    EXCEPTION EX_BAD_SP_NAME USING ('SP_', SP_NAME);
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

docnext count = 3

`WHEN … DO`

Catches an exception for error handling

Syntax

WHEN {<error> [, <error> ...] | ANY}
DO <compound_statement>

<error> ::=
  { EXCEPTION exception_name
  | SQLCODE number
  | GDSCODE errcode
  | SQLSTATE sqlstate_code }

Table 1. `WHEN … DO` Statement Parameters
Argument	Description
exception_name	Exception name
number	SQLCODE error code
errcode	Symbolic GDSCODE error name
sqlstate_code	String literal with the SQLSTATE error code
compound_statement	A single statement, or a block of statements

The WHEN … DO statement handles Firebird 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 statement, and after any other statement.

The keyword DO is followed by a single statement, or statements wrapped in a BEGIN … END block, that handles the exception.The SQLCODE, GDSCODE, and SQLSTATE context variables are available in the context of this statement or block.Use the RDB$ERROR function to obtain the SQLCODE, GDSCODE, SQLSTATE, custom exception name and exception message.The EXCEPTION statement, without parameters, can also be used in this context to re-throw the error or exception.

Targeting GDSCODE

The argument for the WHEN GDSCODE clause is the symbolic name associated with the internally-defined exception, such as grant_obj_notfound for GDS error 335544551.

In a statement or block of statements of the DO clause, a GDSCODE context variable, containing the numeric code, becomes available.That numeric code is required if you want to compare a GDSCODE exception with a targeted error.To compare it with a specific error, you need to use a numeric values, for example 335544551 for grant_obj_notfound.

Similar context variables are available for SQLCODE and SQLSTATE.

The WHEN … DO statement or block is only executed when one of the events targeted by its conditions occurs at run-time.If the WHEN … DO statement is executed, even if it 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 only 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 firebird#4803.
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.

Scope of a `WHEN … DO` Statement

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

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 is performed.Use of external tables can be useful here, as data written to them is transaction-independent.The date inserted into a linked external file will still be there, regardless of whether the overall process succeeds or not.

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,
                             MESSAGE)
      VALUES ('ADD_COUNTRY',
              GDSCODE,
              SQLCODE,
              SQLSTATE,
              RDB$ERROR(MESSAGE));
    -- 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,
    RDB$ERROR(MESSAGE);
  EXIT;
END
...

Catching errors using the SQLSTATE code

EXECUTE BLOCK
AS
  DECLARE VARIABLE I INT;
BEGIN
  BEGIN
    I = 1/0;
    WHEN SQLSTATE '22003' DO
      EXCEPTION E_CUSTOM_EXCEPTION
        'Numeric value out of range.';
    WHEN SQLSTATE '22012' DO
      EXCEPTION E_CUSTOM_EXCEPTION
        'Division by zero.';
    WHEN SQLSTATE '23000' DO
      EXCEPTION E_CUSTOM_EXCEPTION
       'Integrity constraint violation.';
  END
END

Custom Exceptions

Custom Exceptions

EXCEPTION

EXCEPTION Examples

docnext count = 3

WHEN …​ DO

Scope of a WHEN …​ DO Statement

Examples using WHEN…​DO

`EXCEPTION`

`EXCEPTION` Examples

`WHEN … DO`

Scope of a `WHEN … DO` Statement

Examples using `WHEN…DO`