This multi-threaded mode for Classic has been dubbed “Superclassic” for its capability to handle multiple worker threads—dedicated or pooled—inside a single server process. It shares all the usual Classic features, with a few differences:

instsvc install -multithreaded
            

fb_inet_server -a -m
            

The Classic server now detects immediately when a Classic process has been broken by a client disconnection. Its response is to terminate any pending activity, roll back the active transaction and close the network connection.

Tracker reference CORE-818.

Important optimizations include:

The root determination mechanism for the Windows embedded engine has been changed to avoid common problems that occur when an installation of the application structure encounters “DLL Hell”. Previously, the implicit root directory was the directory containing the user application's main executable file. Now it is the directory where the renamed fbembed.dll library is located.

Tracker reference CORE-1814.

Tracker reference CORE-1937.

When a string UDF is written to return a pointer not allocated by the same runtime as the Firebird server is accessing, the presence of the FREE_IT keyword in its declaration corrupts memory and crashes the server. As a safeguard against such dysfunctional UDFs, the engine now

Firebird 2.5 creates databases with an ODS (On-Disk Structure) version of 11.2

Previous versions had no way to interoperate with the character set(s) used by the operating system and its filesystem. Firebird 2.5 has been made “environmentally aware” with regard to the file names of databases and other files accessed through API connection requests, improving significantly its ability to accept and work with file names containing characters that are not in the ASCII subset.

Tracker reference CORE-1761.

A new client-side API function, fb_sqlstate() is available to convert the status vector item for an error into the corresponding SQL-2003 standard 5-alphanumeric SQLSTATE.

Appendix A: SQLSTATE provides a list of all SQLSTATE codes in use in this release, along with the corresponding message texts.

Tracker reference CORE-1741.

The new option DSQL_unprepare (numeric value 4) for the API routine isc_dsql_free_statement() allows the DSQL statement handle to survive the “unpreparing” of the statement.

Previously, the isc_dsql_free_statement() function supported only DSQL_close (for closing a named cursor) and DSQL_drop (which frees the statement handle).

The API addition is:

#define DSQL_close 1
#define DSQL_drop 2
#define DSQL_unprepare 4
        

New fb_cancel_operation() API call, allowing cancellation of the current activity being performed by some kind of blocking API call in the given connection.

Syntax

   ISC_STATUS fb_cancel_operation(ISC_STATUS* status_vector,
                                  isc_db_handle* db_handle,
                                  ISC_USHORT option);
        

Parameters

Thread A:
fb_cancel_operation(isc_status, &DB, fb_cancel_enable);
isc_dsql_execute_immediate(isc_status, &DB, &TR, 0, "long running statement", 3, NULL);
// waits for API call to finish...

		Thread B:
		fb_cancel_operation(local_status, &DB, fb_cancel_raise);

Thread A:
if (isc_status[1])
	isc_print_status(isc_status); // will print "operation was cancelled"
          

This release exposes a variety of API functions for instigating server shutdowns of various types from client applications.

   typedef int (*FB_SHUTDOWN_CALLBACK)(const int reason, const int mask, void* arg);

   int fb_shutdown(unsigned int timeout,
                   const int reason);

   ISC_STATUS fb_shutdown_callback(ISC_STATUS* status_vector,
                                   FB_SHUTDOWN_CALLBACK callback_function,
                                   const int mask,
                                   void* arg);
          

#include <ibase.h>

// callback function for shutdown
static int ignoreCtrlC(const int reason, const int, void*)
{
	return reason == fb_shutrsn_signal ? 1 : 0;
}

int main(int argc, char *argv[])
{
	ISC_STATUS_ARRAY status;
	if (fb_shutdown_callback(status, ignoreCtrlC, fb_shut_preproviders, 0))
	{
		isc_print_status(status);
		return 1;
	}
	// your code continues ...
}
            

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.

Five new services relating to the management of the new user trace sessions have been added to the Services Manager, each with its corresponding Services API action function.

  isc_spb_trc_name : trace session name, string, optional
  isc_spb_trc_cfg  : trace session configuration, string, mandatory
          

  isc_spb_trc_id : trace session ID, integer, mandatory
          

  isc_spb_trc_id : trace session ID, integer, mandatory
          

  isc_spb_trc_id : trace session ID, integer, mandatory
          

Other additions to the Services API include:

On POSIX hosts, the root user always had SYSDBA privileges, but the same was not possible for a domain administrator on Windows until Firebird 2.1. There, a configuration parameter, Authentication, was introduced whereby a user logged in as a Windows domain administrator could automatically gain server access with SYSDBA privileges through trusted user authentication. The mechanism for achieving that has changed with the introduction of the new system role and associated behaviour in v.2.5.

ALTER ROLE RDB$ADMIN
  SET AUTO ADMIN MAPPING;
            

ALTER ROLE RDB$ADMIN
  DROP AUTO ADMIN MAPPING;
            

The new trace and audit facilities enable various events performed inside the engine, such as statement execution, connections, disconnections, etc., to be logged and collated for real-time analysis of the corresponding performance characteristics.

A trace takes place in the context of a trace session. Each trace session has its own configuration, state and output.

The Firebird engine has a fixed list of events it can trace. It can perform two different sort of traces: a system audit and a user trace. How the engine forms the list of events for a session depends on which sort of trace is requested.

A system audit session is started by the engine itself. To determine which events the session is “interested in”, it reads the contents of a trace configuration file as it goes to create the session.

A new parameter in firebird.conf, AuditTraceConfigFile points to the name and location of the file. There can be at most one system audit trace in progress. By default, the value of this parameter is empty, indicating that no system audit tracing is configured.

