Databases created or restored under Firebird 2 have an on-disk structure (ODS) of 11 or higher.

(v.2.1) Newly implemented “database triggers” are user-defined PSQL modules that can be designed to fire in various connection-level and transaction- level events. See Database Triggers.

(v.2.1) The remote protocol has been slightly improved to perform better in slow networks once drivers are updated to utilise the changes. Testing showed that API round trips were reduced by about 50 percent, resulting in about 40 per cent fewer TCP round trips. See Remote Interface Improvement.

Implemented support for derived tables in DSQL (subqueries in FROM clause) as defined by SQL200X. A derived table is a set, derived from a dynamic SELECT statement. Derived tables can be nested, if required, to build complex queries and they can be involved in joins as though they were normal tables or views.

More details under Derived Tables in the DML chapter.

Multiple named (i.e. explicit) cursors are now supported in PSQL and in DSQL EXECUTE BLOCK statements. More information in the PSQL chapter Explicit Cursors.

Two significant changes have been made to the Windows-only protocols.-

Since Firebird 1.0 and earlier, the Superserver engine has performed background garbage collection, maintaining information about each new record version produced by an UPDATE or DELETE statement. As soon as the old versions are no longer “interesting”, i.e. when they become older than the Oldest Snapshot transaction (seen in the gstat -header output) the engine signals for them to be removed by the garbage collector.

Background GC eliminates the need to re-read the pages containing these versions via a SELECT COUNT(*) FROM aTable or other table-scanning query from a user, as occurs in Classic and in versions of InterBase prior to v.6.0. This earlier GC mechanism is known as cooperative garbage collection.

Background GC also averts the possibility that those pages will be missed because they are seldom read. (A sweep, of course, would find those unused record versions and clear them, but the next sweep is not necessarily going to happen soon.) A further benefit is the reduction in I/O, because of the higher probability that subsequently requested pages still reside in the buffer cache.

Between the point where the engine notifies the garbage collector about a page containing unused versions and the point when the garbage collector gets around to reading that page, a new transaction could update a record on it. The garbage collector cannot clean up this record if this later transaction number is higher than the Oldest Snapshot or is still active. The engine again notifies the garbage collector about this page number, overriding the earlier notification about it and the garbage will be cleaned at some later time.

In Firebird 2.0 Superserver, both cooperative and background garbage collection are now possible. To manage it, the new configuration parameter GCPolicy was introduced. It can be set to:

Porting of the Services API to Classic architecture is now complete. All Services API functions are now available on both Linux and Windows Classic servers, with no limitations. Known issues with gsec error reporting in previous versions of Firebird are eliminated.

All Firebird versions provide two transaction wait modes: NO WAIT and WAIT. NO WAIT mode means that lock conflicts and deadlocks are reported immediately, while WAIT performs a blocking wait which times out only when the conflicting concurrent transaction ends by being committed or rolled back.

The new feature extends the WAIT mode by making provision to set a finite time interval to wait for the concurrent transactions. If the timeout has passed, an error (isc_lock_timeout) is reported.

Timeout intervals are specified per transaction, using the new TPB constant isc_tpb_lock_timeout in the API or, in DSQL, the LOCK TIMEOUT <value> clause of the SET TRANSACTION statement.

A reworking has been done to resolve problems with views that are implicitly updatable, but still have update triggers. This is an important change that will affect systems written to take advantage of the undocumented [mis]behaviour in previous versions.

For details, see the notes in the Compatibility chapter of the separate Installation Notes document.

Single-user and full shutdown modes are implemented using new [state] parameters for the gfix -shut and gfix -online commands.

Syntax Pattern

    gfix <command> [<state>] [<options>]
    <command>> ::= {-shut | -online}
    <state> ::= {normal | multi | single | full}
    <options> ::= {-force <timeout> | -tran | -attach}
    

For more details, refer to the section on Gfix New Shutdown Modes, in the Utilities chapter.

For a list of shutdown state flag symbols and an example of usage, see Shutdown State in the API.

Compile-time checking for concatenation overflow has been replaced by run-time checking.

From Firebird 1.0 onward, concatenation operations have been checked for the possibility that the resulting string might exceed the string length limit of 32,000 bytes, i.e. overflow. This check was performed during the statement prepare, using the declared operand sizes and would throw an error for an expressions such as:

  CAST('qwe' AS VARCHAR(30000)) || CAST('rty' AS VARCHAR(30000))
    

From Firebird 2.0 onward, this expression throws only a warning at prepare time and the overflow check is repeated at runtime, using the sizes of the actual operands. The result is that our example will be executed without errors being thrown. The isc_concat_overflow exception is now thrown only for actual overflows, thus bringing the behaviour of overflow detection for concatenation into line with that for arithmetic operations.

Firebird 2.0 will support 64-bit platforms.

40-bit (64-bit internally) record enumerators have been introduced to overcome the ~30GB table size limit imposed by 32-bit record enumeration.

Posix SS builds now handle SIGTERM and SIGINT to shutdown all connections gracefully. (A. Peshkov)

Invariant tracking in PSQL and request cloning logic were reworked to fix a number of issues with recursive procedures, for example SF bug #627057.

Invariant tracking is the process performed by the BLR compiler and the optimizer to decide whether an "invariant" (an expression, which might be a nested subquery) is independent from the parent context. It is used to perform one-time evaluations of such expressions and then cache the result.

If some invariant is not determined, we lose in performance. If some variant is wrongly treated as invariant, we see wrong results.

Example

  select * from rdb$relations
    where rdb$relation_id <
      ( select rdb$relation_id from rdb$database )
    

This query performs only one fetch from rdb$database instead of evaluating the subquery for every row of rdb$relations.

Firebird 2.0 adds an optional RETAIN clause to the DSQL ROLLBACK statement to make it consistent with COMMIT [RETAIN].

See ROLLBACK RETAIN Syntax in the chapter about DML.

The root directory lookup path has changed so that server processes on Windows no longer use the Registry.

Better cost-based calculation has been included in the optimizer routines.

From v.2.1.2 onward
Several DPB parameters have been made inaccessible to ordinary users, closing some dangerous loopholes. In some cases, they are settings that would alter the database header settings and potentially cause corruptions if not performed under administrator control; in others, they initiate operations that are otherwise restricted to the SYSDBA. They are.-

The parameter isc_dpb_set_page_buffers can still be used by ordinary users on Classic and it will set the buffer size temporarily for that user and that session only. When used by the SYSDBA on either Superserver or Classic, it will change the buffer count in the database header, i.e., make a permanent change to the default buffer size.

The API header file, ibase.h has been subjected to a cleanup. with the result that public headers no longer contain private declarations.

The new feature extends the WAIT mode by making provision to set a finite time interval to wait for the concurrent transactions. If the timeout has passed, an error (isc_lock_timeout) is reported.

Timeout intervals can now be specified per transaction, using the new TPB constant isc_tpb_lock_timeout in the API.

The function call isc_dsql_sql_info() has been extended to enable relation aliases to be retrieved, if required.

isc_blob_lookup_desc() now also describes blobs that are outputs of stored procedures

The macro definition FB_API_VER is added to ibase.h to indicate the current API version. The number corresponds to the appropriate Firebird version.

The current value of FB_API_VER is 20 (two-digit equivalent of 2.0). This macro can be used by client applications to check the version of ibase.h its being compiled with.

The following items have been added to the isc_database_info() function call structure:

The following items have been added to the isc_transaction_info() function call structure:

isc_info_tra_isolation,
  1, isc_info_tra_consistency | isc_info_tra_concurrency |
  2, isc_info_tra_read_committed,
     isc_info_tra_no_rec_version | isc_info_tra_rec_version
          

isc_info_tra_access, 1, isc_info_tra_readonly | isc_info_tra_readwrite
        

The following improvements have been added to the Services API:

The new function  fb_interpret()  replaces the former isc_interprete() for extracting the text for a Firebird error message from the error status vector to a client buffer.

API Access to database shutdown is through flags appended to the isc_dpb_shutdown parameter in the DBP argument passed to isc_attach_database(). The symbols for the <state> flags are:

#define isc_dpb_shut_cache               0x1
#define isc_dpb_shut_attachment          0x2
#define isc_dpb_shut_transaction         0x4
#define isc_dpb_shut_force               0x8
#define isc_dpb_shut_mode_mask          0x70

#define isc_dpb_shut_default             0x0
#define isc_dpb_shut_normal             0x10
#define isc_dpb_shut_multi              0x20
#define isc_dpb_shut_single             0x30
#define isc_dpb_shut_full               0x40
        

