Chapter 8Built-in Functions

8.1Context Functions

8.1.1RDB$GET_CONTEXT()

Note

RDB$GET_CONTEXT and its counterpart RDB$SET_CONTEXT are actually predeclared UDFs. They are listed here as internal functions because they are always present — the user doesn’t have to do anything to make them available.

Available inDSQL, PSQL * As a declared UDF it should be available in ESQL

Syntax

  |RDB$GET_CONTEXT ('<namespace>', <varname>)
  | 
  |<namespace> ::= SYSTEM | USER_SESSION | USER_TRANSACTION
  |<varname>   ::= A case-sensitive quoted string of max. 80 characters

Table 8.1RDB$GET_CONTEXT Function Parameters
ParameterDescription

namespace

Namespace

varname

Variable name. Case-sensitive. Maximum length is 80 characters

Result typeVARCHAR(255)

DescriptionRetrieves the value of a context variable from one of the namespaces SYSTEM, USER_SESSION and USER_TRANSACTION.

The namespacesThe USER_SESSION and USER_TRANSACTION namespaces are initially empty. The user can create and set variables in them with RDB$SET_CONTEXT() and retrieve them with RDB$GET_CONTEXT(). The SYSTEM namespace is read-only. It contains a number of predefined variables, shown below.

Context variables in the SYSTEM namespace
DB_NAME

Either the full path to the database or — if connecting via the path is disallowed — its alias.

NETWORK_PROTOCOL

The protocol used for the connection: 'TCPv4', 'WNET', 'XNET' or NULL.

CLIENT_ADDRESS

For TCPv4, this is the IP address. For XNET, the local process ID. For all other protocols this variable is NULL.

CLIENT_PID

Process ID of remote client application.

Added in 2.5.3.

CLIENT_PROCESS

Process name of remote client application.

Added in 2.5.3.

CURRENT_USER

Same as global Section 9.7, “CURRENT_USER variable.

CURRENT_ROLE

Same as global Section 9.3, “CURRENT_ROLE variable.

ISOLATION_LEVEL

The isolation level of the current transaction: 'READ COMMITTED', 'SNAPSHOT' or 'CONSISTENCY'.

LOCK_TIMEOUT

Lock timeout of the current transaction.

Added in 2.5.3.

READ_ONLY

Returns 'TRUE' if current transaction is read-only and 'FALSE' otherwise.

Added in 2.5.3.

SESSION_ID

Same as global Section 9.1, “CURRENT_CONNECTION variable.

TRANSACTION_ID

Same as global Section 9.6, “CURRENT_TRANSACTION variable.

ENGINE_VERSION

The Firebird engine (server) version. Added in 2.1.

Return values and error behaviourIf the polled variable exists in the given namespace, its value will be returned as a string of max. 255 characters. If the namespace doesn’t exist or if you try to access a non-existing variable in the SYSTEM namespace, an error is raised. If you request a non-existing variable in one of the other namespaces, NULL is returned. Both namespace and variable names must be given as single-quoted, case-sensitive, non-NULL strings.

Examples

  |select rdb$get_context('SYSTEM', 'DB_NAME') from rdb$database
  | 
  |New.UserAddr = rdb$get_context('SYSTEM', 'CLIENT_ADDRESS');
  | 
  |insert into MyTable (TestField)
  |  values (rdb$get_context('USER_SESSION', 'MyVar'))

See alsoSection 8.1.2, “RDB$SET_CONTEXT()

8.1.2RDB$SET_CONTEXT()

Note

RDB$SET_CONTEXT and its counterpart RDB$GET_CONTEXT are actually predeclared UDFs. They are listed here as internal functions because they are always present — the user doesn’t have to do anything to make them available.

Available inDSQL, PSQL * As a declared UDF it should be available in ESQL

Syntax

  |RDB$SET_CONTEXT ('<namespace>', <varname>, <value> | NULL)
  | 
  |<namespace> ::= USER_SESSION | USER_TRANSACTION
  |<varname>   ::= A case-sensitive quoted string of max. 80 characters
  |<value>     ::= A value of any type, as long as it's castable
  |                to a VARCHAR(255)

Table 8.2RDB$SET_CONTEXT Function Parameters
ParameterDescription

namespace

Namespace

varname

Variable name. Case-sensitive. Maximum length is 80 characters

value

Data of any type provided it can be cast to VARCHAR(255)

Result typeINTEGER

DescriptionCreates, sets or unsets a variable in one of the user-writable namespaces USER_SESSION and USER_TRANSACTION.

The namespacesThe USER_SESSION and USER_TRANSACTION namespaces are initially empty. The user can create and set variables in them with RDB$SET_CONTEXT() and retrieve them with RDB$GET_CONTEXT(). The USER_SESSION context is bound to the current connection. Variables in USER_TRANSACTION only exist in the transaction in which they have been set. When the transaction ends, the context and all the variables defined in it are destroyed.

Return values and error behaviourThe function returns 1 when the variable already existed before the call and 0 when it didn’t. To remove a variable from a context, set it to NULL. If the given namespace doesn’t exist, an error is raised. Both namespace and variable names must be entered as single-quoted, case-sensitive, non-NULL strings.

Examples

  |select rdb$set_context('USER_SESSION', 'MyVar', 493) from rdb$database
  | 
  |rdb$set_context('USER_SESSION', 'RecordsFound', RecCounter);
  | 
  |select rdb$set_context('USER_TRANSACTION', 'Savepoints', 'Yes')
  |  from rdb$database

Notes
  • The maximum number of variables in any single context is 1000.

  • All USER_TRANSACTION variables will survive a ROLLBACK RETAIN (see ROLLBACK Options) or ROLLBACK TO SAVEPOINT unaltered, no matter at which point during the transaction they were set.

  • Due to its UDF-like nature, RDB$SET_CONTEXT can — in PSQL only — be called like a void function, without assigning the result, as in the second example above. Regular internal functions don’t allow this type of use.

See alsoSection 8.1.1, “RDB$GET_CONTEXT()