FirebirdSQL logo

SQLCODE

SQLCODE of the Firebird error in a WHEN …​ DO block

Available in

PSQL

Deprecated in

2.5.1

Type

INTEGER

Syntax
SQLCODE

In a “WHEN …​ DO” error handling block, the SQLCODE context variable contains the numeric value of the current SQL error code.SQLCODE is non-zero in WHEN …​ DO blocks, if the current error has a SQL error code.Outside error handlers, SQLCODE is always 0.Outside PSQL, it doesn’t exist at all.

Warning

SQLCODE is now deprecated in favour of the SQL-2003-compliant [fblangref50-contextvars-sqlstate] status code.Support for SQLCODE and WHEN SQLCODE will be discontinued in a future version of Firebird.

Example
when any
do
begin
  if (sqlcode <> 0) then
    Msg = 'An SQL error occurred!';
  else
    Msg = 'Something bad happened!';
  exception ex_custom Msg;
end

SQLSTATE

SQLSTATE code of the Firebird error in a WHEN …​ DO block

Available in

PSQL

Type

CHAR(5)

Syntax
SQLSTATE

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

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

  • 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 [fblangref50-appx02-errorcodes].

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

CURRENT_DATE

Current server date in the session time zone

Type

DATE

Syntax
CURRENT_DATE
Note

Within a PSQL module (procedure, trigger or executable block), the value of CURRENT_DATE will remain constant every time it is read.If multiple modules call or trigger each other, the value will remain constant throughout the duration of the outermost module.If you need a progressing value in PSQL (e.g. to measure time intervals), use [fblangref50-contextvars-today].

Examples
select current_date from rdb$database
-- returns e.g. 2011-10-03

'TODAY'

Current date in cast context

Type

CHAR(5), or depends on explicit CAST

'TODAY' is not a variable, but a string literal or date mnemonic.It is, however, special in the sense that when you CAST() it to a date/time type, you will get the current date.If the target datetime type has a time component, it will be set to zero.'TODAY' is case-insensitive, and the engine ignores leading or trailing spaces when casting.

Note
  • 'TODAY' always returns the actual date, even in PSQL modules, where [fblangref50-contextvars-current-date], [fblangref50-contextvars-current-time] and [fblangref50-contextvars-current-timestamp] return the same value throughout the duration of the outermost routine.This makes 'TODAY' useful for measuring time intervals in triggers, procedures and executable blocks (at least if your procedures are running for days).

  • Except in the situation mentioned above, reading CURRENT_DATE, is generally preferable to casting 'TODAY'.

  • Firebird 3.0 and earlier allowed the use of 'TODAY' in datetime literals (a.k.a. "`shorthand casts"`), this is no longer allowed since Firebird 4.0.

  • When cast to a TIMESTAMP WITH TIME ZONE, the time reflected will be 00:00:00 in UTC rebased to the session time zone.

Examples
select 'Today' from rdb$database
-- returns 'Today'

select cast('Today' as date) from rdb$database
-- returns e.g. 2011-10-03

select cast('TODAY' as timestamp) from rdb$database
-- returns e.g. 2011-10-03 00:00:00.0000