A long-standing, legacy loophole in the handling of DPB parameters enabled ordinary users to make connection settings that could lead to database corruptions or give them access to SYSDBA-only operations. The loophole has been closed, a change that could affect several existing applications, database tools and connectivity layers (drivers, components). Details are in Chapter 3, Changes to the Firebird API and ODS.

A regression appeared after v.2.0.1, whereby events cannot work across the WNet protocol. A call to isc_que_events() will cause the server to crash. (Tracker ID CORE-1460).

Bugfix (CORE-1133) "The XNET (IPC) communication protocol would not work across session boundaries" affects those attempting to access databases using the local protocol on Windows Vista as well as those using remote terminal services locally on XP or Server 2003. This fix, done in v.2.0.1, should remove the problems encountered under these conditions.

An important reversion to 1.5 behaviour has occurred in sub-release 2.0.1, as follows:

In Firebird 2.0, a deliberate restriction was imposed to prevent anyone from dropping, altering or recreating a PSQL module if it had been used since the database was opened. An attempt to prepare the DDL statement would result in an “Object in Use” exception.

Many people complained that the restriction was unacceptable because they depended on performing these metadata changes “on the fly”. The restriction has therefore been removed. However, the reversion in no way implies that performing DDL on active PSQL modules is “safer” in Firebird 2.0.1 and higher than it was in V.1.5.

The Firebird Project has an integral user documentation project, a team of volunteers who are writing, editing and adapting user manuals, white papers and HowTos. At the time of this release, the hard-working coordinator of the Docs project is Paul Vinkenoog.

An index of available documents can be found in the on-line documentation index. Published docs currently include the essential Quick Start Guides for Firebird versions 1.5 and 2.0 in English and several other languages.

For the official documentation we use a Docbook XML format for sources and build PDF and HTML output using a suite of Java utilities customised for our purposes. These notes were developed and built under this system.

Paul Vinkenoog has written comprehensive, easy-to-follow manuals for writing Firebird documentation and for using our tools. You can find links to these manuals in the aforementioned index. New team members who want to do some writing or translating are always more than welcome. For information about the team's activities and progress you can visit the Docs Project's homepage. We have a lab forum for documenters and translators, firebird-docs, which you can join by visiting the Lists and Newsgroups page at the Firebird web site.

These release notes are your main documentation for Firebird 2. However, if you are unfamiliar with previous Firebird versions, you will also need the release notes for Firebird 1.5.3. For convenience, copies of both sets of release notes are included in the binary kits. They will be installed in the /doc directory beneath the Firebird root directory. Several other useful README documents are also installed there.

For future reference, if you ever need to get a copy of the latest release notes before beginning installation, you can download them from the Firebird web site. The link can be found on the same page that linked you to the binary kits, towards the bottom of the page.

If you do not own a copy of The Firebird Book, by Helen Borrie, then you will also need to find the beta documentation for InterBase® 6.0. It consists of several volumes in PDF format, of which the most useful will be the Language Reference (LangRef.pdf) and the Data Definition Guide (DataDef.pdf). The Firebird Project is not allowed to distribute these documents but they are easily found at several download sites on the Web using Google and the search key "LangRef.pdf". When you find one, you usually find them all!

--The Firebird Project

Firebird 2.0 has replaced the former implementation of the local transport protocol (often referred to as IPC or IPServer) with a new one, named XNET.

It serves exactly the same goal, to provide an efficient way to connect to server located on the same machine as the connecting client without a remote node name in the connection string. The new implementation is different and addresses the known issues with the old protocol.

Like the old IPServer implementation, the XNET implementation uses shared memory for inter-process communication. However, XNET eliminates the use of window messages to deliver attachment requests and it also implements a different synchronization logic.

WNET (a.k.a. NetBEUI) protocol no longer performs client impersonation.

In all previous Firebird versions, remote requests via WNET are performed in the context of the client security token. Since the server serves every connection according to its client security credentials, this means that, if the client machine is running some OS user from an NT domain, that user should have appropriate permissions to access the physical database file, UDF libraries, etc., on the server filesystem. This situation is contrary to what is generally regarded as proper for a client-server setup with a protected database.

Such impersonation has been removed in Firebird 2.0. WNET connections are now truly client-server and behave the same way as TCP ones, i.e., with no presumptions with regard to the rights of OS users.

BUGCHECK log messages now include file name and line number. (A. Brinkman)

Routines that print out various internal structures (DSQL node tree, BLR, DYN, etc) have been updated. (N. Samofatov)