A user trace session is managed by user, using some new calls to the Services API. There are five new service functions for this purpose:

The syntax for the Services API calls are discussed in the topic New Trace Functions for Applications in the chapter entitled Changes to the Firebird API and ODS.

There are three general use cases:

The original design allowed non-privileged database users to see monitoring information pertaining only to their CURRENT_CONNECTION. Now they can request information for any attachment that was authenticated using the same user name.

Tracker reference CORE-2233.

Examples

“Top 10” statements ranked according to their memory usage:

SELECT FIRST 10
  STMT.MON$ATTACHMENT_ID,
  STMT.MON$SQL_TEXT,
  MEM.MON$MEMORY_USED
FROM MON$MEMORY_USAGE MEM
  NATURAL JOIN MON$STATEMENTS STMT
  ORDER BY MEM.MON$MEMORY_USED DESC
        

To enumerate all session-level context variables for the current connection:

SELECT
  VAR.MON$VARIABLE_NAME,
  VAR.MON$VARIABLE_VALUE
FROM MON$CONTEXT_VARIABLES VAR
  WHERE VAR.MON$ATTACHMENT_ID = CURRENT_CONNECTION
        

DELETE FROM MON$STATEMENTS
  WHERE MON$ATTACHMENT_ID = 32
          

DELETE FROM MON$ATTACHMENTS
  WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION
          

In V.2.1, members of administrative Windows groups were mapped to SYSDBA by default. From V.2.5 forward, SYSDBA mapping is controlled on per-database basis using the new SQL command

ALTER ROLE RDB$ADMIN SET/DROP AUTO ADMIN MAPPING

Tracker reference CORE-2052.

One such change addressed the problem of the visibility of altered stored procedures to other connections to the Classic server. Now, such changes are made visible to the entire server as soon as the modifying transaction has completed its commit.

Tracker reference CORE-696.

In v.2.5, Firebird finally has syntax to enable user accounts on the server to be managed by submitting SQL statements when logged in to a regular database.

Syntax Patterns

A user with SYSDBA privileges can add a new user:

CREATE USER <username> {PASSWORD 'password'}
  [FIRSTNAME 'firstname']
  [MIDDLENAME 'middlename']
  [LASTNAME 'lastname'];
      

A user with SYSDBA privileges can change one or more of the password and proper name attributes of an existing user. Non-privileged users can use this statement to alter only their own attributes.

ALTER USER <username>
  [PASSWORD 'password']
  [FIRSTNAME 'firstname']
  [MIDDLENAME 'middlename']
  [LASTNAME 'lastname'];
      

A user with SYSDBA privileges can delete a user:

DROP USER <username>;
      

Restrictions

CREATE and DROP statements are available only for the SYSDBA or a user that has been granted the RDB$ADMIN role in the security database. An ordinary user can ALTER his own password and elements of his proper name. An attempt to modify another user will fail.

Examples

CREATE USER alex PASSWORD 'test';

ALTER USER alex FIRSTNAME 'Alex' LASTNAME 'Peshkov';

ALTER USER alex PASSWORD 'IdQfA';

DROP USER alex;
      

Previously, in order to alter a view definition, it was necessary to save the view definition off-line somewhere and drop the view, before recreating it with its changes. This made things very cumbersome, especially if there were dependencies. V.2.5 introduces syntaxes for ALTER VIEW and CREATE OR ALTER VIEW.

Tracker references are CORE-770 and CORE-1640.

Syntax Pattern

create [ or alter ] | alter } view <view_name>
  [ ( <field list> ) ]
as <select statement>
      

Example

create table users (
    id integer,
    name varchar(20),
    passwd varchar(20)
);

create view v_users as
    select name from users;

alter view v_users (id, name) as
    select id, name from users;
      

Tracker reference CORE-886.

A selectable stored procedure can now be specified in the FROM clause of a view definition.

Example

    create view a_view as
    select * from a_procedure(current_date);
      

Tracker reference CORE-1454.

A column defined as COMPUTED BY <expression> can now be altered using the ALTER TABLE...ALTER COLUMN syntax. This feature can be used only to change the <expression> element of the column definition to a different expression. It cannot convert a computed column to non-computed or vice versa.

Syntax Pattern

alter table <table-name>
  alter <computed-column-name>
  [type <data-type>]
  COMPUTED BY (<expression>);
      

Examples

create table test (
  n integer,
  dn computed by (n * 2)
);
commit;
alter table test
  alter dn computed by (n + n);
      

The following extensions have been implemented in the area of SQL permissions (privileges).

grant <right> to <object>
  [ { granted by | as } [ user ] <username> ]
--
revoke <right> from <object>
  [ { granted by | as } [ user ] <username> ]
        

create role r1; -- SYSDBA owns the role
  /* next, SYSDBA grants the role to user1
     with the power to grant it to others */
grant r1 to user1 with admin option;
  /* SYSDBA uses GRANTED BY to exercise
     user1's ADMIN OPTION */
grant r1 to public granted by user1;
      

REVOKE ALL ON ALL FROM { <user list> | <role list> }
        

# gsec -del guest
# isql employee
fbs bin # ./isql employee
Database:  employee
SQL> REVOKE ALL ON ALL FROM USER guest;
SQL>
        

Tracker references CORE-1737 and CORE-1803.

An ODS 11.2 or higher database can now have a default COLLATION attribute associated with the default character set, enabling all text column, domain and variable definitions to be created with the same collation order unless a COLLATE clause for a different collation is specified.

