Firebird 2.5 Language Reference → Firebird 2.5 Language Reference → Built-in functions and Variables → Context variables
Firebird Firebird Prev: Built-in functions and VariablesFirebird 2.5 Language ReferenceUp: Built-in functions and VariablesNext: Scalar Functions

Context variables

Table of Contents

CURRENT_CONNECTION
CURRENT_DATE
CURRENT_ROLE
CURRENT_TIME
CURRENT_TIMESTAMP
CURRENT_TRANSACTION
CURRENT_USER
DELETING
GDSCODE
INSERTING
NEW
'NOW'
OLD
ROW_COUNT
SQLCODE
SQLSTATE
'TODAY'
'TOMORROW'
UPDATING
'YESTERDAY'
USER

CURRENT_CONNECTION

Available in: DSQL, PSQL

Description: CURRENT_CONNECTION contains the unique identifier of the current connection.

Type: INTEGER

Examples: 

select current_connection from rdb$database
execute procedure P_Login(current_connection)

The value of CURRENT_CONNECTION is stored on the database header page and reset to 0 upon restore. Since version 2.1, it is incremented upon every new connection. (In previous versions, it was only incremented if the client read it during a session.) As a result, CURRENT_CONNECTION now indicates the number of connections since the creation —or most recent restoration—of the database.

CURRENT_DATE

Available in: DSQL, PSQL, ESQL

Description: CURRENT_DATE returns the current server date.

Type: DATE

Syntax: 

CURRENT_DATE

Examples: 

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

Notes: 

  • 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 'TODAY'.

CURRENT_ROLE

Available in: DSQL, PSQL

Description: CURRENT_ROLE is a context variable containing the role of the currently connected user. If there is no active role, CURRENT_ROLE is NONE.

Type: VARCHAR(31)

Example: 

if (current_role <> 'MANAGER')
  then exception only_managers_may_delete;
else
  delete from Customers where custno = :custno;

CURRENT_ROLE always represents a valid role or NONE. If a user connects with a non-existing role, the engine silently resets it to NONE without returning an error.

CURRENT_TIME

Available in: DSQL, PSQL, ESQL

Description: CURRENT_TIME returns the current server time. In versions prior to 2.0, the fractional part used to be always “.0000”, giving an effective precision of 0 decimals. From Firebird 2.0 onward you can specify a precision when polling this variable. The default is still 0 decimals, i.e. seconds precision.

Type: TIME

Syntax: 

CURRENT_TIME [(precision)]

precision  ::=  0 | 1 | 2 | 3

The optional precision argument is not supported in ESQL.

Table 8.1. CURRENT_TIME Parameter

Parameter Description
precision Precision. The default value is 0. Not supported in ESQL


Examples: 

select current_time from rdb$database
-- returns e.g. 14:20:19.6170
select current_time(2) from rdb$database
-- returns e.g. 14:20:23.1200

Notes: 

  • Unlike CURRENT_TIME, the default precision of CURRENT_TIMESTAMP has changed to 3 decimals. As a result, CURRENT_TIMESTAMP is no longer the exact sum of CURRENT_DATE and CURRENT_TIME, unless you explicitly specify a precision.

  • Within a PSQL module (procedure, trigger or executable block), the value of CURRENT_TIME 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 'NOW'.

CURRENT_TIMESTAMP

Available in: DSQL, PSQL, ESQL

Description: CURRENT_TIMESTAMP returns the current server date and time. In versions prior to 2.0, the fractional part used to be always “.0000”, giving an effective precision of 0 decimals. From Firebird 2.0 onward you can specify a precision when polling this variable. The default is 3 decimals, i.e. milliseconds precision.

Type: TIMESTAMP

Syntax: 

CURRENT_TIMESTAMP [(precision)]

precision  ::=  0 | 1 | 2 | 3

The optional precision argument is not supported in ESQL.

Table 8.2. CURRENT_TIME Parameter

Parameter Description
precision Precision. The default value is 0. Not supported in ESQL


Examples: 

select current_timestamp from rdb$database
-- returns e.g. 2008-08-13 14:20:19.6170
select current_timestamp(2) from rdb$database
-- returns e.g. 2008-08-13 14:20:23.1200

Notes: 

  • The default precision of CURRENT_TIME is still 0 decimals, so in Firebird 2.0 and up CURRENT_TIMESTAMP is no longer the exact sum of CURRENT_DATE and CURRENT_TIME, unless you explicitly specify a precision.

  • Within a PSQL module (procedure, trigger or executable block), the value of CURRENT_TIMESTAMP 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 'NOW'.

CURRENT_TRANSACTION

Available in: DSQL, PSQL

Description: CURRENT_TRANSACTION contains the unique identifier of the current transaction.

Type: INTEGER

Examples: 

select current_transaction from rdb$database
New.Txn_ID = current_transaction;