Thread-safe and signal-safe debug logging facilities have been implemented. (N. Samofatov)

Syslog messages will be copied to the user's tty if a process is attached to it. (A. Peshkov)

From v.2.0.5 and v2.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:

#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
        

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++ = 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);
}
        

On-disk structure (ODS) changes include the following:

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

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

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

is implemented as a shortcut for

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

It returns the value of the first sub-expression if the given search condition evaluates to TRUE, otherwise it returns a value of the second sub-expression.

Example

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

The infamous “Datatype unknown” error (SF Bug #1371274) when attempting some castings has been eliminated. It is now possible to use CAST to advise the engine about the data type of a parameter.

Example

  SELECT CAST(? AS INT) FROM RDB$DATABASE
      

The built-in function SUBSTRING() can now take arbitrary expressions in its parameters.

Formerly, the inbuilt SUBSTRING() function accepted only constants as its second and third arguments (start position and length, respectively). Now, the arguments can be anything that resolves to a value, including host parameters, function results, expressions, subqueries, etc.

x = varchar(50);
substring(x from 1 for 1);
        

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
        

CROSS JOIN is now supported. Logically, this syntax pattern:

   A CROSS JOIN B
      

is equivalent to either of the following:

   A INNER JOIN B ON 1 = 1
      

or, simply:

  FROM A, B
      

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');
      

The RETURNING clause syntax has been implemented for the INSERT statement, enabling the return of a result set from the INSERT statement. The set contains the column values actually stored. Most common usage would be for retrieving the value of the primary key generated inside a BEFORE-trigger.

Available in DSQL and PSQL.

Syntax Pattern

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

Example(s)

1.

  INSERT INTO T1 (F1, F2)
    VALUES (:F1, :F2)
  RETURNING F1, F2 INTO :V1, :V2;
      

2.

 INSERT INTO T2 (F1, F2)
   VALUES (1, 2)
  RETURNING ID INTO :PK;
      

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

Examples

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
      

New flags are added to the header->pag_flags.

The flag btr_large_keys (32) is for storing compressed length/prefix and record-number. This meant also that length and prefix can be up to 1/4 of page-size (1024 for 4096 page-size) and is easy extensible in the future without changing disk-structure again.

Also the record-number can be easy extended to for example 40 bits. Those numbers are stored per 7-bits with 1 bit (highest) as marker (variable length encoding). Every new byte that needs to be stored is shifted by 7.

Examples

25 is stored as 1 byte 0x19, 130 = 2 bytes 0x82  0x01, 65535 = 3 bytes 0xFF  0xFF  0x03.

A new flag is also added for storing record-number on every node (non-leaf pages). This speeds up index-retrieval on many duplicates. The flag is  btr_all_recordnumber (16).

With this added information, key-lookup on inserts/deletes with many duplicates (NULLs in foreign keys, for example) becomes much faster (such as the garbage collection!).

Beside that duplicate nodes (length = 0) don't store their length information, 3 bits from the first stored byte are used to determine if this nodes is a duplicate.

Beside the ZERO_LENGTH (4) there is also END_LEVEL (1), END_BUCKET (2), ZERO_PREFIX_ZERO_LENGTH (3) and ONE_LENGTH (5) marker. Number 6 and 7 are reserved for future use.

A jump node is a reference to a node somewhere in the page.

It contains offset information about the specific node and the prefix data from the referenced node, but prefix compression is also done on the jump-nodes themselves.

Ideally a new jump node is generated after the first node that is found after every jumpAreaSize, but that's only the case on deactivate/active an index or inserting nodes in the same order as they will be stored in the index.

If nodes are inserted between two jump node references only the offsets are updated, but only if the offsets don't exceed a specific threshold (+/-10 %).

When a node is deleted only offsets are updated or a jump node is removed. This means a little hole can exist between the last jump node and the first node, so we don't waste time on generating new jump-nodes.

The prefix and length are also stored by variable length encoding.

Figure 8.3. Example data ((x) = size in x bytes)

Pointer after fixed header = 0x22

Pointer after jump info = 0x29

Pointer to first jump node = 0x29 + 6 (jump node 1) + 5 (jump node 2) = 0x34

Jump node 1 is referencing to the node that represents FIREBIRD as data, because this node has a prefix of 2 the first 2 characters FI are stored also on the jump node.

Our next jump node points to a node that represents FUEL with also a prefix of 2. Thus jump node 2 should contain FU, but our previous node already contained the F so, due to prefix compression, this one is ignored and only U is stored.

The data that needs to be stored is determined in the procedure compress() in btr.cpp.

For ASC (ascending) indexes no data will be stored (key is zero length). This will automatically put them as first entry in the index and thus correct order (For single field index node length and prefix is zero).

DESC (descending) indexes will store a single byte with the value 0xFF (255). To distinguish between a value (empty string can be 255) and an NULL state we insert a byte of 0xFE (254) at the front of the data. This is only done for values that begin with 0xFF (255) or 0xFE (254), so we keep the right order.

Figure 8.4. Examples

The following changes affect all databases.

        (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 under Firebird 2.

LOWER() returns the input argument converted to all lower-case characters.

Example

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>
C      LOWER
====== ======
A      a
E      e
Á      á
É      é
    

TRIM trims characters (default: blanks) from the left and/or right of a string.

Syntax Pattern

TRIM <left paren> [ [ <trim specification> ] [ <trim character> ]
  FROM ] <value expression> <right paren>

   <trim specification> ::=  LEADING  | TRAILING  | BOTH

   <trim character> ::= <value expression>
    

Rules

Examples

A)

  select
    rdb$relation_name,
    trim(leading 'RDB$' from rdb$relation_name)
  from rdb$relations
    where rdb$relation_name starting with 'RDB$';
    

B)

  select
    trim(rdb$relation_name) || ' is a system table'
  from rdb$relations
    where rdb$system_flag = 1;
    