The COLLATION clause is optional. If it is omitted, the default collation order for the character set is used.

Syntax Pattern

create database <file name>
    [ page_size <page size> ]
    [ length = <length> ]
    [ user <user name> ]
    [ password <user password> ]
    [ set names <charset name> ]
    [ default character set <charset name>
      [ collation <collation name> ] ]
    [ difference file <file name> ]
      

Example

create database 'test.fdb'
    default character set win1252
    collation win_ptbr;
      

Tracker reference CORE-1803.

New syntax introduced in this version, enabling the default collation for a character set to be set for a database.

The default collation is used when table columns are created with a given character set (explicitly, through a CHARACTER SET clause in the column or domain definition, or implicitly, through the default character set attribute of the database) and no COLLATION clause is specified.

Syntax Pattern

ALTER CHARACTER SET <charset_name>
    SET DEFAULT COLLATION <collation_name>
      

Example

create database 'people.fdb'
  default character set win1252;

alter character set win1252
  set default collation win_ptbr;

create table person (
  id integer,
  name varchar(50)  /* will use the database default
                       character set and the win1252
                       default collation */
);

insert into person
  values (1, 'adriano');
insert into person
  values (2, 'ADRIANO');

/* will retrieve both records
   because win_ptbr is case insensitive */
select * from person where name like 'A%';
      

Tracker reference CORE-769.

A new SIMILAR TO predicate is introduced to support regular expressions. The predicate's function is to verify whether a given SQL-standard regular expression matches a string argument. It is valid in any context that accepts Boolean expressions, such as WHERE clauses, CHECK constraints and PSQL IF() tests.

Syntax Patterns

<similar predicate> ::=
	<value> [ NOT ] SIMILAR TO <similar pattern> [ ESCAPE <escape character> ]

<similar pattern> ::= <character value expression: regular expression>

<regular expression> ::=
      <regular term>
    | <regular expression> <vertical bar> <regular term>

<regular term> ::=
      <regular factor>
    | <regular term> <regular factor>

<regular factor> ::=
      <regular primary>
    | <regular primary> <asterisk>
    | <regular primary> <plus sign>
    | <regular primary> <question mark>
    | <regular primary> <repeat factor>

<repeat factor> ::=
    <left brace> <low value> [ <upper limit> ] <right brace>

<upper limit> ::= <comma> [ <high value> ]

<low value> ::= <unsigned integer>

<high value> ::= <unsigned integer>

<regular primary> ::=
      <character specifier>
    | <percent>
    | <regular character set>
    | <left paren> <regular expression> <right paren>

<character specifier> ::=
      <non-escaped character>
    | <escaped character>

<regular character set> ::=
      <underscore>
    | <left bracket> <character enumeration>... <right bracket>
    | <left bracket> <circumflex> <character enumeration>... <right bracket>
    | <left bracket> <character enumeration include>... <circumflex> <character enumeration exclude>... 
        <right bracket>

<character enumeration include> ::= <character enumeration>

<character enumeration exclude> ::= <character enumeration>

<character enumeration> ::=
      <character specifier>
    | <character specifier> <minus sign> <character specifier>
    | <left bracket> <colon> <character class identifier> <colon> <right bracket>

<character specifier> ::=
      <non-escaped character>
    | <escaped character>

<character class identifier> ::=
      ALPHA
    | UPPER
    | LOWER
    | DIGIT
    | SPACE
    | WHITESPACE
    | ALNUM
      

Usage Guide

Return true for a string that matches <regular expression> or <regular term>:

      <regular expression> <vertical bar> <regular term>
       

    'ab' SIMILAR TO 'ab|cd|efg'   -- true
    'efg' SIMILAR TO 'ab|cd|efg'  -- true
    'a' SIMILAR TO 'ab|cd|efg'    -- false
       

Match zero or more occurrences of <regular primary>: <regular primary> <asterisk>

    '' SIMILAR TO 'a*'     -- true
    'a' SIMILAR TO 'a*'    -- true
    'aaa' SIMILAR TO 'a*'  -- true
       

Match one or more occurrences of <regular primary>: <regular primary> <plus sign>

    '' SIMILAR TO 'a+'     -- false
    'a' SIMILAR TO 'a+'    -- true
    'aaa' SIMILAR TO 'a+'  -- true
       

Match zero or one occurrence of <regular primary>: <regular primary> <question mark>

    '' SIMILAR TO 'a?'     -- true
    'a' SIMILAR TO 'a?'    -- true
    'aaa' SIMILAR TO 'a?'  -- false
       

Match exact <low value> occurrences of <regular primary>: <regular primary> <left brace> <low value> <right brace>

    '' SIMILAR TO 'a{2}'     -- false
    'a' SIMILAR TO 'a{2}'    -- false
    'aa' SIMILAR TO 'a{2}'   -- true
    'aaa' SIMILAR TO 'a{2}'  -- false
       

Match <low value> or more occurrences of <regular primary>: <regular primary> <left brace> <low value> <comma> <right brace>:

    '' SIMILAR TO 'a{2,}'     -- false
    'a' SIMILAR TO 'a{2,}'    -- false
    'aa' SIMILAR TO 'a{2,}'   -- true
    'aaa' SIMILAR TO 'a{2,}'  -- true
       