Example of Use in C/C++

char dpb_buffer[256], *dpb, *p;
ISC_STATUS status_vector[ISC_STATUS_LENGTH];
isc_db_handle handle = NULL;

dpb = dpb_buffer;

*dpb++ = isc_dpb_version1;

const char* user_name = “SYSDBA”;
const int user_name_length = strlen(user_name);
*dpb++ = isc_dpb_user_name;
*dpb++ = user_name_length;
memcpy(dpb, user_name, user_name_length);
dpb += user_name_length;

const char* user_password = “masterkey”;
const int user_password_length = strlen(user_password);
*dpb++ = isc_dpb_password;
*dpb++ = user_password_length;
memcpy(dpb, user_password, user_password_length);
dpb += user_password_length;

// Force an immediate full database shutdown
*dpb++ = isc_dpb_shutdown;
*dpb++ = 1;   
*dpb++ = isc_dpb_shut_force | isc_dpb_shut_full;

const int dpb_length = dpb - dpb_buffer;

isc_attach_database(status_vector,
                    0, “employee.db”,
                    &handle,
                    dpb_length, dpb_buffer);

if (status_vector[0] == 1 && status_vector[1])
{
  isc_print_status(status_vector);
}
else
{
  isc_detach_database(status_vector, &handle);
}
        

Firebird 2.0 creates databases with an ODS (On-Disk Structure) version of 11.

Maximum size of exception messages raised from 78 to 1021 bytes.

Added RDB$DESCRIPTION to RDB$GENERATORS, so now you can include description text when creating generators.

Added RDB$DESCRIPTION and RDB$SYSTEM_FLAG to RDB$ROLES to allow description text and to flag user-defined roles, respectively.

Introduced a concept of ODS type to distinguish between InterBase and Firebird databases.

The DSQL parser will now try to report the line and column number of an incomplete statement.

A new column RDB$STATISTICS has been added to the system table RDB$INDEX_SEGMENTS to store the per-segment selectivity values for multi-key indexes.

Speed tests on Linux showed that setting O_SYNC on a file has no effect at all on performance! Fine, fast operating system we may think? Alas, no, it's a documented bug in the Linux kernel!

According to the Linux manual, “On Linux this command (i.e. fcntl(fd, F_SETFL, flags)) can only change the O_APPEND, O_ASYNC, O_DIRECT, O_NOATIME, and O_NONBLOCK flags”. Though it is not documented in any place known to me, it turns out that an attempt to set any flag other than those listed in the manual (such as O_SYNC, for example) won't work but it does not cause fcntl() to return an error, either.

For Firebird and for InterBase versions since Day One, it means that Forced Writes has never worked on Linux. It certainly works on Windows. It seems likely that this is not a problem that affects other operating systems, although we cannot guarantee that. To make sure, you can check whether the implementation of fcntl() on your OS is capable of setting the O_SYNC flag.

The technique used currently, introduced in the Beta 2 release of Firebird 2.1, is to re-open the file. It should guarantee correct operation on any OS, provided the open() system call works correctly in this respect. It appears that no such problems are reported.

The Firebird developers have no idea why such a bug would remain unfixed almost two years after getting into the Linux kernel's bug-tracker. Apparently, in Linux, a documented bug evolves into a feature...

Here's a tip if you want to do an instant fix for the problem in an older version of Firebird: use the “sync” option when mounting any partition with a Firebird database on board. An example of a line in /etc/fstab:

Moving your database to a raw device can be as simple as restoring a backup directly to an unformatted partition in the local storage system. For example,

 gbak -c my.fbk /dev/sda7
      

will restore your database on the third logical disk in the extended partition of your first SCSI or SATA hard-drive (disk0).

The physical backup utility nbackup must be supplied with an explicit file path and name for its difference file, in order to avoid this file being written into the /dev/ directory. You can achieve this with the following statement, using isql:

# isql /dev/sda7
SQL> alter database add difference file '/tmp/dev_sda7';
      

# nbackup -s -l /dev/sda7
77173
        

Although no other specific issues are known at this point about the use of raw device storage for databases, keep in mind that

The identifier of the connection character set or, when the connection character set is NONE, the BLOB character set, is now passed in the XSQLVAR::sqlscale item of text BLOBs.

Feature request CORE-1069

An optimization was done for index scanning when more than one index is to be scanned with AND conjunctions.

Feature request CORE-1070

Optimization was done for sparse bitmap operations (set, test and clear) when values are mostly consecutive.

Feature requests CORE-958 and CORE-937

Feature request CORE-969

Page sizes of 1K and 2K are deprecated as inefficient.

Feature request CORE-1229

Until v.2.1, Firebird had no special rules about allocating disk space for database file pages. Because of dependencies between pages that it maintains itself, to service its “careful write” strategy, it has just written to newly-allocated pages in indeterminate order.

For databases using ODS 11.1 and higher, Firebird servers from v.2.1 onward use a different algorithm for allocating disk space, to address two recognised problems associated with the existing approach:

Feature requests CORE-1381 and CORE-1480

Firebird uses and maintains its own cache in memory for page buffers. The operating system, in turn, may re-cache Firebird's cache in its own filesystem cache. If Firebird is configured to use a cache that is large relative to the available RAM and Forced Writes is on, this cache duplication drains resources for little or no benefit.

Often, when the operating system tries to cache a big file, it moves the Firebird page cache to the swap, causing intensive, unnecessary paging. In practice, if the Firebird page cache size for Superserver is set to more than 80 per cent of the available RAM, resource problems will be extreme.

Now, Superserver on both Windows and POSIX can be configured by a new configuration parameter, MaxFileSystemCache, to prevent or enable filesystem caching. It may provide the benefit of freeing more memory for other operations such as sorting and, where there are multiple databases, reduce the demands made on host resources.

For details of the MaxFileSystemCache parameter, see MaxFileSystemCache.

Feature request CORE-1071

The background garbage collector process was reading all back versions of records on a page, including those created by active transactions. Since back versions of active records cannot be considered for garbage collection, it was wasteful to read them.

Feature request CORE- 961

The engine will now release external table files as soon as they are no longer in use by user requests.

Feature request CORE-976

No details.

Feature request CORE-1169

Conversion of temporary blobs to the destination blob type now occurs when materializing.

Feature request CORE-779

Introduced a type flag for stored procedures, adding column RDB$PROCEDURE_TYPE to the table RDB$PROCEDURES. Possible values are:

Feature request CORE-1558

The configuration parameter BugcheckAbort provides the capability to make the server stop trying to continue operation after a bugcheck and instead, to call abort() immediately and dump a core file. Since a bugcheck usually occurs as a result of a problem the server does not recognise, continuing operation with an unresolved problem is not usually possible anyway, and the core dump can provide useful debug information.

In the more recent Linux distributions the default setups no longer dump core automatically when an application crashes. Users often have troubles trying to get them working. Differing rules for Classic and Superserver, combined with a lack of consistency between the OS setup tools from distro to distro, make it difficult to help out with any useful “general rule”.

Code has been added for Classic and Superserver on Linux to bypass these problems and automate generation of a core dump file when an abort() on BUGCHECK occurs. The Firebird server will make the required 'cwd' (change working directory) to an appropriate writable location (/tmp) and set the core file size limit so that the 'soft' limit equals the 'hard' limit.

(v.2.1) A database trigger is a PSQL module that is executed when a connection or transaction event occurs. The events and the timings of their triggers are as follows.-

Syntax

<database-trigger> ::=
  {CREATE | RECREATE | CREATE OR ALTER}
    TRIGGER <name>
    [ACTIVE | INACTIVE]
    ON <event>
    [POSITION <n>]
  AS
    BEGIN
      ...
    END

<event> ::=
  CONNECT
    | DISCONNECT
    | TRANSACTION START
    | TRANSACTION COMMIT
    | TRANSACTION ROLLBACK
      

Rules and Restrictions

  gbak -nodbtriggers
  isql -nodbtriggers
  nbackup -T
        

(v.2.1) Global temporary tables (GTTs) are tables that are stored in the system catalogue with permanent metadata, but with temporary data. Data from different connections (or transactions, depending on the scope) are isolated from each other, but the metadata of the GTT are shared among all connections and transactions.

There are two kinds of GTT:

CREATE GLOBAL TEMPORARY TABLE
  ...
  [ON COMMIT <DELETE | PRESERVE> ROWS]
	

A couple of enhancements were made to view definitions in v.2.1.-

