8.12 Other Functions

Functions that don’t really fit in any other category.

8.12.1 MAKE_DBKEY()

Available inDSQL, PSQL

Result typeBINARY(8)

Syntax

MAKE_DBKEY (relation, recnum [, dpnum [, ppnum]])

Table 8.12.1.1 RDB$GET_TRANSACTION_CN Function Parameters

MAKE_DBKEY creates a DBKEY value using a relation name or ID, record number, and (optionally) logical numbers of data page and pointer page.

Note

1. If relation is a string expression or literal, then it is treated as a relation name, and the engine searches for the corresponding relation ID. The search is case-sensitive. In the case of string literal, relation ID is evaluated at query preparation time. In the case of expression, relation ID is evaluated at execution time. If the relation cannot be found, then error isc_relnotdef is raised.

2. If relation is a numeric expression or literal, then it is treated as a relation ID and used as is, without verification against existing relations. If the argument value is negative or greater than the maximum allowed relation ID (65535 currently), then NULL is returned.

3. Argument recnum represents an absolute record number in the relation (if the next arguments dpnum and ppnum are missing), or a record number relative to the first record, specified by the next arguments.

4. Argument dpnum is a logical number of data page in the relation (if the next argument ppnum is missing), or number of data pages relative to the first data page addressed by the given ppnum.

5. Argument ppnum is a logical number of pointer page in the relation.

6. All numbers are zero-based. Maximum allowed value for dpnum and ppnum is 232 (4294967296). If dpnum is specified, then recnum can be negative. If dpnum is missing and recnum is negative, then NULL is returned. If ppnum is specified, then dpnum can be negative. If ppnum is missing and dpnum is negative, then NULL is returned.

7. If any of specified arguments is NULL, the result is also NULL.

8. Argument relation is described as INTEGER during query preparation, but it can be overridden by a client application as VARCHAR or CHAR. Arguments recnum, dpnum and ppnum are described as BIGINT.

8.12.1.1 Examples of MAKE_DBKEY

1. Select record using relation name (note that relation name is uppercase)

   select *
   from rdb$relations
   where rdb$db_key = make_dbkey('RDB$RELATIONS', 0)

2. Select record using relation ID

   select *
   from rdb$relations
   where rdb$db_key = make_dbkey(6, 0)

3. Select all records physically residing on the first data page

   select *
   from rdb$relations
   where rdb$db_key >= make_dbkey(6, 0, 0)
   and rdb$db_key < make_dbkey(6, 0, 1)

4. Select all records physically residing on the first data page of 6th pointer page

   select *
   from SOMETABLE
   where rdb$db_key >= make_dbkey('SOMETABLE', 0, 0, 5)
   and rdb$db_key < make_dbkey('SOMETABLE', 0, 1, 5)

8.12.2 RDB$ERROR()

Available inPSQL

Result typeVaries (see table below)

Syntax

RDB$ERROR (<context>)
 
<context> ::=
  GDSCODE | SQLCODE | SQLSTATE | EXCEPTION | MESSAGE

Table 8.12.2.1 Contexts

RDB$ERROR returns data of the specified context about the active PSQL exception. Its scope is confined to exception-handling blocks in PSQL (WHEN …​ DO). Outside the exception handling blocks, RDB$ERROR always returns NULL. This function cannot be called from DSQL.

8.12.2.1 Example of RDB$ERROR

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

See alsoTrapping and Handling Errors, GDSCODE, SQLCODE, SQLSTATE

8.12.3 RDB$GET_TRANSACTION_CN()

Available inDSQL, PSQL

Result typeBIGINT

Syntax

RDB$GET_TRANSACTION_CN (transaction_id)

Table 8.12.3.1 RDB$GET_TRANSACTION_CN Function Parameters

RDB$GET_TRANSACTION_CN returns the commit number (CN) of the supplied transaction.

If the return value is greater than 1, it is the actual CN of the transaction if it was committed after the database was started.

The function can also return one of the following results, indicating the commit status of the transaction:

Note

For more information about CN, consult the Firebird 4.0 Release Notes.

8.12.3.1 RDB$GET_TRANSACTION_CN Examples

select rdb$get_transaction_cn(current_transaction) from rdb$database;
select rdb$get_transaction_cn(123) from rdb$database;

8.12.4 RDB$ROLE_IN_USE()

Available inDSQL, PSQL

Result typeBOOLEAN

Syntax

RDB$ROLE_IN_USE (role_name)

Table 8.12.4.1 RDB$ROLE_IN_USE Function Parameters

RDB$ROLE_IN_USE returns TRUE if the specified role is active for the current connection, and FALSE otherwise. Contrary to CURRENT_ROLE —  which only returns the explicitly specified role — this function can be used to check for roles that are active by default, or cumulative roles activated by an explicitly specified role.

8.12.4.1 RDB$ROLE_IN_USE Examples

List currently active roles

select rdb$role_name
from rdb$database
where rdb$role_in_use(rdb$role_name);

See alsoCURRENT_ROLE

8.12.5 RDB$SYSTEM_PRIVILEGE()

Available inDSQL, PSQL

Result typeBOOLEAN

Syntax

RDB$SYSTEM_PRIVILEGE (<sys_privilege>)
 
<sys_privilege> ::=
  !! See CREATE ROLE !!

Table 8.12.5.1 RDB$SYSTEM_PRIVILEGE Function Parameters

RDB$SYSTEM_PRIVILEGE accepts a system privilege name and returns TRUE if the current attachment has the given system privilege, and FALSE otherwise.

8.12.5.1 RDB$SYSTEM_PRIVILEGE Examples

select rdb$system_privilege(user_management) from rdb$database;

See alsoFine-grained System Privileges