Firebird 2.5 Release Notes

English

Russian

Firebird 2.5 Release Notes

Firebird v2.5.0.ReleaseNotes

Firebird 2.5 Release Notes

Helen Borrie (Collator/Editor)

25 November 2009 -Document v.0250_39 -for Firebird 2.5 Release Candidate 1

Firebird 2.5 Release Notes

25 November 2009 -Document v.0250_39 -for Firebird 2.5 Release Candidate 1

Helen Borrie (Collator/Editor)

Table of Contents

1. General Notes ................................................................................................................................... 1

Bug Reporting ............................................................................................................................... 1

Known Issues ............................................................................................................................... 1

An Issue with the RDB$ADMIN Role .................................................................................... 1

SIMILAR TO Works Wrongly ............................................................................................... 1

Hints with Malformed String Exceptions for Restores .............................................................. 2

Documentation ............................................................................................................................... 2

2. New in Firebird 2.5 ........................................................................................................................... 3

Other New Features ....................................................................................................................... 3

Administrative Enhancements ................................................................................................. 3

Other SQL Language Additions and Enhancements ................................................................. 3

Data-handling Enhancements .................................................................................................. 4

API Additions ........................................................................................................................ 4

International Language Support .............................................................................................. 4

3. Changes in the Firebird Engine ........................................................................................................... 5

New Threading Architecture ........................................................................................................... 5

“Superclassic” ........................................................................................................................ 6

Thread-safe Client Library .............................................................................................................. 7

Improvements ............................................................................................................................... 7

Immediate Detection of Disconnected Clients on Classic .......................................................... 7

Optimizations ......................................................................................................................... 7

Cache Size Limit Increased for 64-bit Servers ......................................................................... 8

Default Database Location ...................................................................................................... 8

DLL Loading for Windows Embedded Engine ........................................................................ 9

Large External Table Support Enabled .................................................................................... 9

Statistics Now Work Properly with 64-bit Values .................................................................... 9

UDFs Safeguard ................................................................................................................... 10

Diagnostics .......................................................................................................................... 10

Metadata Improvements ........................................................................................................ 11

4. Changes to the Firebird API and ODS .............................................................................................. 12

ODS (On-Disk Structure) Changes ................................................................................................ 12

New ODS Number ............................................................................................................... 12

Maximum Page Size ............................................................................................................ 12

Maximum Number of Page Buffers in Cache ......................................................................... 12

API (Application Programming Interface) Extensions ..................................................................... 12

Connection Strings & Character Sets ..................................................................................... 12

Support for SQLSTATE Completion Codes ........................................................................... 14

“Efficient Unprepare” ........................................................................................................... 14

Cancel Operation Function ................................................................................................... 15

Shutdown Functions ............................................................................................................. 16

Tighter Control Over Header-level Changes .......................................................................... 21

New Trace Services for Applications .................................................................................... 21

Other Services API Additions ............................................................................................... 24

5. Reserved Words and Changes ...........................................................................................................

Clean-up of Reserved Words ........................................................................................................ 27

Newly Reserved Words ................................................................................................................ 27

Keywords Added as Non-reserved ................................................................................................ 27

6. Configuration Parameter Additions and Changes ................................................................................ 28

Firebird 2.5 Release Notes

AuditTraceConfigFile ................................................................................................................... 28

Parameters Affecting Filesystem Cache Usage ............................................................................... 28

FileSystemCacheSize ............................................................................................................ 28

FileSystemCacheThreshold ................................................................................................... 29

MaxFileSystemCache ........................................................................................................... 29

Authentication .............................................................................................................................. 30

Changes in V.2.5 ................................................................................................................. 30

MaxUserTraceLogSize .................................................................................................................. 30

OldSetClauseSemantics ................................................................................................................ 30

RemoteAuxPort For Classic and Superclassic ............................................................................... 31

Use Hostname for RemoteBindAddress ........................................................................................ 31

RemoteFileOpenAbility ................................................................................................................ 31

7. Administrative Features .................................................................................................................... 33

New RDB$ADMIN System Role .................................................................................................. 33

Multiple Databases and Superusers ....................................................................................... 33

System “Superusers” ............................................................................................................ 34

Escalating RDB$ADMIN Scope for User Management .......................................................... 35

Trace and Audit Services .............................................................................................................. 36

Overview of Features ........................................................................................................... 36

The System Audit Session .................................................................................................... 36

User Trace Sessions ............................................................................................................. 37

Trace Scope on Windows ..................................................................................................... 39

Use Cases ............................................................................................................................ 40

Monitoring Improvements ............................................................................................................. 40

Extended Access for Ordinary Users ..................................................................................... 40

New MON$ Metadata for ODS 11.2 Databases ..................................................................... 41

Usage Notes ......................................................................................................................... 42

8. Security Hardening ........................................................................................................................... 44

Windows Platforms ...................................................................................................................... 44

No SYSDBA Auto-mapping (Windows) ................................................................................ 44

9. Data Definition Language (DDL) ...................................................................................................... 45

Quick Links ................................................................................................................................ 45

Visibility of Procedure Definition Changes on Classic ............................................................ 45

CREATE/ALTER/DROP USER ........................................................................................... 45

Syntaxes for Altering Views ................................................................................................. 47

Extensions for CREATE VIEW ............................................................................................ 48

ALTER Mechanism for Computed Columns .......................................................................... 49

Extensions for SQL Permissions ........................................................................................... 49

Default COLLATION Attribute for a Database ...................................................................... 51

ALTER CHARACTER SET Command ................................................................................. 52

Evolution of CREATE DATABASE ..................................................................................... 53

10. Data Manipulation Language (DML) ............................................................................................... 56

Quick Links ................................................................................................................................ 56

RegEx Search Support using SIMILAR TO ........................................................................... 56

Hex Literal Support .............................................................................................................. 61

New UUID Conversion Functions ......................................................................................... 62

SOME_COL = ? OR ? IS NULL Predication ........................................................................ 62

Extension to LIST() Function ............................................................................................... 63

Optimizer Improvements ...................................................................................................... 64

Other Improvements ............................................................................................................. 64

11. Procedural SQL (PSQL) .................................................................................................................

Quick Links ................................................................................................................................ 66

Firebird 2.5 Release Notes

Autonomous Transactions ............................................................................................................. 66

Borrow Database Column Type for a PSQL Variable ..................................................................... 67

New Extensions to EXECUTE STATEMENT ............................................................................... 68

Context Issues ...................................................................................................................... 68

External Queries from PSQL ................................................................................................ 70

EXECUTE STATEMENT with Dynamic Parameters ............................................................. 71

Examples Using EXECUTE STATEMENT ........................................................................... 72

Other PSQL Improvements ........................................................................................................... 75

Subqueries as PSQL Expressions .......................................................................................... 75

12. International Language Support (INTL) ........................................................................................... 76

Default COLLATION Attribute for a Database .............................................................................. 76

ALTER CHARACTER SET Command ........................................................................................ 76

Connection Strings & Character Sets ............................................................................................. 76

Other Improvements ..................................................................................................................... 76

Introducer Syntax Usage ....................................................................................................... 76

Malformed UNICODE_FSS Characters Disallowed ............................................................... 77

Repair Switches for Malformed Strings ................................................................................. 77

Numeric Sort Attributes ........................................................................................................ 77

Character Sets and Collations ............................................................................................... 78

13. Command-line Utilities ................................................................................................................... 79

fbtracemgr ................................................................................................................................... 79

Action Switches ................................................................................................................... 79

Parameters ........................................................................................................................... 79

Examples using fbtracemgr ................................................................................................... 80

Retrieve Password from a File or Prompt ...................................................................................... 80

New -fetch_password Switch ................................................................................................ 80

gsec ............................................................................................................................................ 81

Mapping Switch for Windows Administrators ........................................................................ 81

Command-line Help for gsec ................................................................................................ 82

fbsvcmgr ..................................................................................................................................... 82

gbak ........................................................................................................................................... 83

Repair Switches for Malformed Strings ................................................................................. 83

Preserve Character Set Default Collation ............................................................................... 83

nBackup ...................................................................................................................................... 83

isql ............................................................................................................................................. 83

SQLSTATE instead of SQLCODE ........................................................................................ 84

Improvement for Exponential Number Output ....................................................................... 84

SHOW COLLATIONS in isql Help ...................................................................................... 84

gpre (Precompiler) ........................................................................................................................ 84

Some Updates ...................................................................................................................... 84

gstat ............................................................................................................................................ 85

14. Installation Notes ........................................................................................................................... 86

Linux (POSIX) ............................................................................................................................. 86

Installation Scripts Cleanup .................................................................................................. 86

Dedicated Firebird Switches for 'configure' ............................................................................ 86

Detection of Path to Firebird's Binaries ................................................................................. 87

Windows ..................................................................................................................................... 87

Managing MSCV8 Assemblies ............................................................................................. 87

15. Compatibility Issues .......................................................................................................................

Effects of Unicode Metadata ......................................................................................................... 89

Configuration Parameters Removed ............................................................................................... 89

SQL Language Changes ............................................................................................................... 89

Firebird 2.5 Release Notes

Reserved Words ................................................................................................................... 89

Execution Results ................................................................................................................. 90

Utilities ....................................................................................................................................... 90

fb_lock_print ........................................................................................................................ 90

API Changes ............................................................................................................................... 91

Rejection of Inconsistent TPB Options .................................................................................. 91

Addition of SQL_NULL Constant ......................................................................................... 91

Security Hardening ....................................................................................................................... 91

No SYSDBA Auto-mapping (Windows) ................................................................................ 91

Default Authentication Method (Windows) ............................................................................ 91

16. Platform Ports ............................................................................................................................... 93

IBM eServer z-Series ................................................................................................................... 93

Linux/s390 (32-bit) ............................................................................................................... 93

Linux/s390x (64-bit) ............................................................................................................. 93

Linux/sh4 (Renesas SH) ............................................................................................................... 93

HP-UX ....................................................................................................................................... 94

Lock Table Improvement for HP-UX .................................................................................... 94

Port for Very Old Windows 32-bit Platforms ................................................................................. 94

17. Bugs Fixed .................................................................................................................................... 95

Firebird 2.5 Release Candidate 1 .................................................................................................. 95

Core Engine/DSQL .............................................................................................................. 95

Server/Client Crashes ......................................................................................................... 100

Security .............................................................................................................................. 100

International Language Support ........................................................................................... 100

Command-line Utilities ....................................................................................................... 101

POSIX-Only Issues ............................................................................................................ 102

Windows-Only Issues ......................................................................................................... 102

Firebird 2.5 Beta 2 ..................................................................................................................... 102

Core Engine/DSQL ............................................................................................................ 102

Server/Client Crashes ......................................................................................................... 106

Remote Interface/API ......................................................................................................... 107

International Language Support ........................................................................................... 107

Command-line Utilities ....................................................................................................... 108

Firebird 2.5 Beta 1 ..................................................................................................................... 109

Core Engine/DSQL ............................................................................................................ 109

Firebird 2.5 Alpha 1 ........................................................................................................... 128

18. Firebird 2.5 Project Teams ............................................................................................................ 137

Appendix A: SQLSTATE ................................................................................................................... 139

SQLSTATE Codes & Messages .................................................................................................. 139

Appendix B: Licence Notice ............................................................................................................... 147

vii

List of Tables

10.1. Character class identifiers ............................................................................................................ 58

18.1. Firebird Development Teams ...................................................................................................... 137

viii

List of Examples

4.1. .................................................................................................................................................... 15

Chapter 1

General Notes

Bug Reporting

•

If you think you have discovered a new bug in this release, please make a point of reading the instructions

for bug reporting in the article How to Report Bugs Effectively, at the Firebird Project website.

•

If you think a bug fix hasn't worked, or has caused a regression, please locate the original bug report in the

Tracker, reopen it if necessary, and follow the instructions below.

Follow these guidelines as you attempt to analyse your bug:

Write detailed bug reports, supplying the exact build number of your Firebird kit. Also provide details of

the OS platform. Include reproducible test data in your report and post it to our Tracker.

You are warmly encouraged to make yourself known as a field-tester of this alpha by subscribing to the

field-testers' list and posting the best possible bug description you can.

If you want to start a discussion thread about a bug or an implementation, please do so by subscribing to the

firebird-devel list. In that forum you might also see feedback about any tracker ticket you post regarding

this alpha.

Known Issues

This release candidate is distributed with the following known issues, due for resolution before final release:

An Issue with the RDB$ADMIN Role

(CORE-2765) A user that has acquired the RDB$ADMIN role should have the same implicit WITH

GRANT OPTION rights in the database as does the SYSDBA. It appears not to be happening.

Allocated to A. Peshkov

~~~

SIMILAR TO Works Wrongly

(CORE-2755) Cases are occuring where the SIMILAR TO predicate is returning false negatives from

certain RegEx comparisons.

Allocated to A. dos Santos Fernandes

~~~

General Notes

Hints with Malformed String Exceptions for Restores

(CORE-nnnn) The final release will supply a hint referring the user to the -FIX_FSS_METADATA and

-FIX_FSS_DATA switches when a restore fails with malformed string errors.

Allocated to A. dos Santos Fernandes

~~~

Documentation

You will find all of the README documents referred to in these notes in the doc sub-directory of your Firebird

2.5 installation.

An automated "Release Notes" page in the Tracker provides lists and links for all of the Tracker tickets associated

with this and other pre-release versions. Use this link.

--The Firebird Project

Chapter 2

New in Firebird 2.5

The primary goal for Firebird 2.5 was to establish the basics for a new threading architecture that is almost

entirely common to the Superserver, Classic and Embedded models, taking in lower level synchronization and

thread safety generally.

Although SQL enhancements are not a primary objective of this release, for the first time, user management becomes

accessible through SQL CREATE/ALTER/DROP USER statements and syntaxes for ALTER VIEW and

CREATE OR ALTER VIEW are implemented. PSQL improvements include the introduction of autonomous

transactions and ability to query another database via EXECUTE STATEMENT.

Other New Features

Other new features and improvements in this release include:

Administrative Enhancements

•

System audit tracing and user trace sessions via the Services API, making it possible to monitor and analyse

everything going on in a database in real time

•

New system role RDB$ADMIN in the ODS 11.2 database allows SYSDBA to transfer its privileges to another

user on a per-database basis

•

More information in the monitoring tables

•

Asynchronous cancellation of connections

•

Capability for ordinary users to monitor any of their own attachments as well as CURRENT_CONNECTION

Other SQL Language Additions and Enhancements

•

Regular expression support using the SIMILAR TO predicate

•

ALTER COLUMN for computed columns

•

Autonomous transactions within a PSQL module (stored procedure, trigger or dynamically executable PSQL

block)

•

Enhanced access to stored procedures in view definitions

•

Optional GRANTED BY or GRANTED AS for GRANT and REVOKE statements, enabling the grantor to

be a user other than the CURRENT_USER (the default).

•

REVOKE ALL syntax to dispose of all privileges for a user or role at once

New in Firebird 2.5

•

Support for WHERE SOME_COL = ? OR ? IS NULL predications

•

Removal of “reserved” status for all but a handful of keywords that are not reserved in the SQL standard

Data-handling Enhancements

•

New built-in functions for converting UUID CHAR(16) OCTETS strings to RFC4122-compliant format and

vice versa

•

Ability to pass 32-bit and 64-bit integers as hexadecimal in numeric literal and X-prefixed binary string literal

formats

API Additions

•

Statements now return an SQL-2003 standard 5-alphanumeric SQLSTATE completion code

•

New constant DSQL_unprepare available for use with isc_dsql_free_statement for efficient unpreparing of

statements

International Language Support

•

Default COLLATE clause for CREATE DATABASE

•

Ability to change the default COLLATE for a used character set

•

GBAK restore switches FIX_FSS_DATA and FIX_FSS_METADATA to restore legacy databases with

UNICODE_FSS data and/or metadata correctly without resorting to scripts and manual techniques

•

Accent-insensitive collation for Unicode

Chapter 3

Changes in the

Firebird Engine

The primary objective of this release was to refactor Firebird's threading architecture to take advantage of the

symmetric multiprocessing (SMP) capabilities of multiprocessor hardware. This has a noticeable effect on the

scaleability of Superserver when multiple databases are being accessed simultaneously but its major effect is the

emergence of the architectural “Superclassic” model that will underpin the fine-grained multi-threading under

development for Firebird 3.

New Threading Architecture

Dmitry Yemanov

Vladyslav Khorsun

Alex Peshkov

also -

Nickolay Samofatov

Roman Simakov

For Superserver, the new architecture will be most obvious in two ways:

In a multiple database environment, Superserver threads for each database are allotted evenly to available

processors.

Note

The default CpuAffinity setting still binds SuperServer to a single processor only. In order to take advantage

of this improvement when working with multiple databases, this setting should be changed in

firebird.conf.

The default value may change before the final release.

2. A slight improvement in scaling should be apparent for single database usage on SMP hardware

It is with Classic that the effects are most evident:

Classic Server can now be multi-threaded. The one worker thread per process model remains but now it is

possible to use additional threads for parallel tasks such as asynchronous shutdown, sweep, inter-process

communications with the lock manager and more.

On POSIX, services in Classic also run in threads now, rather than in forked processes as previously.

Changes in the Firebird Engine

Note

For Windows Classic, services became threadable in v.2.1.

The embedded libraries—libfbembed.so on POSIX and fbembed.dll on Windows—are now multi-threadcapable

and thread-safe, so they can be used in multi-threaded applications.

Testing suggests that the performance of Classic in this version will be be significantly faster than previous

Classic versions.

“Superclassic”

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:

•

Safe, full shutdown of the server engine is possible on any platform

•

Under some TPC conditions, it can outperform Classic—by about 15-20%

•

It uses fewer kernel resources (although not less memory)

•

When a Superclassic process crashes, it takes all its connections with it

•

Recognised limitations in the Services API for the Classic server, such as the inability to retrieve the list of

attachments/active users, do not apply to SuperClassic.

•

On POSIX, Superclassic does not require [x]inetd.

Embedded Server Notes

•

The embedded server in the Windows library, fbembed.dll, now uses Superclassic, not Superserver as previously,

thus unifying its model with that of local connection to Superclassic on POSIX. The database file-

lock that previously restricted connections to a single application space is replaced by a global lock table that

allows simultaneous access to the same database from different embedded server modules. This facilitates

concurrent debugging of applications and use of native utility tools like gbak, gstat and so on.

•

A single attachment handle can now be shared by simultaneous threads. (Tracker reference CORE-2498,

A. dos Santos Fernandes).

Usage Notes

Windows