The value of CURRENT_TRANSACTION is stored on the database header page and reset to 0 upon restore. It is incremented with every new transaction.

CURRENT_USER

Available in: DSQL, PSQL

Description: CURRENT_USER is a context variable containing the name of the currently connected user. It is fully equivalent to USER.

Type: VARCHAR(31)

Example: 

create trigger bi_customers for customers before insert as
begin
  New.added_by  = CURRENT_USER;
  New.purchases = 0;
end

DELETING

Available in: PSQL

Description: Available in triggers only, DELETING indicates if the trigger fired because of a DELETE operation. Intended for use in multi-action triggers.

Type: boolean

Example: 

if (deleting) then
begin
  insert into Removed_Cars (id, make, model, removed)
    values (old.id, old.make, old.model, current_timestamp);
end

GDSCODE

Available in: PSQL

Description: In a “WHEN ... DO” error handling block, the GDSCODE context variable contains the numerical representation of the current Firebird error code. Prior to Firebird 2.0, GDSCODE was only set in WHEN GDSCODE handlers. Now it may also be non-zero in WHEN ANY, WHEN SQLCODE and WHEN EXCEPTION blocks, provided that the condition raising the error corresponds with a Firebird error code. Outside error handlers, GDSCODE is always 0. Outside PSQL it doesn't exist at all.

Type: INTEGER

Example: 

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);
  exit;
end
[Note] Notice

After WHEN GDSCODE, you must use symbolic names like grant_obj_notfound etc. But the GDSCODE context variable is an INTEGER. If you want to compare it against a specific error, the numeric value must be used, e.g. 335544551 for grant_obj_notfound.

INSERTING

Available in: PSQL

Description: Available in triggers only, INSERTING indicates if the trigger fired because of an INSERT operation. Intended for use in multi-action triggers.

Type: boolean

Example: 

if (inserting or updating) then
begin
  if (new.serial_num is null) then
    new.serial_num = gen_id(gen_serials, 1);
end

NEW

Available in: PSQL, triggers only

Description: NEW contains the new version of a database record that has just been inserted or updated. Starting with Firebird 2.0 it is read-only in AFTER triggers.

Type: Data row

[Note] Note

In multi-action triggers—introduced in Firebird 1.5—NEW is always available. But if the trigger is fired by a DELETE, there will be no new version of the record. In that situation, reading from NEW will always return NULL; writing to it will cause a runtime exception.

'NOW'

Available in: DSQL, PSQL, ESQL

Changed in: 2.0

Description: 'NOW' is not a variable but a string literal. It is, however, special in the sense that when you CAST() it to a date/time type, you will get the current date and/or time. The fractional part of the time used to be always “.0000”, giving an effective seconds precision. Since Firebird 2.0 the precision is 3 decimals, i.e. milliseconds. 'NOW' is case-insensitive, and the engine ignores leading or trailing spaces when casting.

Note: Please be advised that these shorthand expressions are evaluated immediately at parse time and stay the same as long as the statement remains prepared. Thus, even if a query is executed multiple times, the value for e.g. “timestamp 'now'” won't change, no matter how much time passes. If you need the value to progress (i.e. be evaluated upon every call), use a full cast.

Type: CHAR(3)

Examples: 

select 'Now' from rdb$database
-- returns 'Now'
select cast('Now' as date) from rdb$database
-- returns e.g. 2008-08-13
select cast('now' as time) from rdb$database
-- returns e.g. 14:20:19.6170
select cast('NOW' as timestamp) from rdb$database
-- returns e.g. 2008-08-13 14:20:19.6170

Shorthand syntax for the last three statements: 

select date 'Now' from rdb$database
select time 'now' from rdb$database
select timestamp 'NOW' from rdb$database

Notes: 

  • 'NOW' always returns the actual date/time, even in PSQL modules, where CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP return the same value throughout the duration of the outermost routine. This makes 'NOW' useful for measuring time intervals in triggers, procedures and executable blocks.

  • Except in the situation mentioned above, reading CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP is generally preferable to casting 'NOW'. Be aware though that CURRENT_TIME defaults to seconds precision; to get milliseconds precision, use CURRENT_TIME(3).

OLD

Available in: PSQL, triggers only

Description: OLD contains the existing version of a database record just before a deletion or update. Starting with Firebird 2.0 it is read-only.

Type: Data row

[Note] Note

In multi-action triggers —introduced in Firebird 1.5—OLD is always available. But if the trigger is fired by an INSERT, there is obviously no pre-existing version of the record. In that situation, reading from OLD will always return NULL; writing to it will cause a runtime exception.

ROW_COUNT

Available in: PSQL

Changed in: 2.0

Description: The ROW_COUNT context variable contains the number of rows affected by the most recent DML statement (INSERT, UPDATE, DELETE, SELECT or FETCH) in the current trigger, stored procedure or executable block.

