Chapter 15Management Statements

Management statement are a class of SQL statements for administering aspects of the client/server environment, usually for the current session. Typically, such statements start with the verb SET.

Note

The isql tool also has a collection of SET commands. Those commands are not part of Firebird’s SQL lexicon. For information on isqls SET commands, see Isql Set Commands in Firebird Interactive SQL Utility.

Management statements can run anywhere DSQL can run, but typically, the developer will want to run a management statement in a database trigger. A subset of management statement can be used directly in PSQL modules without the need to wrap them in an EXECUTE STATEMENT block. For more details of the current set, see Management Statements in PSQL in the PSQL chapter.

Most of the management statements affect the current connection (attachment, or session) only, and do not require any authorization over and above the login privileges of the current user without elevated privileges.

Some management statements operate beyond the scope of the current session. Examples are the ALTER DATABASE {BEGIN | END} BACKUP statements to control the copy-safe mode, or the ALTER EXTERNAL CONNECTIONS POOL statements to manage connection pooling. A set of system privileges, analogous with SQL privileges granted for database objects, is provided to enable the required authority to run a specific management statement in this category.

Note

Some management statements use the verb ALTER, but those statements should not be confused with DDL ALTER statements that modify database objects like tables, views, procedures, roles, et al.

Although some ALTER DATABASE clauses (e.g. BEGIN BACKUP) can be considered as management statements, they are documented in the DDL chapter.

Unless explicitly mentioned otherwise in an Available in section, management statements are available in DSQL and PSQL. Availability in ESQL is not tracked by this Language Reference.

15.1Data Type Behaviour

15.1.1SET BIND (Data Type Coercion Rules)

Configures data type coercion rules for the current session

Syntax

   |SET BIND OF <type_from> TO <type_to>
   | 
   |<type_from> ::=
   |    <scalar_datatype>
   |  | <blob_datatype>
   |  | TIME ZONE
   |  | VARCHAR | {CHARACTER | CHAR} VARYING
   | 
   |<type_to> ::=
   |    <scalar_datatype>
   |  | <blob_datatype>
   |  | VARCHAR | {CHARACTER | CHAR} VARYING
   |  | LEGACY | NATIVE | EXTENDED
   |  | EXTENDED TIME WITH TIME ZONE
   |  | EXTENDED TIMESTAMP WITH TIME ZONE
   | 
   |<scalar_datatype> ::=
   |  !! See Scalar Data Types Syntax !!
   | 
   |<blob_datatype> ::=
   |  !! See BLOB Data Types Syntax !!

This statement makes it possible to substitute one data type with another when performing client-server interactions. In other words, type_from returned by the engine is represented as type_to in the client API.

Only fields returned by the database engine in regular messages are substituted according to these rules. Variables returned as an array slice are not affected by the SET BIND statement.

When a partial type definition is used (e.g. CHAR instead of CHAR(n)) in from_type, the coercion is performed for all CHAR columns. The special partial type TIME ZONE stands for TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE. When a partial type definition is used in to_type, the engine defines missing details about that type automatically based on source column.

Changing the binding of any NUMERIC or DECIMAL data type does not affect the underlying integer type. In contrast, changing the binding of an integer data type also affects appropriate NUMERIC and DECIMAL types. For example, SET BIND OF INT128 TO DOUBLE PRECISION will also map NUMERIC and DECIMAL with precision 19 or higher, as these types use INT128 as their underlying type.

The special type LEGACY is used when a data type, missing in previous Firebird version, should be represented in a way, understandable by old client software (possibly with data loss). The coercion rules applied in this case are shown in the table below.

Table 15.1Native to LEGACY coercion rules
Native data typeLegacy data type

BOOLEAN

CHAR(5)

DECFLOAT

DOUBLE PRECISION

INT128

BIGINT

TIME WITH TIME ZONE

TIME WITHOUT TIME ZONE

TIMESTAMP WITH TIME ZONE

TIMESTAMP WITHOUT TIME ZONE

Using EXTENDED for type_to causes the engine to coerce to an extended form of the type_from data type. Currently, this works only for TIME/TIMESTAMP WITH TIME ZONE, they are coerced to EXTENDED TIME/TIMESTAMP WITH TIME ZONE. The EXTENDED type contains both the time zone name, and the corresponding GMT offset, so it remains usable if the client application cannot process named time zones properly (e.g. due to the missing ICU library).

Setting a binding to NATIVE resets the existing coercion rule for this data type and returns it in its native format.

The initial bind rules of a connection be configured through the DPB by providing a semicolon separated list of <type_from> TO <type_to> options as the string value of isc_dpb_set_bind.

Execution of Section 15.7.1, “ALTER SESSION RESET will revert to the binding rules configured through the DPB, or otherwise the system default.

Tip

