FirebirdSQL logo

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

  1. 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 ^;
  2. 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
  3. 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
    ...
  4. 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