Three new functions will return information about the size of strings:

Syntax Pattern

These three functions share a similar syntax pattern, as follows.-

<length function> ::=
{ BIT_LENGTH | CHAR_LENGTH | CHARACTER_LENGTH | OCTET_LENGTH } ( <value expression> <)
    

Example

  select
    rdb$relation_name,
    char_length(rdb$relation_name),
    char_length(trim(rdb$relation_name))
  from rdb$relations;
    

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 doesn't specifically included a CHARACTER SET clause to use it.

At attachment time you can 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 do not follow the rule that other charsets do regarding conversions.

Enhancements 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';
        

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

  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
        

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

  C
  ======
  A
  a
  á
  b

  SQL> select * from t where c = 'â';

  C
  ======
  a
  A
  â
        

The following bugs related to character sets and collations were fixed:

SF #1073212   An Order By on a big column with a COLLATE clause would terminate the server.

SF #939844   A query in a UNICODE database would throw a GDS Exception if it was longer than 263 characters.

SF #977785   Wrong character lengths were being returned from some multi-byte character sets (UTF-8, East-Asian charsets).

SF #536243   A correct result is now returned when the UPPER() function is applied to a UNICODE_FSS string.

SF #942726   UPPER did not convert aacute to Aacute for ISO8859_1

SF #544630   Some problems were reported when connecting using UNICODE.

SF #540547   Some problems involving concatenation, numeric fields and character set were fixed.

Unregistered bug   A query could produce different results, depending on the presence of an index, when the last character of the string was the first character of a compression pair.

Unregistered bug   SUBSTRING did not work correctly with a BLOB in a character set.

Unregistered bug   Pattern matching with multi-byte BLOBs was being performed in binary mode.

Unregistered bug   Connecting with a multi-byte character set was unsafe if the database had columns using a different character set.

The new security database is renamed as security2.fdb. Inside, the user authentication table, where user names and passwords are stored, is now called RDB$USERS. There is no longer a table named “users” but a new view over RDB$USERS that is named “USERS”. Through this view, users can change their passwords.

For details of the new database, see New Security Database in the section about authentication later in this chapter.

For instructions on updating previous security databases, refer to the section Dealing with the New Security Database at the end of this chapter.

Password encryption/decryption now uses a more secure password hash calculation algorithm.

The SYSDBA remains the keeper of the security database. However, users can now modify their own passwords.

gsec now uses the Services API. The server will refuse any access to security2.fdb except through the Services Manager.

Attempts to get access to the server using brute-force techniques on accounts and passwords are now detected and locked out.

Support for brute-force attack protection has been included in both the attachment functions of the Firebird API and the Services API. For more details, see Protection from Brute-force Hacking

Several known vulnerabilities in the API have been closed.

Firebird authentication checks a server-wide security database in order to decide whether a database or server connection request is authorised. The security database stores the user names and passwords of all authorised login identities.

