Input and output parameters or local variables can also be declared using the data type of columns in existing tables and views.The TYPE OF COLUMN clause is used for that, specifying relationname.columnname as its argument.

When TYPE OF COLUMN is used, the parameter or variable inherits only the data type and — for string types — the character set and collation.The constraints and default value of the column are ignored.

The BLOB data types hold binary, character or custom format data of unspecified size.For more information, see [fblangref50-datatypes-bnrytypes].

If the SUB_TYPE and CHARACTER SET clauses are absent, then subtype BINARY (or 0) is used.If the SUB_TYPE clause is absent and the CHARACTER SET clause is present, then subtype TEXT (or 1) is used.

The array data types hold multiple scalar values in a single or multi-dimensional array.For more information, see [fblangref50-datatypes-array]

Approximate floating-point values are stored in an IEEE 754 binary format that comprises sign, exponent and mantissa.Precision is dynamic, corresponding to the physical storage format of the value, which is exactly 4 bytes for the FLOAT type and 8 bytes for DOUBLE PRECISION.

Considering the peculiarities of storing floating-point numbers in a database, these data types are not recommended for storing monetary data.For the same reasons, columns with floating-point data are not recommended for use as keys or to have uniqueness constraints applied to them.

For testing data in columns with floating-point data types, expressions should check using a range, for instance, BETWEEN, rather than searching for exact matches.

When using these data types in expressions, extreme care is advised regarding the rounding of evaluation results.

The FLOAT data type defaults to a 32-bit single precision floating-point type with an approximate precision of 7 decimal digits after the decimal point (24 binary digits).To ensure the safety of storage, rely on 6 decimal digits of precision.

The syntax FLOAT(bin_prec) behaves as follows:

The behaviour of FLOAT (without explicit precision) behaves as the SQL standard type [fblangref50-datatypes-real].

The data type REAL is a synonym for FLOAT, and is provided for syntax compatibility.When used to define a column or parameter, it’s indistinguishable from using FLOAT or FLOAT(1) — FLOAT(24).

The DOUBLE PRECISION data type is stored with an approximate precision of 15 digits.

Decimal floating-point values are stored in an IEEE 754 decimal format that comprises sign, exponent and coefficient.Contrary to the approximate floating-point data types, precision is either 16 or 34 decimal digits.

DECFLOAT is a SQL standard-compliant numeric type that stores floating-point number precisely (decimal floating-point type), unlike FLOAT or DOUBLE PRECISION that provide a binary approximation of the purported precision.

The type is stored and transmitted as IEEE 754 standard types Decimal64 (DECFLOAT(16)) or Decimal128 (DECFLOAT(34)).

All intermediate calculations are performed with 34-digit values.

Observe that although the smallest exponent for DECFLOAT(16) is -383, the smallest value has an exponent of -398, but 15 fewer digits.And similar for DECFLOAT(34), smallest exponent is -6143, but the smallest value has an exponent of -6176, but 33 fewer digits.The reason is that precision was “sacrificed” to be able to store a smaller value.

This is a result of how the value is stored: as a decimal value of 16 or 34 digits and an exponent.For example, 1.234567890123456e-383 is stored as coefficient 1234567890123456 and exponent -398, while 1E-398 is stored as coefficient 1, exponent -398.

The behaviour of DECFLOAT operations in a session, specifically rounding and error behaviour, can be configured using the SET DECFLOAT management statement, and the isc_dpb_decfloat_round and isc_dpb_decfloat_traps DPB items.

It is possible to express DECFLOAT(34) values in approximate numeric literals, but only for values with a mantissa of 20 or more digits, or an absolute exponent larger than 308.Scientific notation literals with fewer digits or a smaller absolute exponent are DOUBLE PRECISION literals.Exact numeric literals with 40 or more digits — actually 39 digits, when larger than the maximum INT128 value — are also handled as DECFLOAT(34).

Alternatively, use a string literal and explicitly cast to the desired DECFLOAT type.

The length of DECFLOAT literals cannot exceed 1024 characters.Scientific notation is required for greater values.For example, 0.0<1020 zeroes>11 cannot be used as a literal, but the equivalent in scientific notation, 1.1E-1022, is valid.Similarly, 10<1022 zeroes>0 can be presented as 1.0E1024.Literals with more than 34 significant digits are rounded using the DECFLOAT rounding mode of the session.

A number of standard scalar functions can be used with expressions and values of the DECFLOAT type.They are:

The aggregate functions SUM, AVG, MAX and MIN work with DECFLOAT data, as do all the statistical aggregates (including but not limited to STDDEV or CORR).

Firebird supports four functions, designed to support DECFLOAT data specifically:

Detailed descriptions are available in the Special Functions for DECFLOAT section of the Built-in Scalar Functions chapter.

Further to the explanation above, Firebird will store NUMERIC data according the declared precision and scale.Some more examples are:

The storage format in the database for DECIMAL is similar to NUMERIC, with some differences that are easier to observe with the help of some more examples:

The DATE data type in Dialect 3 stores only date without time.The available range for storing data is from January 01, 1 to December 31, 9999.

In Dialect 1, DATE is an alias for [fblangref50-datatypes-timestamp].Dialect 1 has no “date-only” type.

For a bare TIME, WITHOUT TIME ZONE is assumed.

The TIME data type is available in Dialect 3 only.It stores the time of day within the range from 00:00:00.0000 to 23:59:59.9999.

If you need to get the time-part from DATE in Dialect 1, you can use the EXTRACT function.

See also the EXTRACT() function in the chapter entitled Built-in Scalar Functions.

The TIME (or synonym TIME WITHOUT TIME ZONE) represents a time without time zone information.

The TIME WITH TIME ZONE represents a time with time zone information (either an offset or a named zone).

Firebird uses the ICU implementation of the IANA Time Zone Database for named zones.

For a bare TIMESTAMP, WITHOUT TIME ZONE is assumed.

The TIMESTAMP data type is available in Dialect 3 and Dialect 1.It comprises two 32-bit integers — a date-part and a time-part — to form a structure that stores both date and time-of-day.In Dialect 1, [fblangref50-datatypes-date] is an alias for TIMESTAMP.

The EXTRACT function works equally well with TIMESTAMP as with the Dialect 1 DATE type.

The TIMESTAMP (or synonym TIMESTAMP WITHOUT TIME ZONE) represents a time and date without time zone information.

The TIMESTAMP WITH TIME ZONE represents a time with time zone information (either an offset or a named zone).

As the name implies, the session time zone can be different for each database attachment.It can be set explicitly in the DPB with the item isc_dpb_session_time_zone;otherwise, by default, it uses the same time zone as the operating system of the Firebird server process.This default can be overridden in firebird.conf, setting DefaultTimeZone.

Subsequently, the time zone can be changed to a given time zone using a SET TIME ZONE statement or reset to its original value with SET TIME ZONE LOCAL or ALTER SESSION RESET.

A time zone is specified as a string, either a time zone region (for example, 'America/Sao_Paulo') or a displacement from GMT in hours:minutes (for example, '-03:00').

A time/timestamp with time zone is considered equal to another time/timestamp with time zone if their conversions to UTC are equivalent.For example, time '10:00 -02:00' and time '09:00 -03:00' are equivalent, since both are the same as time '12:00 GMT'.

The method of storing date and time values makes it possible to involve them as operands in some arithmetic operations.In storage, a date value or date-part of a timestamp is represented as the number of days elapsed since “date zero” — November 17, 1858 — whilst a time value or the time-part of a timestamp is represented as the number of deci-milliseconds (100 microseconds) since midnight.

An example is to subtract an earlier date, time or timestamp from a later one, resulting in an interval of time, in days and fractions of days.

DATEADD, DATEDIFF

Firebird provides a number of features to discover time zone information.

A virtual table listing time zones supported in the engine.

See also RDB$TIME_ZONES in System Tables.

A package of time zone utility functions and procedures.

See also RDB$TIME_ZONE_UTIL in System Packages.

Time zones are often changed: of course, when it happens, it is desirable to update the time zone database as soon as possible.

Firebird stores WITH TIME ZONE values translated to UTC time.Suppose a value is created with one time zone database, and a later update of that database changes the information in the range of our stored value.When that value is read, it will be returned as different to the value that was stored initially.

Firebird uses the IANA time zone database through the ICU library.The ICU library included in the Firebird kit (Windows), or installed in a POSIX operating system, can sometimes have an outdated time zone database.

An updated database can be found on this page on the FirebirdSQL GitHub.Filename le.zip stands for little-endian and is the necessary file for most computer architectures (Intel/AMD compatible x86 or x64), while be.zip stands for big-endian architectures and is necessary mostly for RISC computer architectures.The content of the zip file must be extracted in the /tzdata sub-directory of the Firebird installation, overwriting existing *.res files belonging to the database.

Most current development tools support Unicode, implemented in Firebird with the character sets UTF8 and UNICODE_FSS.UTF8 comes with collations for many languages.UNICODE_FSS is more limited and was previously used mainly by Firebird internally for storing metadata.Keep in mind that one UTF8 character occupies up to 4 bytes, thus limiting the size of CHAR fields to 8,191 characters (32,767/4).

The UTF8 character set implemented in Firebird supports the latest version of the Unicode standard, thus recommending its use for international databases.