CREATE VIEW V_TEST AS
  SELECT ID,
         COL1 AS CODE,
         COL2 AS NAME
  FROM TAB;
        

Feature request CORE-711

(v.2.1) Alternative syntax is now available for CREATE TRIGGER that complies with SQL2003.

Syntax Patterns

Existing Form

create trigger t1
  FOR atable
  [active] before insert or update
as
  begin
    ...
  end
      

SQL2003 Form

create trigger t2 
  [active] before insert or update 
  ON atable
as 
  begin
    ...
  end
      

Note the different positions of the clause identifying the table and the different keywords pointing to the table identifier (existing: FOR; SQL2003: ON).

Both syntaxes are valid and are available also for all CREATE TRIGGER, RECREATE TRIGGER and CREATE OR ALTER TRIGGER statements.

Feature request CORE-1386

(v.2.1) SQL-compliant alternative syntax GENERATED ALWAYS AS was implemented for defining a computed field in CREATE/ALTER TABLE.

Syntax Pattern

<column name> [<type>] GENERATED ALWAYS AS ( <expr> )
      

It is fully equivalent semantically with the legacy form:

<column name> [<type>] COMPUTED [BY] ( <expr> )
      

Example

CREATE TABLE T (PK INT, EXPR GENERATED ALWAYS AS (PK + 1))
      

SEQUENCE has been introduced as a synonym for GENERATOR, in accordance with SQL-99. SEQUENCE is a syntax term described in the SQL specification, whereas GENERATOR is a legacy InterBase syntax term. Use of the standard SEQUENCE syntax in your applications is recommended.

A sequence generator is a mechanism for generating successive exact numeric values, one at a time. A sequence generator is a named schema object. In dialect 3 it is a BIGINT, in dialect 1 it is an INTEGER.

Syntax patterns

    CREATE { SEQUENCE | GENERATOR } <name>
    DROP { SEQUENCE | GENERATOR } <name>
    SET GENERATOR <name> TO <start_value>
    ALTER SEQUENCE <name> RESTART WITH <start_value>
    GEN_ID (<name>, <increment_value>)
    NEXT VALUE FOR <name>
      

Examples

1.

   CREATE SEQUENCE S_EMPLOYEE;
      

2.

   ALTER SEQUENCE S_EMPLOYEE RESTART WITH 0;
      

See also the notes about NEXT VALUE FOR.

SYSDBA, the database creator or the owner of an object can grant rights on that object to other users. However, those rights can be made inheritable, too. By using WITH GRANT OPTION, the grantor gives the grantee the right to become a grantor of the same rights in turn. This ability can be removed by the original grantor with REVOKE GRANT OPTION FROM user.

However, there's a second form that involves roles. Instead of specifying the same rights for many users (soon it becomes a maintenance nightmare) you can create a role, assign a package of rights to that role and then grant the role to one or more users. Any change to the role's rights affect all those users.

By using WITH ADMIN OPTION, the grantor (typically the role creator) gives the grantee the right to become a grantor of the same role in turn. Until FB v2, this ability couldn't be removed unless the original grantor fiddled with system tables directly. Now, the ability to grant the role can be removed by the original grantor with REVOKE ADMIN OPTION FROM user.

Domains allow their defaults to be changed or dropped. It seems natural that table fields can be manipulated the same way without going directly to the system tables.

Syntax Pattern

  ALTER TABLE t ALTER [COLUMN] c SET DEFAULT default_value;
  ALTER TABLE t ALTER [COLUMN] c DROP DEFAULT;
      