Special measures were thus taken to make remote connection to the security database completely impossible. Don't be surprised if some old program fails on attempting direct access: this is by design. Users information may now be accessed only through the Services API and the equivalent internal access to services now implemented in the isc_user_* API functions.

Current high-speed CPUs and fast WAN connections make it possible to try to brute-force Firebird server users' passwords. This is especially dangerous for Superserver which, since Firebird 1.5, performs user authentication very fast. Classic is slower, since it has to create new process for each connection, attach to the security database within that connection and compile a request to the table RDB$USERS before validating login and password. Superserver caches the connection and request, thus enabling a much faster user validation.

Given the 8-byte maximum length of the traditional Firebird password, the brute-force hacker had a reasonable chance to break into the Firebird installation.

The v.2.0 Superserver has active protection to make a brute-force attack more difficult. After a few failed attempts to log in, the user and IP address are locked for a few seconds, denying any attempt to log in with that particular user name OR from that particular IP address for a brief period.

No setup or configuration is required for this feature. It is active automatically as soon as the Firebird 2.0 SuperServer starts up.

To do the upgrade, follow these steps:

Now you should be able to connect to the Firebird 2 server using your old logins and passwords.

  RDB$PASSWD RDB$PASSWD,
        

  RDB$PASSWD RDB$PASSWD NOT NULL,
        

Fast, on-line, page-level incremental backup facilities have been implemented.

The backup engine comprises two parts:

    nbackup <options>
          

   -L <database>   Lock database for filesystem copy
   -N <database>       Unlock previously locked database
   -F <database>              Fixup database after filesystem copy
   -B <level> <database> [<filename>]  Create incremental backup
   -R <database> [<file0> [<file1>...]]   Restore incremental backup
   -U <user>             User name
   -P <password>         Password
            

Content

  -RECREATE_DATABASE [OVERWRITE]
          

  -R[EPLACE_DATABASE]
          

The following command-line switches were added:

The following commands have been added or enhanced.

  SET HEADing [ON | OFF]
          

  SET TRANSACTION WAIT SNAPSHOT NO AUTO UNDO LOCK TIMEOUT 10
          

opt/firebird/bin] isql -?
Unknown switch: ?
usage:    isql [options] [<database>]
        -a(all)                 extract metadata incl. legacy non-SQL tables
        -b(ail)                 bail on errors (set bail on)
        -c(ache) <num>          number of cache buffers
        -ch(arset) <charset>    connection charset (set names)
        -d(atabase) <database>  database name to put in script creation
        -e(cho)                 echo commands (set echo on)
        -ex(tract)              extract metadata
        -i(nput) <file>         input file (set input)
        -m(erge)                merge standard error
        -m2                     merge diagnostic
        -n(oautocommit)         no autocommit DDL (set autoddl off)
        -now(arnings)           do not show warnings
        -o(utput) <file>        output file (set output)
        -pag(elength) <size>    page length
        -p(assword) <password>  connection password
        -q(uiet)                do not show the message "Use CONNECT..."
        -r(ole) <role>          role name
        -r2 <role>              role (uses quoted identifier)
        -sqldialect <dialect>   SQL dialect (set sql dialect)
        -t(erminator) <term>    command terminator (set term)
        -u(ser) <user>          user name
        -x                      extract metadata
        -z                      show program and server version
          

SF #910430     ISQL and database dialect

fixed by C. Valderrama, B. Rodriguez Somoza

What was fixed    When ISQL disconnected from a database, either by dropping it or by trying to connect to a non-existent database, it remembered the SQL dialect of the previous connection, which could lead to some inappropriate warning messages.

    ~ ~ ~     

SF #223126     Misplaced collation when extracting metadadata with ISQL

fixed by B. Rodriguez Somoza

    ~ ~ ~     

SF #223513     Ambiguity between tables and views

fixed by B. Rodriguez Somoza

    ~ ~ ~     

SF #518349     ISQL SHOW mangles relationship

fixed by B. Rodriguez Somoza

    ~ ~ ~     

Unregistered bug     Possible crashes with long terminators

fixed by C. Valderrama

    ~ ~ ~     

Unregistered bug     Avoided several SQL> prompts when using the INPUT command interactively.

implemented by C. Valderrama

    ~ ~ ~     

Unregistered bugs     Some memory leaks

fixed by C. Valderrama

    ~ ~ ~     

gsec now returns an error code when used as a non-interactive utility. Zero indicates success; any other code indicates failure.

The options for gfix -shut[down] have been extended to include two extra states or modes to govern the shutdown.