On Windows, the same fb_inet_server.exebinary delivers either the Classic or the Superclassic working

modes, according to switch settings. Classic is the default mode.

To use the Superclassic mode as a service, add the -m[ulti-threaded] switch to the instsvc.execommand

line, as follows:

Changes in the Firebird Engine

instsvc install -multithreaded

When intending to run Superclassic as an application, use

fb_inet_server -a -m

New Binary for POSIX

On POSIX, the new binary fb_smp_server is supplied for the Superclassic model. It contains the network

listener, meaning it works similarly to fbserver with regard to attachment requests and does not require

[x]inetd.

The multi-threaded engine used by fb_smp_serveris the libfbembed.solibrary, in accordance with OSRI

requirements. The Classic packages also include fbguard (the Guardian) which, for Classic, starts fb_smp_

server, rather than fbserveras it does when the Superserver model is installed with Guardian.

Thread-safe Client Library

Dmitry Yemanov

Vladyslav Khorsun

Alex Peshkov

Tracker reference CORE-707.

The client libraries, including the embedded one, can now be used in multi-threaded applications without any

application-level synchronization.

Improvements

Improvements implemented include:

Immediate Detection of Disconnected Clients on Classic

Vladyslav Khorsun

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.

Optimizations

Important optimizations include:

Changes in the Firebird Engine

Data Retrieval

Dmitry Yemanov

An optimization improves data retrieval performance for tables from which no fields are accessed. This applies,

for example to the SELECT COUNT(*) style of query.

Tracker reference CORE-1598.

BLOB Memory Usage

Adriano dos Santos Fernandes

An optimization avoids memory consumption of <page size> bytes for each temporary BLOB created during

assignment.

Tracker reference CORE-1658.

Performance Improvement for Updates

V. Khorsun

The aim of this improvement was to reduce the amount of precedence writing that the engine undertakes when

performing its “careful write” procedure for updates. The existing procedure had a noticeable effect on the

performance of mass updates, especially for “updates-in-place”, where the same records are updated more than

once in the same transaction. In the worst case, a page could be written to disk for every single new record

version created during an update.

See the Tracker reference (CORE-2672)for a simplified technical description of what was involved internally.

The solution addresses the potentially time-consuming process involved in protecting the write operations from

causing circular references between the pages on which new record versions and back versions are placed to

maintain correct precedence.

Cache Size Limit Increased for 64-bit Servers

V. Khorsun

Tracker reference CORE-1687

Previous 64-bit Firebird server versions could not benefit from 64-bit address space and be configured for more

than 2 GB (16K * 128 K)of database cache. The problem has been rectified in this version. On 64-bit Firebird,

if the resources are available, it is now possible to configure cache large enough to accommodate a database

of 5-10 GB completely in RAM .

Although Firebird's caching can get a lot of help from the filesystem cache, it is a feature that could be important

for high-throughput systems whose load is mainly reads. The theoretical upper limit for caches on x64 Firebird

servers is now 2^31 -1 (2,147,483,647) pages.

Default Database Location

A. Peshkov

Tracker reference CORE-1643

Changes in the Firebird Engine

The configuration parameter DatabaseAccess now has more “meaning” attached to it. In the absence of any

other indication, the first location defined in the “Restrict” list for DatabaseAccess is taken by the engine as the

default location for creating a new database and for locating a database where the connection parameters do not

specify either an alias or the full path specification.

The seek logic is similar to that use for finding external tables from the Restrict list supplied to the External-

FileAccess parameter, viz.,

1. All directories in the Restrict list are searched first.

2. If the database specified is not found:

• If CREATE DATABASE is involved, then the first location in the Restrict list is used.

• Otherwise, the attach fails in the expected fashion.

NOTE :: Current Working Directory

This feature does not suppress the use of the current working directory as the implicit location of the specified

database file for direct local connections. The Y-valve handles the path resolution in these cases, just as it

ever did.

For a stand-alone server working via the remote subsystem, trying to connect using the database file name

with no path, although unlikely, is not recommended, since there is not really any way to be certain where

the database would “land”. On Windows, for example, under these conditions the current working directory

would be %system%.

DLL Loading for Windows Embedded Engine

Adriano dos Santos Fernandes

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.

Large External Table Support Enabled

Vlad Khorsun

Previous Firebird versions used 32-bit I/O when working with external tables, limiting the size of the external

file to < 2 GB. The mechanism has been enhanced to use 64-bit I/O on filesystems that support it, effectively

eliminating the 2 GB limit.

Tracker reference CORE-2492.

Statistics Now Work Properly with 64-bit Values

V. Khorsun

A. Peshkov

Tracker reference CORE-2619

Changes in the Firebird Engine

Memory and other statistics did not work properly 64-bit values in older Firebird versions. The issue had two

parts:

a. To make the engine use 64-bit integers for statistics, the internals of AtomicCounter were changed.

b. The isql and qli tools had to be taught to work with 64-bit values.

Incompatibility with Older Clients

To enable the 32-bit tools to work correctly with a 64-bit server, it was necessary to introduce some new internal

API functions (struct perf64 and perf64_xxx) and change isql and qli to use them. This means that the isql

and qli programs in V.2.5 are not compatible with older Firebird clients.

UDFs Safeguard

Adriano dos Santos Fernandes

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

1. detects such UDFs and throws an exception

2. depends on the presence of the updated ib_util library in the path for all server models, including embedded

Diagnostics

Transaction Diagnostics

Claudio Valderrama

Better diagnostics and error reporting when TPB contents are malformed. The new TPB validation logic now

rejects:

•

explicitly conflicting options within the same category, e.g., {WAIT} and {NOWAIT} specified together,

or {READ COMMITTED} and {SNAPSHOT}, or {READ ONLY} and {WRITE}

•

options making no sense, e.g. [NO] RECORD VERSION specified for a SNAPSHOT isolation mode

•

incorrect order of table reservation options, e.g. {PROTECTED READ <TABLE>} instead of {READ

<TABLE> PROTECTED}

Tracker reference CORE-1600.

Access Privilege Error Messages

Alex Peshkov

Both table and column names are now reported when access privilege exceptions occur for a column.

Tracker reference CORE-1234.

Changes in the Firebird Engine

Message Improvement

V. Khorsun

Tracker reference CORE-2587

The diagnostic message when the engine cannot create shared memory that has already been mapped by another

engine process in another Windows session is now a bit more user-friendly. It used to say:

The requested operation cannot be performed on a file with a user-mapped section open.

Now, it says:

Database is probably already opened by another engine instance in another Windows session.

Metadata Improvements

Preserve Character Set Default Collation

Adriano dos Santos Fernandes

An improvement allows the current value of RDB$DEFAULT_COLLATE_NAME in the system table RDB

$CHARACTER_SETS to survive the backup/restore cycle. The mechanism for such customisation is the new

ALTER CHARACTER SET command.

Tracker reference CORE-789.

Chapter 4

Changes to the

Firebird API and ODS

ODS (On-Disk Structure) Changes

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

New ODS Number

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

Maximum Page Size

The maximum page size remains 16 KB (131072 bytes).

Maximum Number of Page Buffers in Cache

The maximum number of pages that can be configured for the database cache depends on whether the database

is running under 64-bit or 32-bit Firebird:

• 64-bit :: 2^31 -1 (2,147,483,647) pages

• 32-bit :: 128,000 pages, i.e., unchanged from V.2.1

API (Application Programming Interface) Extensions

Additions to the Firebird API include.

Connection Strings & Character Sets

A. dos Santos Fernandes

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,

other files and string parameters generally, when accessed through and/or passed in API connection requests.

This change significantly improves Firebird's ability to accept and work with file names and other parameters

containing characters that are not in the ASCII subset.

Changes to the Firebird API and ODS

Only DPB Connections Support this Feature

In the current implementation, only connections made through the DPB (database parameter block) support

this feature. It is not supported for Services API (isc_spb*) functions.

isc_dpb_utf8_filename

The new connection option isc_dpb_utf8_filename has been introduced, to enable Firebird to be specifically

informed that the file name or other character item being passed is in the UTF8 (UTF-8) character set. If the

option is not used, the character set defaults to the codepage of the operating system.

Client-Server Compatibility

New client, older server

If the client is V.2.5 or newer and it is connecting to a pre-V.2.5 remote server, using the

isc_dpb_utf8_filename option causes the client to convert the file name from UTF-8 to the client codepage

before passing it to the server. It removes the isc_dpb_utf8_filename option from the DPB.

Compatibility is assured when the same codepage is being used on both the the client and server stations.

New client, new server, without isc_dpb_utf8_filename

If the client is V.2.5 or newer and it is connecting to a V.2.5 or newer remote server without using the

isc_dpb_utf8_filename, the client converts the file name from the OS codepage to UTF-8 and inserts the

isc_dpb_utf8_filename option into the DPB.

The file name received on the server is not subject to any special treatment. However, unlike older clients,

the V.2.5 client may convert the file name automatically and insert the isc_dpb_utf8_filename option into

the DPB. Compatibility is guaranteed, regardless, when the host and client are using the same code page.

New client, new server, with isc_dpb_utf8_filename

Whenever the isc_dpb_utf8_filename option is used, the client passes the unmodified filename to the server.

The client thus always passes a UTF-8 file name to the server along with the isc_dpb_utf8_filename option.

Code Page Conversions

On Windows the code page used for conversions is Windows ANSI. On all other platforms, UTF-8 is used.

The operating system codepage and UTF-8 may not be the best choice for file names. For example, if you had

a script or other text file for processing in isql or some other script-running tool that used another connection

character set, it would not be possible to edit the file correctly using multiple character sets (code pages).

There is a solution: the Unicode code point. If used correctly, it enables correct interpretation of a character

even if the client is older than V.2.5.

Using Unicode Code Points

Any Unicode character may now now be encoded on the connection string file name as though it were an ASCII

character. It is accomplished by using the symbol # as a prefix for a Unicode code point number (in hexadecimal

format, similar to U+XXXX notation).

Changes to the Firebird API and ODS

Write it as #XXXX with X being 0-9, a-f, A-F.

If one of the characters happens to be the literal #, you could either “double” the hash character ( ## ) or use

the code point number for it, #0023.

Note

The hash character is interpreted at the server with these new semantics, even if the client is older than v2.5.

Support for SQLSTATE Completion Codes

W. Oliver

D. Yemanov

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.

•

The SQLSTATE code represents the concatenation of a 2-character SQL CLASS and a 3-character SQL

SUBCLASS.

•

Statements now return an SQLSTATE completion code.

•

The isql utility now prints the SQLSTATE diagnostic for errors instead of the SQLCODE one

•

The SQLCODE diagnostic is deprecated—meaning it will disappear in a future release

Deprecated SQLCODE

Although the SQLCODE is deprecated and use of the SQLSTATE is preferred, it remains in Firebird for the

time being. The isc_sqlcode() API function is still supported, as is the WHEN SQLCODE exception handling.

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

message texts.

“Efficient Unprepare”

W. Oliver

D. Yemanov

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:

Changes to the Firebird API and ODS

#define DSQL_close 1

#define DSQL_drop 2

#define DSQL_unprepare 4

Cancel Operation Function

Alex Peshkov

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

status vector (ISC_STATUS* status_vector)

A regular status vector pointer structure.

db_handle (pointer to a isc_db_handle)

A regular, valid database handle. It identifies the attachment.

option (unsigned short: symbol)

Determines the action to be performed. The option symbols are:

•

fb_cancel_raise: cancels any activity related to the db_handle specified in the second parameter. The

effect will be that, as soon as possible, the engine will try to stop the running request and return an

exception to the caller via the status vector of the interrupted API call.

“..as soon as possible” will be, under normal conditions, at the next rescheduling point.

Example 4.1.

Thread1:

Thread2:

isc_dsql_execute(status, ....)

........ fb_cancel_operation(cancel_status, ...)

status[1] == isc_cancelled; cancel_status[1] = 0;

•

fb_cancel_disable: disables execution of fb_cancel_raise requests for the specified attachment. It can be

useful when your program is executing critical operations, such as cleanup, for example.

•

fb_cancel_enable: re-enables delivery of a cancel execution that was previously disabled. The 'cancel'

state is effective by default, being initialized when the attachment is created.

•

fb_cancel_abort: : forcibly close client side of connection. Useful if you need to close a connection urgently.

All active transactions will be rolled back by the server. 'Success' is always returned to the application.

Use with care !

Changes to the Firebird API and ODS

Usage

The cycle of fb_cancel_disable and fb_cancel_enable requests may be repeated as often as necessary. If the

engine is already in the requested state there is no exception: it is simply a no-op.

Usually fb_cancel_raise is called when you need to stop a long-running request. It is called from a separate

thread, not from the signal handler, because it is not async signal safe.

Pay attention to the asynchronous nature of this API call!

Another aspect of asynchronous execution is that, at the end of API call, the attachment's activity might be

cancelled or it might not. The latter is always a possibility. The asynchronicity also means that returned status

vector will almost always return FB_SUCCESS. Exceptions, though, are possible: a network packet error, for

example.

An Example

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"

Shutdown Functions

Alex Peshkov

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

applications.

Two Interrelated fb_shutdown* Functions

This release exposes two fb_shutdown* functions that may be useful for embedded server applications:

fb_shutdown() and fb_shutdown_callback.

Prototypes

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

int fb_shutdown(unsigned int timeout,

const int reason);

Changes to the Firebird API and ODS

ISC_STATUS fb_shutdown_callback(ISC_STATUS* status_vector,

FB_SHUTDOWN_CALLBACK callback_function,

const int mask,

void* arg);

fb_shutdown()

fb_shutdown() performs a smart shutdown of various Firebird subsystems (yValve, engine, redirector). It was

primarily designed for use by the internal engine, since it is only applicable to the current process. It is exposed

by the API for its possible usefulness to user applications in the embedded server environment.

Currently operational only for the embedded engine, this function terminates all the current activity, rolls back

active transactions, disconnects active attachments and shuts down the embedded engine instance gracefully.

Important for Application Developers

fb_shutdown() does not perform a shutdown of a remote server to which your application might be concurrently

attached. In fact, all of the Firebird client libraries—including the one in embedded—call it automatically

at exit(), as long as the client is attached to at least one database or service.

Hence, it should never be called by a client in the context of a remote attachment.

Parameters

fb_shutdown() takes two parameters:

1. timeout in milliseconds

2. reason for shutdown

The reason codes (const int reason), which are negative, are listed in ibase.h: refer to constants starting

with fb_shutrsn.

Note

When calling fb_shutdown() from your program, you must pass the value as positive, for it will be passed

as an argument to fb_shutdown_callback() by way of your callback_function, the routine where you

would code the appropriate actions.

Return Values

•

A return value of zero means shutdown was successful

•

A non-zero value means some errors occurred during the shutdown. Details will be written to firebird.

log.

fb_shutdown_callback()

fb_shutdown_callback() sets up the callback function that is to be called during shutdown. It is a call that

almost always returns successfully, although there are cases, such as an out-of-memory condition, which could

cause it to return an error.

Changes to the Firebird API and ODS

Parameters

fb_shutdown_callback() takes four parameters:

status vector (ISC_STATUS* status_vector)

A regular status vector pointer structure.

pointer to callback function (FB_SHUTDOWN_CALLBACK callback_function)

This points to the callback function you have written to perform the actions (if any) to be taken when the

callback occurs.

Your callback function can take three parameters. The first and second parameters help to determine what

action is to be taken in your callback:

reason for shutdown

Two shutdown reasons are of especial interest:

•

fb_shutrsn_exit_called: Firebird is closing due to exit() or unloaded client/embedded library

•

fb_shutrsn_signal, applies only to POSIX: a SIGINT or SIGTERM signal was caught

Note

Firebird code always uses negative reasons. Users are expected to use positive values when calling

fb_shutdown() themselves.

actual value of the mask with which it was called

The purpose of this parameter to help determine whether the callback was invoked before or after engine

shutdown.

argument passed to fb_shutdown_callback() by the user application

Can be used for any purpose you like and may be NULL.

Return Value from the Callback Function

If the callback function returns zero, it means it performed its job successfully. A non-zero return value is

interpreted according to the call mask (see next parameter topic, below):

•

For fb_shut_postproviders calls, it means some errors occurred and it will result in a non-zero value being

returned from fb_shutdown(). It is the responsibility of the callback function to notify the world of the

exact reasons for the error condition being returned.

•

For fb_shut_preproviders calls, it means that shutdown will not be performed.

Tip

It is NOT a good idea to return non-zero if the shutdown is due to exit() having been called ! ;-)

call mask (const int mask)

Can have the following symbolic values:

•

fb_shut_preproviders: callback function will be called before shutting down engine

Changes to the Firebird API and ODS

• fb_shut_postproviders: callback function will be called after shutting down engine

• An ORed combination of them, to have the same function called in either case

Values for call mask

fb_shut_confirmation

Engine queries: Is everyone ready to shut down?

fb_shut_preproviders

Actions to be done before providers are closed

fb_shut_postproviders

Aactions to be done when providers are already closed

fb_shut_finish

Final cleanup

Returning non-zero for fb_shut_confirmation (although not fb_shut_preproviders) means that shutdown

will not be performed.

argument (void* arg)

This is the argument to be passed to callback_function.

Using the fb_shutdown Functions

Following is a sample of using the shutdown and shutdown callback feature to prevent your program from being

terminated if someone presses Ctrl-C while it is has database attachments.

#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_confirmation, 0))

{

isc_print_status(status);

return 1;

}

// your code continues ...

}

New isc_spb_prp_* Constants for Shutdown

The new database shutdown modes can now be set using calls to the Services API. A number of new

isc_spb_prp_* constants are available as arguments.

Changes to the Firebird API and ODS

isc_spb_prp_shutdown_mode and isc_spb_prp_online_mode

These arguments are used for shutting down a database and bringing it back on-line, respectively. Each carries

a single-byte parameter to set the new shutdown mode, exactly in accord with the gfix -shut settings:

• isc_spb_prp_sm_normal

• isc_spb_prp_sm_multi

• isc_spb_prp_sm_single

• isc_spb_prp_sm_full

The shutdown request also requires the type of shutdown to be specified, viz., one of

• isc_spb_prp_force_shutdown

• isc_spb_prp_attachments_shutdown

• isc_spb_prp_transactions_shutdown

Each takes a 4-byte integer parameter, specifying the timeout for the shutdown operation requested.

Note

The older-style parameters are also supported and should be used to enter the default shutdown (currently

'multi') and online ('normal') modes.

Usage Examples

Following are a few examples of using the new parameters with the fbsvmgr utility. For simplicity, it is assumed