While working with strings, it is essential to keep the character set of the client connection in mind.If there is a mismatch between the character sets of the stored data and that of the client connection, the output results for string columns are automatically re-encoded, both when data are sent from the client to the server and when they are sent back from the server to the client.For example, if the database was created in the WIN1251 encoding but KOI8R or UTF8 is specified in the client’s connection parameters, the mismatch will be transparent.

The character set NONE is a special character set in Firebird.It can be characterized such that each byte is a part of a string, but the string is stored in the system without any clues about what constitutes any character: character encoding, collation, case, etc. are simply unknown.It is the responsibility of the client application to deal with the data and provide the means to interpret the string of bytes in some way that is meaningful to the application and the human user.

Data in OCTETS encoding are treated as bytes that may not be interpreted as characters.OCTETS provides a way to store binary data.The database engine has no concept of what it is meant to do with a string of bytes in OCTETS, other than store and retrieve it.Again, the client side is responsible for validating the data, presenting them in formats that are meaningful to the application and its users and handling any exceptions arising from decoding and encoding them.CHAR and VARCHAR with character set OCTETS have synonyms BINARY and VARBINARY.

Each character set has a default collation (COLLATE) that specifies the collation order (or, collation sequence, or collating sequence).Usually, it provides nothing more than ordering based on the numeric code of the characters and a basic mapping of upper- and lower-case characters.If some behaviour is needed for strings that is not provided by the default collation and a suitable alternative collation is supported for that character set, a COLLATE collation clause can be specified in the column definition.

A COLLATE collation clause can be applied in other contexts besides the column definition.For comparison operations, it can be added in the WHERE clause of a SELECT statement.If output needs to be sorted in a special alphabetic sequence, or case-insensitively, and the appropriate collation exists, then a COLLATE clause can be included with the ORDER BY clause when rows are being sorted on a character field and with the GROUP BY clause in case of grouping operations.

For a case-insensitive search, the UPPER function could be used to convert both the search argument and the searched strings to upper-case before attempting a match:

For strings in a character set that has a case-insensitive collation available, you can apply the collation, to compare the search argument and the searched strings directly.For example, using the WIN1251 character set, the collation PXW_CYRL is case-insensitive for this purpose:

CONTAINING

The following table shows the possible collations for the UTF8 character set.

An example of collation for the UTF8 character set without taking into account the case or accentuation of characters (similar to COLLATE PXW_CYRL in the earlier example).

The maximum length for an index key equals one quarter of the page size, i.e. from 1,024 — for page size 4,096 — to 8,192 bytes — for page size 32,768.The maximum length of an indexed string is 9 bytes less than that quarter-page limit.

The table below shows the maximum length of an indexed string (in characters), according to page size and character set, calculated using this formula.

CREATE DATABASE, Collation, SELECT, WHERE, GROUP BY, ORDER BY

BINARY is a fixed-length binary data type, and is an SQL standard-compliant alias for CHAR(length) CHARACTER SET OCTETS.Values shorter than the declared length are padded with NUL (0x00) up to the declared length.

[fblangref50-datatypes-chartypes-char], [fblangref50-datatypes-chartypes-varbinary]

CHAR is a fixed-length character data type.Values shorter than the declared length are padded with spaces up to the declared length.The pad character does not have to be a space (0x20): it depends on the character set.For example, the pad character for the OCTETS character set is NUL (0x00).

Fixed-length character data can be used to store codes whose length is standard and has a definite “width”.An example of such a code is an EAN13 barcode — 13 characters, all filled.

[fblangref50-datatypes-chartypes-binary], [fblangref50-datatypes-chartypes-varchar]

VARBINARY is a variable-length binary type, and is an SQL standard-compliant alias for VARCHAR(length) CHARACTER SET OCTETS.

[fblangref50-datatypes-chartypes-varchar], [fblangref50-datatypes-chartypes-binary]

VARCHAR is a variable-length character data type, up to a maximum of 32,765 bytes.The stored structure is equal to the actual size of the data plus 2 bytes to record the length of the data.

All characters that are sent from the client application to the database are considered meaningful, including leading and trailing spaces.

[fblangref50-datatypes-chartypes-varbinary], [fblangref50-datatypes-chartypes-char]

NCHAR is a fixed-length character data type with the ISO8859_1 character set.In all other respects it is the same as CHAR.

A similar data type is available for the variable-length string type: NATIONAL {CHAR | CHARACTER} VARYING.

[fblangref50-datatypes-chartypes-char], [fblangref50-datatypes-chartypes-varchar]

