9.16. SQLSTATE

Available in

PSQL

Added in

2.5.1

Type

CHAR(5)

Description

In a “WHEN …​ DO” error handler, the SQLSTATE context variable contains the 5-character, SQL-2003-compliant status code resulting from the statement that raised the error. Outside error handlers, SQLSTATE is always '00000'. Outside PSQL, it is not available at all.

Example

when any
do
begin
  Msg = case sqlstate
          when '22003' then 'Numeric value out of range.'
          when '22012' then 'Division by zero.'
          when '23000' then 'Integrity constraint violation.'
          else 'Something bad happened! SQLSTATE = ' || sqlstate
        end;
  exception ex_custom Msg;
end

Notes

• SQLSTATE is destined to replace SQLCODE. The latter is now deprecated in Firebird and will disappear in some future version.

• Firebird does not (yet) support the syntax “WHEN SQLSTATE …​ DO”. You have to use WHEN ANY and test the SQLSTATE variable within the handler.

• Each SQLSTATE code is the concatenation of a 2-character class and a 3-character subclass. Classes 00 (successful completion), 01 (warning) and 02 (no data) represent completion conditions. Every status code outside these classes is an exception. Because classes 00, 01 and 02 don’t raise an error, they won’t ever show up in the SQLSTATE variable.

• For a complete listing of SQLSTATE codes, consult the SQLSTATE Codes and Message Texts section in Appendix B: Exception Handling, Codes and Messages.