Match <low value> to <high value> occurrences of <regular primary> <regular primary> <left brace> <low value> <comma> <high value> <right brace>:

    '' SIMILAR TO 'a{2,4}'       -- false
    'a' SIMILAR TO 'a{2,4}'      -- false
    'aa' SIMILAR TO 'a{2,4}'     -- true
    'aaa' SIMILAR TO 'a{2,4}'    -- true
    'aaaa' SIMILAR TO 'a{2,4}'   -- true
    'aaaaa' SIMILAR TO 'a{2,4}'  -- false
       

Match any (non-empty) character: <underscore>

    '' SIMILAR TO '_'    -- false
    'a' SIMILAR TO '_'   -- true
    '1' SIMILAR TO '_'   -- true
    'a1' SIMILAR TO '_'  -- false
       

Match a string of any length (including empty strings): <percent>

    '' SIMILAR TO '%'         -- true
    'az' SIMILAR TO 'a%z'     -- true
    'a123z' SIMILAR TO 'a%z'  -- true
    'azx' SIMILAR TO 'a%z'    -- false
       

Group a complete <regular expression> to use as one single <regular primary> as a sub-expression: <left paren> <regular expression> <right paren>

    'ab' SIMILAR TO '(ab){2}'    -- false
    'aabb' SIMILAR TO '(ab){2}'  -- false
    'abab' SIMILAR TO '(ab){2}'  -- true
       

Match a character identical to one of <character enumeration>: <left bracket> <character enumeration>... <right bracket>

    'b' SIMILAR TO '[abc]'    -- true
    'd' SIMILAR TO '[abc]'    -- false
    '9' SIMILAR TO '[0-9]'    -- true
    '9' SIMILAR TO '[0-8]'    -- false
       

Match a character not identical to one of <character enumeration>: <left bracket> <circumflex> <character enumeration>... <right bracket>

    'b' SIMILAR TO '[^abc]'    -- false
    'd' SIMILAR TO '[^abc]'    -- true
       

Match a character identical to one of <character enumeration include> but not identical to one of <character enumeration exclude>: <left bracket> <character enumeration include>... <circumflex> <character enumeration exclude>...

    '3' SIMILAR TO '[[:DIGIT:]^3]'    -- false
    '4' SIMILAR TO '[[:DIGIT:]^3]'    -- true
       

Match a character identical to one character included in <character class identifier>. Refer to the table of Character Class Identifiers, above. May be used with <circumflex> to invert the logic (see above): <left bracket> <colon> <character class identifier> <colon> <right bracket>

    '4' SIMILAR TO '[[:DIGIT:]]'  -- true
    'a' SIMILAR TO '[[:DIGIT:]]'  -- false
    '4' SIMILAR TO '[^[:DIGIT:]]' -- false
    'a' SIMILAR TO '[^[:DIGIT:]]' -- true
       

Examples