The DDL statements RECREATE EXCEPTION and CREATE OR ALTER EXCEPTION (feature request SF #1167973) have been implemented, allowing either creating, recreating or altering a custom exception, depending on whether it already exists.

ALTER EXTERNAL FUNCTION has been implemented, to enable the entry_point or the module_name to be changed when the UDF declaration cannot be dropped due to existing dependencies.

The COMMENT statement has been implemented for setting metadata descriptions.

Syntax Pattern

  COMMENT ON DATABASE IS {'txt'|NULL};
  COMMENT ON <basic_type> name IS {'txt'|NULL};
  COMMENT ON COLUMN tblviewname.fieldname IS {'txt'|NULL};
  COMMENT ON PARAMETER procname.parname IS {'txt'|NULL};
        

An empty literal string '' will act as NULL since the internal code (DYN in this case) works this way with blobs.

  <basic_type>:
     DOMAIN
     TABLE
     VIEW
     PROCEDURE
     TRIGGER
     EXTERNAL FUNCTION
     FILTER
     EXCEPTION
     GENERATOR
     SEQUENCE
     INDEX
     ROLE
     CHARACTER SET
     COLLATION
     SECURITY CLASS1
      

¹not implemented, because this type is hidden.

FIRST/SKIP and ROWS syntaxes and PLAN and ORDER BY clauses can now be used in view specifications.

From Firebird 2.0 onward, views are treated as fully-featured SELECT expressions. Consequently, the clauses FIRST/SKIP, ROWS, UNION, ORDER BY and PLAN are now allowed in views and work as expected.

Syntax

For syntax details, refer to Select Statement & Expression Syntax in the chapter about DML.

The DDL statement RECREATE TRIGGER statement is now available in DDL. Semantics are the same as for other RECREATE statements.

The following changes will affect usage or existing, pre-Firebird 2 workarounds in existing applications or databases to some degree.

   declare filter <name> input_type <number> output_type <number>
     entry_point <function_in_library> module_name <library_name>;
      

   declare filter <name> input_type <mnemonic> output_type <mnemonic>
     entry_point <function_in_library> module_name <library_name>;
      

   select RDB$TYPE, RDB$TYPE_NAME, RDB$SYSTEM_FLAG
     from rdb$types
     where rdb$field_name = 'RDB$FIELD_SUB_TYPE';

   RDB$TYPE   RDB$TYPE_NAME                RDB$SYSTEM_FLAG
   ========= ============================ =================

           0 BINARY                                     1
           1 TEXT                                       1
           2 BLR                                        1
           3 ACL                                        1
           4 RANGES                                     1
           5 SUMMARY                                    1
           6 FORMAT                                     1
           7 TRANSACTION_DESCRIPTION                    1
           8 EXTERNAL_FILE_DESCRIPTION                  1
      

    declare filter pesh input_type 0 output_type 3
      entry_point 'f' module_name 'p';
      

    declare filter pesh input_type binary output_type acl
      entry_point 'f' module_name 'p';
      

  SQL> insert into rdb$types
  CON> values('RDB$FIELD_SUB_TYPE', -100, 'XDR', 'test type', 0);
  SQL> commit;
  SQL> declare filter pesh2 input_type xdr output_type text
  CON> entry_point 'p2' module_name 'p';
  SQL> show filter pesh2;
  BLOB Filter: PESH2
          Input subtype: -100 Output subtype: 1
          Filter library is p
          Entry point is p2
        

(v.2.1) A common table expression (CTE) is like a view that is defined locally within a main query. The engine treats a CTE like a derived table and no intermediate materialisation of the data is performed.

select :
  select_expr for_update_clause lock_clause
select_expr :
  with_clause select_expr_body order_clause rows_clause
            | select_expr_body order_clause rows_clause
with_clause :
  WITH RECURSIVE with_list | WITH with_list
with_list :
  with_item | with_item ',' with_list
with_item :
  symbol_table_alias_name derived_column_list
    AS '(' select_expr ')'
select_expr_body :
  query_term
  | select_expr_body UNION distinct_noise query_term
             | select_expr_body UNION ALL query_term
        

WITH [RECURSIVE]
	CTE_A [(a1, a2, …)]
	AS ( SELECT … ),

	CTE_B [(b1, b2, …)]
	AS ( SELECT … ),
...
SELECT ...
  FROM CTE_A, CTE_B, TAB1, TAB2 ...
 WHERE ...
        

WITH
  DEPT_YEAR_BUDGET AS (
    SELECT FISCAL_YEAR, DEPT_NO,
        SUM(PROJECTED_BUDGET) AS BUDGET
      FROM PROJ_DEPT_BUDGET
    GROUP BY FISCAL_YEAR, DEPT_NO
  )
SELECT D.DEPT_NO, D.DEPARTMENT,
  B_1993.BUDGET AS B_1993, B_1994.BUDGET AS B_1994,
       B_1995.BUDGET AS B_1995, B_1996.BUDGET AS B_1996
  FROM DEPARTMENT D
    LEFT JOIN DEPT_YEAR_BUDGET B_1993
      ON D.DEPT_NO = B_1993.DEPT_NO
      AND B_1993.FISCAL_YEAR = 1993
    LEFT JOIN DEPT_YEAR_BUDGET B_1994
      ON D.DEPT_NO = B_1994.DEPT_NO
      AND B_1994.FISCAL_YEAR = 1994
    LEFT JOIN DEPT_YEAR_BUDGET B_1995
      ON D.DEPT_NO = B_1995.DEPT_NO
      AND B_1995.FISCAL_YEAR = 1995
    LEFT JOIN DEPT_YEAR_BUDGET B_1996
      ON D.DEPT_NO = B_1996.DEPT_NO
      AND B_1996.FISCAL_YEAR = 1996

  WHERE EXISTS (
    SELECT * FROM PROJ_DEPT_BUDGET B
    WHERE D.DEPT_NO = B.DEPT_NO)
          

WITH RECURSIVE
  DEPT_YEAR_BUDGET AS
  (
    SELECT FISCAL_YEAR, DEPT_NO,
        SUM(PROJECTED_BUDGET) AS BUDGET
      FROM PROJ_DEPT_BUDGET
    GROUP BY FISCAL_YEAR, DEPT_NO
  ),

  DEPT_TREE AS
  (
    SELECT DEPT_NO, HEAD_DEPT, DEPARTMENT,
        CAST('' AS VARCHAR(255)) AS INDENT
      FROM DEPARTMENT
     WHERE HEAD_DEPT IS NULL

    UNION ALL

    SELECT D.DEPT_NO, D.HEAD_DEPT, D.DEPARTMENT,
    H.INDENT || '  '
      FROM DEPARTMENT D
      JOIN DEPT_TREE H
        ON D.HEAD_DEPT = H.DEPT_NO
  )

  SELECT D.DEPT_NO,
	D.INDENT || D.DEPARTMENT AS DEPARTMENT,
	B_1993.BUDGET AS B_1993,
	B_1994.BUDGET AS B_1994,
	B_1995.BUDGET AS B_1995,
	B_1996.BUDGET AS B_1996

  FROM DEPT_TREE D
    LEFT JOIN DEPT_YEAR_BUDGET B_1993
      ON D.DEPT_NO = B_1993.DEPT_NO
      AND B_1993.FISCAL_YEAR = 1993

    LEFT JOIN DEPT_YEAR_BUDGET B_1994
      ON D.DEPT_NO = B_1994.DEPT_NO
      AND B_1994.FISCAL_YEAR = 1994

    LEFT JOIN DEPT_YEAR_BUDGET B_1995
      ON D.DEPT_NO = B_1995.DEPT_NO
      AND B_1995.FISCAL_YEAR = 1995

    LEFT JOIN DEPT_YEAR_BUDGET B_1996
      ON D.DEPT_NO = B_1996.DEPT_NO
      AND B_1996.FISCAL_YEAR = 1996
          

(v.2.1) This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.

Format

<list function> ::=
  LIST '(' [ {ALL | DISTINCT} ] <value expression> [',' <delimiter value>
   ] ')'

<delimiter value> ::=
      { <string literal> | <parameter> | <variable> }
    

Syntax Rules

Other Notes

Examples

/* A */
  SELECT LIST(ID, ':')
  FROM MY_TABLE

/* B */
  SELECT TAG_TYPE, LIST(TAG_VALUE)
  FROM TAGS
  GROUP BY TAG_TYPE
      

(v.2.1) The purpose of this SQL enhancement is to enable the column values stored into a table as a result of the INSERT, UPDATE OR INSERT, UPDATE and DELETE statements to be returned to the client.

The most likely usage is for retrieving the value generated for a primary key inside a BEFORE-trigger. The RETURNING clause is optional and is available in both DSQL and PSQL, although the rules differ slightly.

In DSQL, the execution of the operation itself and the return of the set occur in a single protocol round trip.

Because the RETURNING clause is designed to return a singleton set in response to completing an operation on a single record, it is not valid to specify the clause in a statement that inserts, updates or deletes multiple records.

Syntax Patterns

INSERT INTO ... VALUES (...)
    [RETURNING <column_list> [INTO <variable_list>]]

INSERT INTO ... SELECT ...
    [RETURNING <column_list> [INTO <variable_list>]]

UPDATE OR INSERT INTO ... VALUES (...) ...
    [RETURNING <column_list> [INTO <variable_list>]]

UPDATE ... [RETURNING <column_list> [INTO <variable_list>]]

DELETE FROM ...
    [RETURNING <column_list> [INTO <variable_list>]]
      

(v.2.1) This syntax has been introduced to enable a record to be either updated or inserted, according to whether or not it already exists (checked with IS NOT DISTINCT). The statement is available in both DSQL and PSQL.

Syntax Pattern

UPDATE OR INSERT INTO <table or view> [(<column_list>)]
    VALUES (<value_list>)
    [MATCHING (<column_list>)]
    [RETURNING <column_list> [INTO <variable_list>]]
      

Examples

(v.2.1) This syntax has been introduced to enable a record to be either updated or inserted, according to whether or not a stated condition is met. The statement is available in both DSQL and PSQL.

Syntax Pattern

<merge statement> ::=
MERGE
  INTO <table or view> [ [AS] <correlation name> ]
  USING <table or view or derived table> [ [AS] <correlation name> ]
    ON <condition>
    [ <merge when matched> ]
    [ <merge when not matched> ]

<merge when matched> ::=
  WHEN MATCHED THEN
    UPDATE SET <assignment list>

<merge when not matched> ::=
  WHEN NOT MATCHED THEN
    INSERT [ <left paren> <column list> <right paren> ]
    VALUES <left paren> <value list> <right paren>
      

Example

MERGE INTO customers c
  USING (SELECT * FROM customers_delta WHERE id > 10) cd
  ON (c.id = cd.id)
    WHEN MATCHED THEN
      UPDATE SET
        name = cd.name
    WHEN NOT MATCHED THEN
      INSERT (id, name)
      VALUES (cd.id, cd.name)
      

(v.2.1) Two new JOIN types are introduced: the NAMED COLUMNS join and its close relative, the NATURAL join.

<named columns join> ::=
  <table reference> <join type> JOIN <table reference>
    USING ( <column list> )

<natural join> ::=
 <table reference> NATURAL <join type> JOIN <table primary>
        

/* 1 */
select * from employee
  join department
  using (dept_no);

/* 2 */
select * from employee_project
  natural join employee
  natural join project;
        

   A CROSS JOIN B
        

   A INNER JOIN B ON 1 = 1
        

  FROM A, B
        

Feature request

(v.2.1) It is now possible to INSERT without supplying values, if Before Insert triggers and/or declared defaults are available for every column and none is dependent on the presence of any supplied 'NEW' value.

Example

INSERT INTO <table>
  DEFAULT VALUES
  [RETURNING <values>]
      

(v.2.1) At various levels of evaluation, the engine now treats text BLOBs that are within the 32,765-byte string size limit as though they were VARCHARs. Operations that now allow text BLOBs to behave like strings are assignments, conversions and concatenations, as well as the functions CAST, LOWER, UPPER, TRIM and SUBSTRING.

(v.2.0.x) Comparison can be performed on the entire content of a text BLOB.

Feature request CORE-979

(v.2.1) By some anomaly, the physical RDB$DB_KEY has always returned a value on every output row when specified in an outer join, thereby making a test predicated on the assumption that a non-match returns NULL in all fields return False when it ought to return True. Now, RDB$DB_KEY returns NULL when it should do so.

(v.2.1) In earlier pre-release versions of Firebird 2.1, changes were introduced to reject sorts (ORDER BY, GROUP BY and SELECT DISTINCT operations) at prepare time if the sort clause implicitly or explicitly involved sorting on a BLOB or ARRAY column.

That change was reversed in the RC2 pre-release version, not because it was wrong but because so many users complained that it broke the behaviour of legacy applications.

(v.2.1) Some existing built-in functions have been enhanced, while a large number of new ones has been added.

Some function enhancements were already available in the V.2.0.x releases:

   IIF (<search_condition>, <value1>, <value2>)
        

   CASE
     WHEN <search_condition> THEN <value1>
     ELSE <value2>
   END
        

    SELECT IIF(VAL > 0, VAL, -VAL) FROM OPERATION
        

  SELECT CAST(? AS INT) FROM RDB$DATABASE
        

Alias handling and ambiguous field detecting have been improved. In summary:

Examples

The SQL language extension EXECUTE BLOCK makes "dynamic PSQL" available to SELECT specifications. It has the effect of allowing a self-contained block of PSQL code to be executed in dynamic SQL as if it were a stored procedure.

Syntax pattern

   EXECUTE BLOCK [ (param datatype = ?, param datatype = ?, ...) ]
     [ RETURNS (param datatype, param datatype, ...) ]
   AS
   [DECLARE VARIABLE var datatype; ...]
   BEGIN
     ...
   END
      

For the client, the call isc_dsql_sql_info with the parameter isc_info_sql_stmt_type returns

The client should preprocess only the head of the SQL statement or use '?' instead of ':' as the parameter indicator because, in the body of the block, there may be references to local variables or arguments with a colon prefixed.

Example

The user SQL is

   EXECUTE BLOCK (X INTEGER = :X)
     RETURNS (Y VARCHAR)
   AS
   DECLARE V INTEGER;
   BEGIN
     INSERT INTO T(...) VALUES (... :X ...);
     SELECT ... FROM T INTO :Y;
     SUSPEND;
   END
      

The preprocessed SQL is

   EXECUTE BLOCK (X INTEGER = ?)
     RETURNS (Y VARCHAR)
   AS
   DECLARE V INTEGER;
   BEGIN
     INSERT INTO T(...) VALUES (... :X ...);
     SELECT ... FROM T INTO :Y;
     SUSPEND;
   END
      

Implemented support for derived tables in DSQL (subqueries in FROM clause) as defined by SQL200X. A derived table is a set, derived from a dynamic SELECT statement. Derived tables can be nested, if required, to build complex queries and they can be involved in joins as though they were normal tables or views.

Syntax Pattern

    SELECT
      <select list>
    FROM
      <table reference list>

    <table reference list> ::= <table reference> [{<comma> <table reference>}...]

    <table reference> ::=
        <table primary>
      | <joined table>

    <table primary> ::=
        <table> [[AS] <correlation name>]
      | <derived table>

    <derived table> ::=
        <query expression> [[AS] <correlation name>]
          [<left paren> <derived column list> <right paren>]

    <derived column list> ::= <column name> [{<comma> <column name>}...]
      

Examples

a) Simple derived table:

  SELECT
    *
  FROM
    (SELECT
       RDB$RELATION_NAME, RDB$RELATION_ID
     FROM
       RDB$RELATIONS) AS R (RELATION_NAME, RELATION_ID)
      