New Syntax Pattern

      gfix <command> [<state>] [<options>]

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

- “normal” state = online database

- “multi” state = multi-user shutdown mode (the legacy one, unlimited attachments of SYSDBA/owner are allowed)

- “single” state = single-user shutdown (only one attachment is allowed, used by the restore process)

- “full” state = full/exclusive shutdown (no attachments are allowed)

The modes can be switched sequentially:

Examples

  gfix -shut single -force 0
  gfix -shut full -force 0
  gfix -online single
  gfix -online
        

You cannot use -shut to bring a database one level “more online” and you cannot use -online to make a database more protected (an error will be thrown).

For example, these sequence-pairs are prohibited:

  gfix -shut single -force 0
  gfix -shut multi -force 0
::
  gfix -online
  gfix -online full
::
  gfix -shut -force 0
  gfix -online single
        

In previous versions, the external function rand() sets the random number generator's starting point based on the current time and then generates the pseudo-random value.

   srand((unsigned) time(NULL));
   return ((float) rand() / (float) RAND_MAX);
      

The problem with this algorithm is that it will return the same value for two calls done within a second.

To work around this issue, rand() was changed in Firebird 2.0 so that the starting point is not set explicitly. This ensures that different values will always be returned.

In order to keep the legacy behaviour available in case somebody needs it, srand() has been introduced. It does exactly the same as the old rand() did.

The function IB_UDF_lower() in the IB_UDF library might conflict with the new internal function lower(), if you try to declare it in a database using the ib_udf.sql script from a previous Firebird version.

  /* ib_udf.sql declaration that now causes conflict */
  DECLARE EXTERNAL FUNCTION lower
      CSTRING(255)
      RETURNS CSTRING(255) FREE_IT
      ENTRY_POINT 'IB_UDF_lower' MODULE_NAME 'ib_udf';
      

The problem will be resolved in the latest version of the new ib_udf2.sql script, where the old UDF is declared using a quoted identifier.

  /* New declaration in ib_udf2.sql */
  DECLARE EXTERNAL FUNCTION "LOWER"
    CSTRING(255) NULL
    RETURNS CSTRING(255) FREE_IT
    ENTRY_POINT 'IB_UDF_lower' MODULE_NAME 'ib_udf';
      

The FBUDF library no longer depends on FBCLIENT to be built.

When you attach to some database using multiple hosts in the connection string, only the last host in this list is the one that opens the database. The other hosts act as intermediate gateways on port gds_db. Previously, when working, this feature was available unconditionally. Now, it can be configured.

Remote redirection is turned off by default.

The option CreateInternalWindow is no longer required to run multiple server instances and it has been removed.

The DeadThreadsCollection parameter is no longer used at all. Dead threads are now efficiently released "on the fly", making configuration unnecessary. Firebird 2.0 silently ignores this parameter.

An important change has been done to prevent accidental database overwrites as the result of users mistakenly treating “-R” as an abbreviation for “restore”. gbak -R was formerly a shortcut for “-REPLACE_DATABASE”. Now the -R switch no longer restores a database by overwriting an existing one, but instead reports an error.

If you actually want the former behaviour, you have two alternatives:

The transport internals for the local protocol have been reimplemented (XNET instead of IPServer). With regard to the local protocol, the new client library is therefore incompatible with older servers and older client libraries are incompatible with the Firebird 2 servers.

If you need to use the local protocol, please ensure your server and client binaries have exactly the same version numbers.

WNET (a.k.a. NetBEUI, Named Pipes) protocol no longer performs client impersonation. For more information, refer to Change to WNET Protcol in the chapter about new features.

The optional switch -i[nteractive] has been implemented in instsvc.exe to enable an interactive mode for LocalSystem services.

For v.1.5, it was required (as Allow service to interact with desktop) to run the local IPC protocol, as it used a windows message to connect the server. In v.2.0, it is no longer necessary and the server itself does not need this option.

However, some custom UDFs may use the Win32 messaging facilities and this option allows them to work as expected.

On Windows, you have three server models to choose from: Superserver, Classic and Embedded Server. This means you have some decisions to make before installing Firebird 2.0.

If you already have an earlier version of Firebird or InterBase® on your server and you think you might want to go back to it, set up your fall-back position before you begin.

This is really the easy part: the actual install. Just run the executable and respond to the dialogs. After you have answered a few dialogs about licensing and installation notes, you should see one where you decide on the location of the Firebird root directory.

Next, you should see a screen where you choose the