create table department (
  number numeric(3) not null,
  name varchar(25) not null,
  phone varchar(14) 
  check (phone similar to '\([0-9]{3}\) [0-9]{3}\-[0-9]{4}' escape '\')
);

insert into department 
  values ('000', 'Corporate Headquarters', '(408) 555-1234');
insert into department 
  values ('100', 'Sales and Marketing', '(415) 555-1234');
insert into department 
  values ('140', 'Field Office: Canada', '(416) 677-1000');

insert into department
  values ('600', 'Engineering', '(408) 555-123'); -- check constraint violation

select * from department
  where phone not similar to '\([0-9]{3}\) 555\-%' escape '\';
      

Tracker reference CORE-1760.

Support for hexadecimal numeric and binary string literals has been introduced.

Syntax Patterns

<numeric hex literal> ::=
    { 0x | 0X } <hexit> [ <hexit>... ]

<binary string literal> ::=
    { x | X } <quote> [ { <hexit> <hexit> }... ] <quote>

<digit> ::=
    0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

<hexit> ::=
    <digit> | A | B | C | D | E | F | a | b | c | d | e | f
      

Examples

select 0x10, cast('0x0F0000000' as bigint)
  from rdb$database;
select x'deadbeef'
  from rdb$database;
      

Tracker references CORE-1656 and CORE-1682.

Two new built-in functions, UUID_TO_CHAR and CHAR_TO_UUID, enable conversion between a UUID in the form of a CHAR(16) OCTETS string and the RFC4122-compliant form.

CHAR_TO_UUID( <string> )
        

select char_to_uuid('93519227-8D50-4E47-81AA-8F6678C096A1')
  from rdb$database;
        

UUID_TO_CHAR( <string> )
        

select uuid_to_char(gen_uuid())
  from rdb$database;
        

Tracker reference CORE-2298

By popular request, particularly from Delphi programmers, support has been implemented for a predication that is able to “OR” test both the equivalence between a column and a parameter and whether the value passed to the parameter is NULL. This construct is often desired as a way to avoid the need to prepare one query to request a filtered result set and another for the same query without the filter.

Users of Delphi and other programming interfaces that apply client-side object names to parameters wanted the ability for the DSQL engine to recognise a usage like the following:

  WHERE col1 = :param1 OR :param1 IS NULL
      

At the API level, the language interface translates the query to

  WHERE col1 = ? OR ? IS NULL
      

That presented two problems:

What was needed to solve this problem was to introduce a new data type to handle the “? is NULL” condition and teach Firebird to do the right thing when it received such a request.

The implementation works like this. To resolve the first problem, the request must supply two parameters (for our Delphi example):

  WHERE col1 = :param1 OR :param2 IS NULL
      

In other words, for the parameter ( ? ) in ? IS NULL

Tracker reference CORE-1453

A string expression is now allowed as the delimiter argument of the LIST() function.

Example

SELECT
  DISCUSSION_ID,
  LIST(COMMMENT, ASCII_CHAR(13))
FROM COMMENTS
  GROUP BY DISCUSSION_ID;
      

Changes in optimizer logic that address recognised problems include:

Other changes to smooth out the little annoyances include:

If there is no ON EXTERNAL DATA SOURCE clause present, EXECUTE STATEMENT is normally executed within the CURRENT_CONNECTION context. This will be the case if the AS USER clause is omitted, or it is present with its <user_name> argument equal to CURRENT_USER.

However, if <user_name> is not equal to CURRENT_USER, then the statement is executed in a separate connection, established without Y-Valve and remote layers, inside the same engine instance.

Tracker reference CORE-1853.

EXECUTE STATEMENT now supports queries against external databases by inclusion of the ON EXTERNAL DATA SOURCE clause with its <connection_string> argument.

   [<host_name><protocol_delimiter>]database_path
        

Tracker reference CORE-1221.

The new extensions provide the ability to prepare a statement with dynamic input parameters (placeholders) in a manner similar to a parameterised DSQL statement. The actual text of the query itself can also be passed as a parameter.

    <input_parameters> ::=
      <named_parameter> | <input_parameters>, <named_parameter>

    <named_parameter> ::=
      <parameter name> := <expression>
          

EXECUTE BLOCK AS
  DECLARE S VARCHAR(255);
  DECLARE N INT = 100000;
  BEGIN
  /* Normal PSQL string assignment of <query_text> */
    S = 'INSERT INTO TTT VALUES (:a, :b, :a)';

    WHILE (N > 0) DO
    BEGIN
    /* Each loop execution applies both the string value
       and the values to be bound to the input parameters */

      EXECUTE STATEMENT (:S) (a := CURRENT_TRANSACTION, b := CURRENT_CONNECTION)
      WITH COMMON TRANSACTION;
      N = N - 1;
    END
  END
          

EXECUTE BLOCK AS
  DECLARE S VARCHAR(255);
  DECLARE N INT = 100000;
  BEGIN
    S = 'INSERT INTO TTT VALUES (?, ?, ?)';

    WHILE (N > 0) DO
    BEGIN
      EXECUTE STATEMENT (:S) (CURRENT_TRANSACTION, CURRENT_CONNECTION, CURRENT_TRANSACTION);
      N = N - 1;
    END
  END
          

    EXECUTE STATEMENT (:sql) (p1 := 'abc', p2 := :second_param) ...
            

The following examples offer a sampler of ways that the EXECUTE STATEMENT extensions might be applied in your applications.

EXECUTE BLOCK
  RETURNS (CONN INT, TRAN INT, DB VARCHAR(255))
AS
  DECLARE I INT = 0;
  DECLARE N INT = 3;
  DECLARE S VARCHAR(255);
BEGIN
  SELECT A.MON$ATTACHMENT_NAME FROM MON$ATTACHMENTS A
   WHERE A.MON$ATTACHMENT_ID = CURRENT_CONNECTION
    INTO :S;

  WHILE (i < N) DO
  BEGIN
    DB = TRIM(CASE i - 3 * (I / 3)
      WHEN 0 THEN '\\.\' WHEN 1 THEN 'localhost:' ELSE '' END) || :S;

    FOR EXECUTE STATEMENT
      'SELECT CURRENT_CONNECTION, CURRENT_TRANSACTION
      FROM RDB$DATABASE'
      ON EXTERNAL :DB
      AS USER CURRENT_USER PASSWORD 'masterkey' -- just for example
      WITH COMMON TRANSACTION
      INTO :CONN, :TRAN
    DO SUSPEND;

    i = i + 1;
  END
END
          

EXECUTE BLOCK
  RETURNS (CONN INT, TRAN INT, DB VARCHAR(255))
AS
  DECLARE I INT = 0;
  DECLARE N INT = 3;
  DECLARE S VARCHAR(255);
BEGIN
  SELECT A.MON$ATTACHMENT_NAME
    FROM MON$ATTACHMENTS A
  WHERE A.MON$ATTACHMENT_ID = CURRENT_CONNECTION
   INTO :S;

  WHILE (i < N) DO
  BEGIN
    DB = TRIM(CASE i - 3 * (I / 3)
      WHEN 0 THEN '\\.\'
      WHEN 1 THEN 'localhost:'
      ELSE '' END) || :S;

    FOR EXECUTE STATEMENT
    'SELECT CURRENT_CONNECTION, CURRENT_TRANSACTION FROM RDB$DATABASE'
      ON EXTERNAL :DB
      WITH AUTONOMOUS TRANSACTION -- note autonomous transaction
      INTO :CONN, :TRAN
    DO SUSPEND;

    i = i + 1;
  END
END
          

EXECUTE BLOCK
  RETURNS (A INT, B INT, C INT)
AS
BEGIN
  EXECUTE STATEMENT (
    'SELECT CAST(:X AS INT),
            CAST(:X AS INT),
            CAST(:X AS INT)
       FROM RDB$DATABASE')
      (x := GEN_ID(G, 1))
    INTO :A, :B, :C;

  SUSPEND;
END
        

RECREATE TABLE TTT (
  TRAN INT,
  CONN INT,
  ID INT);

-- Direct inserts:

EXECUTE BLOCK AS
  DECLARE N INT = 100000;
BEGIN
  WHILE (N > 0) DO
  BEGIN
    INSERT INTO TTT VALUES (CURRENT_TRANSACTION, CURRENT_CONNECTION, CURRENT_TRANSACTION);
    N = N - 1;
  END
END

-- Inserts via prepared dynamic statement
-- using named input parameters:

EXECUTE BLOCK AS
  DECLARE S VARCHAR(255);
  DECLARE N INT = 100000;
BEGIN
  S = 'INSERT INTO TTT VALUES (:a, :b, :a)';

  WHILE (N > 0) DO
  BEGIN
    EXECUTE STATEMENT (:S)
      (a := CURRENT_TRANSACTION, b := CURRENT_CONNECTION)
    WITH COMMON TRANSACTION;
    N = N - 1;
  END
END

-- Inserts via prepared dynamic statement
-- using unnamed input parameters:

EXECUTE BLOCK AS
DECLARE S VARCHAR(255);
DECLARE N INT = 100000;
BEGIN
  S = 'INSERT INTO TTT VALUES (?, ?, ?)';

  WHILE (N > 0) DO
  BEGIN
    EXECUTE STATEMENT (:S) (CURRENT_TRANSACTION, CURRENT_CONNECTION, CURRENT_TRANSACTION);
    N = N - 1;
  END
END
        

Tracker reference CORE-1600.

Malformed characters are no longer allowed in data for UNICODE_FSS columns.

New restore switches were added to the gbak utility code for the purpose of repairing malformed UNICODE_FSS data and metadata by restoring a backup of the affected database. Details are in the gbak section of the Utilities chapter.

Tracker reference: CORE-1945)

For UNICODE collations only, a custom attribute NUMERIC-SORT has been enabled for specifying the order by which to sort numerals.

Format & Usage

The default, 0, sorts numerals in alphabetical order. For example:

    1
    10
    100
    2
    20
      

1 sorts numerals in numerical order. For example:

    1
    2
    10
    20
    100
      

Example

create collation unicode_num for utf8
from unicode 'NUMERIC-SORT=1';
      

Firebird 2.5 introduces the new switch -fet[ch_password] as an optional replacement for -pa[ssword] for all command-line utilities that take a password for authentication purposes. The switch may be progressively abbreviated from the right, conforming to the established rules.

  isql -user sysdba -fet passfile server:employee
        

  isql -user sysdba -fet stdin server:employee
        

  Enter password:
        

    -fetch /dev/tty
          

  bunzip2 -c emp.fbk.bz2 | gbak -c stdin /db/new.fdb
     -user sysdba -fetch /dev/tty
          

The new -mapping switch is used to enable or disable the OS user's association with the RDB$ADMIN role in the security database. Its format is:

  mapping {set|drop}
      

The corresponding new tag items for the Service Parameter Block are isc_action_svc_set_mapping and isc_action_svc_drop_mapping. The appropriate support for these tag items has been added to the fbsvcmgr utility.

Tracker reference CORE-1831.

The gbak utility has two new restore switches intended to repair malformed UNICODE_FSS character data and metadata, respectively, when restoring the backup of an affected database.

Switch Syntax

  -FIX_FSS_D(ATA) <charset> -- fix malformed UNICODE_FSS data
  -FIX_FSS_M(ETADATA) <charset> -- fix malformed UNICODE_FSS metadata
      

An improvement allows the current value of RDB$DEFAULT_COLLATE_NAME in the system table RDB$CHARACTER_SETS to survive the backup/restore cycle.

Tracker reference CORE-789.

isql now returns the SQLSTATE completion code in diagnostics, instead of the now deprecated SQLCODE. For more information, see the topic Support for SQLSTATE Completion Codes in the chapter Changes to the Firebird API and ODS.

Tracker reference CORE-1527.

GPRE now supports the IS NOT DISTINCT predicate and CASE/NULLIF/COALESCE/SUBSTRING functions, as well as the whole set of CURRENT_* context variables.

Firebird 2.5 is built by the Microsoft MSVC8 compiler in Visual Studio 2005. Because all the Firebird binaries are built to use dynamic linking, they all require run-time libraries.

To avoid the dll-hell issue Microsoft introduced new rules for the distribution of components that may be shared by multiple applications. From Windows XP forward, shared libraries—such as the Visual C++ and Visual C runtimes msvcp80.dll, msvcr80.dll and mscvcm80.dll—must be distributed as shared or as private assemblies.

Some new reserved keywords are introduced. The full list is available in the chapter New Reserved Words and Changes. Any identifiers using those words in DSQL statements or PSQL code will need to be changed, either by renaming the identifiers concerned or, for dialect 3 databases, by double-quoting them.

Some changes will now cause exceptions during run-time execution of queries, including those that are run during the execution of the gbak utility code (backups and restores).

Because v.2.5 maintains separate lock structures for each database on the server, fb_lock_print now requires a database path name in order to print the lock table. Include the new switch -d <path name> in the command line to specify the filesystem path to the database you wish to analyse.

The API functions isc_start_transaction() and isc_start_multiple() will now reject combinations of transaction parameter buffer (TPB) items that do not “belong together”.

For example, a non-zero wait timeout is inconsistent with the no wait option; and no record version is inconsistent with any transaction isolation mode other than ReadCommitted. Now, instead of making some arbitrary (and possibly incorrect) assumption about the inherent ambiguities, the engine will reject such combinations as invalid.

For more information, see the topic Transaction Diagnostics in the chapter Changes in the Firebird Engine.

New SQL_NULL constant was introduced to enable the predication OR ? IS NULL to be recognised and processed with the expected outcome and without engendering the “Data type unknown” exception. This affects how the XSQLVAR structures are populated for such queries. For information, refer to the topic SOME_COL = ? OR ? IS NULL Predication in the DML chapter.

In V.2.1, members of administrative Windows groups were mapped to SYSDBA by default. From V.2.5 forward, SYSDBA mapping is controlled on per-database basis using the new SQL command

ALTER ROLE RDB$ADMIN SET/DROP AUTO ADMIN MAPPING

For more details, refer to the chapter on Security.

In V.2.1, where support for Windows trusted authentication was introduced, the default authentication method applied was mixed, i.e., the DPB or SPB would accept either native Firebird logins or trusted user logins. Thus, he Authentication parameter in firebird.conf showed mixed as the default.

From V.2.5 forward, the default is native. To have mixed or trusted, it is now necessary to configure this parameter specifically.

Tracker reference CORE-2376)

(CORE-2389)     Wrong matching of SIMILAR TO expression with brackets.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2356)     On Windows the listener process of Classic Server was unable to create the necessary resources after restart if any worker process was present.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2355)     Incorrect handling of LOWER/UPPER when result string shrinks in terms of byte length.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2351)     It was not possible to create a database whose <file specification> was an alias, even though the alias existed.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2349)     The “Invalid SQLDA” error was being falsely thrown.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2348)     More database corruption problems showed up resulting from transaction numbers overflowing 32-bit signed integer.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2347)     “Deadlock. Page type <N> lock conversion denied” errors were reported under a concurrent “connect-work-disconnect” style of load.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2341)     Hidden variables could conflict with output parameters, causing assertions, unexpected errors or even incorrect results.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2340)     Bugcheck 258 (page slot not empty) could occur under high concurrent load.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2339)     Incorrect results were being returned for derived expressions based on aggregation and computation.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2265)     Grouping by function was not working properly.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2320)     A complex recursive query did not always return all rows.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2317)     select * from (select cast(.... was returning null.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2313)     INF_* functions could invalidate the whole output buffer with isc_info_truncated at the beginning, due to a boundary condition.

fixed by C. Valderrama

    ~ ~ ~     

(CORE-2311)     A WITH RECURSIVE query could cause memory leakage.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2308)     SIMILAR TO was producing random results with [x-y] expressions.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2300)     The second evaluation of SUBSTRING() would throw an unexpected arithmetic exception, numeric overflow, or string truncation error.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2293)     The wrong dependent object type (RELATION) was being stored in RDB$DEPENDENCIES for views.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2289)     The wrong name was being reported for the referenced primary key when a foreign key violation occurred during foreign key creation.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2264)     Doing ALTER DOMAIN on a domain with dependencies could leave a transaction handle in an inconsistent state and cause segmentation faults.