b) Aggregate on a derived table which also contains an aggregate

  SELECT
    DT.FIELDS,
    Count(*)
  FROM
    (SELECT
       R.RDB$RELATION_NAME,
       Count(*)
     FROM
       RDB$RELATIONS R
       JOIN RDB$RELATION_FIELDS RF ON (RF.RDB$RELATION_NAME = R.RDB$RELATION_NAME)
     GROUP BY
       R.RDB$RELATION_NAME) AS DT (RELATION_NAME, FIELDS)
  GROUP BY
    DT.FIELDS
      

c) UNION and ORDER BY example:

  SELECT
    DT.*
  FROM
    (SELECT
       R.RDB$RELATION_NAME,
       R.RDB$RELATION_ID
     FROM
       RDB$RELATIONS R
     UNION ALL
     SELECT
       R.RDB$OWNER_NAME,
       R.RDB$RELATION_ID
     FROM
       RDB$RELATIONS R
     ORDER BY
       2) AS DT
  WHERE
    DT.RDB$RELATION_ID <= 4
      

Points to Note

The ROLLBACK RETAIN statement is now supported in DSQL.

A “rollback retaining” feature was introduced in InterBase 6.0, but this rollback mode could be used only via an API call to isc_rollback_retaining(). By contrast, “commit retaining” could be used either via an API call to isc_commit_retaining() or by using a DSQL COMMIT RETAIN statement.

Firebird 2.0 adds an optional RETAIN clause to the DSQL ROLLBACK statement to make it consistent with COMMIT [RETAIN].

Syntax pattern: follows that of COMMIT RETAIN.

ROWS syntax is used to limit the number of rows retrieved from a select expression. For an uppermost-level select statement, it would specify the number of rows to be returned to the host program. A more understandable alternative to the FIRST/SKIP clauses, the ROWS syntax accords with the latest SQL standard and brings some extra benefits. It can be used in unions, any kind of subquery and in UPDATE or DELETE statements.

It is available in both DSQL and PSQL.

Syntax Pattern

   SELECT ...
     [ORDER BY <expr_list>]
     ROWS <expr1> [TO <expr2>]
      

Examples

1.

  SELECT * FROM T1
    UNION ALL
  SELECT * FROM T2
    ORDER BY COL
    ROWS 10 TO 100
      

2.

   SELECT COL1, COL2,
     ( SELECT COL3 FROM T3 ORDER BY COL4 DESC ROWS 1 )
   FROM T4
      

3.

   DELETE FROM T5
     ORDER BY COL5
     ROWS 1
      

Points to Note

The rules for UNION queries have been improved as follows:

  UNION [{DISTINCT | ALL}]
        

The following features involving NULL in DSQL have been implemented:

   <value> IS [NOT] DISTINCT FROM <value>
        

   SELECT * FROM T1
     JOIN T2
       ON T1.NAME IS NOT DISTINCT FROM T2.NAME;
        

   SELECT * FROM T
     WHERE T.MARK IS DISTINCT FROM 'test';
        

  A = NULL
  B > NULL
  A + NULL
  B || NULL
        

  Database:  proc.fdb
  SQL> create table gnull(a int);
  SQL> insert into gnull values(null);
  SQL> insert into gnull values(1);
  SQL> select a from gnull order by a;
             A
  ============
        <null>
             1

  SQL> select a from gnull order by a asc;

             A
  ============
        <null>
             1

  SQL> select a from gnull order by a desc;

             A
  ============
             1
        <null>

  SQL> select a from gnull order by a asc nulls first;

             A
  ============
        <null>
             1

  SQL> select a from gnull order by a asc nulls last;

             A
  ============
             1
        <null>


  SQL> select a from gnull order by a desc nulls last;

             A
  ============
             1
        <null>

  SQL> select a from gnull order by a desc nulls first;

             A
  ============
        <null>
             1
        

SELECT specifications used in subqueries and in INSERT INTO <insert-specification> SELECT.. statements can now specify a UNION set.

ROWS specifications and PLAN and ORDER BY clauses can now be used in UPDATE and DELETE statements.

Users can now specify explicit plans for UPDATE/DELETE statements in order to optimize them manually. It is also possible to limit the number of affected rows with a ROWS clause, optionally used in combination with an ORDER BY clause to have a sorted recordset.

Syntax Pattern

  UPDATE ... SET ... WHERE ...
  [PLAN <plan items>]
  [ORDER BY <value list>]
  [ROWS <value> [TO <value>]]
      

or

  DELETE ... FROM ...
  [PLAN <plan items>]
  [ORDER BY <value list>]
  [ROWS <value> [TO <value>]]
      

A number of new facilities have been added to extend the context information that can be retrieved:

    CURRENT_TIME [(<seconds precision>)]
    CURRENT_TIMESTAMP [(<seconds precision>)]
          

    1. SELECT CURRENT_TIME FROM RDB$DATABASE;
    2. SELECT CURRENT_TIME(3) FROM RDB$DATABASE;
    3. SELECT CURRENT_TIMESTAMP(3) FROM RDB$DATABASE;
          

    RDB$SET_CONTEXT( <namespace>, <variable>, <value> )
    RDB$GET_CONTEXT( <namespace>, <variable> )
       

  DECLARE EXTERNAL FUNCTION RDB$GET_CONTEXT
      VARCHAR(80),
      VARCHAR(80)
  RETURNS VARCHAR(255) FREE_IT;

  DECLARE EXTERNAL FUNCTION RDB$SET_CONTEXT
      VARCHAR(80),
      VARCHAR(80),
      VARCHAR(255)
  RETURNS INTEGER BY VALUE;
       

   set term ^;
   create procedure set_context(User_ID varchar(40), Trn_ID integer) as
   begin
     RDB$SET_CONTEXT('USER_TRANSACTION', 'Trn_ID', Trn_ID);
     RDB$SET_CONTEXT('USER_TRANSACTION', 'User_ID', User_ID);
   end ^

  create table journal (
     jrn_id integer not null primary key,
     jrn_lastuser varchar(40),
     jrn_lastaddr varchar(255),
     jrn_lasttransaction integer
  )^

