Firebird Documentation IndexFirebird 3.0.2 Release NotesProcedural SQL (PSQL) → Exceptions with parameters
Firebird Home Firebird Home Prev: Scrollable (Bi-directional) Cursor SupportFirebird Documentation IndexUp: Procedural SQL (PSQL)Next: CONTINUE in Looping Logic

Exceptions with parameters

Adriano dos Santos Fernandes

An exception can now be defined with a message containing slots for parameters which are filled and passed when raising the exception, using the syntax pattern

  EXCEPTION <name> USING ( <value list> )
      

Examples

  create exception e_invalid_val 'Invalid value @1 for the field @2';
    
...
if (val < 1000) then
   thing = val;
else
   exception e_invalid_val using (val, 'thing');
end
    
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;
    

Notes

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

Since a new error code (isc_formatted_exception) is used, the client must be v.3.0, or at least use the firebird.msg file from v.3.0, in order to translate the status vector to a string.

Considering, in left-to-right order, each parameter passed in the exception-raising statement as “the Nth”, with N starting at 1:

  • If an Nth parameter is not passed, the text is not substituted.

  • If NULL is passed, it is replaced by the string '*** null ***'.

  • If more parameters are passed than are defined in the exception message, the surplus ones are ignored.

  • The total length of the message, including the values of the parameters, is still limited to 1053 bytes.

Prev: Scrollable (Bi-directional) Cursor SupportFirebird Documentation IndexUp: Procedural SQL (PSQL)Next: CONTINUE in Looping Logic
Firebird Documentation IndexFirebird 3.0.2 Release NotesProcedural SQL (PSQL) → Exceptions with parameters