fixed by A. dos Santos Fernandes, D. Yemanov

    ~ ~ ~     

(CORE-2258)     Selecting UPPER (<blob>) from a UNION was causing an internal error.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2257)     The error Bugcheck 167 (invalid send request) could occur while altering dependent procedures.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2255)     The error String right truncation could occur while altering a view with a join.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2242)     The engine was incorrectly populating integer containers in the blob parameter buffer (BPB) with integers in machine-local format, causing errors on Big Endian platforms.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2241)     If an ALTER TABLE ALTER COLUMN.. operation was performed on a table in the course of a bulk insert operation, minor index corruption could occur causing subsequent queries to return the wrong number of records. The bug was traced to legacy code in BTR\compress_root().

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2237)     An unresolved assertion was exhibited at src\jrd\intl.cpp, line 569.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2231)     A Bugcheck 179 (decompression overran buffer) was thrown when the v.2.5 server attempted to read from the table RDB$TRIGGER_MESSAGES in a ODS 10.x database.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2230)     Input parameters for EXECUTE BLOCK were not being domain-checked.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2202)     RDB$VIEW_RELATIONS was not being cleaned up when ALTER VIEW was processed.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2186)     In the Windows embedded server, fbintl.dll was not being unloaded after the isc_dsql_execute_immediate() during the processing for CREATE DATABASE.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2183)     An error was being thrown when a server shutdown was started while an “execute statement” request was open.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2182)     It was not possible to drop an existing UDF whose name was duplicated by the name of a new built-in function.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2179)     Deadlocks were exhibited when trying to shut down the server whilst an “execute statement” request was open.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2176)     COALESCE and GROUP BY were returning unexpected wrong results.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2174)     The DATEADD() and DATEDIFF() functions were causing an assert in TimeStamp::decode().

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2154)     A “request synchronization error” would occur when calling isc_dsql_sql_info() with the isc_info_sql_records parameter after the last record had been fetched with EXECUTE PROCEDURE.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2153)     The “|” option was causing the SIMILAR TO predicate to hang.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2140)     Error messages after substitution of parameters contained '\n' characters instead of the actual line break.