CREATE TRIGGER UI_JOURNAL FOR JOURNAL BEFORE INSERT OR UPDATE
  as
  begin
    new.jrn_lastuser = rdb$get_context('USER_TRANSACTION', 'User_ID');
    new.jrn_lastaddr = rdb$get_context('SYSTEM', 'CLIENT_ADDRESS');
    new.jrn_lasttransaction = rdb$get_context('USER_TRANSACTION', 'Trn_ID');
  end ^
  commit ^
  execute procedure set_context('skidder', 1) ^

  insert into journal(jrn_id) values(0) ^
  set term ;^
          

  SQL> select rdb$set_context('USER_SESSION', 'Nickolay', 'ru') 
  CNT> from rdb$database;

  RDB$SET_CONTEXT
  ===============
                0
          

  SQL> select rdb$set_context('USER_SESSION', 'Nickolay', 'ca')
  CNT> from rdb$database;

  RDB$SET_CONTEXT
  ===============
                1
          

  SQL> select rdb$set_context('USER_SESSION', 'Nickolay', NULL)
  CNT> from rdb$database;

  RDB$SET_CONTEXT
  ===============
                1
          

  SQL> select rdb$set_context('USER_SESSION', 'Nickolay', NULL)
  CNT> from rdb$database;

  RDB$SET_CONTEXT
  ===============
                0
          

Syntax rules

The following schema describing the syntax rules should be helpful when composing plans.

PLAN ( { <stream_retrieval> | <sorted_streams> | <joined_streams> } )

<stream_retrieval> ::= { <natural_scan> | <indexed_retrieval> | 
    <navigational_scan> }

<natural_scan> ::= <stream_alias> NATURAL

<indexed_retrieval> ::= <stream_alias> INDEX ( <index_name> 
    [, <index_name> ...] )

<navigational_scan> ::= <stream_alias> ORDER <index_name>
    [ INDEX ( <index_name> [, <index_name> ...] ) ]

<sorted_streams> ::= SORT ( <stream_retrieval> )

<joined_streams> ::= JOIN ( <stream_retrieval>, <stream_retrieval> 
    [, <stream_retrieval> ...] ) 
    | [SORT] MERGE ( <sorted_streams>, <sorted_streams> )
      

Details

Natural scan means that all rows are fetched in their natural storage order. Thus, all pages must be read before search criteria are validated.

Indexed retrieval uses an index range scan to find row ids that match the given search criteria. The found matches are combined in a sparse bitmap which is sorted by page numbers, so every data page will be read only once. After that the table pages are read and required rows are fetched from them.

Navigational scan uses an index to return rows in the given order, if such an operation is appropriate.-

A sort operation performs an external sort of the given stream retrieval.

A join can be performed either via the nested loops algorithm (JOIN plan) or via the sort merge algorithm (MERGE plan).-

A sort merge operates with two input streams which are sorted beforehand, then merged in a single run.

Examples

    SELECT RDB$RELATION_NAME
    FROM RDB$RELATIONS
    WHERE RDB$RELATION_NAME LIKE 'RDB$%'
    PLAN (RDB$RELATIONS NATURAL)
    ORDER BY RDB$RELATION_NAME

    SELECT R.RDB$RELATION_NAME, RF.RDB$FIELD_NAME
    FROM RDB$RELATIONS R
      JOIN RDB$RELATION_FIELDS RF
      ON R.RDB$RELATION_NAME = RF.RDB$RELATION_NAME
    PLAN MERGE (SORT (R NATURAL), SORT (RF NATURAL))
      

Notes

Some useful improvements have been made to SQL sorting operations:

  ...
    GROUP BY
    SUBSTRING(CAST((A * B) / 2 AS VARCHAR(15)) FROM 1 FOR 2)
        

   SELECT *
     FROM RDB$RELATIONS
     ORDER BY 9
        

Added SQL-99 compliant NEXT VALUE FOR <sequence_name> expression as a synonym for GEN_ID(<generator-name>, 1), complementing the introduction of CREATE SEQUENCE syntax as the SQL standard equivalent of CREATE GENERATOR.

Examples

1.

   SELECT GEN_ID(S_EMPLOYEE, 1) FROM RDB$DATABASE;
      

2.

   INSERT INTO EMPLOYEE (ID, NAME)
     VALUES (NEXT VALUE FOR S_EMPLOYEE, 'John Smith');
      

About the semantics

Syntax rules

  <select statement> ::=
    <select expression> [FOR UPDATE] [WITH LOCK]

  <select expression> ::=
    <query specification> [UNION [{ALL | DISTINCT}] <query specification>]

  <query specification> ::=
    SELECT [FIRST <value>] [SKIP <value>] <select list>
    FROM <table expression list>
    WHERE <search condition>
    GROUP BY <group value list>
    HAVING <group condition>
    PLAN <plan item list>
    ORDER BY <sort value list>
    ROWS <value> [TO <value>]

  <table expression> ::=
    <table name> | <joined table> | <derived table>

  <joined table> ::=
    {<cross join> | <qualified join>}

  <cross join> ::=
    <table expression> CROSS JOIN <table expression>

  <qualified join> ::=
    <table expression> [{INNER | {LEFT | RIGHT | FULL} [OUTER]}] JOIN <table expression>
    ON <join condition>

  <derived table> ::=
    '(' <select expression> ')'
    

Conclusions

Notes

When aggregations, CASE evaluations and UNIONs for output columns are performed over a mix of comparable data types, the engine has to choose one data type for the result. The developer often has to prepare a variable or buffer for such results and is mystified when a request returns a data type exception. The rules followed by the engine in determining the data type for an output column under these conditions are explained here.

In days gone by, before the advent of context variables like CURRENT_DATE, CURRENT_TIMESTAMP, et al., we had predefined date literals, such as 'NOW', 'TODAY', 'YESTERDAY' and so on. These predefined date literals survive in Firebird's SQL language set and are still useful.

In InterBase 5.x and lower, the following statement was “legal” and returned a DATE value ( remembering that the DATE type then was what is now TIMESTAMP):

select 'NOW' from rdb$database /* returns system date and time */
      

In a database of ODS 10 or higher, that statement returns the string 'NOW'. We have had to learn to cast the date literal to get the result we want:

select cast('NOW' as TIMESTAMP) from rdb$database
      

For a long time—probably since IB 6— there has been an undocumented “short expression syntax” for casting not just the predefined date/time literals but any date literals. Actually, it is defined in the standard. Most of us were just not aware that it was available. It takes the form <data type> <date literal>. Taking the CAST example above, the short syntax would be as follows:

select TIMESTAMP 'NOW' from rdb$database
      

This short syntax can participate in other expressions. The following example illustrates a date/time arithmetic operation on a predefined literal:

update mytable
  set OVERDUE = 'T'
  where DATE 'YESTERDAY' - DATE_DUE > 10
      

(V.2.1) It is now possible to use a domain when declaring the data types of arguments and variables in PSQL modules. Depending on your requirements, you can declare the argument or variable using

Syntax

data_type ::=
    <builtin_data_type>
    | <domain_name>
    | TYPE OF <domain_name>
      

Examples

CREATE DOMAIN DOM AS INTEGER;

CREATE PROCEDURE SP (
  I1 TYPE OF DOM,
  I2 DOM)
RETURNS (
  O1 TYPE OF DOM,
  O2 DOM)
AS
  DECLARE VARIABLE V1 TYPE OF DOM;
  DECLARE VARIABLE V2 DOM;

BEGIN
  ...
END
      

(V.2.1) Collations can now be applied to PSQL variables, including stored procedure parameters.

Feature request CORE-1213

(V.2.1) The cursor operator WHERE CURRENT OF can now step through a cursor set selected from a view set, just as it does in a cursor set output from a SELECT on a table. For example:

...
FOR SELECT ...
   FROM MY_VIEW INTO ... AS CURSOR VIEW_CURSOR DO
BEGIN
  ...
  DELETE FROM MY_VIEW
    WHERE CURRENT OF VIEW_CURSOR;
  ...
END
      

ROW_COUNT has been enhanced so that it can now return the number of rows returned by a SELECT statement.

For example, it can be used to check whether a singleton SELECT INTO statement has performed an assignment:

  ..
  BEGIN
    SELECT COL FROM TAB INTO :VAR;

    IF (ROW_COUNT = 0) THEN
      EXCEPTION NO_DATA_FOUND;
  END
  ..
      

See also its usage in the examples below for explicit PSQL cursors.

It is now possible to declare and use multiple cursors in PSQL. Explicit cursors are available in a DSQL EXECUTE BLOCK structure as well as in stored procedures and triggers.