that login has already been established. Each example, though broken to fit the page-width, is a single line

command.

Shutdown database to single-user maintenance mode:

fbsvcmgr service_mgr action_properties dbname employee

prp_shutdown_mode prp_sm_single prp_force_shutdown 0

Next, enable multi-user maintenance:

fbsvcmgr service_mgr action_properties dbname employee

prp_online_mode prp_sm_multi

Now go into full shutdown mode, disabling new attachments for the next 60 seconds:

fbsvcmgr service_mgr action_properties dbname employee

prp_shutdown_mode prp_sm_full prp_attachments_shutdown 60

Return to normal state:

Changes to the Firebird API and ODS

fbsvcmgr service_mgr action_properties dbname employee

prp_online_mode prp_sm_normal

Tighter Control Over Header-level Changes

Alex Peshkov

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.

• isc_dpb_shutdown and isc_dpb_online

• isc_dpb_gbak_attach, isc_dpb_gfix_attach and isc_dpb_gstat_attach

• isc_dpb_verify

• isc_dpb_no_db_triggers

• isc_dpb_set_db_sql_dialect

• isc_dpb_sweep_interval

• isc_dpb_force_write

• isc_dpb_no_reserve

• isc_dpb_set_db_readonly

• isc_dpb_set_page_buffers (on Superserver)

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.

Important Note for Developers and Users of Data Access Drivers and Tools

This change will affect any of the listed DPB parameters that have been explicitly set, either by including

them in the DPB implementation by default property values or by enabling them in tools and applications

that access databases as ordinary users. For example, a Delphi application that included 'RESERVE PAGE

SPACE=TRUE' and 'FORCED WRITES=TRUE' in its database Params property, which caused no problems

when the application connected to Firebird 1.x, 2.0.1. 2.0.3, 2.04 or 2.1.0/2.1.1, now rejects a connection by a

non-SYSDBA user with ISC ERROR CODE 335544788, “Unable to perform operation. You must be either

SYSDBA or owner of the database.”

New Trace Services for Applications

Vlad Khorsun

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.

Changes to the Firebird API and ODS

isc_action_svc_trace_start

Starts a user trace session

Parameter(s)

isc_spb_trc_name : trace session name, string, optional

isc_spb_trc_cfg : trace session configuration, string, mandatory

The mandatory parameter is a string encompassing the text for the desired configuration. A template file named

fbtrace.confis provided in Firebird's root directory as a guide to the contents of this string.

Note

Unlike system audit sessions, a user session does not read the configuration from a file. It will be the

responsibility of the application developer to devise a mechanism for storing configurations locally at the

client and retrieving them for run-time use.

Superfluous white space in the string is fine: it will simply be ignored.

Output

Results of the trace session in text format.

isc_action_svc_trace_stop

Stops a designated trace session

Parameter(s)

isc_spb_trc_id : trace session ID, integer, mandatory

Output

A text message providing the result (status) of the request:

• Trace session ID NNN stopped

• No permissions to stop other user trace session

• Trace session ID NNN not found

isc_action_svc_trace_suspend

Suspends a designated trace session

Parameter(s)

isc_spb_trc_id : trace session ID, integer, mandatory

Changes to the Firebird API and ODS

Output

A text message providing the result (status) of the request:

• Trace session ID NNN paused

• No permissions to change other user trace session

• Trace session ID NNN not found

isc_action_svc_trace_resume

Resumes a designated trace session that has been suspended

Parameter(s)

isc_spb_trc_id : trace session ID, integer, mandatory

Output

A text message providing the result (status) of the request:

• Trace session ID NNN resumed

• No permissions to change other user trace session

• Trace session ID NNN not found

isc_action_svc_trace_list

Lists existing trace sessions

No parameters

Output

A text message listing the trace sessions and their states:

• Session ID: <number>

• name: <string>. Prints the trace session name if it is not empty

• user: <string>. Prints the user name of the user that created the trace session

• date: YYYY-MM-DD HH:NN:SS, start date and time of the user session

• flags: <string>, a comma-separated set comprising some or all of the following:

active | suspend

Run state of the session.

admin

Shows admin if an administrator user created the session. Absent if an ordinary user created the session.

system

Shows system if the session was created by the Firebird engine (system audit session). Absent if an ordinary

user created the session.

Changes to the Firebird API and ODS

audit | trace

Indicates the kind of session: audit for an engine-created audit session or trace for a user trace session.

log full

Conditional, appears if it is a user trace session and the session log file is full.

Note

The output of each service can usually be obtained using a regular isc_service_query call with either of the

isc_info_svc_line or isc_info_svc_to_eof information items.

Other Services API Additions

Alex Peshkov

Other additions to the Services API include:

Mapping for RDB$ADMIN Role in Services API

Two tag items have been added to the services parameter block (SPB) to to enable or disable the RDB$ADMIN

role for a privileged operating system user when requesting access to the security database.

Note

This capability is implemented in the gsec utility by way of the new -mapping switch. Refer to the notes in

the relevant section of the Command-line Utilities chapter.

Tag Item isc_action_svc_set_mapping

Enables the RDB$ADMIN role for the appointed OS user for a service request to access security2.fdb.

Tag Item isc_action_svc_drop_mapping

Disables the RDB$ADMIN role for the appointed OS user for a service request to access security2.fdb.

Parameter isc_spb_sec_admin

The new parameter isc_spb_sec_admin, is the SPB implementation of the new DDL syntax introduced to enable

SYSDBA or another sufficiently privileged user to grant or revoke the RDB$ADMIN role in the security

database (security2.fdb) to or from an ordinary Firebird user. An ordinary user needs this role to acquire the

same privileges as SYSDBA to create, alter or drop users in the security database.

isc_spb_sec_admin is of type spb_long with a value of either 0 (meaning REVOKE ADMIN ROLE) or a nonzero

number (meaning GRANT ADMIN ROLE).

For more information, refer to the topic CREATE/ALTER/DROP USER in the chapter Data Definition Language.

Changes to the Firebird API and ODS

Tag item isc_spb_bkp_no_triggers

This new SPB tag reflects the Services API side of the -nodbtriggers switch introduced in the gbak utility

at V.2.1 to prevent database-level and transaction-level triggers from firing during backup and restore. It

is intended for use as a member of the isc_spb_options set of optional directives that includes items like

isc_spb_bkp_ignore_limbo, etc.

nBackup Support

Tracker reference: CORE-1758.

The nBackup utility performs two logical groups of operations: locking or unlocking a database and backing

it up or restoring it. While there is no rationale for providing a service action for the lock/unlock operations—

they can be requested remotely by way of an SQL language request for ALTER DATABASE—a Services API

interface to the backup/restore operations is easily justified.

Backup and restore must be run on the host station and the only way to access them was by running nBackup.

The two new service actions now enabling nBackup backup and restore to be requested through the Services

API are:

• isc_action_svc_nbak -incremental nbackup

• isc_action_svc_nrest -incremental database restore

The parameter items are:

• isc_spb_nbk_level -backup level (integer)

• isc_spb_nbk_file -backup file name (string)

• isc_spb_nbk_no_triggers -option to suppress database triggers

Usage Examples

Following are a few examples of using the new parameters with the fbsvcmgr utility. For simplicity, it is assumed

that login has already been established. Each example, though broken to fit the page-width, is a single line

command.

Create backup level 0:

fbsvcmgr service_mgr action_nbak dbname employee

nbk_file e.nb0 nbk_level 0

Create backup level 1:

fbsvcmgr service_mgr action_nbak dbname employee

nbk_file e.nb1 nbk_level 1

Restore database from those files:

fbsvcmgr service_mgr action_nrest dbname e.fdb

nbk_file e.nb0 nbk_file e.nb1

Changes to the Firebird API and ODS

FIX_FSS_DATA and FIX_FSS_METADATA Options Enabled in Services API

A. Peshkov

Tracker reference CORE-2439

The FIX_FSS_DATA and FIX_FSS_METADATA functions that were implemented as gbak -restore switches

in Firebird 2.1 have been implemented in the core engine and exposed as corresponding tag constants for the

isc_action_svc_restore structure in the Services API. Thus, developers now have a path for writing applications

to automate the migration of older Firebird databases to the new on-disk structure.

The new SPB tags are isc_spb_res_fix_fss_data and isc_spb_res_fix_fss_metadata.

Chapter 5

Reserved Words and Changes

Note

Asterisks (*) mark keywords that are reserved, or otherwise recognised by Firebird's grammar as keywords,

but are not reserved words in the SQL standard.

Clean-up of Reserved Words

A. Peshkov

Tracker reference CORE-2638

The number of Firebird-specific reserved words has been reduced significantly, in order to mitigate the pain of

keyword conflicts when converting other databases to Firebird. Where possible, words that are not reserved by

the standard have been made non-reserved in Firebird's grammar.

A small list remains of words that are reserved in Firebird but not in the SQL standard. They are:

ADD * DB_KEY * GDSCODE * INDEX *

LONG * PLAN * POST_EVENT * RETURNING_VALUES *

SQLCODE * VARIABLE * VIEW * WEEK *

WHILE

All other non-standard keywords previously reserved are now available for any reasonable purpose.

Newly Reserved Words

SIMILAR

Keywords Added as Non-reserved

AUTONOMOUS * BIN_NOT * CALLER *

CHAR_TO_UUID * COMMON * DATA

FIRSTNAME * GRANTED LASTNAME *

MIDDLENAME * MAPPING * OS_NAME *

SOURCE * TWO_PHASE * UUID_TO_CHAR *

Chapter 6

Configuration Parameter

Additions and Changes

The following changes or additions to firebird.confshould be noted:

AuditTraceConfigFile

V. Khorsun

This parameter points to the name and location of the file that the Firebird engine is to read to determine the list

of events required for the next system audit trace. By default, the value of this parameter is empty, indicating

that no system audit tracing is configured.

Note

The template file file fbtrace.conf, found in Firebird's root directory, contains the full list of available

events, with format, rules and syntax for composing an audit trace configuration file.

For more information, see the topic System Audit Session in section [Trace and Audit Services] in the chapter

about the new administrative features.

Parameters Affecting Filesystem Cache Usage

There are now two parameters for configuring how Firebird interacts with the filesystem cache.

FileSystemCacheSize

N. Samofatov

New in Firebird 2.5, FileSystemCacheSize controls the maximum amount of RAM used by a Windows file

system cache on 64-bit Windows XP or a Microsoft Server 2003 host with Service Pack 1 or higher.

At the V.2.5 initial release, it has no effect on POSIX host systems.

The setting for this parameter is an integer expressing the percentage of the total physical RAM that is available

to the OS. To be valid, settings must be within the range 10 (per cent) to 95 (per cent), or explicitly set to 0 to

enforce the host caching settings. Numbers outside that range will assume the default, which is 30 (per cent).

As with any firebird.confsetting, changes will not take effect until the server process is restarted.

Configuration Parameter Additions and Changes

Windows Security Privileges

The OS user needs the SeIncreaseQuotaPrivilege in order to adjust the filesystem cache settings. This right is

built in for users with Administrator privileges and for service accounts and it is also granted to the Firebird

service account explicitly by the Windows installer.

Under other conditions, e.g., embedded, or where the Firebird server is run as an application, or in a custom

service installation, the user may not have that privilege. The process startup does not fail as a result of this

misconfiguration: it will write a warning to the firebird.log and start-up will simply proceed with the

host OS settings.

FileSystemCacheThreshold

V. Khorsun

This parameter was introduced in V.2.1 as MaxFileSystemCache. Because its name has been changed, its

description is repeated here to alert upgraders.

FileSystemCacheThreshold sets a threshold determining whether Firebird will allow the page cache to be duplicated

to the filesystem cache or not. If this parameter is set to any (integer) value greater than zero, its effect

depends on the current default size of the page cache: if the default page cache (in pages) is less than the value

of MaxFileSystemCache (in pages) then filesystem caching is enabled, otherwise it is disabled.

Note

This applies both when the page cache buffer size is set implicitly by the DefaultDBCachePages setting or

explicitly as a database header attribute. It applies to all platforms.

Thus,

•

To disable filesystem caching always, set FileSystemCacheThreshold to zero

•

To enable filesystem caching always, set FileSystemCacheThreshold an integer value that is sufficiently large

to exceed the size of the database page cache. Remember that the effect of this value will be affected by

subsequent changes to the page cache size.

Important

•

The default setting for FileSystemCacheThreshold is 65536 pages, i.e. filesystem caching is enabled.

•

Observe that, if the configured cache size affecting a particular database is greater than the FileSystemCacheThreshold

then the setting for FileSystemCacheSize (see above) will have no effect on that database.

MaxFileSystemCache

MaxFileSystemCache, introduced in Firebird 2.1, is no longer a valid parameter.

Configuration Parameter Additions and Changes

Authentication

A. Peshkov

On Windows server platforms, since V.2.1, Authentication has been used for configuring the server authentication

mode if you need it to be other than the default.

The mode settings for v.2.5 are the same, viz.

•

trusted makes use of Windows “trusted authentication”. Under the right conditions, this may be the most

secure way to authenticate on Windows.

•

native sets the traditional Firebird server authentication mode, requiring users to log in using a user name

and password defined in the security database.

•

mixed allows both.

Changes in V.2.5

•

Under v.2.5, although the modes are unchanged, configuring 'mixed' or 'trusted' mode no longer confers

SYSDBA privileges on Windows domain administrators automatically by default. Please read the notes in

the Administrative Features chapter regarding the new RDB$ADMIN role in ODS 11.2 databases and auto-

mapping SYSDBA privileges to domain administrators.

•

The default configuration has been changed from mixed to native. To enable trusted user authentication

