In FirebirdSql, how to return exception message from procedure

Question

I want to return the error message from a procedure when an exception happens. In SQL Server you would select the Error_Number() and Error_Message(). How would I do it in FirebirdSql ``` SET TERM ^ ; CREATE PROCEDURE sprocname ( id int ) RETURNS ( gcode int, errmsg varchar(250) ) AS BEGIN gcode = 0; errmsg = ''; -- do procedure code here WHEN ANY DO BEGIN gcode = gdscode; -- ?? errmsg = ??; END SUSPEND; END^ SET TERM ; ^ ```

Accepted Answer

Unfortunately, you will need to do that at the client side, as it is currently not possible to obtain this in PSQL. There is a feature request in the Firebird tracker, which has been implemented for Firebird 4, which is expected to be released in 2019.

See Firebird 4 release notes, section System Function RDB$ERROR():

The function `RDB$ERROR()` takes a PSQL error context as input and returns the specific context of the active exception. Its scope is confined to the context of the exception-handling block in PSQL. Outside the exception handling block, `RDB$ERROR` always contains `NULL`.

The type of the return value depends on the context.

Syntax Rules

```
RDB$ERROR ( context )
context ::= { GDSCODE | SQLCODE | SQLSTATE | EXCEPTION | MESSAGE }
```

[..]

Example

```
BEGIN
  ...
WHEN ANY DO
  EXECUTE PROCEDURE P_LOG_EXCEPTION(RDB$ERROR(MESSAGE));
END
```

In FirebirdSql, how to return exception message from procedure

Solution

Problem