Syntax pattern

    DECLARE [VARIABLE] <cursor_name> CURSOR FOR ( <select_statement> );
    OPEN <cursor_name>;
    FETCH <cursor_name> INTO <var_name> [, <var_name> ...];
    CLOSE <cursor_name>;
      

Examples

1.

    DECLARE RNAME CHAR(31);
    DECLARE C CURSOR FOR ( SELECT RDB$RELATION_NAME
                              FROM RDB$RELATIONS );
    BEGIN
      OPEN C;
      WHILE (1 = 1) DO
      BEGIN
        FETCH C INTO :RNAME;
        IF (ROW_COUNT = 0) THEN
          LEAVE;
        SUSPEND;
      END
      CLOSE C;
    END
      

2.

    DECLARE RNAME CHAR(31);
    DECLARE FNAME CHAR(31);
    DECLARE C CURSOR FOR ( SELECT RDB$FIELD_NAME
                           FROM RDB$RELATION_FIELDS
                           WHERE RDB$RELATION_NAME = :RNAME
                           ORDER BY RDB$FIELD_POSITION );
    BEGIN
      FOR
        SELECT RDB$RELATION_NAME
        FROM RDB$RELATIONS
        INTO :RNAME
      DO
      BEGIN
        OPEN C;
        FETCH C INTO :FNAME;
        CLOSE C;
        SUSPEND;
      END
    END
      

Defaults can now be declared for stored procedure arguments.

The syntax is the same as a default value definition for a column or domain, except that you can use '=' in place of 'DEFAULT' keyword.

Arguments with default values must be last in the argument list; that is, you cannot declare an argument that has no default value after any arguments that have been declared with default values. The caller must supply the values for all of the arguments preceding any that are to use their defaults.

For example, it is illegal to do something like this:   supply arg1, arg2, miss arg3, set arg4...

Substitution of default values occurs at run-time. If you define a procedure with defaults (say P1), call it from another procedure (say P2) and skip some final, defaulted arguments, then the default values for P1 will be substituted by the engine at time execution P1 starts. This means that, if you change the default values for P1, it is not necessary to recompile P2.

However, it is still necessary to disconnect all client connections, as discussed in the Borland InterBase 6 beta "Data Definition Guide" (DataDef.pdf), in the section "Altering and dropping procedures in use".

Examples

   CONNECT ... ;
   SET TERM ^;
   CREATE PROCEDURE P1 (X INTEGER = 123)
   RETURNS (Y INTEGER)
   AS
   BEGIN
     Y = X;
     SUSPEND;
   END ^
   COMMIT ^
   SET TERM ;^

   SELECT * FROM P1;

              Y
   ============

            123

   EXECUTE PROCEDURE P1;

              Y
   ============
            123

   SET TERM ^;
   CREATE PROCEDURE P2
   RETURNS (Y INTEGER)
   AS
   BEGIN
     FOR SELECT Y FROM P1 INTO :Y
     DO SUSPEND;
   END ^
   COMMIT ^
   SET TERM ;^

   SELECT * FROM P2;

              Y
   ============

            123

   SET TERM ^;
   ALTER PROCEDURE P1 (X INTEGER = CURRENT_TRANSACTION)
            RETURNS (Y INTEGER)
   AS
   BEGIN
     Y = X;
    SUSPEND;
   END; ^
   COMMIT ^
   SET TERM ;^

   SELECT * FROM P1;

              Y
   ============

           5875

   SELECT * FROM P2;

              Y
   ============

            123

   COMMIT;

   CONNECT  ... ;

   SELECT * FROM P2;

              Y
   ============

           5880

      

New LEAVE <label> syntax now allows PSQL loops to be marked with labels and terminated in Java style. The purpose is to stop execution of the current block and unwind back to the specified label. After that execution resumes at the statement following the terminated loop.

Syntax pattern

    <label_name>: <loop_statement>
    ...
    LEAVE [<label_name>]
      

where <loop_statement> is one of: WHILE, FOR SELECT, FOR EXECUTE STATEMENT.

Examples

1.

   FOR
     SELECT COALESCE(RDB$SYSTEM_FLAG, 0), RDB$RELATION_NAME
       FROM RDB$RELATIONS
       ORDER BY 1
     INTO :RTYPE, :RNAME
     DO
     BEGIN
       IF (RTYPE = 0) THEN
         SUSPEND;
       ELSE
         LEAVE; -- exits current loop
     END
      

2.

   CNT = 100;
   L1:
   WHILE (CNT >= 0) DO
   BEGIN
     IF (CNT < 50) THEN
       LEAVE L1; -- exists WHILE loop
     CNT = CNT - l;
   END
      

3.

   STMT1 = 'SELECT RDB$RELATION_NAME FROM RDB$RELATIONS';
   L1:
   FOR
     EXECUTE STATEMENT :STMT1 INTO :RNAME
   DO
   BEGIN
     STMT2 = 'SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS
       WHERE RDB$RELATION_NAME = ';
     L2:
     FOR
       EXECUTE STATEMENT :STMT2 || :RNAME INTO :FNAME
     DO
     BEGIN
       IF (RNAME = 'RDB$DATABASE') THEN
         LEAVE L1; -- exits the outer loop
       ELSE IF (RNAME = 'RDB$RELATIONS') THEN
         LEAVE L2; -- exits the inner loop
       ELSE
         SUSPEND;
     END
   END
      

The set of OLD context variables available in trigger modules is now read-only. An attempt to assign a value to OLD.something will be rejected.

The API client can now extract a simple stack trace Error Status Vector when an exception occurs during PSQL execution (stored procedures or triggers). A stack trace is represented by one string (2048 bytes max.) and consists of all the stored procedure and trigger names, starting from the point where the exception occurred, out to the outermost caller. If the actual trace is longer than 2Kb, it is truncated.

Additional items are appended to the status vector as follows:

   isc_stack_trace, isc_arg_string, <string length>, <string>
      

isc_stack_trace is a new error code with value of 335544842L.

Examples

Metadata creation

  CREATE TABLE ERR (
    ID INT NOT NULL PRIMARY KEY,
    NAME VARCHAR(16));

  CREATE EXCEPTION EX '!';
  SET TERM ^;

  CREATE OR ALTER PROCEDURE ERR_1 AS
  BEGIN
    EXCEPTION EX 'ID = 3';
  END ^

  CREATE OR ALTER TRIGGER ERR_BI FOR ERR
    BEFORE INSERT AS
  BEGIN
    IF (NEW.ID = 2)
    THEN EXCEPTION EX 'ID = 2';

    IF (NEW.ID = 3)
    THEN EXECUTE PROCEDURE ERR_1;

    IF (NEW.ID = 4)
    THEN NEW.ID = 1 / 0;
  END ^

  CREATE OR ALTER PROCEDURE ERR_2 AS
  BEGIN
    INSERT INTO ERR VALUES (3, '333');
  END ^
      

1. User exception from a trigger:

  SQL" INSERT INTO ERR VALUES (2, '2');
  Statement failed, SQLCODE = -836
  exception 3
  -ID = 2
  -At trigger 'ERR_BI'
      

2. User exception from a procedure called by a trigger:

  SQL" INSERT INTO ERR VALUES (3, '3');
  Statement failed, SQLCODE = -836
  exception 3
  -ID = 3
  -At procedure 'ERR_1'
  At trigger 'ERR_BI'
      

3. Run-time exception occurring in trigger (division by zero):

  SQL" INSERT INTO ERR VALUES (4, '4');
  Statement failed, SQLCODE = -802
  arithmetic exception, numeric overflow, or string truncation
  -At trigger 'ERR_BI'
      

4. User exception from procedure:

  SQL" EXECUTE PROCEDURE ERR_1;
  Statement failed, SQLCODE = -836
  exception 3
  -ID = 3
  -At procedure 'ERR_1'
      

5. User exception from a procedure with a deeper call stack:

  SQL" EXECUTE PROCEDURE ERR_2;
  Statement failed, SQLCODE = -836
  exception 3
  -ID = 3
  -At procedure 'ERR_1'
  At trigger 'ERR_BI'
  At procedure 'ERR_2'
      

In PSQL, supported UDFs, e.g. RDB$SET_CONTEXT, can be called as though they were void functions (a.k.a “procedures” in Object Pascal). For example:

  BEGIN
     ...
     RDB$SET_CONTEXT('USER_TRANSACTION', 'MY_VAR', '123');
     ...
  END
      