(whether mixed or trusted, it is now necessary to configure this parameter specifically.

Tracker reference CORE-2376

MaxUserTraceLogSize

V. Khorsun

Stores the maximum total size of the temporary files to be created by a user trace session using the new Trace

functions in the Services API. The default limit is 10 MB. Use this parameter to raise or lower the maximum

total size of the temporary files storing the output.

OldSetClauseSemantics

D. Yemanov

Before Firebird 2.5, the SET clause of the UPDATE statement assigned columns in the user-defined order, with

the NEW column values being immediately accessible to the subsequent assignments. This did not conform to

the SQL standard, which requires the starting value of the column to persist during execution of the statement.

Now, only the OLD column values are accessible to any assignment in the SET clause.

The OldSetClauseSemantics enables you to revert to the legacy behavior via the OldSetClauseSemantics, if

required. Values are 1 for the legacy behaviour, 0 (the default) for the corrected behaviour.

Configuration Parameter Additions and Changes

Warning

• Changing this parameter affects all databases on your server.

• This parameter is provided as a temporary solution to resolve backward compatibility issues. It will be

deprecated in future Firebird versions.

RemoteAuxPort For Classic and Superclassic

Dmitry Yemanov

Tracker entry: CORE-2263

Classic and SuperClassic servers can now be configured to listen for events on a single, designated RemoteAux-

Port port, as SuperServer has been able to do since v.1.5.

This long-awaited improvement now enables applications that connect to databases over the Internet through a

firewall or a secure tunnel to use events, regardless of the server model in use.

Use Hostname for RemoteBindAddress

Alex Peshkov

Tracker entry: CORE-2094

It is now possible to use the hostname of the host where the Firebird server is running to configure Remote-

BindAddress, where previously, only an IP address was allowed.

Important

RemoteBindAddress can be used to “pin” user connections to a specific NIC card on the host server. Take

care that the hostname specified is not associated concurrently with more than one IP address, anywhere! In

particular, check the etc/hostsfile on all stations, including the host station itself.

RemoteFileOpenAbility

Nickolay Samofatov

Tracker entry: CORE-2263

Code from Red Soft was incorporated, to make this extreme option available to Windows and allow a database

to be opened on a network share, in line with the long-time ability to allow access to a database on a NFS device

on POSIX.

It is offered in the interests of maintaining feature consistency across platforms. There is no associated architectural

change or any implication that its use in practice is considered safer now than in the past. However, it makes

it possible to shadow databases to mapped locations and to connect to a database on an external filesystem for a

specific, well-tested, safe purpose. An example given was a database kept under lock-and-key on a USB device

that could be plugged in to a diskless workstation for performing an occasional, isolated security task.

Warning

READ THE NOTES IN FIREBIRD.CONF BEFORE YOU CONSIDER ACTIVATING THIS!

Chapter 7

Administrative Features

Certain improvements to Firebird's administrative features will be welcomed by many.

New RDB$ADMIN System Role

Alex Peshkov

A new pre-defined system role RDB$ADMIN has been added for transferring SYSDBA privileges to another

user. Any user, when granted the role in a particular database, acquires SYSDBA-like rights when attaching to

that database with the RDB$ADMIN role specified.

To assign it, SYSDBA should log in to that database and grant the role RDB$ADMIN to the user, in the same

way one would grant any other role to a user. After the user has been granted the role, s/he must include it in

the log-in in order to access the “superuser” privileges in that database.

Important

If the user attaches with a user database role passed in the DPB (connection parameters), it will not be replaced

with RDB$ADMIN, i.e., he/she will not get SYSDBA rights.

The following example transfers SYSDBA privileges to users named User1 and Admins\ADMINS. The second

user in our example is typical for a Windows system user with access enabled via trusted authentication:

GRANT RDB$ADMIN TO User1;

GRANT RDB$ADMIN TO "Admins\ADMINS";

Multiple Databases and Superusers

It should be understood that acquiring the RDB$ADMIN role does not make a regular user into SYSDBA.

Rather, it gives that user the same privileges as SYSDBA over the objects in the database in which the role

is granted to that user.

•

If the same user needs Superuser privileges in more than one database, the RDB$ADMIN role must be

explicitly granted for that user in each database.

•

If more than one user is to have Superuser privileges in a database then each user needs to be granted the

RDB$ADMIN role.

•

One user that has acquired the RDB$ADMIN role in the database can grant it to another user.

•

It is not necessary to specify WITH ADMIN OPTION (for the privilege to grant this role to others) or WITH

GRANT OPTION (for the privilege to grant permissions on objects to other users without being the owner

of those objects). The ADMIN and GRANT options are implicit.

Administrative Features

System “Superusers”

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. In V.2.1, 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. On POSIX, the mechanism has not changed but

on Windows, the introduction of the RDB$ADMIN role has changed the way Windows administrators acquire

SYSDBA privileges.

Windows trusted user authentication is no longer available by default!

By default, the Authentication parameter in firebird.confis configured as native. It must be explicitly

configured to either trusted or mixed to enable trusted user authentication.

Global Admin Privileges for Windows Administrators

For a trusted domain administrator to get SYSDBA access privileges on a Firebird server that is configured for

trusted user authentication, the domain administrator must acquire the RDB$ADMIN role. The manual method

described above for ordinary users will work, by granting RDB$ADMIN to each specific administrator, database

by database.

However, there is a way for the SYSDBA to configure the server so that the RDB$ADMIN role will be mapped

to administrators automatically when they log into any database, thus arriving at a situation parallel to the association

of root-privileged users on POSIX systems with the SYSDBA privileges. The new ALTER ROLE

statement achieves this (and only this) purpose.

ALTER ROLE Statement

To configure a database to map the RDB$ADMIN role to administrators automatically on a Windows server

that is configured to enable trusted user authentication, the SYSDBA logs in to any database and issues the

following statement:

ALTER ROLE RDB$ADMIN

SET AUTO ADMIN MAPPING;

To revert to the default setting, preventing administrators from getting SYSDBA privileges automatically, issue

this statement:

ALTER ROLE RDB$ADMIN

DROP AUTO ADMIN MAPPING;

Services API Tag Items

The same effects are supported in the Services API by the provision of two tag items:

isc_action_svc_set_mapping to enable the automatic mapping and isc_action_svc_drop_mapping to disable

it.

Administrative Features

These tags are supported in the fbsvcmgr utility.

Escalating RDB$ADMIN Scope for User Management

The new DDL command ALTER USER enables an “ordinary” user (a regular Firebird user, a non-root user

on POSIX or a trusted user on a Windows system where trusted authentication is enabled) the ability to change

his or her password and/or personal name elements, while logged in to any database. Superusers can also use

the same command to create and drop users. For more information about this new command, refer to the topic

CREATE/ALTER/DROP USER in the chapter Data Definition Language.

Because security2.fdb is created as (or should be upgraded to) an ODS 11.2 database, it has the pre-defined RDB

$ADMIN role, too. Since no user—not even SYSDBA—can log into the security database, alternative means

have been provided to enable the SYSDBA or a Superuser to apply the RDB$ADMIN role in security2.fdb to

an ordinary user that needs the ability to create or drop users. There are three ways, each having the equivalent

effect:

Use the optional parameter GRANT ADMIN ROLE with a CREATE USER or ALTER USER statement.

Notes

Notice that GRANT ADMIN ROLE and REVOKE ADMIN ROLE here are not GRANT/REVOKE statements

but 3-keyword parameters to the CREATE USER and ALTER USER statements. There is no system

role named 'ADMIN'.

Any user that acquires the RDB$ADMIN role in a database implicitly acquires the extended privileges

WITH ADMIN OPTION and WITH GRANT OPTION.

Examples

To grant the RDB$ADMIN role to user alex in the security database:

ALTER USER alex GRANT ADMIN ROLE;

To revoke the RDB$ADMIN role from user alex in the security database:

ALTER USER alex REVOKE ADMIN ROLE;

To drop user alex and destroy his privileges in all databases:

DROP USER alex;

Use the gsec utility with the new switch -admin. The switch takes one argument: YES to apply the RDB

$ADMIN role to the user, or NO to revoke it. For more details, refer to the topic Granting the RDB$ADMIN

Role to An Ordinary User in the gsec section of the Utilities chapter.

Use the new SPB parameter isc_spb_sec_admin which implements the assignment of the RDB$ADMIN

role for ordinary users in security2.fdb via a SPB connection. It is described in more detail in the chapter

Changes to the Firebird API and ODS, in the topic Parameter isc_spb_sec_admin.

Administrative Features

The fbsvmgr utility also supports the use of this parameter.

Tip

Firebird 2.5 does not allow you to set up more than one security database on a server. From V.3.0, it is intended

to be possible to have separate security databases for each database. For now, you can be connected to any

database on the server (even employee.fdb) to update its one-and-only security2.fdb.

In future, it will be essential to send these requests from a database that is associated with the security database

that is to be affected by them.

Trace and Audit Services

Vlad Khorsun

The new trace and audit facilities in v.2.5 were initially developed from the TraceAPI contributed to us by

Nickolay Samofatov that he had developed for the Red Soft Database, a commercial product based on Firebird's

code.

Overview of Features

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.

The System Audit Session

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 configuration file contains list of traced events and points to the placement of the trace log(s) for each event.

It is sufficiently flexible to allow different sets of events for different databases to be logged to separate log

files. The template file file fbtrace.conf, found in Firebird's root directory, contains the full list of available

events, with format, rules and syntax for composing an audit trace configuration file.

Administrative Features

Note

Initially, regular expression database names configured in fbtrace.conf were (wrongly) being matched

byte-by-byte. The matching algorithm was improved to interpret strings in the configuration file according to

the platform character set and, basing the strings on UTF-8, to apply platform rules, e.g., treating file names

as case-insensitive on Windows and case-sensitive on POSIX.

(Tracker reference CORE-2404, A. dos Santos Fernandes).

User Trace Sessions

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:

• start: isc_action_svc_trace_start

• stop: isc_action_svc_trace_stop

• suspend: isc_action_svc_trace_suspend

• resume: isc_action_svc_trace_resume

• list all known trace sessions: isc_action_svc_trace_list

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.

Workings of a User Trace Session

When a user application starts a trace session, it sets a session name (optional) and the session configuration

(mandatory). The session configuration is a text file conforming to the rules and syntax modelled in the fbtrace.

conftemplate that is in Firebird's root directory, apart from the lines relating to placement of the output.

Note

Such files obviously do not live on the server. It will be the job of the application developer to design a suitable

mechanism for storing and retrieving texts for passing in the user trace request.

For example, the command-line fbsvcmgr utility supports a saved-file parameter, trc_cfg.

The output of a user session is stored in set of temporary files, each of 1 MB. Once a file has been completely

read by the application, it is automatically deleted. By default, the maximum total size of the output is limited

to 10 MB. It can be changed to a smaller or larger value using the MaxUserTraceLogSize in firebird.conf.

Once the user trace session service has been started by the application, the application has to read its output,

using calls to isc_service_query(). The service could be generating output faster than the application can read

it. If the total size of the output reaches the MaxUserTraceLogSize limit, the engine automatically suspends the

trace session. Once the application has finished reading a file (a 1 MB part of the output) that file is deleted,

capacity is returned and the engine resumes the trace session automatically.

At the point where the application decides to stop its trace session, it simply requests a detach from the service.

Alternatively, the application can use the isc_action_svc_trace_* functions to suspend, resume or stop the trace

session at will.

Administrative Features

Who Can Manage Trace Sessions?

Any user can initiate and manage a trace session. An ordinary user can request a trace only on its own connections

and cannot manage trace sessions started by any other users. Administrators can manage any trace session.

Abnormal Endings

If all Firebird processes are stopped, no user trace sessions are preserved. What this means is that if a Superserver

or Superclassic process is shut down, any user trace sessions that were in progress, including any that were

awaiting a resume condition, are fully stopped and a resume cannot restart them.

Note

This situation doesn't apply to the Classic server, of course, since each connection involves its own dedicated

server instance. Thus, there is no such thing as “shutting down” a Classic server instance. No service instance

can outlive the connection that instigated it.

User Trace Sample Configuration Texts

The following samples provide a reference for composing configuration texts for user trace sessions.

Trace prepare, free and execution of all statements within connection 12345

enabled true

connection_id 12345

log_statement_prepare true

log_statement_free true

log_statement_start true

log_statement_finish true

time_threshold 0

</database>

Trace all connections of given user to database mydatabase.fdb, logging executed INSERT, UPDATE

and DELETE statements and nested calls to procedures and triggers, and show corresponding PLANs and

performance statistics.

enabled true

include_filter %(INSERT|UPDATE|DELETE)%

log_statement_finish true

log_procedure_finish true

log_trigger_finish true

print_plan true

print_perf true

time_threshold 0

</database>

Administrative Features

Command-line Requests for User Trace Services

A new command-line utility, named fbtracemgr, has been added for working interactively with trace services.

It has its own syntax of switches and parameters, discussed in detail, with examples, in the Utilities chapter.

As well, the general Services utility, fbsvcmgr, can be used for submitting service requests from the command

line, as exemplified in the following examples.

Start a user trace named “My trace” using a configuration file named fbtrace.confand read its output

on the screen:

fbsvcmgr service_mgr action_trace_start trc_name "My trace" trc_cfg fbtrace.conf

To stop this trace session, press Ctrl+C at the fbsvcmgr console prompt. (See also (e), below).

List trace sessions:

fbsvcmgr service_mgr action_trace_list

Suspend trace sesson with ID 1

fbsvcmgr service_mgr action_trace_suspend trc_id 1

Resume trace sesson with ID 1

fbsvcmgr service_mgr action_trace_resume trc_id 1

Stop trace sesson with ID 1

fbsvcmgr service_mgr action_trace_stop trc_id 1

Tip

List sessions (see b.) in another console, look for the ID of a session of interest and use it in the current

console to stop the session.

Trace Scope on Windows

Tracker reference CORE-2588

On Windows, the trace tools exhibit shared memory conflicts if multiple engine instances in different Windows

sessions are allowed to run trace in global namespace. Tracing scope has therefore been restricted to only those

processes that are accessible from the current Windows session.

Administrative Features

Use Cases

There are three general use cases:

Constant audit of engine activity

This is served by system audit trace. Administrator creates or edits the trace configuration file, sets its name

via the AuditTraceConfigFile setting in firebird.conf and restarts Firebird. Later, the administrator could

suspend, resume or stop this session without needing to restart Firebird.

Important

To make audit configuration changes known to the engine, Firebird must be restarted.

On-demand interactive trace of some (or all) activity in some (or all) databases

An application (which could be the fbsvcmgr utility) starts user trace session, reads its output and shows

traced events to the user in real time on the screen. The user can suspend and resume the trace and, finally,

stop it.

Engine activity collection for a significant period of time (a few hours or perhaps even a whole day)

for later analysis

An application starts a user trace session, reading the trace output regularly and saving it to one or more

files. The session must be stopped manually, by the same application or by another one. If multiple trace

sessions are running, a listing can be requested in order to identify the session of interest.

Monitoring Improvements

Dmitry Yemanov

Firebird 2.5 sees the enhancement of the “MON$” database monitoring features introduced in V.2.1, with new

tables delivering data about context variables and memory usage in ODS 11.2 and higher databases. Also, in

these databases, it becomes possible to terminate a client connection from another connection through the MON

$ structures.

Extended Access for Ordinary Users

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.

Administrative Features

Notes

For an application architecture that entails a middleware tier logging in multiple times concurrently with

the same user name on behalf of different end users, consideration should be given to the impact on

performance and privacy of exposing the monitoring features to the end users.

The same extension was implemented in V.2.1.2.

New MON$ Metadata for ODS 11.2 Databases

Note

For the ODS 11.1 metadata please refer to the V.2.1 documentation.

Character Set Change for MON$ Metadata

The system domain RDB$FILE_NAME2, that is used to define those columns in the MON$ tables that pertain

to file specifications has been altered from CHARACTER SET NONE to CHARACTER SET UNICODE_FSS.

The columns currently affected are MON$DATABASE_NAME, MON$ATTACHMENT_NAME and MON

$REMOTE_PROCESS. This change makes the affected data consistent with the updated v.2.5 handling of

filespec and other character parameter items in the DPB.

(Tracker entry CORE-2551, A. dos Santos Fernandes)

MON$MEMORY_USAGE (current memory usage)

-MON$STAT_ID (statistics ID)

-MON$STAT_GROUP (statistics group)

0: database

1: attachment

2: transaction

3: statement

4: call

-MON$MEMORY_USED (number of bytes currently in use)

High-level memory allocations performed by the engine from its pools.

Can be useful for tracing memory leaks and for investigating unusual

memory consumption and the attachments, procedures, etc. that might

be responsible for it.

-MON$MEMORY_ALLOCATED (number of bytes currently allocated at the OS level)

Low-level memory allocations performed by the Firebird memory manager.

These are bytes actually allocated by the operating system, so it enables

the physical memory consumption to be monitored.

Administrative Features

Note

Not all records have non-zero values. On the whole, only MON$DATABASE and memory-bound objects

point to non-zero “allocated” values. Small allocations are not allocated at this level, being redirected to

the database memory pool instead.

-MON$MAX_MEMORY_USED (maximum number of bytes used by this object)

-MON$MAX_MEMORY_ALLOCATED (maximum number of bytes allocated from

the operating system by this object)

MON$CONTEXT_VARIABLES (known context variables)

-MON$ATTACHMENT_ID (attachment ID)

Contains a valid ID only for session-level context variables.

Transaction-level variables have this field set to NULL.

-MON$TRANSACTION_ID (transaction ID)

Contains a valid ID only for transaction-level context variables.

Session-level variables have this field set to NULL.

-MON$VARIABLE_NAME (name of context variable)

-MON$VARIABLE_VALUE (value of context variable)

Memory Usage in MON$STATEMENTS and MON$STATE

Memory usage statistics in MON$STATEMENTS and MON$STATE represent actual CPU consumption.

Tracker reference: CORE-1583)

Usage Notes

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

Administrative Features

FROM MON$CONTEXT_VARIABLES VAR

WHERE VAR.MON$ATTACHMENT_ID = CURRENT_CONNECTION

Terminating a Client

The MON$ structures are, by design, read-only. Thus, user DML operations on them are prohibited. However,

a mechanism is built in to allow deleting (only) of records in the MON$STATEMENTS and MON$ATTACHMENTS

tables. The effect of this mechanism is to make it possible, respectively, to cancel running statements

and, for ODS 11.2 databases, to terminate client sessions.

To cancel all current activity for a specified connection:

DELETE FROM MON$STATEMENTS

WHERE MON$ATTACHMENT_ID = 32

To disconnect all clients except the “Me” connection:

DELETE FROM MON$ATTACHMENTS

WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION

Note

• A statement cancellation attempt becomes a void operation (“no-op”) if the client has no statements

currently running.

• Upon cancellation, the execute/fetch API call returns the isc_cancelled error code.

• Subsequent operations are allowed.

• Any active transactions in the connection being terminated will have their activities cancelled immediately

and they are rolled back.

• Once terminated, the client session receives the isc_att_shutdown error code.

• Subsequent attempts to use this connection handle will cause network read/write errors.

Chapter 8

Security Hardening

Windows Platforms

No SYSDBA Auto-mapping (Windows)

In V.2.1, members of administrative Windows groups were mapped to SYSDBA by default. From V.2.5 forward,

automatic SYSDBA mapping is controlled on per-database basis using the new SQL command

ALTER ROLE RDB$ADMIN SET/DROP AUTO ADMIN MAPPING

Note

For a full overview of the RDB$ADMIN role, refer to the topic New RDB$ADMIN System Role in the Administrative

Features chapter.

Chapter 9

Data Definition

Language (DDL)

V.2.5 brings a few significant additions and enhancements to DDL.

Quick Links

• CREATE/ALTER/DROP USER

• Syntaxes for Altering Views

• SSP Extension for CREATE VIEW

• ALTER Mechanism for Computed Columns

• GRANTED BY Extension for GRANT and REVOKE

• ALTER ROLE

• REVOKE ALL

• Default COLLATION Attribute for a Database

• ALTER CHARACTER SET

• The DIFFERENCES FILE Argument for CREATE DATABASE

Although this release emphasises architectural changes in the movement towards Firebird 3, a number of improvements

and extensions have been implemented, in many cases as a response to feature requests in the Tracker.

Visibility of Procedure Definition Changes on Classic

Dmitry Yemanov

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.

CREATE/ALTER/DROP USER

Alex Peshkov

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.

The CREATE USER and ALTER USER statements also include the parameters GRANT ADMIN ROLE and

REVOKE ADMIN ROLE to enable a user with SYSDBA privileges user to grant the RDB$ADMIN role in the

Data Definition Language (DDL)

security database to an ordinary user. For the usage description and a full overview of the RDB$ADMIN role,

refer to the topic New RDB$ADMIN System Role in the Administrative Features chapter.

Syntax Patterns

The SYSDBA, or a user with SYSDBA privileges in both the current database and the security database, can

add a new user:

CREATE USER <username> {PASSWORD 'password'}

[FIRSTNAME 'firstname']

[MIDDLENAME 'middlename']

[LASTNAME 'lastname']

[GRANT ADMIN ROLE];

Note

The PASSWORD clause is required when creating a new user. It should be the initial password for that new

user. The user can change it later, using ALTER USER.

The SYSDBA, or a user with SYSDBA privileges in both the current database and the security database, 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']

[{GRANT | REVOKE} ADMIN ROLE];

Note

At least one of PASSWORD, FIRSTNAME, MIDDLENAME or LASTNAME must be present.

ALTER USER does not allow the <username> to be changed. If a different <username> is required, the old

one should be deleted (dropped) and a new one created.

The SYSDBA, or a user with SYSDBA privileges in both the current database and the security database, can

delete a user:

DROP USER <username>;

Restrictions

CREATE USER and DROP USER statements and GRANT | REVOKE ADMIN ROLE are available only for

the SYSDBA, or a user that has acquired the RDB$ADMIN role in both the current database and 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.

Data Definition Language (DDL)

Examples

SYSDBA or a user with equivalent privileges in both the current database and the security database, can do:

CREATE USER alex PASSWORD 'test';

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

ALTER USER alex PASSWORD 'IdQfA';

Syntaxes for Altering Views

Adriano dos Santos Fernandes

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.

ALTER VIEW

ALTER VIEW enables a view definition to be altered without the need to recreate (drop and create) the old

version of the view and all of its dependencies.

CREATE OR ALTER VIEW

With CREATE OR ALTER VIEW, the view definition will be altered (as with ALTER VIEW) if it exists, or

created if it does not exist.

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;

Data Definition Language (DDL)

Extensions for CREATE VIEW

The following extensions have been added for CREATE VIEW.

Specify Stored Procedure in FROM Clause

Adriano dos Santos Fernandes

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

Create UNION View Without Column List

Dmitry Yemanov

Tracker reference CORE-1402.

The column list can now be omitted from CREATE VIEW when the set is defined by a UNION.

Example

recreate view V1 as

select d.rdb$relation_id from rdb$database d

union all

select d.rdb$relation_id from rdb$database d

recreate view V2 as

select d.rdb$relation_id as q from rdb$database d

union all

select d.rdb$relation_id as w from rdb$database d

Inferred Column Names

Adriano dos Santos Fernandes

Tracker reference CORE-2424.

CREATE VIEW now has the capability to infer column names for views involving a GROUP BY clause or

a derived table.

Example

create view V as

select d.rdb$relation_id from rdb$database d