It is also possible to configure a default set of data type coercion rules for all clients through the DataTypeCompatibility configuration option, either as a global configuration in firebird.conf or per database in databases.conf.

DataTypeCompatibility currently has two possible values: 3.0 and 2.5. The 3.0 option maps data types introduced after Firebird 3.0 — in particular DECIMAL/NUMERIC with precision 19 or higher, INT128, DECFLOAT, and TIME/TIMESTAMP WITH TIME ZONE — to data types supported in Firebird 3.0. The 2.5 option also converts the BOOLEAN data type.

See the Table 15.1, “Native to LEGACY coercion rules” for details. This setting allows legacy client applications to work with Firebird 5.0 without recompiling or otherwise adjusting them to understand the new data types.

15.1.1.1SET BIND Examples

   |-- native
   |SELECT CAST('123.45' AS DECFLOAT(16)) FROM RDB$DATABASE;
   | 
   |                   CAST
   |=======================
   |                 123.45
   | 
   |-- double
   |SET BIND OF DECFLOAT TO DOUBLE PRECISION;
   |SELECT CAST('123.45' AS DECFLOAT(16)) FROM RDB$DATABASE;
   | 
   |                   CAST
   |=======================
   |      123.4500000000000
   | 
   |-- still double
   |SET BIND OF DECFLOAT(34) TO CHAR;
   |SELECT CAST('123.45' AS DECFLOAT(16)) FROM RDB$DATABASE;
   | 
   |                   CAST
   |=======================
   |      123.4500000000000
   | 
   |-- text
   |SELECT CAST('123.45' AS DECFLOAT(34)) FROM RDB$DATABASE;
   | 
   |CAST
   |==========================================
   |123.45

In the case of missing ICU on the client side:

   |SELECT CURRENT_TIMESTAMP FROM RDB$DATABASE;
   | 
   |                                        CURRENT_TIMESTAMP
   |=========================================================
   |2020-02-21 16:26:48.0230 GMT*
   | 
   |SET BIND OF TIME ZONE TO EXTENDED;
   |SELECT CURRENT_TIMESTAMP FROM RDB$DATABASE;
   | 
   |                                        CURRENT_TIMESTAMP
   |=========================================================
   |2020-02-21 19:26:55.6820 +03:00

15.1.2SET DECFLOAT

Configures DECFLOAT rounding and error behaviour for the current session

Syntax

   |SET DECFLOAT
   |  { ROUND <round_mode>
   |  | TRAPS TO [<trap_opt> [, <trap_opt> ...]] }
   | 
   |<round_mode> ::=
   |    CEILING | UP | HALF_UP | HALF_EVEN
   |  | HALF_DOWN | DOWN | FLOOR | REROUND
   | 
   |<trap_opt> ::=
   |    DIVISON_BY_ZERO | INEXACT | INVALID_OPERATION
   |  | OVERFLOW | UNDERFLOW

15.1.2.1SET DECFLOAT ROUND

SET DECFLOAT ROUND changes the rounding behaviour of operations on DECFLOAT. The default rounding mode is HALF_UP. The initial configuration of a connection can also be specified using the DPB tag isc_dpb_decfloat_round with the desired round_mode as string value.

The valid rounding modes are:

CEILING

towards +infinity

UP

away from 0

HALF_UP

to nearest, if equidistant, then up (default)

HALF_EVEN

to nearest, if equidistant, ensure last digit in the result will be even

HALF_DOWN

to nearest, if equidistant, then down

DOWN

towards 0

FLOOR

towards -infinity

REROUND

up if digit to be rounded is 0 or 5, down in other cases

The current value for the connection can be found using RDB$GET_CONTEXT('SYSTEM', 'DECFLOAT_ROUND').

Execution of Section 15.7.1, “ALTER SESSION RESET will revert to the value configured through the DPB, or otherwise the system default.

15.1.2.2SET DECFLOAT TRAPS

SET DECFLOAT TRAPS changes the error behaviour of operations on DECFLOAT. The default traps are DIVISION_BY_ZERO,INVALID_OPERATION,OVERFLOW; this default matches the behaviour specified in the SQL standard for DECFLOAT. This statement controls whether certain exceptional conditions result in an error (trap) or alternative handling (for example, an underflow returns 0 when not set, or an overflow returns an infinity). The initial configuration of a connection can also be specified using the DPB tag isc_dpb_decfloat_traps with the desired comma-separated trap_opt values as a string value.

Valid trap options (exceptional conditions) are:

Division_by_zero

(set by default)

Inexact

 — 

Invalid_operation

(set by default)

Overflow

(set by default)

Underflow

 — 

The current value for the connection can be found using RDB$GET_CONTEXT('SYSTEM', 'DECFLOAT_TRAPS').

Execution of Section 15.7.1, “ALTER SESSION RESET will revert to the value configured through the DPB, or otherwise the system default.