fixed by C. Valderrama

    ~ ~ ~     

(CORE-2138)     If a stored procedure with an EXECUTE STATEMENT against an external database failed at runtime, the external database would remain attached.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2132)     Indexed retrieval could not be chosen if a stored procedure call was used in the comparison predicate.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2117)     Incorrect ROW_COUNT values were being returned with indexed retrieval and subqueries.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2115)     For some long queries the query plan could go missing.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2101)     A Bugcheck 249 (pointer page vanished) error would be thrown when an attempt was made to fetch beyond the end-of-stream mark of an open PSQL cursor.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2098)     It was not possible to create a view that selected from a global temporary table.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2081)     RDB$DB_KEY in a subquery expression would incorrectly return NULL.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2078)     If selective non-indexed predicates were involved in a join, the join plan was not optimized as well as it could be.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2075)     Parts of the RDB$DB_KEY of views could be inverted when using outer joins.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2073)     Expression indices bug: incorrect result for the inverted boolean.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2069)     Incorrect view expansion when RDB$DB_KEY was used inside a view body.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2068)     Comparision would return a wrong result with the IN <subquery_expression> operand if the <subquery_expression> argument involved the RDB$DB_KEY.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2067)     ?? GROUP BY and RDB$DB_KEY problems ??

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2066)     ?? Conversion of SQL_TEXT / SQL_VARCHAR to SQL_TIMESTAMP / SQL_TIME / SQL_DATE ??

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2062)     Classic server exhibited a lock file remapping error.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2053)     Computed expressions could suffer from poor optimization if used inside the RETURNING clause of the INSERT statement.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2045)     A v.2.1 regression was picked up, whereby references to non-existent system fields with blr_field were not being resolved to NULL, whereas a parallel change involving blr_fld was exhibiting the proper corrective behaviour.