The SQL-compliant BOOLEAN data type (8 bits) comprises the distinct truth values TRUE and FALSE.Unless prohibited by a NOT NULL constraint, the BOOLEAN data type also supports the truth value UNKNOWN as the null value.The specification does not make a distinction between the NULL value of this data type, and the truth value UNKNOWN that is the result of an SQL predicate, search condition, or Boolean value expression: they may be used interchangeably to mean the same thing.

As with many programming languages, the SQL BOOLEAN values can be tested with implicit truth values.For example, field1 OR field2 and NOT field1 are valid expressions.

Predicates can use the operator Boolean IS [NOT] for matching.For example, field1 IS FALSE, or field1 IS NOT TRUE.

Although BOOLEAN is not inherently convertible to any other data type, the strings 'true' and 'false' (case-insensitive) will be implicitly cast to BOOLEAN in value expressions.For example:

The value 'false' is converted to BOOLEAN.Any attempt to use the Boolean operators AND, NOT, OR and IS will fail.NOT 'False', for example, is invalid.

A BOOLEAN can be explicitly converted to and from string with CAST.UNKNOWN is not available for any form of casting.

The optional SUB_TYPE parameter specifies the nature of data written to the column.Firebird provides two pre-defined subtypes for storing user data:

It is also possible to add custom data subtypes, for which the range of enumeration from -1 to -32,768 is reserved.Custom subtypes enumerated with positive numbers are not allowed, as the Firebird engine uses the numbers from 2-upward for some internal subtypes in metadata.Custom subtype aliases can be inserted into the RDB$TYPES table by users with the system privilege CREATE_USER_TYPES.

The maximum size of a BLOB field depends on the page size of the database, whether the blob value is created as a stream blob or a segmented blob, and if segmented, the actual segment sizes used when populating the blob.For most built-in functions, the maximum size of a BLOB field is 4 GB, or data beyond the 4 GB limit is not addressable.For a page size of 4 KB (4096 bytes) the maximum size is slightly less than 4 GB.

Text BLOBs of any length and any character set — including multi-byte — can be operands for practically any statement or internal functions.The following operators are fully supported:

As an efficient alternative to concatenation, you can also use BLOB_APPEND() or the functions and procedures of system package RDB$BLOB_UTIL.

Partial support:

FILTER, DECLARE FILTER, BLOB_APPEND(), RDB$BLOB_UTIL

By default, dimensions are 1-based — subscripts are numbered from 1.To specify explicit upper and lower bounds of the subscript values, use the following syntax:

A new dimension is added using a comma in the syntax.In this example we create a table with a two-dimensional array, with the lower bound of subscripts in both dimensions starting from zero:

The database employee.fdb, found in the ../examples/empbuild directory of any Firebird distribution package, contains a sample stored procedure showing some simple work with arrays:

If the features described are enough for your tasks, you might consider using arrays in your projects.Currently, no improvements are planned to enhance support for arrays in Firebird.

The SQL_NULL type holds no data, but only a state: NULL or NOT NULL.It is not available as a data type for declaring table fields, PSQL variables or parameter descriptions.This data type exists to support the use of untyped parameters in expressions involving the IS NULL predicate.

An evaluation problem occurs when optional filters are used to write queries of the following type:

After processing, at the API level, the query will look like this:

This is a case where the developer writes an SQL query and considers :param1 as though it were a variable that they can refer to twice.However, at the API level, the query contains two separate and independent parameters.The server cannot determine the type of the second parameter since it comes in association with IS NULL.

The SQL_NULL data type solves this problem.Whenever the engine encounters an “? IS NULL” predicate in a query, it assigns the SQL_NULL type to the parameter, which will indicate that parameter is only about “nullness” and the data type or the value need not be addressed.

The following example demonstrates its use in practice.It assumes two named parameters — say, :size and :colour — which might, for example, get values from on-screen text fields or drop-down lists.Each named parameter corresponds with two positional parameters in the query.

Explaining what happens here assumes the reader is familiar with the Firebird API and the passing of parameters in XSQLVAR structures — what happens under the surface will not be of interest to those who are not writing drivers or applications that communicate using the “naked” API.

The application passes the parameterized query to the server in the usual positional ?-form.Pairs of “identical” parameters cannot be merged into one, so for the two optional filters in the example, four positional parameters are needed: one for each ? in our example.

After the call to isc_dsql_describe_bind(), the SQLTYPE of the second and fourth parameters will be set to SQL_NULL.Firebird has no knowledge of their special relation with the first and third parameters: that responsibility lies entirely on the application side.

Once the values for size and colour have been set (or left unset) by the user, and the query is about to be executed, each pair of XSQLVARs must be filled as follows:

In other words: The value compare parameter is always set as usual.The SQL_NULL parameter is set the same, except that sqldata remains null at all times.