Data Definition Language (DDL)

group by d.rdb$relation_id

create view V as

select a from (select 1 a from rdb$database);

ALTER Mechanism for Computed Columns

Adriano dos Santos Fernandes

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

Extensions for SQL Permissions

Alex Peshkov

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

GRANTED BY Clause

A GRANTED BY or GRANTED AS clause can now be optionally included in GRANT and REVOKE statements,

enabling the grantor to be a user other than the CURRENT_USER (the default).

Syntax Pattern

grant <right> to <object>

[ { granted by | as } [ user ] <username> ]

revoke <right> from <object>

[ { granted by | as } [ user ] <username> ]

Data Definition Language (DDL)

Tip

GRANTED BY and GRANTED AS are equivalent. GRANTED BY is the form recommended by the SQL

standard. We support GRANTED AS for compatibility with some other servers (Informix, for example).

Example

Logged in as SYSDBA:

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;

In isql, we look at the effects:

SQL>show grant;

/* Grant permissions for this database */

GRANT R1 TO PUBLIC GRANTED BY USER1

GRANT R1 TO USER1 WITH ADMIN OPTION

SQL>

ALTER ROLE

Tracker reference CORE-1660.

The new ALTER ROLE statement has a specialised function to control the assignment of SYSDBA permissions

to Windows administrators during trusted authentication. It has no other purpose currently.

Note

For the usage description of ALTER ROLE and a full overview of the RDB$ADMIN role, refer to the topic

New RDB$ADMIN System Role in the Administrative Features chapter.

REVOKE ALL

Tracker reference CORE-2113.

When a user is removed from the security database or another authentication source, such as the operating system

ACL, any associated cleanup of SQL privileges in databases has to be performed manually. This extension adds

the capability to revoke all privileges in one stroke from a particular user or role.

Syntax Pattern

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

Data Definition Language (DDL)

Example

Logged in as SYSDBA:

# gsec -del guest

# isql employee

fbs bin # ./isql employee

Database: employee

SQL> REVOKE ALL ON ALL FROM USER guest;

SQL>

Default COLLATION Attribute for a Database

Adriano dos Santos Fernandes

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.

Tip

Note also that the default collation order for a character set used in a database can now also be changed, thanks

to the introduction of syntax for ALTER CHARACTER SET.

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

Note

The parameter DIFFERENCE FILE is not a new one for CREATE DATABASE. It was quietly introduced

in association with the nBackup utility in V.2.0 and has been lurking undocumented for three years. For more

information, see Evolution of CREATE DATABASE at the end of this chapter.

Example

create database 'test.fdb'

default character set win1252

Data Definition Language (DDL)

collation win_ptbr;

ALTER CHARACTER SET Command

Adriano dos Santos Fernandes

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.

Note

String constants also use the default collation of the connection character set.

The character set and collation of existing columns are not affected by ALTER CHARACTER SET changes.

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

Tip

Another improvement allows the current value of RDB$DEFAULT_COLLATE_NAME in the system table

RDB$CHARACTER_SETS to survive the backup/restore cycle.

Data Definition Language (DDL)

Evolution of CREATE DATABASE

DDL support for the database header attributes introduced to register and change the nBackup states has been

evolving since Firebird 2.0. Users of nBackup will be familiar with the ALTER DATABASE statements to

begin and end the storage of the “delta” data, in a file apart from the main database, during a full nBackup.

Naming the Delta File for nBackup

ALTER DATABASE also has another argument that allows you to set the name that will be used for the file

that stores the delta data. To quote from Paul Vinkenoog's excellent manual for nBackup:

By default, the delta file lives in the same directory as the database itself. It also has the same name as the

database file, but with .delta appended. There is usually no reason to change this, but it can be done if need be

—though not via nbackup itself.

Make a connection to the database with any client that allows you to enter your own SQL statements and give

the command:

alter database

add difference file 'path-and-filename'

The custom delta file specification is persistent in the database; it is stored in the system table RDB$FILES.

To revert to the default behaviour, issue the following statement:

alter database

drop difference file

Those who are still curious may study the details in the V.2.0 release notes or in the nBackup manual.

The DIFFERENCES FILE Argument for CREATE DATABASE

C. Valderrama

In Firebird 2.0, syntax for prescribing a custom name for the delta file appeared as an extra, optional argument

for CREATE DATABASE. You can observe its placement in the syntax pattern given above for the topic

Default COLLATION Attribute for a Database. As with ALTER DATABASE, the keyword for the argument

is DIFFERENCE FILE and the argument is a valid file specification. It allows you to specify a custom name

for the delta file that will be created whenever ALTER DATABASE BEGIN BACKUP is called, or when the

equivalent nBackup shell command is invoked.

Examples of Usage

]..\bin> isql -user sysdba -pass masterke

Use CONNECT or CREATE DATABASE to specify a database

SQL> create database 'ticks' difference file 'jaguar';

SQL> shell dir jaguar;

Volume in drive F is Firebird

Data Definition Language (DDL)

Volume Serial Number is BCD9-4211

Directory of ..\bin

File Not Found

This is correct, we only defined the file name. Now it will be used:

SQL> alter database begin backup;

SQL> shell dir jaguar;

Volume in drive F is Firebird

Volume Serial Number is BCD9-4211

Directory of ..\bin

10-11-2009 00:59 8.192 jaguar

1 File(s) 8.192 bytes

0 Dir(s) 16.617.979.904 bytes free

SQL> alter database end backup;

SQL> shell dir jaguar;

Volume in drive F is Firebird

Volume Serial Number is BCD9-4211

Directory of ..\bin

SQL> drop database;

SQL> .Z

Since the argument is a file name, it goes inside single quotes. Double-quotes are not valid: the statement will

fail and return a confusing error message.

]..\bin> isql -user sysdba -pass masterke

Use CONNECT or CREATE DATABASE to specify a database

SQL> create database 'ticks' difference file 'jaguar';

SQL> alter database add difference file 'leopard';

Statement failed, SQLCODE = -607

unsuccessful metadata update

-Difference file is already defined

The message is correct. Even though the delta was deleted by the ALTER DATABASE END BACKUP call,

the name of the difference file is stored persistently and only one may exist.

SQL> alter database drop difference file;

SQL> alter database begin backup;

Statement failed, SQLCODE = -607

unsuccessful metadata update

-STORE RDB$FILES failed

-message length error (encountered 278, expected 276)

It is not a user friendly message but it means we have no difference file. Perhaps curiously, the engine does not

rescue the situation and create the delta using the default mechanism.

Data Definition Language (DDL)

SQL> alter database add difference file 'leopard';

SQL> alter database begin backup;

SQL> alter database drop difference file;

Statement failed, SQLCODE = -607

unsuccessful metadata update

-Cannot change difference file name while database is in backup mode

This is correct validation.

SQL> alter database end backup;

SQL> drop database;

SQL> .Z

Chapter 10

Data Manipulation

Language (DML)

In this chapter are the additions and improvements that have been added to the SQL data manipulation language

subset in Firebird 2.5.

Quick Links

• RegEx Search Support using SIMILAR TO

• Hex Literal Support

• New UUID Conversion Functions

• Extension to LIST() Function

• SOME_COL = ? OR ? IS NULL Predication

• Optimizer Improvements

RegEx Search Support using SIMILAR TO

Adriano dos Santos Fernandes

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

<regular expression> ::=

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

<regular term> ::=

| <regular term> <regular factor>

<regular factor> ::=

| <regular primary> <asterisk>

| <regular primary> <plus sign>

| <regular primary> <question mark>

Data Manipulation Language (DML)

| <regular primary> <repeat factor>

<repeat factor> ::=

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

<regular primary> ::=

| <percent>

| <regular character set>

| <left paren> <regular expression> <right paren>

<character specifier> ::=

<non-escaped character>

| <escaped character>

<regular character set> ::=

| <left bracket> <character enumeration>... <right bracket>

| <left bracket> <circumflex> <character enumeration>... <right bracket>

| <left bracket> <character enumeration include>... <circumflex> <character enumeration exclude>...

<character enumeration> ::=

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

Note

<non-escaped character> is any character except <left bracket>, <right bracket>, <left paren>, <right

paren>, <vertical bar>, <circumflex>, <minus sign>, <plus sign>, <asterisk>, <underscore>, <percent>,

<question mark>, <left brace> and <escape character>.

<escaped character> is the <escape character> succeeded by one of <left bracket>, <right bracket>, <left

paren>, <right paren>, <vertical bar>, <circumflex>, <minus sign>, <plus sign>, <asterisk>, <underscore>,

<percent>, <question mark>, <left brace> or <escape character>.

Data Manipulation Language (DML)

Table 10.1. Character class identifiers

Identifier Description Note

ALPHA All characters that are simple latin letters

(a-z, A-Z)

Includes latin letters with accents when

using accent-insensitive collation

UPPER All characters that are simple latin uppercase

letters (A-Z)

Includes lowercase latters when using

case-insensitive collation

LOWER All characters that are simple latin lowercase

letters (a-z)

Includes uppercase latters when using

case-insensitive collation

DIGIT All characters that are numeric digits

(0-9)

SPACE All characters that are the space character

(ASCII 32)

WHITESPACE All characters that are whitespaces (vertical

tab (9), newline (10), horizontal tab

(11), carriage return (13), formfeed (12),

space (32))

ALNUM All characters that are simple latin letters

(ALPHA) or numeric digits (DIGIT)

Usage Guide

Return true for a string that matches <regular expression> or <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>

Data Manipulation Language (DML)

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

'ab' SIMILAR TO '(ab){2}' --false

Data Manipulation Language (DML)

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

'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

Data Manipulation Language (DML)

select * from department

where phone not similar to '$[0-9]{3}$ 555\-%' escape '\';

Hex Literal Support

Bill Oliver

Adriano dos Santos Fernandes

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

Numeric Hex Literals

•

The number of <hexit> in the string cannot exceed 16.

•

If the number of <hexit> is greater than eight, the constant data type is a signed BIGINT. If it is eight or less,

the data type is a signed INTEGER.

Tip

That means 0xF0000000 is -268435456 and 0x0F0000000 is 4026531840.

Binary String Literals

The resulting string is defined as a CHAR(n/2) CHARACTER SET OCTETS, where n is the number of <hexit>.

Examples

select 0x10, cast('0x0F0000000' as bigint)

from rdb$database;

select x'deadbeef'

from rdb$database;

Data Manipulation Language (DML)

New UUID Conversion Functions

Adriano dos Santos Fernandes

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

The function CHAR_TO_UUID() converts the CHAR(32) ASCII representation of a UUID (XXXXXXXXXXXX-

XXXX-XXXX-XXXXXXXXXXXX) to the CHAR(16) OCTETS representation, optimized for storage.

Syntax Model

CHAR_TO_UUID( <string> )

Example

select char_to_uuid('93519227-8D50-4E47-81AA-8F6678C096A1')

from rdb$database;

UUID_TO_CHAR()

The function UUID_TO_CHAR() converts a CHAR(16) OCTETS UUID (as returned by the

GEN_UUID() function) to the CHAR(32) ASCII representation (XXXXXXXX-XXXX-XXXX-XXXXXXXXXXXXXXXX).

Syntax Model

UUID_TO_CHAR( <string> )

Example

select uuid_to_char(gen_uuid())

from rdb$database;

SOME_COL = ? OR ? IS NULL Predication

Adriano dos Santos Fernandes

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

Data Manipulation Language (DML)

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:

While the programmer treated the parameter :param1 as though it were a single variable with two references,

the API could not: it is presented with two parameters

The second parameter is of an unknown data type and the program has no way to assign to it

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

•

If “param1” is not NULL, the language interface is required to assign the correct value for the first parameter,

set the XSQLVAR.sqlind to NOT NULL and leave XSQLVAR.sqldata NULL.

•

If “param2” is NULL, the language interface is required to set the XSQLVAR.sqlind of both parameters to

NULL and leave the XSQLVAR.sqldata NULL.

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

•

XSQLVAR.sqlind should be set in accordance with NULL/NON-NULL state of the parameter. This is the

type of parameter that is described by the new constant SQL_NULL.

•

The XSQLVAR.sqldata of a SQL_NULL type of parameter should always be passed by the client application

as a NULL pointer and should never be accessed.

NULL Specified in the Output Set

When NULL is specified as an output constant (select NULL from ...), it continues to be described as CHAR(1),

rather than by SQL_NULL. That may change in a future version.

Extension to LIST() Function

Adriano dos Santos Fernandes

Tracker reference CORE-1453

Data Manipulation Language (DML)

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;

Optimizer Improvements

Changes in optimizer logic that address recognised problems include:

CROSS JOIN Logic (D. Yemanov)

When a CROSS JOIN involved an empty table, the optimizer had no special logic to detect that the query

was futile and return the empty set immediately. That shortcut logic has now been implemented.

Tracker reference CORE-2200.

Note

The same change was implemented in V.2.1.2.

Derived Tables (A. dos Santos Fernandes)

The limit on the number of contexts available when using derived tables has been raised.

Tracker reference CORE-2029.

Timing of DEFAULT Evaluation (A. dos Santos Fernandes)

Under rare conditions, the early evaluation of a DEFAULT value or expression defined for a column might

give rise to a confused situation regarding the evaluation of an input value supplied for that column. The

possibility was addressed by deferring the evaluation of DEFAULT and not actually performing the evaluation

at all unless it was actually necessary.

Tracker reference CORE-1842.

Index Use for NOT IN Searches (A. dos Santos Fernandes)

Better performance has been achieved for the NOT IN predicate by enabling the use of an index.

Tracker reference CORE-1137.

Undo Log Memory Consumption (D. Yemanov)

Excessive memory consumption by the Undo log after a lengthy series of updates in a single transaction

has been avoided.

Tracker reference CORE-1477.

Other Improvements

Other changes to smooth out the little annoyances include:

Data Manipulation Language (DML)

FREE_IT Error Detection (A. dos Santos Fernandes)

Previously, a UDF declared with FREE_IT would crash if the pointer returned had not been allocated by

the ib_util_malloc() function. Now, such a condition is detected, an exception is thrown and the pointer

remains in its allocated state.

Tracker reference CORE-1937.

“Expression evaluation not supported” message improved (C. Valderrama)

A number of secondary GDS codes were introduced to provide more details about an operation that fails

with an “Expression evaluation not supported” exception, for example:

'Argument for @1 in dialect 1 must be string or numeric'

'Strings cannot be added to or subtracted from DATE or TIME types'

'Invalid data type for subtraction involving DATE, TIME or TIMESTAMP types'

etc.

These detailed messages follow the GDS code for the isc_expression_eval_err (expression evaluation not

supported) error in the status vector.

Tracker reference CORE-1799.

Chapter 11

Procedural SQL (PSQL)

Several significant changes appear in Firebird's procedural language (PSQL), the language set for triggers, stored

procedures and dynamic executable blocks, especially with regard to new extensions to the capabilities of EXECUTE

STATEMENT. This release also heralds the arrival of the “autonomous transaction”.

Quick Links

•

Autonomous Transactions

•

Borrow Database Column Type for a PSQL Variable

•

New Extensions to EXECUTE STATEMENT

-Context Issues

•

Authentication

•

Transaction Behaviour

• Inherited Access Privileges

-External Queries from PSQL

-EXECUTE STATEMENT with Dynamic Parameters

-Examples Using EXECUTE STATEMENT

•

Other PSQL Improvements

-Subqueries as PSQL Expressions

Autonomous Transactions

Adriano dos Santos Fernandes

Tracker reference CORE-1409.

This new implementation allows a piece of code to run in an autonomous transaction within a PSQL module.

It can be handy for a situation where you need to raise an exception but do not want the database changes to

be rolled back.

The new transaction is initiated with the same isolation level as the one from which it is launched. Any exception

raised in a block within the autonomous transaction will cause changes to be rolled back. If the block runs

through until its end, the transaction is committed.

Warning

Because the autonomous transaction is independent from the one from which is launched, you need to use this

feature with caution to avoid deadlocks.

Syntax Pattern

IN AUTONOMOUS TRANSACTION

Procedural SQL (PSQL)

Example of Use

create table log (

logdate timestamp,

msg varchar(60)

);

create exception e_conn 'Connection rejected';

set term !;

create trigger t_conn on connect

begin

if (current_user = 'BAD_USER') then

begin

in autonomous transaction

begin

insert into log (logdate, msg) values (current_timestamp, 'Connection rejected');

end

exception e_conn;

end

end!

set term ;!

Borrow Database Column Type for a PSQL Variable

Adriano dos Santos Fernandes

Tracker reference CORE-1356.

This feature extends the implementation in v.2 whereby domains became available as “data types” for declaring

variables in PSQL. Now it is possible to borrow the data type of a column definition from a table or view for

this purpose.

Syntax Pattern

data_type ::=

<builtin_data_type>

| <domain_name>

| TYPE OF <domain_name>

| TYPE OF COLUMN <table or view>.<column>

Note

TYPE OF COLUMN gets only the type of the column. Any constraints or default values defined for the column

are ignored.

Examples

Procedural SQL (PSQL)

CREATE TABLE PERSON (

ID INTEGER,

NAME VARCHAR(40)

);

CREATE PROCEDURE SP_INS_PERSON (

ID TYPE OF COLUMN PERSON.ID,

NAME TYPE OF COLUMN PERSON.NAME

)

DECLARE VARIABLE NEW_ID TYPE OF COLUMN PERSON.ID;

BEGIN

INSERT INTO PERSON (ID, NAME)

VALUES (:ID, :NAME)

RETURNING ID INTO :NEW_ID;

END

New Extensions to EXECUTE STATEMENT

Unusually for our release notes, we begin this chapter with the full, newly extended syntax for the EXECUTE

STATEMENT statement in PSQL and move on afterwards to explain the various new features and their usage.

[FOR] EXECUTE STATEMENT <query_text> [(<input_parameters>)]

[ON EXTERNAL [DATA SOURCE] <connection_string>]

[WITH {AUTONOMOUS | COMMON} TRANSACTION]

[AS USER <user_name>]

[PASSWORD <password>]

[ROLE <role_name>]

[WITH CALLER PRIVILEGES]

[INTO <variables>]

Note

The order of the optional clauses is not fixed so, for example, a statement based on the following model would

be just as valid:

[ON EXTERNAL [DATA SOURCE] <connection_string>]

[WITH {AUTONOMOUS | COMMON} TRANSACTION]

[AS USER <user_name>]

[PASSWORD <password>]

[ROLE <role_name>]

[WITH CALLER PRIVILEGES]

Clauses cannot be duplicated.

Context Issues

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.

Procedural SQL (PSQL)

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.

Note

In the absence of an AS USER <user_name> clause, CURRENT_USER is the default.

Authentication

Where server authentication is needed for a connection that is different to CURRENT_CONNECTION, e.g., for

executing an EXECUTE STATEMENT command on an external datasource, the AS USER and PASSWORD

clauses are required. However, under some conditions, the PASSWORD may be omitted and the effects will

be as follows:

On Windows, for the CURRENT_CONNECTION (i.e., no external data source), trusted authentication

will be performed if it is active and the AS USER parameter is missing, null or equal to CURRENT_USER.

If the external data source parameter is present and its <connection_string> refers to the same database as

the CURRENT_CONNECTION, the effective user account will be that of the CURRENT_USER.

If the external data source parameter is present and its <connection_string> refers to a different database

than the one CURRENT_CONNECTION is attached to, the effective user account will be the operating

system account under which the Firebird process is currently running.

In any other case where the PASSWORD clause is missing, only isc_dpb_user_name will be presented in the

DPB (attachment parameters) and native authentication will be attempted.

Transaction Behaviour

The new syntax has an optional clause for setting the appropriate transaction behaviour: WITH AUTONOMOUS

TRANSACTION and WITH COMMON TRANSACTION. WITH COMMON TRANSACTION

is the default and does not need to be specified. Transaction lifetimes are bound to the

lifetime of CURRENT_TRANSACTION and are committed or rolled back in accordance with the

CURRENT_TRANSACTION.

The behaviour for WITH COMMON TRANSACTION is as follows:

Causes any transaction in an external data source to be started with the same parameters as

CURRENT_TRANSACTION; otherwise

Executes the statement inside the CURRENT_TRANSACTION; or

May use another transaction that is started internally in CURRENT_CONNECTION.

The WITH AUTONOMOUS TRANSACTION setting starts a new transaction with the same parameters as

CURRENT_TRANSACTION. That transaction will be committed if the statement is executed without exceptions

or rolled back if the statement encounters an error.

Inherited Access Privileges

Vladyslav Khorsun

Tracker reference CORE-1928.

Procedural SQL (PSQL)

By design, the original implementation of EXECUTE STATEMENT isolated the executable code from the

access privileges of the calling stored procedure or trigger, falling back to the privileges available to the

CURRENT_USER. In general, the strategy is wise, since it reduces the vulnerability inherent in providing for

the execution of arbitrary statements. However, in hardened environments, or where privacy is not an issue, it

could present a limitation.

The introduction of the optional clause WITH CALLER PRIVILEGES now makes it possible to have the executable

statement inherit the access privileges of the calling stored procedure or trigger. The statement is prepared

using any additional privileges that apply to the calling stored procedure or trigger. The effect is the same

as if the statement were executed by the stored procedure or trigger directly.

Important

The WITH CALLER PRIVILEGES option is not compatible with the ON EXTERNAL DATA SOURCE

option.

External Queries from PSQL

Vladyslav Khorsun

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.

The <connection_string> Argument

The format of <connection_string> is the usual one that is passed through the API function

isc_attach_database(), viz.

[<host_name><protocol_delimiter>]database_path

Character Set

The connection to the external data source uses the same character set as is being used by the

CURRENT_CONNECTION context.

Access Privileges

If the external data source is on another server then the clauses AS USER <user_name> and PASSWORD

<password> will be needed.

The clause WITH CALLER PRIVILEGES is a no-op if the external data source is on another server.

MORE INFORMATION REQUIRED. ROLES?

Note

Use of a two-phase transaction for the external connection is not available in V.2.5.

Procedural SQL (PSQL)

EXECUTE STATEMENT with Dynamic Parameters

Vladyslav Khorsun

Alex Peshkov

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.

Syntax Conventions

The mechanism employs some conventions to facilitate the run-time parsing and to allow the option of “naming”

parameters in a style comparable with the way some popular client wrapper layers, such as Delphi, handle DSQL

parameters. The API's own convention, of passing unnamed parameters in a predefined order, is also supported.

However, named and unnamed parameters cannot be mixed.

The New Binding Operator

At this point in the implementation of the dynamic parameter feature, to avoid clashes with equivalence tests,

it was necessary to introduce a new assignment operator for binding run-time values to named parameters. The

new operator mimics the Pascal assignment operator:“:=”.

Syntax for Defining Parameters

<input_parameters> ::=

<named_parameter> | <input_parameters>, <named_parameter>

<named_parameter> ::=

Example for named input parameters

For example, the following block of PSQL defines both <query_text> and named <input_parameters>

(<named_parameter>):

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

Procedural SQL (PSQL)

EXECUTE STATEMENT (:S) (a := CURRENT_TRANSACTION, b := CURRENT_CONNECTION)

WITH COMMON TRANSACTION;

N = N -1;

END

Example for unnamed input parameters

A similar block using a set of unnamed input parameters instead and passing constant arguments directly:

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

Note

Observe that, if you use both <query_text> and <input_parameters> then the <query_text> must be enclosed

in parentheses, viz.

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

Examples Using EXECUTE STATEMENT

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

in your applications.

Test Connections and Transactions

A couple of tests you can try to compare variations in settings:

Test a) :Execute this block few times in the same transaction -it will create three new connections to the current

database and reuse it in every call. Transactions are also reused.

EXECUTE BLOCK

RETURNS (CONN INT, TRAN INT, DB VARCHAR(255))

DECLARE I INT = 0;

DECLARE N INT = 3;

DECLARE S VARCHAR(255);

BEGIN

SELECT A.MON$ATTACHMENT_NAME FROM MON$ATTACHMENTS A

Procedural SQL (PSQL)

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

Test b) : Execute this block few times in the same transaction -it will create three new connections to the current

database on every call.

EXECUTE BLOCK

RETURNS (CONN INT, TRAN INT, DB VARCHAR(255))

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

Input Evaluation Demo

Demonstrating that input expressions evaluated only once:

Procedural SQL (PSQL)

EXECUTE BLOCK

RETURNS (A INT, B INT, C INT)

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

Insert Speed Test

Recycling our earlier examples for input parameter usage for comparison with the non-parameterised form of

EXECUTE STATEMENT:

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

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

--Inserts via prepared dynamic statement

--using unnamed input parameters:

Procedural SQL (PSQL)

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

Other PSQL Improvements

Improvements made to existing PSQL syntax include the following:

Subqueries as PSQL Expressions

A. dos Santos Fernandes

Tracker reference CORE-2580

Previously, a subquery used as a PSQL expression would return an exception, even though it was logically valid

in SQL terms. For example, the following constructions would all return errors:

var = (select ... from ...);

if ((select ... from ...) = 1) then

if (1 = any (select ... from ...)) then

if (1 in (select ... from ...)) then

Now, such potentially valid expressions are allowed, removing the need to jump through hoops to fetch the

output of a scalar subquery into an intermediate variable using SELECT...INTO.

Chapter 12

International Language

Support (INTL)

Adriano dos Santos Fernandes

Some improvements appear in this release to tighten and enhance Firebird's handling capabilities for international

language environment.

Default COLLATION Attribute for a Database

Databases of ODS 11.2 and higher can now optionally be created with a default collation associated with the

default character set. For details, please see Default COLLATION Attribute for a Database in the DDL chapter.

ALTER CHARACTER SET Command

DDL syntax has been introduced to enable the default collation for a character set to be set at database level.

For details, please see ALTER CHARACTER SET Command in the DDL chapter.

Connection Strings & Character Sets

Capability has been implemented in the API database connection (DPB) area to interoperate with the character

set and/or code page of server and client, to avoid the previous problems that could occur when file names

contained non-ASCII characters.

Refer to the topic Connection Strings & Character Sets in the chapter Changes to the Firebird API and ODS.

Even if you are not normally interested in the API, this topic will be a worthwhile read if you have been bothered

with such issues.

Other Improvements

Introducer Syntax Usage

The usage of introducer syntax, i.e., prefixing an underscore to a character set name, to force the succeeding text

literal to transliterated to that character set, has caused some problems in situations where a single SQL statement

entails usage of more than one character set. The actual problems differ from version to version, showing up as

transliteration errors, malformed string errors or just as some kind of unexpected behaviour.

Problems could occur in two different usage scenarios.

International Language Support (INTL)

One query is employing the introducer syntax when another query perfoms a select from MON$STATEMENTS

Introducer syntax was used in a PSQL module

To enable a workaround for such problems, it is now possible to transform the literal string into the hex representation

of the ASCII characters being submitted by the introducer. For example:

select _dos850 '123ae456' from rdb$database

may be transformed to

select _dos850 X'313233A082343536' from rdb$database

Malformed UNICODE_FSS Characters Disallowed

Tracker reference CORE-1600.

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

Repair Switches for Malformed Strings

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.

Numeric Sort Attributes

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

NUMERIC-SORT={0 | 1}

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

100

International Language Support (INTL)

1 sorts numerals in numerical order. For example:

100

Example

create collation unicode_num for utf8

from unicode 'NUMERIC-SORT=1';

Character Sets and Collations

UNICODE_CI_AI

Tracker reference CORE-824.

UNICODE_CI_AI: case-insensitive, accent-insensitive collation added for UNICODE.

WIN_1258

Tracker reference CORE-2185.

Added alias WIN_1258 for WIN1258 character set, for consistency with other WIN* character sets.

SJIS and EUCJ Character Sets

Tracker reference CORE-2103.

Strings in SJIS and EUCJ character sets are now verified for well-formedness.

Character set GB18030

Tracker reference CORE-2636.

GB18030 is a Chinese national standard describing the required language and character support necessary for

software in China. It has been activated from ICU.

Chapter 13

Command-line Utilities

Incompatibility with Older Clients

To enable the 32-bit tools to work correctly with new structures that enable statistics routines to work properly

with a 64-bit server, it was necessary to introduce some new internal API functions (struct perf64 and

perf64_xxx) and change isql and qli to use them. This means that the isql and qli programs in V.2.5 are not

compatible with older Firebird clients.

For more information, see the topic Statistics Now Work Properly with 64-bit Values in the Engine chapter.

fbtracemgr

Vlad Khorsun

Tracker reference CORE-2524.

This is a new CLI utility for interfacing with the new trace facilities. Usage is as follows.

fbtracemgr <action>> [<parameters>]

Action Switches

-STA[RT] Start trace session

-STO[P] Stop trace session

-SU[SPEND] Suspend trace session

-R[ESUME] Resume trace session

-L[IST] List existing trace sessions

Parameters

Action parameters

-N[AME] <string> Session name

-I[D] <number> Session ID

-C[ONFIG] <string> Trace configuration file name

Connection parameters

-SE[RVICE] <string> Service name

-U[SER] <string> User name

Command-line Utilities

-P[ASSWORD] <string> Password

-FE[TCH] <string> Fetch password from file

-T[RUSTED] <string> Force trusted authentication

Examples using fbtracemgr

fbtracemgr -SE remote_host:service_mgr -USER SYSDBA -PASS masterkey -LIST

fbtracemgr -SE service_mgr -START -NAME my_trace -CONFIG my_cfg.txt

fbtracemgr -SE service_mgr -SUSPEND -ID 2

fbtracemgr -SE service_mgr -RESUME -ID 2

fbtracemgr -SE service_mgr -STOP -ID 4

Notes

1. All switches and parameter identifiers are case-insensitive

2. To stop an interactive trace session on any platform, press Ctrl-C.

Retrieve Password from a File or Prompt

Alex Peshkov

Any command-line utility that takes a -password parameter is vulnerable to password sniffing, especially when

the utility is run from a script. Since v.2.1, the [PASSWORD] argument has displayed in the process list on

POSIX platforms as an asterisk ( * ), which was an improvement on showing it in clear.

As a second stage towards hiding the password from unauthorised eyes, this release enables it to be retrieved

from a file or (on POSIX) from STDIN.

New -fetch_password Switch

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.

PLEASE NOTE

1. The exception to the rules is the qli utility, for which only -F is valid.

2. The new switch cannot be applied to substitute for the -pw switch of the gsec utility.

Usage of -fetch_password

The switch requires one parameter, an unquoted string that is the file path for the file containing the password.

If the call is not made by a system user with Superuser/Administrator privileges, the location must be accessible

by the system user making the call.

Command-line Utilities

For example,

isql -user sysdba -fet passfile server:employee

extracts the first line of from a file named “passfile” in the current working directory and loads it into the

[PASSWORD] argument of the call.

The filename can be specified as stdin:

isql -user sysdba -fet stdin server:employee

If stdin is the terminal, a prompt is presented—

Enter password:

—requiring the operator to type in the password.

Tip

On POSIX, the operator will also be prompted if s/he specifies

-fetch /dev/tty

This technique could be useful if, for example, you needed to restore from stdin (all one line):

bunzip2 -c emp.fbk.bz2 | gbak -c stdin /db/new.fdb

-user sysdba -fetch /dev/tty

gsec

The following improvements have been added for gsec:

Mapping Switch for Windows Administrators

Alex Peshkov

Since v.2.1, Windows domain administrators have had full access to the user management functions. In v.2.5

they do not get these privileges automatically unless the SYSDBA has configured the security database to make

it happen automatically.

In the Administrative Features chapter is a detailed overview of the new system role RDB$ADMIN. There, you

will find descriptions of the new ALTER ROLE syntax that can be used by the SYSDBA to enable or disable the

automatic mapping of Windows administrators to the RDB$ADMIN role in databases, including the security

database which they access when creating, altering and dropping users.

Command-line Utilities

This automatic mapping can also be done in a gsec command-line call, using the new -mapping switch.

Mapping an OS Administrator to the RDB$ADMIN Role

The new -mapping switch is used to enable or disable the association of an operating system user with the RDB

$ADMIN role in the security database. It takes one argument: either set to enable the association or drop to

disable it. The syntax is:

-mapping {set | drop}

Granting the RDB$ADMIN Role to a Firebird User

The introduction of the RDB$ADMIN system role has made it possible to escalate the privileges of an ordinary

user. However, it was (and still is) not possible any for any user, even SYSDBA, to attach directly to the security

database and grant the required permissions for the user to manage other users. A parameter—GRANT ADMIN

ROLE—was included in the new CREATE USER and ALTER USER statement syntaxes to enable SYSDBA,

or another user that has already acquired the RDB$ADMIN role in the security database, to have the RDB

$ADMIN role applied to an ordinary user “at arm's length”, as it were.

The same can be achieved in gsec using the new switch -admin. It takes one argument: either YES (to grant the

RDB$ADMIN role to the specified user in security2.fdb) or NO (to revoke it). The syntax is:

-admin {YES | NO}

Command-line Help for gsec

Claudio Valderrama

Tracker reference: CORE-756)

Parameter help has been implemented for gsec, accessible by using the -help or -? switches.

fbsvcmgr

Additions made to gsec and the Service Parameter Block (SPB) relating to the system role RDB$ADMIN are

covered by the appropriate support in the fbsvcmgr utility.

•

for gsec -mapping, two new tag items: isc_action_svc_set_mapping and isc_action_svc_drop_mapping

•

for gsec -admin: the new parameter isc_spb_sec_admin, of spb_long with either 0 as its value (meaning

REVOKE ADMIN ROLE) or non-zero (meaning GRANT ADMIN ROLE).

For a full overview of the RDB$ADMIN role, refer to the topic New RDB$ADMIN System Role in the Administrative

Features chapter.

Command-line Utilities

gbak

Repair Switches for Malformed Strings

Adriano dos Santos Fernandes

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

Preserve Character Set Default Collation

Adriano dos Santos Fernandes

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.

nBackup

An improvement has been done for POSIX versions to address a problem whereby the full backup tool of

the nBackup incremental backup utility would hog I/O resources when backing up large databases, bringing

production work to a standstill. Now, nBackup tries to read from the operating system cache before attempting

to read from disk, thus reducing the I/O load substantially.

Note

The “cost” may be a 10 to 15 percent increase in the time taken to complete the full backup under high-load

conditions.

Tracker reference CORE-2316.

isql

Some changes to the isql interactive query tool have been implemented.

Command-line Utilities

SQLSTATE instead of SQLCODE

Claudio Valderrama

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.

Improvement for Exponential Number Output

Claudio Valderrama

Tracker reference CORE-1171.

isql has always output different formatting of numbers on Windows and POSIX for two-digit exponents. The

default behaviour of Microsoft and Intel compilers is to zero-pad the exponent to three digits regardless. For

example,

select cast ('-2.488355210669293e+39' as double precision)

from rdb$database;

• On POSIX, the result is -2.488355210669293e+39

• On Windows, the result was -2.488355210669293e+039

The isql output has been modified so that the Windows output now conforms with that on other platforms.

SHOW COLLATIONS in isql Help

Help for SHOW COLLATIONS has been added to the command-line help for isql. (Tracker reference

CORE-2432, A. dos Santos Fernandes).

gpre (Precompiler)

Some Updates

Stephen Boyd

Adriano dos Santos Fernandes

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.

Command-line Utilities

Deprecated Features with Future Impact on Utilities

In anticipation of the dropping of the intrinsic function isc_ddl from the Firebird 3 codebase, certain features

currently available in the gdef and gpre tools are deprecated—meaning that, whilst they may work in V.2.5,

they will fail in Firebird 3. More details can be found in the Compatibility chapter.

gstat

Claudio Valderrama

Tracker reference CORE-1411.

The gstat statistics reporting utility now has -? and -help switches for requesting help about available switches

and arguments.

Chapter 14

Installation Notes

Installation And Migration Guide

The latest version of Installation and Migration Guide for Firebird versions 2.0.x and 2.1.x is still applicable

to the v.2.5 series. If a copy of this document is not present in your /doc/ directory, you can download it from

the Firebird website.

Some improvements have been done to solve issues that could arise with the binary installation packages.

Linux (POSIX)

The following improvements apply to POSIX installations.

Installation Scripts Cleanup

Alex Peshkov

•

(CORE-2195): the Linux Classic installation scripts were reviewed to improve the assignment of ownership

and access rights to documentation and other components.

•

(CORE-2392): Cleanups of installation scripts for all active POSIX ports for Superserver and Superclassic

were done to address a problem with the Guardian on these platforms.

•

(CORE-2626): The startup scripts in /etc/init.d did not previously take into account the presence of

the directories /var/run/firebird and /tmp/firebird on the host system. Various types of startup

failures were traceable back to this deficiency.

The startup scripts distributed with the V.2.5 and later builds address and solve the problem.

Dedicated Firebird Switches for 'configure'

Alex Peshkov

A lot of the standard configure switches for fine-tuning the installation directories on POSIX platforms do not

work for Firebird.

It was close to impossible to make the standard GNU switches work without changing the defaults for them, a

rigmarole that is far from obvious or easy. Instead, a set of new switches for the configure has been added to

enable fine-level configuration of the locations of Firebird's files.

The ib_util loader was also improved to make the engine work correctly with the configured layout.

Available Switches

Installation Notes

--with-fbbin executables DIR (PREFIX/bin)

--with-fbsbin system admin executables DIR (PREFIX/bin)

--with-fbconf config files DIR (PREFIX)

--with-fblib object code libraries DIR (PREFIX/lib)

--with-fbinclude C/C++ header files DIR (PREFIXinclude)

--with-fbdoc documentation root DIR (PREFIX/doc)

--with-fbudf UDF DIR (PREFIX/UDF)

--with-fbsample examples DIR (PREFIX/examples)

--with-fbsample-db examples database DIR (PREFIX/examples/empbuild)

--with-fbhelp QLI help DIR (PREFIX/help)

--with-fbintl international DIR (PREFIX/intl)

--with-fbmisc misc DIR (PREFIX/misc)

--with-fbsecure-db security database DIR (PREFIX)

--with-fbmsg message files DIR (PREFIX)

--with-fblog log files DIR (PREFIX)

--with-fbglock guardian lock DIR (PREFIX)

--with-fbplugins plugins DIR (PREFIX)

Detection of Path to Firebird's Binaries

Adriano dos Santos Fernandes

Tracker reference: CORE-2398)

A feature has been implemented for POSIX builds, to have Firebird correctly detect the path where its binaries

are installed.

Important

This is an experimental state currently and is disabled in the distributed binaries. To activate it, build Firebird

from source and pass --enable-binreloc to autogen.sh when building.

Windows

Vlad Khorsun

Tracker entry: CORE-2243

Note

Because the changes took effect from V.2.1.2, this discussion also appears as a special topic in the V.2 Installation

and Migration document.

Managing MSCV8 Assemblies

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.

Installation Notes

•

The Microsoft MSI Installer installs shared assemblies into the common special folder SxSfor use by multiple

applications.

•

Private assemblies are distributed with applications and should be put into the application folder. Use of the

\system32folder for assemblies is now prohibited on the XP, Server2003 and Vista platform families.

Installing Runtimes as a Shared Assembly

To install the runtimes as a shared assembly, the deployment system must have MSI 3.0 installed and the user

must have administrative privileges. Often, this is not possible with an application being deployed with Firebird

Embedded: it must be installed ready-to-run. In that case, do not plan to install the runtimes as a shared assembly.

Installing Runtimes as a Private Assembly

To install the MSVC8 run-time libraries as a private assembly its contents—the three DLLs mentioned above

and the assembly's manifest file, Microsoft VC80.CRT.manifest—must be put into every folder where a

dependent binary (.exe or .dll) resides, because of built-in checks for the folders that are the expected location

of the runtimes that are equivalent to the compile-time libraries that were used.

A typical installation of Firebird Embedded would thus require three complete copies of the MSVC8 run-time

assembly: one in the application folder and one each into the \intl and \udf folders. To avoid the issue of bloating

the installation, some changes were done for V.2.1.2 in the way some of the Firebird binaries are built. (See

also Tracker entry CORE-2243).

These are the changes that enable Firebird Embedded to work even if the application structure does not incorporate

the MSVC8 runtime assembly:

The libraries ib_util.dll, fbudf.dll, ib_udf.dll, fbintl.dll are built without any embedded manifest. The effect

is to avoid having the loader search for a MSVC8 assembly in the same folder as corresponding DLL.

For this to work, the host process must have already loaded the MSVC8 run-time via manifest before any

attempt is made to load these secondary DLL's.

fbembed.dll now has code to create and activate the activation context from its own manifest before loading

any secondary DLL that might be required.

Notes

It is highly recommended to use the Microsoft redistribution package to install the MSVC8 run-time! The

executable installer vcredist_x86.exeor vcredist_x64.exe(as appropriate to your kit selection)

should be present in the zip kits for the full installation and the Embedded version. If not, it can be downloaded

from the Microsoft download site.

Third party UDFs must satisfy one of the following requirements if a MSVC8 run-time assembly is installed

as private assembly. When compiling the UDF library, the MSVC8 runtime EITHER:

•

is NOT used

•

is used but the build is done without the embedded manifest

•

is used and the build is done with the embedded manifest—the default option in the MSVC IDE. In

this case the MSVC8 assembly must be in the same folder as the UDF library

Chapter 15

Compatibility Issues

Dmitry Yemanov

For migrating v.2.0.x or v.2.1.x databases to Firebird 2.5, a number of incompatibilities that are likely to affect

existing databases or existing applications should be noted. It is not recommended that you begin a migration

until you have resolved these.

Incompatibility with Older Clients

To enable the 32-bit tools to work correctly with new structures that enable statistics routines to work properly

with a 64-bit server, it was necessary to introduce some new internal API functions (struct perf64 and

perf64_xxx) and change isql and qli to use them. This means that the isql and qli programs in V.2.5 are not

compatible with older Firebird clients.

For more information, see the topic Statistics Now Work Properly with 64-bit Values in the Engine chapter.

Effects of Unicode Metadata

If you have not previously updated text objects within the metadata of your databases to be in character set

UTF8, restoring a database until V.2.5 will fail with “malformed string” errors. To resolve this it is necessary to

pay attention to the files in the /misc/upgrade/metadatadirectory of your installation and to use the new

-fix_fss_data and -fix_fss_metadataswitches in the gbak command line.

Configuration Parameters Removed

The deprecated configuration parameters OldParameterOrdering and CreateInternalWindow are no

longer supported and have been removed from firebird.conf.

Two parameters that allowed tuning of the Lock Manager in previous versions are not required with the new

Lock Manager implementation and have been removed. They are LockSemCountand LockSignal.

The parameter MaxFileSystemCache has been renamed to FileSystemCacheThreshold.

SQL Language Changes

It will be necessary to pay attention to some changes in the SQL language implementation.

Reserved Words

While some new reserved keywords are introduced, generally the overall list of reserved words has been reduced

dramatically by making Firebird's parser grammar allow most of the previously reserved non-standard keywords

as non-reserved. The list of those still reserved, and of SQL standard keywords newly reserved, is available in

the chapter Reserved Words and Changes.

Compatibility Issues

Execution Results

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

Malformed String Errors

Well-formedness checks are now performed on UNICODE_FSS strings and text blobs. If new or existing

UNICODE_FSS is malformed, it will now cause exceptions at execution time.

Logic Change in SET Clause

Previously, when the SET clause of the UPDATE statement assigned new values to columns, the new value

replaced the old value immediately. If the same column was assigned or assigned to more than once, the current

value would be that of the assignment most recently done. In other words, previously, assignment order mattered.

To bring Firebird in line with the standard, from this version forward, only the original value of a column is

accessible to any assignment in the SET clause.

For a period, it is possible to revert to the legacy behavior by setting the temporary parameter OldSetClauseSemantics

in firebird.conf. This parameter will be deprecated and removed in future releases.

Utilities

Be on the watch for the effects of the following changes to the Firebird command-line utilities.

fb_lock_print

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.

Deprecated Features with Future Impact

In anticipation of the dropping of the intrinsic function isc_ddl from the Firebird 3 codebase, certain features

currently available in the gdef and gpre tools are deprecated—meaning that, whilst they may work in V.2.5,

they will fail in Firebird 3.

• gdef will no longer be supported at all. Instead, isql should be used, with regular DDL commands.

• For gpre pre-processing, replace all DDL operations with

EXEC SQL

EXECUTE IMMEDIATE "..."

• In all custom applications, calls to isc_ddl must be replaced with SQL DDL statement requests.

Compatibility Issues

API Changes

Notice the following changes to the application programming interface (API) that is implemented in the client

libraries.

Rejection of Inconsistent TPB Options

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.

Addition of SQL_NULL Constant

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.

Security Hardening

The following changes should be noted.

No SYSDBA Auto-mapping (Windows)

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.

Default Authentication Method (Windows)

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.confshowed mixed as the default.

Compatibility Issues

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)

Chapter 16

Platform Ports

In this chapter are topics about ports of Firebird to non-mainstream platforms, along with notes about changes

and improvements to ports that have been done previously.

IBM eServer z-Series

Linux/s390 (32-bit)

D. Ivanov

A. Peshkov

Tracker reference CORE-2625

Port for Linux/s390 (32-bit) platform, built on s390x architecture.

The patch for this port removes the -DS390X define from prefix.linux_s390x and replaces it with checks for

__s390__ and __s390x__ defines which are presented by gcc. This way, both ports can use the one prefix file.

Note

s390 has no alignment restrictions.

Linux/s390x (64-bit)

D. Horak

A. Peshkov

Tracker reference CORE-2559

Port for Linux/s390x (64-bit) platform.

Linux/sh4 (Renesas SH)

N. Iwamatsu

D. Ivanov

A. Peshkov

Tracker reference CORE-2655

Port for Linux/sh4 (Renesas SH) platform

Platform Ports

Linux/sh4 (Renesas SH) architecture comes in little-and big-endian variants. The port supports both. SH has

alignment constraints.

HP-UX

Lock Table Improvement for HP-UX

A. Peshkov

Tracker reference CORE-2644

All of the POSIX platform ports except HP-UX enable the lock table to grow when the space initially allocated

for it becomes exhausted. For the HP-UX port, the hardware limitations of the PA-RISC platform prevented

the same implementation of dynamic resizing because it does not support remapping the same file region to a

different virtual address in the same process. ISC_remap_file therefore does not work and the lock table could

not grow.

The SAS team had developed a solution in their edition of the Vulcan code to make the lock table expansible

on this platform. It is this solution that has been imported to the Firebird 2.5 port for HP-UX.

Port for Very Old Windows 32-bit Platforms

At the request of N. Samofatov

Tracker reference CORE-2609

It had been signalled that versions of Firebird beyond V. 2.1.x would not support Microsoft Windows 98, ME, or

NT4. However, Red Soft, a commercial company which wants to support Win98/ME and an idiomatic version

of NT4 used by and maintained for government departments in Russia, requested permission for the inclusion of

some re-implemented compiler conditions, isolated in separate units, that would enable the building of “ports”

of Firebird 2.5 to those old or idiomatic Windows platforms.

These ports are not part of the Firebird 2.5 main stream distribution!

• The Windows binaries distributed by the Firebird Project do not contain this support.

• No such ports are subjected to any QA testing by the project.

• Any QA, future maintenance and development or bug-fixing affecting such ports are the responsibility of

those who use them.

Chapter 17

Bugs Fixed

Firebird 2.5 Release Candidate 1

The following bugs were reported as fixed in the first release candidate phase:

Core Engine/DSQL

(CORE-2742) The server could stop responding to a particular client attachment under some conditions.

There would be a small time window during which the remote server would ignore the incoming operation

request it had just received and, thereby, seem to block the client application. The listener thread would remain

operational, but the client would be synchronously waiting for the reply packet that would not come.

It appears rare: the one known condition is a client application sending packets at intervals of 60 seconds, which

just happens to coincide with a timeout for shutting down the inactive worker thread. Reported against Classic,

but SuperServer may also have been affected.

fixed by V. Khorsun

~~~

(CORE-2739) ASSERT at parse.cpp line 22885, caused by a text size mismatch involving introducer

syntax with WIN1251 character set.

fixed by A. dos Santos Fernandes

~~~

(CORE-2731) EXECUTE STATEMENT failed to break out of infinite recursion.

fixed by V. Khorsun

~~~

(CORE-2730) A bus error could occur when working with DB_KEY on RISC machines: when casting from

a literal (which has no alignment requirements) to db_key, alignment is necessary at the QWORD boundary

and should have been enforced.

fixed by A. Peshkov

~~~

(CORE-2729) EXECUTE STATEMENT was using the current connection for its execution, instead of

creating a new attachment.

fixed by V. Khorsun

Bugs Fixed

~~~

(CORE-2722) Storage of a malformed blob was being allowed when copying from a blob with NONE/

OCTETS character set.

fixed by A. dos Santos Fernandes

~~~

(CORE-2721) When SIMILAR TO was applied to UTF-8 fields the pattern length was being miscalculated.

fixed by A. dos Santos Fernandes

~~~

(CORE-2711) Problems with location or version of MSVC8 runtimes for some VS2005 installations

(service packs) would compile a FbEmbed.dllthat could not load ib_util.dll.

fixed by V. Khorsun

~~~

(CORE-2685) System triggers on a view WITH CHECK OPTION were not being removed.

fixed by A. dos Santos Fernandes

~~~

(CORE-2660) COUNT(*) would incorrectly return 0 when a match was not found for an outer join

condition. Now it correctly returns NULL.

fixed by A. dos Santos Fernandes

~~~

(CORE-2659) The query plan for an outer join involving complex views could be sub-optimal, by not

making use of an available index.

fixed by D. Yemanov

~~~

(CORE-2656 and CORE-2647) Copious “Error reading data from the connection” messages were being

reported in firebird.log on Classic, due to a race condition that could occur on disconnection. It could also lead

to a memory access violation.

fixed by V. Khorsun

~~~

(CORE-2646) When an engine process gets a request to 'ALTER DATABASE BEGIN [or END ] BACKUP'

it should change the physical backup state for nBackup on the header page of the database and immediately tell

the engine to direct all processes to flush all “dirty” pages to disk, to either the database file or the nBackup delta

file, as appropriate for the state change. (“Dirty” pages are pages of any type that contain uncommitted changes.)

Under some conditions, an engine process would continue to write dirty pages to disk immediately, even after

all processes had acknowledged the changed backup state.

Bugs Fixed

fixed by V. Khorsun, R. Simakov

~~~

(CORE-2640) Under some conditions, the lock manager could fail to detect a regular deadlock and cause

the server to hang.

fixed by V. Khorsun, D. Yemanov

~~~

(CORE-2635) A unique index could be corrupted at level 1 if it contained a lot of NULL keys.

fixed by V. Khorsun

~~~

(CORE-2633) SELECT... WITH LOCK could corrupt tables by clearing their data if no fields were accessed.

fixed by V. Khorsun

~~~

(CORE-2632) Unexpected “Invalid BLOB ID” errors could occur when working with the monitoring tables.

fixed by V. Khorsun

~~~

(CORE-2616) Error “page <N> is of wrong type (expected 7, found 5)” could occur under load, giving the

impression that something had corrupted the database. On restart, there would be no evidence of corruption.

fixed by V. Khorsun

~~~

(CORE-2612) Connection is lost immediately after compiling a PSQL module that calls the system function

RPAD().

fixed by A. dos Santos Fernandes

~~~

(CORE-2608) On latter versions of Windows (64-bit XP or later, 32-bit Vista or later), when Firebird

is working with large databases, the operating system dedicates all RAM to the filesystem cache and stops

responding. Windows may also crash.

It is a documented issue. For Firebird, it has been addressed by implementing a new parameter, FileSystem-

CacheSize in firebird.conf to control the amount of RAM used for filesystem caching.

fixed by N. Samofatov

~~~

(CORE-2602) Attachments using character set NONE could fail to read from the monitoring tables.

fixed by A. dos Santos Fernandes

Bugs Fixed

~~~

(CORE-2600) The server could deadlock while running multiple remote EXECUTE STATEMENTs

concurrently.

fixed by V. Khorsun

~~~

(CORE-2599) Closing an attachment that was in active use by other threads could cause segmentation

fault in the Y-valve.

fixed by A. Peshkov

~~~

(CORE-2591) High mutex wait ratio and degraded performance would start to show up after a period

of normal performance.

fixed by D. Yemanov

~~~

(CORE-2590) Segmentation fault could occur in the page cache AST routine under high load.

fixed by A. Peshkov, D. Yemanov

~~~

(CORE-2584) Wrong results were being produced when CASE was used in conjunction with GROUP BY.

fixed by A. dos Santos Fernandes

~~~

(CORE-2581 and CORE-2582) Infinity and NAN results from any expressions, including internal and

external function calls, were not trapped by the engine as exceptions in some circumstances.

fixed by C. Valderrama

~~~

(CORE-2579) A regression introduced in Beta 2 caused a bug whereby parameters and local variables could

not be used as expressions in parameters for EXECUTE PROCEDURE calls in PSQL without being prefixed

by colons.

fixed by A. dos Santos Fernandes

~~~

(CORE-2578) Selecting RDB$DBKEY from a view with more than one table joined would return a

conversion error.

fixed by A. dos Santos Fernandes, A Peshkov

~~~

Bugs Fixed

(CORE-2514) An error concerning CreateFile was being reported when space on the 'temp' drive was

insufficient.

fixed by D. Yemanov

~~~

(CORE-2478) Incorrect database memory was being reported in MON$MEMORY_USAGE on Classic

and SuperClassic.

fixed by D. Yemanov

~~~

(CORE-2477) Sorting memory should have been reported in MON$MEMORY_USAGE as owned by the

statement but instead it was attributed as database memory.

fixed by D. Yemanov

~~~

(CORE-2422 and CORE-2321) The server was not switching between multiple locations configured in

TempDirectories. Instead, the sorting would fail when the first configured temporary directory had insufficient

free space. Typically, the user would see a sorted or other query that required a large amount of temp space fail

with an error like 'operating system directive write failed. Invalid argument.'

fixed by D. Yemanov

~~~

(CORE-2315) Firebird FLOAT support did not conform to the original InterBase specification. According

to the IB documentation, a FLOAT value should have a range from 1.175E-38 to 3.402E38. Cross-platform tests

proved that the largest value that would not overflow was < 3.4E38.

fixed by W. Oliver

~~~

(CORE-2252) EXECUTE STATEMENT on EXTERNAL SOURCE was not checking the status of the

transaction.

fixed by V. Khorsun

~~~

(CORE-1991) When a UDF was declared with BLOB parameters, the engine was storing null in RDB

$FUNCTION_PARAMETERS.RDB$FIELD_LENGTH. This was causing a run-time 'message length error'.

fixed by D. Sibiryakov

~~~

(CORE-1781) The engine would throw a consistency check error when a subquery was ordered by an

aggregate function from another context.

fixed by A. dos Santos Fernandes

Bugs Fixed

~~~

Server/Client Crashes

(CORE-2576) The server could crash when parsing wrong or truncated BLR.

fixed by A. dos Santos Fernandes

~~~

(CORE-2573) The server would crash when selecting from the MON$ tables in an ON DISCONNECT

trigger

fixed by D. Yemanov

~~~

(CORE-2558) The engine would crash when some statement under an autonomous transaction dropped

the transaction level-savepoint.

fixed by V. Khorsun

~~~

Security

(CORE-2657) A poorly documented SPB tag provided the undesirable ability to pass an arbitrary trusted

user name to a service and enable that user to acquire any permissions, including those of SYSDBA.

fixed by A. Peshkov

~~~

International Language Support

(CORE-2651) A string overflow error (“string right truncation”) error was incorrectly being thrown when

writing to a character set NONE column from a connection configured for a multi-byte character set.

fixed by A. dos Santos Fernandes

~~~

(CORE-2642) An obscure ICU initialization problem on Windows could cause misbehaviour in a multi-

threaded environment.

fixed by A. dos Santos Fernandes

~~~

(CORE-2615) Silent truncation could occur when using UTF-8 parameters and UTF-8 client character

set encoding.

100

Bugs Fixed

fixed by A. dos Santos Fernandes

~~~

(CORE-2607) Introducer syntax(_charset) problems were occurring when it was used in association with

monitoring queries or PSQL modules.

For information about the problem and a workaround provided for it, see the topic Introducer Syntax Usage

under Other Improvements in the International Language Support chapter.

fixed by A. dos Santos Fernandes

~~~

Command-line Utilities

isql

(CORE-915) The metadata extract tools of isql were doubling line breaks in PSQL body code in cases

where the PSQL body had been written in a third-party Windows text editor.

fixed by A. dos Santos Fernandes

~~~

gbak

(CORE-2634) A regression had arisen whereby performance dropped when restoring a database with a

large amount of metadata.

fixed by A. Peshkov

~~~

nBackup

(CORE-2648) NBackup's delta file was not respecting the “Forced Writes” database setting.

fixed by V. Khorsun

~~~

gfix

(CORE-97) A lock on the database file was being left by gfix -shut -force, preventing a subsequent restore.

fixed by D. Yemanov

~~~

101

Bugs Fixed

fb_lock_print

(CORE-2598) fb_lock_print -c[onsistency] switch was not working on Windows.

fixed by D. Yemanov

~~~

POSIX-Only Issues

(CORE-2601) A lot of the standard configure switches for fine-tuning the installation directories on POSIX

platforms do not work for Firebird.

It was close to impossible to make the standard GNU switches work without changing the defaults for them, a

rigmarole that is far from obvious or easy. Instead, a set of new switches for the configure has been added to

enable fine-level configuration of the locations of Firebird's files.

The switches are listed in the Installation chapter, in the topic Dedicated Firebird Switches for 'configure'.

fixed by A. Peshkoff

~~~

(CORE-2572) Locks of type LCK_page_space were being processed incorrectly on big-endian machines.

fixed by A. Peshkov

~~~

Windows-Only Issues

(CORE-2586) For client-server and server-server syncronization, Firebird uses various named kernel

objects (such as Events, Mutexes, Memory-mapped files, etc). To work in proper isolation, the objects that a

process creates must belong to the same namespace. Some objects were being created with the namespace set

explicitly, while others were not. This could have corruption implications for certain deployment arrangements.

fixed by V. Khorsun

~~~

Firebird 2.5 Beta 2

The following bugs were reported as fixed in the Beta 2 release:

Core Engine/DSQL

(CORE-2564) Bus errors would be thrown when working with the monitoring tables on RISC machines.

102

Bugs Fixed

fixed by A. Peshkov

~~~

(CORE-2552) Calls isc_dsql_execute2() were not returning errors when the output SQLDA was non-null.

fixed by A. dos Santos Fernandes

~~~

(CORE-2550) Bus errors would be thrown when working with DB_KEY on big-Endian machines.

fixed by A. Peshkov

~~~

(CORE-2538) PSQL would not allow use of a singleton query result as an input parameter to a stored

procedure if the procedure was called using EXECUTE PROCEDURE.

fixed by A. dos Santos Fernandes

~~~

(CORE-2532) Volume sizes in a multi-volume database were being assigned incorrectly.

fixed by V. Khorsun

~~~

(CORE-2526) The server could be shut down without regard to connections to services.

fixed by A. Peshkov, D. Yemanov

~~~

(CORE-2518) It was impossible to connect to a database via any engine when using the isc_dpb_num_buffers

parameter. Since it is expected that this parameter be available to a Classic process, the behaviour has been

changed to allow the parameter to be present and for it to be simply ignored by a Superserver server.

fixed by A. Peshkov

~~~

(CORE-2516) Attempts to define an input or output parameter for a stored procedure using a domain or

TYPE OF COLUMN where the domain was an array were not detected by the DSQL compiler as invalid and

thus caused run-time anomalies. Now, such attempts will cause exceptions.

fixed by A. dos Santos Fernandes

~~~

(CORE-2505) Built-in trigonometric functions could produce NaN and Infinity.

fixed by C. Valderrama

~~~

103

Bugs Fixed

(CORE-2501) Binary shift functions would give wrong results with negative shift values.

fixed by C. Valderrama

~~~

(CORE-2499) The implementation limit of DISTINCT items was not being enforced, causing incorrect

BLR to be generated.

fixed by A. dos Santos Fernandes

~~~

(CORE-2488) Execution of a query involving multiple re-entrant joins was taking much longer than on v.2.1.

fixed by D. Yemanov

~~~

(CORE-2482) Monitoring tables data collection was unstable when the database was accepting attachments

or detachments.

fixed by A. Peshkov

~~~

(CORE-2475) External table data was not visible to Classic sessions other than the first to access the table.

fixed by V. Khorsun

~~~

(CORE-2462) The firebird.log file would grow too fast and too much.

fixed by D. Yemanov

~~~

(CORE-2449) An unexpected “lock conflict” error could be thrown in lieu of the expected exception.

fixed by D. Yemanov

~~~

(CORE-2444) The engine could hang when multiple attachments registered their interest in events simultaneously

and free space in the events table became exhausted.

fixed by V. Khorsun

~~~

(CORE-2434) The CREATE USER command was returning an invalid error message.

fixed by A. dos Santos Fernandes

~~~

(CORE-2430) The server was appending “NOT” at the end of a default value for a TIMESTAMP field.

104

Bugs Fixed

fixed by D. Yemanov

~~~

(CORE-2427) ALTER VIEW was not clearing the dependencies pertaining to the old version of the view.

fixed by A. dos Santos Fernandes

~~~

(CORE-2426) ALTER TABLE was not respecting collations.

fixed by A. dos Santos Fernandes

~~~

(CORE-2420) A parsing error would occur in EXECUTE STATEMENT with named parameters.

fixed by V. Khorsun

~~~

(CORE-2419) An assert would occur in the DEBUG build while preparing a query with UNION inside

a derived table.

fixed by V. Khorsun

~~~

(CORE-2417) An index that should be used with a <field> LIKE <constant%> predicate in the HAVING

clause was not being considered.

fixed by A. dos Santos Fernandes

~~~

(CORE-2416) Preparing a query with aggregation over a derived table would cause an access violation.

fixed by V. Khorsun

~~~

(CORE-2411) The optimizer would choose a slower PLAN for certain types of query than it would in

versions 2.0.4 and 2.1.1. (The bug affected versions 2.0.5 and 2.1.2 as well.)

fixed by D. Yemanov

~~~

(CORE-2402) Bad permissions on temp files; /tmp was polluted with a lot of files.

fixed by A. Peshkov

~~~

(CORE-2397) Dropping more than one index on a table within the same transaction could cause corruption.

fixed by V. Khorsun

105

Bugs Fixed

~~~

(CORE-2386) ALTER VIEW was not prevented from removing columns used in stored procedures or

triggers.

fixed by V. Khorsun

~~~

(CORE-2359) Logical multibyte maximum string length was not respected when assigning numbers.

fixed by A. dos Santos Fernandes

~~~

(CORE-2331) ALTER DOMAIN would result in an invalid RDB$FIELD_SUB_TYPE value being stored.

fixed by A. dos Santos Fernandes

~~~

(CORE-2272) The server would start returning garbage when killing an events connection attempt.

fixed by A. Peshkov

~~~

(CORE-1971) Set the fixed and documented evaluation order (always left to right) for the WHERE clause

and other predicates.

fixed by D. Yemanov

~~~

(CORE-1346) LPAD() and RPAD() functions hit implementation restrictions when applied to more than

one column in a statement.

fixed by A. dos Santos Fernandes

~~~

Server/Client Crashes

(CORE-2455) The server would fail when doing DROP DATABASE right after an error occurred in a

statistical function.

fixed by A. Peshkov

~~~

(CORE-2454) An access violation could occur when enumerating attachments in Superserver or Superclassic

if more than 8 databases were attached.

fixed by V. Khorsun

~~~

106

Bugs Fixed

(CORE-2453) An access violation could occur in the engine when an XNET client using events disconnected.

fixed by V. Khorsun

~~~

(CORE-2441) The server could crash on executing an UPDATE OR INSERT statement.

fixed by A. Peshkov

~~~

(CORE-2306) Superserver could terminate abnormally when some worker thread failed to start.

fixed by A. Peshkov

~~~

Remote Interface/API

(CORE-2563) It was possible to shut down the Superserver's main port (3050 by default) by sending a

malformed packet of some special format, that would lead to a Denial of Service condition for new incoming

connections. This exploit could be used by an unauthenticated client.

Reported 15-Jul-2009 by Core Security Technologies.

fixed by D. Yemanov

~~~

(CORE-2561) Database name transliteration was incorrect when connecting using WNET (Named Pipes)

or XNET.

fixed by A. dos Santos Fernandes

~~~

(CORE-2437) A buffer overflow could occur on the client when receiving events.

fixed by A. Peshkov

~~~

International Language Support

~~~

(CORE-2361) String truncation was occurring when reading a character set 8859_1 Spanish column using

isc_dsql_fetch() with a UTF8 connection.

fixed by A. dos Santos Fernandes

~~~

107

Bugs Fixed

Command-line Utilities

gsec

(CORE-2528) The gsec utility did not return error codes to the operating system.

fixed by C. Valderrama

~~~

gstat

(CORE-2519) Output from gstat was incorrect for tables with more than 2 billion records.

fixed by V. Khorsun

~~~

(CORE-1412) Some long-standing bugs in gstat's processing of parameters needed fixing.

fixed by C. Valderrama

~~~

gbak

(CORE-2461) The restore process was being terminated by a deadlock error twhile restoring privileges.

fixed by D. Yemanov

~~~

isql

(CORE-2408) The isql metadata extraction process was placing default values for procedure parameters

before the NOT NULL and COLLATE flags.

fixed by A. dos Santos Fernandes

~~~

(CORE-2407) The isql metadata extraction process was omitting the PAGE_SIZE clause from the CREATE

DATABASE statement.

fixed by A. dos Santos Fernandes

~~~

(CORE-2370) An SQL plan of more than 2048 characters was not printed at all in isql.

108

Bugs Fixed

fixed by C. Valderrama

~~~

gfix

(CORE-2268) Non-valid transaction numbers would cause gfix to throw BUGCHECK errors.

fixed by V. Khorsun

~~~

Firebird 2.5 Beta 1

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

Core Engine/DSQL

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

109

Bugs Fixed

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.

110

Bugs Fixed

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

~~~

111

Bugs Fixed

(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

112

Bugs Fixed

~~~

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

113

Bugs Fixed

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

114

Bugs Fixed

~~~

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

115

Bugs Fixed

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.

116

Bugs Fixed

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

117

Bugs Fixed

~~~

(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

118

Bugs Fixed

~~~

(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

~~~

119

Bugs Fixed

Server/Client Crashes

(CORE-2372) The server would crash inside CMP_release() while releasing an already freed collation

resource.

fixed by V. Khorsun

~~~

(CORE-2368) An access violation would follow the call to isc_cancel_events() if the event was not found.

fixed by V. Khorsun

~~~

(CORE-2248) During port validation, the server was crashing in server.cpp/loopThread().

fixed by V. Khorsun

~~~

(CORE-2222) Storing a text blob with a transliterating blob filter could cause an access violation in the

engine.

fixed by V. Khorsun

~~~

(CORE-2158) Client and embedded libraries could crash while being unloaded.

fixed by A. Peshkov

~~~

(CORE-2137) A database restore could crash the server when the configuration parameter DummyPacketInterval

was set explicitly.

fixed by D. Yemanov

~~~

(CORE-2121) The Client library could crash in the course of an operation involving BLOBs.

fixed by A. Peshkov

~~~

(CORE-2071) The client library would crash if isc_dsql_prepare() was called with a NULL statement text.

fixed by A. Peshkov

~~~

(CORE-2064) A server process could crash during an exit while under high load.

120

Bugs Fixed

fixed by A. Peshkov

~~~

(CORE-2061) ALTER VIEW WITH CHECK OPTION could crash the engine.

fixed by D. Yemanov

~~~

(CORE-2042) ?? Connection lost to a database when using AUTONOMOUS TRANSACTION ??

fixed by A. dos Santos Fernandes

~~~

(CORE-1983) An out-of-memory condition in the operating system would cause an access violation.

fixed by A. Peshkov

~~~

(CORE-1965) The Lock Manager would crash with an invalid lock ID under concurrent DDL load.

fixed by D. Yemanov

~~~

(CORE-1894) Circular dependencies between computed fields would crash the server.

fixed by A. dos Santos Fernandes

~~~

(CORE-1963) The server could crash on commit when granting/revoking privileges from multiple connections

simultaneously.

fixed by D. Yemanov

~~~

(CORE-1506) Server crashes with isc_dsql_execute_immediate() and a zero-length string.

fixed by A. Peshkov

~~~

(CORE-210) The Classic server would crash if the same stored procedure was being altered in two separate

processes.

fixed by D. Yemanov

~~~

Remote Interface/API

(CORE-2307) API information requests were returning incomplete values in the results.

121

Bugs Fixed

fixed by D. Yemanov

~~~

(CORE-2262) Abrupt termination of a client connection could occur.

fixed by A. Peshkov

~~~

(CORE-2234) Sometimes, terminated worker processes in Classic on Windows were still considered to

be alive after termination, due to improper checks on the Firebird server's part. The same bug could cause the

Firebird server to misbehave with prolonged deadlocks when under load.

fixed by V. Khorsun

~~~

(CORE-2151) When a temporary directory path had spaces within it, it was (wrongly) being truncated

at the rightmost space.

fixed by V. Khorsun

~~~

(CORE-2033) The symbol _Unwind_GetIP in the client library was being left unresolved due to a missing

static library linkage.

fixed by A. Peshkov

~~~

(CORE-2018) Only a single client could access a read-only database.

fixed by V. Khorsun

~~~

(CORE-2016) A client's attempt to use the XNET protocol causes it to hang if the attachment or the

database has been shut down.

fixed by D. Yemanov

~~~

(CORE-1972) A non-SYSDBA user was able to change the Forced Writes mode of any database, along

with several other database characteristics that should be restricted to the SYSDBA. This long-standing, legacy

loophole in the handling of DPB parameters could lead to database corruptions or give ordinary users access

to SYSDBA-only operations.

The changes could affect several existing applications, database tools and connectivity layers (drivers, components).

Same fix was backported to v.2.1.2 and v.2.0.5.

fixed by A. Peshkov

122

Bugs Fixed

~~~

POSIX-specific

(CORE-2221) On POSIX platforms, any attachment to any database would fail after the access rights for

security2.fdb were modified from 0660 to 0666.

fixed by P. Beach, A. Peshkov

~~~

(CORE-2093) SuperServer startup would fail on Solaris 64-bit.

fixed by A. Peshkov

~~~

Windows-specific

(CORE-2108) When using the Windows local protocol (XNET), the next available map number was

calculated incorrectly, thus allowing the server to try to reuse a map number that already existed. If the “new”

map's timestamp was equal to the timestamp of the pre-existing map, it would cause the get_free_slot()

function to fail.

fixed by V. Khorsun

~~~

(CORE-2107) Establishing a TCP\IP connection to the Windows Classic Server could fail under high load.

fixed by V. Khorsun

~~~

(CORE-1923) Successful execution of instsvc.exe remove was returning 1 as its completion code, instead

of 0.

fixed by D. Yemanov

~~~

MacOSX-specific

(CORE-2102) Firebird 2.5 would not build on MacOS (Darwin).

fixed by A. Peshkov

~~~

(CORE-2065) The MacOSX installation package was in violation of platform rules by not including the

client library in the dynamic loader search paths.

fixed by P. Beach

123

Bugs Fixed

~~~

Database Monitoring/Administration

(CORE-2209) Monitoring requests in high load conditions could become very slow and even block other

activity during that time.

fixed by D. Yemanov

~~~

(CORE-2171) The column MON$CALLER_ID of table MON$CALL_STACK was reporting invalid IDs.

fixed by D. Yemanov

~~~

(CORE-2017) I/O statistics for stored procedures were not being kept account of in the monitoring tables.

fixed by D. Yemanov

~~~

(CORE-1944) On Big Endian platforms, the monitoring tables contained wrong data.

fixed by A. Peshkov

~~~

Security

(CORE-2087) When the configuration parameter RemoteBindAddress specified a hostname instead of an

IP address, or specified a non-existent IP address, it would be silently ignored and the server would bind to all

interfaces, without any notification in firebird.log or the system log. This was considered a potential security

risk if the system had ports open to the Internet. Now, an invalid or unavailable IP address will be resolved to

localhost (127.0.0.1).

fixed by A. Peshkov

~~~

(CORE-2055) Buffer overflow in Firebird client library.

fixed by A. Peshkov

~~~

International Language Support

(CORE-2278) Conversion from and to CP943C was incorrect on RISC machines.

fixed by A. dos Santos Fernandes

124

Bugs Fixed

~~~

(CORE-2227) Problems were occurring in some environments when creating triggers that referred to

column names with accented characters.

fixed by A. dos Santos Fernandes

~~~

(CORE-2123) ?? Problem with getting UNICODE_FSS data in the CP943C connection charset ??

fixed by A. dos Santos Fernandes, D. Kovalenko

~~~

(CORE-2122) ?? Translation of large text blobs between UNICODE_FSS / UTF8 and other charsets ??

fixed by A. dos Santos Fernandes, D. Kovalenko

~~~

(CORE-2095) Bug in CVJIS_eucj_to_unicode().

fixed by A. dos Santos Fernandes, D. Kovalenko

~~~

(CORE-2019) A UTF-8 conversion error (string truncation) was being thrown unexpectedly.

fixed by dos Santos Fernandes

~~~

(CORE-1989) A column with UNICODE_CI collation for UTF8 could not be used in a foreign key

constraint.

fixed by A. dos Santos Fernandes

~~~