fixed by dos Santos Fernandes

    ~ ~ ~     

(CORE-2044)     ?? Incorrect result for UPDATE OR INSERT ... RETURNING OLD and non-nullable columns ???

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2041)     UPDATE OR INSERT with GEN_ID() was causing the generator to step by 3.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2039)     Domain-level CHECK constraints were processing NULL values wrongly.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2038)     The new EXECUTE STATEMENT implementation would assert or throw an error if used both before and after Commit Retaining or Rollback Retaining.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-2036)     The order of parameters in an EXECUTE BLOCK statement was being reversed if the block was called from EXECUTE STATEMENT.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2031)     ??? NULL in the first record in a condition on RDB$DB_KEY ???

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2027)     The buffer size for ORDER BY expressions involving system fields was being calculated wrongly.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2026)     ??? Problem with a read-only marked database ???

fixed by V. Khorsun

    ~ ~ ~     

(CORE-2022)     CREATE USER was not being supported by the EXECUTE BLOCK statement.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-2008)     ??? NOT NULL flag for procedure parameters in the system schema ???

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-2002)     A conversion error from a UDF result would leave a memory leak if the result was marked with FREE_IT.

fixed by C. Valderrama

    ~ ~ ~     

(CORE-2001)     When trying to show a conversion error, the message arithmetic exception or string truncation was sometimes appearing instead.

fixed by C. Valderrama

    ~ ~ ~     

(CORE-2000)     The Lock Manager could report false deadlocks under high load.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-1994)     An “invalid database handle” could occur while executing a CREATE USER statement.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-1986)     Altering the name of a domain was causing dependencies on the domain to be dropped.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-1985)     The Lock Manager code could periodically cause 100% CPU load.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-1984)     The Lock Manager could falsely report a deadlock if one of the alleged participants was waiting with a permitted timeout.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-1980)     Sweeper could consume 100% of CPU indefinitely.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-1970)     Lock conversion denied (215) errors could occur.

fixed by V. Khorsun

    ~ ~ ~     

(CORE-1962)     The function EXTRACT (MILLISECONDS FROM aTimeStampOrTime) was returning incorrect results.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-1958)     A Bugcheck 179 (decompression overran buffer) consistency check error would be thrown when attempts were made to update the same record multiple times in a transaction.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-1957)     Long access control lists (ACLs) were being truncated, causing privileges to disappear.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-1943)     A statement that aggregated on a RAND() expression would return infinite rows.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-1938)     Bugcheck 243 (missing pointer page) was being thrown on preparing or executing statements that referred to a table being dropped or recreated by another connection.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-1936)     The built-in function LOG(base, number) was not checking parameters and would deliver NAN values for out-of-range input instead of excepting.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-1935)     SIMILAR TO character classes were not being recognised correctly.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-1914)     If a problem occurred during table creation, the database could be left in an inconsistent state.

fixed by A. Peshkov

    ~ ~ ~     

(CORE-1812)     For some date/time expressions in dialect 1, indexes were not being used when they should have been.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-1650)     An improbable case was demonstrated whereby something like SELECT GEN_ID(..) FROM RDB$DATABASE with a GROUP BY operation would cause rows to be generated infinitely.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-1607)     A correlated subquery that depended on a UNION stream would be poorly optimized.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-1606)     ??? Ability to insert child record if parent record is locked but foreign key target unchanged ???

fixed by A. Potapchenko, V. Khorsun

    ~ ~ ~     

(CORE-1575)     Multiple updates to a table in a single transaction would throw up a serious memory bug.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-1544)     When a user application created “temporary” stored procedures in run-time for some run-time purpose, the internal generator for the RDB$PROCEDURES.RDB$PROCEDURE_ID column could easily overflow the 32K limit (a signed SMALLINT) in its internal generator and cause a “numeric overflow” exception on trying to create the new stored procedure.

The fix wraps around the generated value at the 32K boundary, allowing reuse of existing gaps in the ID numbering. A similar fix was applied to RDB$GENERATORS and RDB$EXCEPTIONS as well.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-1343)     ?? simple case and a subquery ??

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-1246)     Outer joins with derived tables returned incorrect results.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-1245)     Outer joins with views returned incorrect results.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-903)     Firebird's behaviour with regard to multiple assignments referring to the same column in the SET clause of an UPDATE statement did not comply with the standard.

fixed by D. Yemanov

    ~ ~ ~     

(CORE-501)     COALESCE exhibited an optimization problem.

fixed by A. dos Santos Fernandes

    ~ ~ ~     

(CORE-216)     Privileges could be lost if a database contained too many.

fixed by A. Peshkov

    ~ ~ ~     

The following bugs were reported as fixed in the Alpha 1 release: