Used forConfiguring data type coercion rules

Available inDSQL, PSQL

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 !!

SET BIND configures data type coercion rules for the current session. 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.

When an incomplete type definition is used (i.e. simply CHAR instead of CHAR(n)) in from_type, the coercion is performed for all CHAR columns. The special incomplete type TIME ZONE stands for TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE. When an incomplete 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 NUMERICs/DECIMALs (e.g. 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 some data loss). The coercion rules applied in this case are shown in the table below.

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 semi-colon separated list of <type_from> TO <type_to> options as the string value of isc_dpb_set_bind.

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

   |-- 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

   |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

Used forConfiguring DECFLOAT rounding and error behaviour

Available inDSQL, PSQL

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

SET DECFLOAT configures the rounding and error behaviour of operations on DECFLOAT types in the current session.