Type: INTEGER

Example: 

update Figures set Number = 0 where id = :id;
if (row_count = 0) then
  insert into Figures (id, Number) values (:id, 0);

Behaviour with SELECT and FETCH: 

  • After a singleton SELECT, ROW_COUNT is 1 if a data row was retrieved and 0 otherwise.

  • In a FOR SELECT loop, ROW_COUNT is incremented with every iteration (starting at 0 before the first).

  • After a FETCH from a cursor, ROW_COUNT is 1 if a data row was retrieved and 0 otherwise. Fetching more records from the same cursor does not increment ROW_COUNT beyond 1.

  • In Firebird 1.5.x, ROW_COUNT is 0 after any type of SELECT statement.

[Note] Note

ROW_COUNT cannot be used to determine the number of rows affected by an EXECUTE STATEMENT or EXECUTE PROCEDURE command.

SQLCODE

Available in: PSQL

Deprecated in: 2.5.1

Description: In a “WHEN ... DO” error handling block, the SQLCODE context variable contains the current SQL error code. Prior to Firebird 2.0, SQLCODE was only set in WHEN SQLCODE and WHEN ANY handlers. Now it may also be non-zero in WHEN GDSCODE and WHEN EXCEPTION blocks, provided that the condition raising the error corresponds with an SQL error code. Outside error handlers, SQLCODE is always 0. Outside PSQL it doesn't exist at all.

Type: INTEGER

Example: 

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

Important notice: SQLCODE is now deprecated in favour of the SQL-2003-compliant SQLSTATE status code. Support for SQLCODE and WHEN SQLCODE will be discontinued in some future version of Firebird.

SQLSTATE

Available in: PSQL

Added in: 2.5.1

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.

Type: CHAR(5)

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.

'TODAY'

Available in: DSQL, PSQL, ESQL

Description: 'TODAY' is not a variable but a string literal. It is, however, special in the sense that when you CAST() it to a date/time type, you will get the current date. 'TODAY' is case-insensitive, and the engine ignores leading or trailing spaces when casting.

Type: CHAR(5)

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

Shorthand syntax for the last two statements: 

select date 'Today' from rdb$database
		select timestamp 'TODAY' from rdb$database

Notes: 

  • 'TODAY' always returns the actual date, even in PSQL modules, where CURRENT_DATE, CURRENT_TIME and 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 'NOW'.

'TOMORROW'

Available in: DSQL, PSQL, ESQL

Description: 'TOMORROW' is not a variable but a string literal. It is, however, special in the sense that when you CAST() it to a date/time type, you will get the date of the next day. See also 'TODAY'.

Type: CHAR(8)

Examples: 

select 'Tomorrow' from rdb$database
		-- returns 'Tomorrow'
select cast('Tomorrow' as date) from rdb$database
		-- returns e.g. 2011-10-04
select cast('TOMORROW' as timestamp) from rdb$database
		-- returns e.g. 2011-10-04 00:00:00.0000

Shorthand syntax for the last two statements: 

select date 'Tomorrow' from rdb$database
		select timestamp 'TOMORROW' from rdb$database

UPDATING

Available in: PSQL

Description: Available in triggers only, UPDATING indicates if the trigger fired because of an UPDATE operation. Intended for use in multi-action triggers.

Type: boolean

Example: 

if (inserting or updating) then
begin
  if (new.serial_num is null) then
    new.serial_num = gen_id(gen_serials, 1);
end

'YESTERDAY'

Available in: DSQL, PSQL, ESQL

Description: 'YESTERDAY' is not a variable but a string literal. It is, however, special in the sense that when you CAST() it to a date/time type, you will get the date of the day before. See also 'TODAY'.

Type: CHAR(9)

Examples: 

select 'Yesterday' from rdb$database
		-- returns 'Tomorrow'
select cast('Yesterday as date) from rdb$database
		-- returns e.g. 2011-10-02
select cast('YESTERDAY' as timestamp) from rdb$database
		-- returns e.g. 2011-10-02 00:00:00.0000

Shorthand syntax for the last two statements: 

select date 'Yesterday' from rdb$database
		select timestamp 'YESTERDAY' from rdb$database

USER

Available in: DSQL, PSQL

Description: USER is a context variable containing the name of the currently connected user. It is fully equivalent to CURRENT_USER.

Type: VARCHAR(31)

Example: 

create trigger bi_customers for customers before insert as
begin
  New.added_by  = USER;
  New.purchases = 0;
end
Prev: Built-in functions and VariablesFirebird 2.5 Language ReferenceUp: Built-in functions and VariablesNext: Scalar Functions
Firebird 2.5 Language Reference → Firebird 2.5 Language Reference → Built-in functions and Variables → Context variables