The first group of changes affect all databases, including those not yet upgraded to ODS 11.x.

        (NOT NOT A = 0) -> (A = 0)
        (NOT A > 0) -> (A <= 0)
          

  select rdb$relation_id, count(*)
  from rdb$relations
  group by rdb$relation_id
  having rdb$relation_id > 10

  select * from ( 
    select rdb$relation_id, count(*)
    from rdb$relations
    group by rdb$relation_id
    ) as grp (id, cnt)
  where grp.id > 10
          

This group of optimizations affects databases that were created or restored under Firebird 2 or higher.

New and reworked index code is very fast and tolerant of large numbers of duplicates. The old aggregate key length limit of 252 bytes is removed. Now the limit depends on page size: the maximum size of the key in bytes is 1/4 of the page size (512 on 2048, 1024 on 4096, etc.)

A 40-bit record number is included on “non leaf-level pages” and duplicates (key entries) are sorted by this number.

Arbitrary expressions applied to values in a row in dynamic DDL can now be indexed, allowing indexed access paths to be available for search predicates that are based on expressions.

Syntax Pattern

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX <index name>
  ON <table name>
  COMPUTED BY ( <value expression> )
        

Examples

1.

CREATE INDEX IDX1 ON T1
  COMPUTED BY ( UPPER(COL1 COLLATE PXW_CYRL) );
COMMIT;
/*  */
SELECT * FROM T1
  WHERE UPPER(COL1 COLLATE PXW_CYRL) = 'ÔÛÂÀ'
-- PLAN (T1 INDEX (IDX1))
        

2.

CREATE INDEX IDX2 ON T2
  COMPUTED BY ( EXTRACT(YEAR FROM COL2) || EXTRACT(MONTH FROM COL2) );
COMMIT;
/*  */
SELECT * FROM T2
  ORDER BY EXTRACT(YEAR FROM COL2) || EXTRACT(MONTH FROM COL2)
-- PLAN (T2 ORDER IDX2)
        

A full reworking of the index compression algorithm has made a manifold improvement in the performance of many queries.

Index selectivities are now stored on a per-segment basis. This means that, for a compound index on columns (A, B, C), three selectivity values will be calculated, reflecting a full index match as well as all partial matches. That is to say, the selectivity of the multi-segment index involves those of segment A alone (as it would be if it were a single-segment index), segments A and B combined (as it would be if it were a double-segment index) and the full three-segment match (A, B, C), i.e., all the ways a compound index can be used.

This opens more opportunities to the optimizer for clever access path decisions in cases involving partial index matches.

The per-segment selectivity values are stored in the column RDB$STATISTICS of table RDB$INDEX_SEGMENTS. The column of the same name in RDB$INDICES is kept for compatibility and still represents the total index selectivity, that is used for a full index match.

Firebird allows character sets and collations to be declared in any character field or variable declaration. The default character set can also be specified at database create time, to cause every CHAR/VARCHAR declaration that does not specifically include a CHARACTER SET clause to use this default.

At attachment time you normally specify the character set that the client is to use to read strings. If no "client" (or "connection") character set is specified, character set NONE is assumed.

Two special character sets, NONE and OCTETS, can be used in declarations. However, OCTETS cannot be used as a connection character set. The two sets are similar, except that the space character of NONE is ASCII 0x20, whereas the space character OCTETS is 0x00. NONE and OCTETS are “special” in the sense that they follow different rules from those applicable to other character sets regarding conversions.

Enhancements that the new system brings include:

  isql -q -ch dos850
  SQL> create database 'test.fdb';
  SQL> create table t (c char(1) character set dos850);
  SQL> insert into t values ('a');
  SQL> insert into t values ('e');
  SQL> insert into t values ('á');
  SQL> insert into t values ('é');
  SQL>
  SQL> select c, upper(c) from t;

  C      UPPER
  ====== ======
       a      A
       e      E
       á      á
       é      é
        

  C      UPPER
  ====== ======
       a      A
       e      E
       á      Á
       é      É
        

  select blob_column from table
    where blob_column collate unicode = 'foo';
          

Character sets and collations are installed using a manifest file.

The manifest file should be put in the $rootdir/intl with a .conf extension. It is used to locate character sets and collations in the libraries. If a character set/collation is declared more than once, it is not loaded and the error is reported in the log.

The file /intl/fbintl.conf is an example of a manifest file. The following snippet is an excerpt from /intl/fbintl.conf:

   <intl_module fbintl>
       filename    $(this)/fbintl
   </intl_module>

   <charset ISO8859_1>
       intl_module    fbintl
       collation    ISO8859_1
       collation    DA_DA
       collation    DE_DE
       collation    EN_UK
       collation    EN_US
       collation    ES_ES
       collation    PT_BR
       collation    PT_PT
   </charset>

   <charset WIN1250>
       intl_module    fbintl
       collation    WIN1250
       collation    PXW_CSY
       collation    PXW_HUN
       collation    PXW_HUNDC
   </charset>
      

Two character sets introduced in Firebird 2 will be of particular interest if you have struggled with the shortcomings of UNICODE_FSS in past versions.

  isql -q -ch dos850
  SQL> create database 'test.fdb';
  SQL> create table t (c char(1) character set utf8);
  SQL> insert into t values ('a');
  SQL> insert into t values ('A');
  SQL> insert into t values ('á');
  SQL> insert into t values ('b');
  SQL> insert into t values ('B');
  SQL> select * from t order by c collate ucs_basic;

  C
  ======
  A
  B
  a
  b
  á

  SQL> select * from t order by c collate unicode;

  C
  ======
  a
  A
  á
  b
  B
        

The 2.1 release sees further capabilities implemented for

All non-wide and ASCII-based character sets present in ICU can be used by Firebird 2.1. To reduce the size of the distribution kit, we customize ICU to include only essential character sets and any for which there was a specific feature request.

If the character set you need is not included, you can replace the ICU libraries with another complete module, found at our site or already installed in your operating system.

<charset         NAME>
    intl_module     fbintl
    collation       NAME [REAL-NAME]
</charset>
          

<charset         GB>
    intl_module     fbintl
    collation       GB GB18030
</charset>
            

execute procedure sp_register_character_set ('GB', 4);
            

CREATE COLLATION <name>
    FOR <charset>
    [ FROM <base> | FROM EXTERNAL ('<name>') ]
    [ NO PAD | PAD SPACE ]
    [ CASE SENSITIVE | CASE INSENSITIVE ]
    [ ACCENT SENSITIVE | ACCENT INSENSITIVE ]
    [ '<specific-attributes>' ]
            

/* 1 */
CREATE COLLATION UNICODE_ENUS_CI
    FOR UTF8
    FROM UNICODE
    CASE INSENSITIVE
    'LOCALE=en_US';
/* 2 */
CREATE COLLATION NEW_COLLATION
    FOR WIN1252
    PAD SPACE;

/* NEW_COLLATION should be declared in .conf file
   in the $root/intl directory */
          

The UNICODE collations (case sensitive and case insensitive) can be applied to any character set that is present in fbintl. They are already registered in fbintl.conf, but you need to register them in the databases, with the desired associations and attributes.

Naming Conventions

The naming convention you should use is charset_collation. For example,

create collation win1252_unicode
  for win1252;

create collation win1252_unicode_ci
  for win1252
   from win1252_unicode
   case insensitive;
      

If your metadata text was created with non-ASCII encoding, you need to repair your database in order to read the metadata correctly after upgrading it to v.2.1.

[1] isql /path/to/your/database.fdb
[2] SQL> input '$fbroot$/misc/upgrade/metadata/metadata_charset_create.sql';
        

[1] isql /path/to/your/database.fdb
[2] SQL> select * from rdb$check_metadata;
        

[1] isql /path/to/your/database.fdb
[2] SQL> input '$fbroot$/misc/upgrade/metatdata/metadata_charset_create.sql';
[3] SQL> select * from rdb$fix_metadata('WIN1252');  -- replace WIN1252 by your charset
[4] SQL> commit;
        

[1] isql /path/to/your/database.fdb
[2] SQL> input '$fbroot$/misc/upgrade/metadata/metadata_charset_drop.sql';
        

The key term of the monitoring feature is an activity snapshot. It represents the current state of the database, comprising a variety of information about the database itself, active attachments and users, transactions, prepared and running statements, and more.

A snapshot is created the first time any of the monitoring tables is being selected from in the given transaction and it is preserved until the transaction ends, in order that multiple-table queries (e.g., master-detail ones) will always return a consistent view of the data.

In other words, the monitoring tables always behave like a snapshot table stability (“consistency”) transaction, even if the host transaction has been started with a lower isolation level.

To refresh the snapshot, the current transaction should be finished and the monitoring tables should be queried in a new transaction context.