Firebird 5.0 Language Reference

About the Firebird 5.0 Language Reference

Subject

Authorship

Contributors

Authorship

Reporting Errors or Missing Content

Acknowledgments

Sponsors and Other Donors

Sponsors of the Russian Language Reference Manual

Acknowledgments

Contributing

SQL Language Structure

Background to Firebird's SQL Language

SQL Flavours

SQL Dialects

Error Conditions

Background to Firebird's SQL Language

Basic Elements: Statements, Clauses, Keywords

Identifiers

Rules for Regular Identifiers

Rules for Delimited Identifiers

Identifiers

Literals

Operators and Special Characters

Comments

Data Types and Subtypes

Integer Data Types

SMALLINT

INTEGER

BIGINT

INT128

Hexadecimal Format for Integer Numbers

Integer Data Types

Floating-Point Data Types

Approximate Floating-Point Data Types

FLOAT

REAL

DOUBLE PRECISION

Decimal Floating-Point Types

DECFLOAT

Behaviour of DECFLOAT Operations

Length of DECFLOAT Literals

DECFLOAT and Functions Use with Standard Functions Special Functions for DECFLOAT

Length of DECFLOAT Literals

DECFLOAT and Functions Use with Standard Functions Special Functions for DECFLOAT

DECFLOAT and Functions Use with Standard Functions Special Functions for DECFLOAT

Floating-Point Data Types

Fixed-Point Data Types

NUMERIC

DECIMAL

Fixed-Point Data Types

Data Types for Dates and Times

DATE

TIME

TIME [WITHOUT TIME ZONE]

TIME WITH TIME ZONE

TIMESTAMP

TIMESTAMP [WITHOUT TIME ZONE]

TIMESTAMP WITH TIME ZONE

Session Time Zone

Time Zone Format

Operations Using Date and Time Values

Supplemental Time Zone Features

Virtual table RDB$TIME_ZONES

Package RDB$TIME_ZONE_UTIL

Updating the Time Zone Database

Data Types for Dates and Times

Character Data Types

Unicode

Client Character Set

Special Character Sets

Collation

Case-Insensitive Searching

UTF8 Collations

Character Indexes

Character Types in Detail

BINARY

CHAR

VARBINARY

VARCHAR

NCHAR

Character Data Types

Boolean Data Type

BOOLEAN

The IS Operator

BOOLEAN Examples

Use of Boolean Against Other Data Types

Boolean Data Type

Binary Data Types

BLOB Subtypes

BLOB Specifics

Binary Data Types

Array Types

Specifying Explicit Boundaries for Dimensions

Adding More Dimensions PSQL Source for SHOW_LANGS, a procedure involving an array

Array Types

Special Data Types

SQL_NULL Data Type

Special Data Types

Conversion of Data Types

Explicit Data Type Conversion

Casting to a Domain

Casting to TYPE OF COLUMN

Conversions Possible for the CAST Function

Datetime Formats

Shorthand Casts for Datetime Data Types

Implicit Data Type Conversion

Implicit Conversion During String Concatenation

Conversion of Data Types

Custom Data Types — Domains

Domain Attributes

Domain Override

Creating and Administering Domains

Altering a Domain

Deleting (Dropping) a Domain

Custom Data Types — Domains

Data Type Declaration Syntax

Scalar Data Types Syntax

Use of Domains in Declarations

Use of Column Type in Declarations

BLOB Data Types Syntax

Array Data Types Syntax

Data Type Declaration Syntax

Common Language Elements

Expressions

Literals (Constants)

String Literals

String Literals in Hexadecimal Notation

Alternative String Literals

Introducer Syntax for String Literals

Alternative String Literals

Introducer Syntax for String Literals

Introducer Syntax for String Literals

Number Literals

Hexadecimal Notation for Numbers

Boolean Literals

Datetime Literals

SQL Operators

Operator Precedence

Concatenation Operator

Arithmetic Operators

Comparison Operators

Logical Operators

Arithmetic Operators

Comparison Operators

Logical Operators

Comparison Operators

Logical Operators

Logical Operators

NEXT VALUE FOR

AT Time Zone Expression

Conditional Expressions

CASE

Simple CASE

Searched CASE

Searched CASE

NULL in Expressions

Expressions Returning NULL

NULL in Logical Expressions

Subqueries

Correlated Subqueries

Scalar Results

Expressions

Predicates

Conditions

Comparison Predicates

Other Comparison Predicates

BETWEEN

LIKE

Wildcards

Using the ESCAPE Character Option Examples using LIKE

STARTING WITH

CONTAINING

SIMILAR TO

Syntax: SQL Regular Expressions

Building Regular Expressions

Characters

Wildcards

Character Classes

Predefined Character Classes

Quantifiers

OR-ing Terms

Subexpressions

Escaping Special Characters

IS [NOT] DISTINCT FROM

Boolean IS [NOT]

IS [NOT] NULL

LIKE

STARTING WITH

CONTAINING

SIMILAR TO

Syntax: SQL Regular Expressions

Building Regular Expressions

Characters

Wildcards

Character Classes

Predefined Character Classes

Quantifiers

OR-ing Terms

Subexpressions

Escaping Special Characters

IS [NOT] DISTINCT FROM

Boolean IS [NOT]

IS [NOT] NULL

STARTING WITH

CONTAINING

SIMILAR TO

Syntax: SQL Regular Expressions

Building Regular Expressions

Characters

Wildcards

Character Classes

Predefined Character Classes

Quantifiers

OR-ing Terms

Subexpressions

Escaping Special Characters

IS [NOT] DISTINCT FROM

Boolean IS [NOT]

IS [NOT] NULL

CONTAINING

SIMILAR TO

Syntax: SQL Regular Expressions

Building Regular Expressions

Characters

Wildcards

Character Classes

Predefined Character Classes

Quantifiers

OR-ing Terms

Subexpressions

Escaping Special Characters

IS [NOT] DISTINCT FROM

Boolean IS [NOT]

IS [NOT] NULL

SIMILAR TO

IS [NOT] DISTINCT FROM

Boolean IS [NOT]

IS [NOT] NULL

IS [NOT] DISTINCT FROM

Boolean IS [NOT]

IS [NOT] NULL

Boolean IS [NOT]

IS [NOT] NULL

IS [NOT] NULL

Existential Predicates

EXISTS

IN

SINGULAR

Quantified Subquery Predicates

ALL

ANY and SOME

Predicates

Data Definition (DDL) Statements

DATABASE

CREATE DATABASE

Using a Database Alias

Creating a Database on a Remote Server

Optional Parameters for CREATE DATABASE

Specifying the Database Dialect

Who Can Create a Database

Examples Using CREATE DATABASE

CREATE DATABASE

ALTER DATABASE

Who Can Alter the Database

Parameters for ALTER DATABASE

Examples of ALTER DATABASE Usage

ALTER DATABASE

DROP DATABASE

Who Can Drop a Database

Example of DROP DATABASE

DROP DATABASE

SHADOW

CREATE SHADOW

AUTO | MANUAL Modes

Options for CREATE SHADOW

Who Can Create a Shadow

Examples Using CREATE SHADOW

CREATE SHADOW

DROP SHADOW

Who Can Drop a Shadow

Example of DROP SHADOW

DROP SHADOW

DOMAIN

CREATE DOMAIN

Type-specific Details

Who Can Create a Domain

CREATE DOMAIN Examples

CREATE DOMAIN

ALTER DOMAIN

ALTER DOMAIN clauses

What ALTER DOMAIN Cannot Alter

Who Can Alter a Domain

ALTER DOMAIN Examples

ALTER DOMAIN

DROP DOMAIN

Who Can Drop a Domain

Example of DROP DOMAIN

DROP DOMAIN

TABLE

CREATE TABLE

Character Columns

Setting a DEFAULT Value

Domain-based Columns

Identity Columns (Autoincrement)

GENERATED ALWAYS

GENERATED BY DEFAULT

START WITH Option

INCREMENT Option

Computed Columns

Defining an Array Column

Constraints

Names for Constraints and Their Indexes

Named Constraints

The USING Clause

The USING Clause

PRIMARY KEY

The UNIQUE Constraint

NULL in Unique Keys

FOREIGN KEY

Foreign Key Actions

CHECK Constraint

NOT NULL Constraint

SQL SECURITY Clause

Replication Management

Who Can Create a Table

CREATE TABLE Examples

Global Temporary Tables (GTT)

Restrictions on GTTs

Examples of Global Temporary Tables

External Tables

External File Format

Row Delimiters

External Table Example

External Table Example

CREATE TABLE

ALTER TABLE

Version Count Increments

The ADD Clause

The DROP Clause

The DROP CONSTRAINT Clause

The ALTER [COLUMN] Clause

Renaming a Column: the TO Clause

Changing the Data Type of a Column: the TYPE Clause

Changing the Position of a Column: the POSITION Clause

The DROP DEFAULT and SET DEFAULT Clauses

The SET NOT NULL and DROP NOT NULL Clauses

The COMPUTED [BY] or GENERATED ALWAYS AS Clauses

Changing Identity Columns

Identity Type

RESTART

SET INCREMENT

DROP IDENTITY

Changing SQL Security

Replication Management

Attributes that Cannot Be Altered

Who Can Alter a Table?

Examples Using ALTER TABLE

ALTER TABLE

DROP TABLE

Who Can Drop a Table?

Example of DROP TABLE

DROP TABLE

RECREATE TABLE

Example of RECREATE TABLE

RECREATE TABLE

INDEX

CREATE INDEX

Who Can Create an Index?

Unique Indexes

Partial Indexes

Index Direction

Computed (Expression) Indexes

Limits on Indexes

Maximum Indexes per Table

Character Index Limits

Parallelized Index Creation

Examples Using CREATE INDEX

CREATE INDEX

ALTER INDEX

Who Can Alter an Index?

Use of ALTER INDEX on a Constraint Index

ALTER INDEX Examples

ALTER INDEX

DROP INDEX

Who Can Drop an Index?

DROP INDEX Example

DROP INDEX

SET STATISTICS

Who Can Update Index Statistics?

Index Selectivity

Example Using SET STATISTICS

SET STATISTICS

VIEW

CREATE VIEW

Updatable Views

WITH CHECK OPTION

Who Can Create a View?

Examples of Creating Views

CREATE VIEW

ALTER VIEW

Who Can Alter a View?

Example using ALTER VIEW

ALTER VIEW

CREATE OR ALTER VIEW

Example of CREATE OR ALTER VIEW

CREATE OR ALTER VIEW

DROP VIEW

Who Can Drop a View?

Example

DROP VIEW

RECREATE VIEW

Example of RECREATE VIEW

RECREATE VIEW

TRIGGER

CREATE TRIGGER

Statement Terminators

SQL Security

The Trigger Body

DML Triggers (on Tables or Views)

Who Can Create a DML Trigger?

Forms of Declaration

Phase

Row Events

Firing Order of Triggers

Examples of CREATE TRIGGER for Tables and Views

Database Triggers

Who Can Create a Database Trigger?

Execution of Database Triggers and Exception Handling

Traps

Suppressing Database Triggers

Two-phase Commit

Some Caveats

Suppressing Database Triggers

Two-phase Commit

Some Caveats

Two-phase Commit

Some Caveats

Some Caveats

Examples of CREATE TRIGGER for Database Triggers

DDL Triggers

Who Can Create a DDL Trigger?

Suppressing DDL Triggers

Examples of DDL Triggers

CREATE TRIGGER

ALTER TRIGGER

Permitted Changes to Triggers

Who Can Alter a Trigger?

Examples using ALTER TRIGGER

ALTER TRIGGER

CREATE OR ALTER TRIGGER

Example of CREATE OR ALTER TRIGGER

CREATE OR ALTER TRIGGER

DROP TRIGGER

Who Can Drop a Trigger?

Example of DROP TRIGGER

DROP TRIGGER

RECREATE TRIGGER

Example of RECREATE TRIGGER

RECREATE TRIGGER

PROCEDURE

CREATE PROCEDURE

Statement Terminators

Parameters

SQL Security

Variable, Cursor and Subroutine Declarations

External UDR Procedures

Who Can Create a Procedure

Examples

CREATE PROCEDURE

ALTER PROCEDURE

Who Can Alter a Procedure

ALTER PROCEDURE Example

ALTER PROCEDURE

CREATE OR ALTER PROCEDURE

CREATE OR ALTER PROCEDURE Example

CREATE OR ALTER PROCEDURE

DROP PROCEDURE

Who Can Drop a Procedure

DROP PROCEDURE Example

DROP PROCEDURE

RECREATE PROCEDURE

RECREATE PROCEDURE Example

RECREATE PROCEDURE

FUNCTION

CREATE FUNCTION

Statement Terminators

Parameters

Deterministic functions

SQL Security

Variable, Cursor and Subroutine Declarations

Function Body

External UDR Functions

Who Can Create a Function

CREATE FUNCTION Examples

CREATE FUNCTION

ALTER FUNCTION

Who Can Alter a Function

Examples of ALTER FUNCTION

ALTER FUNCTION

CREATE OR ALTER FUNCTION

Examples of CREATE OR ALTER FUNCTION

CREATE OR ALTER FUNCTION

DROP FUNCTION

Who Can Drop a Function

Examples of DROP FUNCTION

DROP FUNCTION

RECREATE FUNCTION

Examples of RECREATE FUNCTION

RECREATE FUNCTION

EXTERNAL FUNCTION

DECLARE EXTERNAL FUNCTION

DECLARE EXTERNAL FUNCTION Input Parameters

Clauses and Keywords

Who Can Create an External Function Examples using DECLARE EXTERNAL FUNCTION

DECLARE EXTERNAL FUNCTION

ALTER EXTERNAL FUNCTION

Who Can Alter an External Function

Examples using ALTER EXTERNAL FUNCTION

ALTER EXTERNAL FUNCTION

DROP EXTERNAL FUNCTION

Who Can Drop an External Function

Example using DROP EXTERNAL FUNCTION

DROP EXTERNAL FUNCTION

PACKAGE

CREATE PACKAGE

Statement Terminators

SQL Security

Procedure and Function Parameters

Who Can Create a Package

Examples of CREATE PACKAGE

CREATE PACKAGE

ALTER PACKAGE

Who Can Alter a Package

Examples of ALTER PACKAGE

ALTER PACKAGE

CREATE OR ALTER PACKAGE

Examples of CREATE OR ALTER PACKAGE

CREATE OR ALTER PACKAGE

DROP PACKAGE

Who Can Drop a Package

Examples of DROP PACKAGE

DROP PACKAGE

RECREATE PACKAGE

Examples of RECREATE PACKAGE

RECREATE PACKAGE

PACKAGE BODY

CREATE PACKAGE BODY

Who Can Create a Package Body

Examples of CREATE PACKAGE BODY

CREATE PACKAGE BODY

ALTER PACKAGE BODY

Who Can Alter a Package Body

Examples of ALTER PACKAGE BODY

ALTER PACKAGE BODY

DROP PACKAGE BODY

Who Can Drop a Package Body

Examples of DROP PACKAGE BODY

DROP PACKAGE BODY

RECREATE PACKAGE BODY

Examples of RECREATE PACKAGE BODY

RECREATE PACKAGE BODY

FILTER

DECLARE FILTER

Specifying the Subtypes

Parameters

Who Can Create a BLOB Filter?

Examples of DECLARE FILTER

DECLARE FILTER

DROP FILTER

Who Can Drop a BLOB Filter?

DROP FILTER Example

DROP FILTER

SEQUENCE (GENERATOR)

CREATE SEQUENCE

Who Can Create a Sequence?

Examples of CREATE SEQUENCE

CREATE SEQUENCE

ALTER SEQUENCE

Who Can Alter a Sequence?

Examples of ALTER SEQUENCE

ALTER SEQUENCE

CREATE OR ALTER SEQUENCE

Example of CREATE OR ALTER SEQUENCE

CREATE OR ALTER SEQUENCE

DROP SEQUENCE

Who Can Drop a Sequence?

Example of DROP SEQUENCE

DROP SEQUENCE

RECREATE SEQUENCE

Example of RECREATE SEQUENCE

RECREATE SEQUENCE

SET GENERATOR

Who Can Use a SET GENERATOR?

Example of SET GENERATOR

SET GENERATOR

EXCEPTION

CREATE EXCEPTION

Who Can Create an Exception

CREATE EXCEPTION Examples

CREATE EXCEPTION

ALTER EXCEPTION

Who Can Alter an Exception

ALTER EXCEPTION Examples

ALTER EXCEPTION

CREATE OR ALTER EXCEPTION

CREATE OR ALTER EXCEPTION Example

CREATE OR ALTER EXCEPTION

DROP EXCEPTION

Who Can Drop an Exception

DROP EXCEPTION Examples

DROP EXCEPTION

RECREATE EXCEPTION

RECREATE EXCEPTION Example

RECREATE EXCEPTION

COLLATION

CREATE COLLATION

How the Engine Detects the Collation

Specific Attributes

Who Can Create a Collation

Examples using CREATE COLLATION

CREATE COLLATION

DROP COLLATION

Who Can Drop a Collation

Example using DROP COLLATION

DROP COLLATION

CHARACTER SET

ALTER CHARACTER SET

Who Can Alter a Character Set

ALTER CHARACTER SET Example

ALTER CHARACTER SET

Comments

COMMENT ON

Who Can Add a Comment

Examples using COMMENT ON

COMMENT ON

Data Manipulation (DML) Statements

SELECT

FIRST, SKIP

Characteristics of FIRST and SKIP

Examples of FIRST/SKIP

The SELECT Columns List

Examples of SELECT queries with different types of column lists

The FROM clause

Selecting FROM a table or view

Examples

Selecting FROM a stored procedure

Selecting FROM a derived table

LATERAL Derived Tables

Example using a derived table A more useful example

Example using a derived table A more useful example

Selecting FROM a Common Table Expression (CTE)

Joins

Inner vs. Outer Joins

Qualified joins

Explicit-condition joins

Named columns joins

Named columns joins

Natural joins

Cross joins

Implicit Joins

Mixing Explicit and Implicit Joins

Mixing Explicit and Implicit Joins

A Note on Equality

Ambiguous field names in joins

Joins with LATERAL Derived Tables

The WHERE clause

Examples

The GROUP BY clause

HAVING

The WINDOW Clause

The PLAN Clause

Simple Plans

Composite Plans

UNION

ORDER BY

Sorting Direction

Collation Order

NULLs Position

Ordering UNION-ed Sets

Examples of ORDER BY

ROWS

Replacing of FIRST/SKIP and OFFSET/FETCH

Mixing ROWS and FIRST/SKIP or OFFSET/FETCH

ROWS Syntax in UNION Queries

Examples of ROWS

OFFSET, FETCH

Examples of OFFSET and FETCH

FOR UPDATE [OF]

WITH LOCK

Usage with a FOR UPDATE Clause

How the engine deals with WITH LOCK

Caveats using WITH LOCK

Examples using explicit locking

OPTIMIZE FOR

INTO

Common Table Expressions (WITH AS SELECT)

Recursive CTEs

CTE Notes Example

Recursive CTEs

Full SELECT Syntax

SELECT

INSERT

INSERT VALUES

INSERT SELECT

INSERT DEFAULT VALUES

OVERRIDING

The RETURNING Clause

Inserting into BLOB columns

INSERT

UPDATE

Using an alias

The SET Clause

The WHERE Clause

The ORDER BY and ROWS Clauses

The SKIP LOCKED Clause

The RETURNING Clause

The INTO Sub-clause

RETURNING Example (DSQL)

Updating BLOB columns

UPDATE

UPDATE OR INSERT

The ORDER BY and ROWS Clauses

The RETURNING Clause

Example of UPDATE OR INSERT

UPDATE OR INSERT

DELETE

Aliases

WHERE

PLAN

ORDER BY and ROWS

SKIP LOCKED

RETURNING

DELETE

MERGE

The ORDER BY Clause

The RETURNING Clause

Examples of MERGE

MERGE

EXECUTE PROCEDURE

Executable Stored Procedure Examples of EXECUTE PROCEDURE

EXECUTE PROCEDURE

EXECUTE BLOCK

Examples

Input and output parameters

Statement Terminators

EXECUTE BLOCK

Procedural SQL (PSQL) Statements

Elements of PSQL

DML Statements with Parameters

Transactions

Module Structure

The Module Header

The Module Body

The PSQL Module Body

isql Command SET TERM

The External Module Body

Elements of PSQL

Stored Procedures

Benefits of Stored Procedures

Types of Stored Procedures

Executable Procedures

Selectable Procedures

Creating a Stored Procedure

Modifying a Stored Procedure

Dropping a Stored Procedure

Stored Procedures

Stored Functions

Creating a Stored Function

Modifying a Stored Function

Dropping a Stored Function

Stored Functions

PSQL Blocks

Packages

Benefits of Packages

Creating a Package

Modifying a Package

Dropping a Package

Packages

Triggers

Firing Order (Order of Execution)

DML Triggers

Trigger Options

OLD and NEW Context Variables

Database Triggers

DDL Triggers

Semantics

The DDL_TRIGGER Context Namespace

Creating Triggers

Modifying Triggers

Dropping a Trigger

Triggers

Writing the Body Code

Assignment Statements

Example using assignment statements

Management Statements in PSQL

Example of Management Statements in PSQL

DECLARE VARIABLE

Data Type for Variables

NOT NULL Constraint

CHARACTER SET and COLLATE clauses

Initializing a Variable

Examples of various ways to declare local variables

DECLARE .. CURSOR

Cursor Idiosyncrasies

Examples Using Named Cursors

DECLARE FUNCTION

Examples of Sub-Functions

DECLARE PROCEDURE

Examples of Sub-Procedures

BEGIN END

BEGIN END Examples

IF THEN ELSE

IF Examples

WHILE DO

WHILE DO Examples

BREAK

LEAVE

LEAVE Examples

CONTINUE

CONTINUE Examples

EXIT

SUSPEND

SUSPEND Examples

EXECUTE STATEMENT

Parameterized Statements

Special Rules for Parameterized Statements

Examples of EXECUTE STATEMENT with parameters

Examples of EXECUTE STATEMENT with parameters

WITH {AUTONOMOUS | COMMON} TRANSACTION

WITH CALLER PRIVILEGES

ON EXTERNAL [DATA SOURCE]

Connection Pooling

Transaction Pooling

Exception Handling

Miscellaneous Notes

Transaction Pooling

Exception Handling

Miscellaneous Notes

Exception Handling

Miscellaneous Notes

Miscellaneous Notes

AS USER, PASSWORD and ROLE

Caveats with EXECUTE STATEMENT

FOR SELECT

The Undeclared Cursor

Examples using FOR SELECT

FOR EXECUTE STATEMENT

FOR EXECUTE STATEMENT Examples

OPEN

OPEN Examples

FETCH

FETCH Examples

CLOSE

CLOSE Examples

IN AUTONOMOUS TRANSACTION

IN AUTONOMOUS TRANSACTION Examples

POST_EVENT

POST_EVENT Examples

RETURN

RETURN Examples

Writing the Body Code

Trapping and Handling Errors

System Exceptions

Custom Exceptions

EXCEPTION

WHEN DO

Scope of a WHEN DO Statement

Examples using WHENDO

Trapping and Handling Errors

Built-in Scalar Functions

Context Functions

RDB$GET_CONTEXT()

The SYSTEM Namespace

The DDL_TRIGGER Namespace

Examples

RDB$SET_CONTEXT()

Context Functions

Mathematical Functions

ABS()

ACOS()

ACOSH()

ASIN()

ASINH()

ATAN()

ATAN2()

ATANH()

CEIL(), CEILING()

COS()

COSH()

COT()

EXP()

FLOOR()

LN()

LOG()

LOG10()

MOD()

PI()

POWER()

RAND()

ROUND()

ROUND Examples

SIGN()

SIN()

SINH()

SQRT()

TAN()

TANH()

TRUNC()

Mathematical Functions

String and Binary Functions

ASCII_CHAR()

ASCII_VAL()

BASE64_DECODE()

Example of BASE64_DECODE

BASE64_ENCODE()

Example of BASE64_ENCODE

BIT_LENGTH()

BIT_LENGTH Examples

BLOB_APPEND()

BLOB_APPEND Examples

CHAR_LENGTH(), CHARACTER_LENGTH()

CHAR_LENGTH Examples

CRYPT_HASH()

Examples of CRYPT_HASH

HASH()

Examples of HASH

HEX_DECODE()

Example of HEX_DECODE

HEX_ENCODE()

Example of HEX_ENCODE

LEFT()

LOWER()

LOWER Examples

LPAD()

LPAD Examples

OCTET_LENGTH()

OCTET_LENGTH Examples

OVERLAY()

OVERLAY Examples

POSITION()

POSITION Examples

REPLACE()

REPLACE Examples

REVERSE()

REVERSE Examples

RIGHT()

RPAD()

RPAD Examples

SUBSTRING()

Positional SUBSTRING

Regular Expression SUBSTRING

TRIM()

TRIM Examples

UNICODE_CHAR()

UNICODE_VAL()

UPPER()

UPPER Examples

String and Binary Functions

Date and Time Functions

DATEADD()

Examples of DATEADD

DATEDIFF()

DATEDIFF Examples

EXTRACT()

Returned Data Types and Ranges

MILLISECOND

WEEK

WEEK

FIRST_DAY()

Examples of FIRST_DAY

LAST_DAY()

Examples of LAST_DAY

Date and Time Functions

Type Casting Functions

CAST()

Shorthand Syntax

Allowed Type Conversions

Casting Parameters

Casting to a Domain or its Type

Casting to a Column's Type

Cast Examples

Type Casting Functions

Bitwise Functions

BIN_AND()

BIN_NOT()

BIN_OR()

BIN_SHL()

BIN_SHR()

BIN_XOR()

Bitwise Functions

UUID Functions

CHAR_TO_UUID()

CHAR_TO_UUID Examples

GEN_UUID()

GEN_UUID Example

UUID_TO_CHAR()

UUID_TO_CHAR Examples

UUID Functions

Functions for Sequences (Generators)

GEN_ID()

GEN_ID Example

Functions for Sequences (Generators)

Conditional Functions

COALESCE()

COALESCE Examples

DECODE()

DECODE Examples

IIF()

IIF Examples

MAXVALUE()

MAXVALUE Examples

MINVALUE()

MINVALUE Examples

NULLIF()

NULLIF Example

Conditional Functions

Special Functions for DECFLOAT

COMPARE_DECFLOAT()

NORMALIZE_DECFLOAT()

Examples of NORMALIZE_DECFLOAT

QUANTIZE()

Examples of QUANTIZE

TOTALORDER()

Special Functions for DECFLOAT

Cryptographic Functions

DECRYPT()

DECRYPT Examples

ENCRYPT()

ENCRYPT Examples

RSA_DECRYPT()

RSA_DECRYPT Examples

RSA_ENCRYPT()

RSA_ENCRYPT Examples

RSA_PRIVATE()

RSA_PRIVATE Examples

RSA_PUBLIC()

RSA_PUBLIC Examples

RSA_SIGN_HASH()

RSA_SIGN_HASH Examples

RSA_VERIFY_HASH()

RSA_VERIFY_HASH Examples

Cryptographic Functions

Other Functions

MAKE_DBKEY()

Examples of MAKE_DBKEY

RDB$ERROR()

Example of RDB$ERROR

RDB$GET_TRANSACTION_CN()

RDB$GET_TRANSACTION_CN Examples

RDB$ROLE_IN_USE()

RDB$ROLE_IN_USE Examples

RDB$SYSTEM_PRIVILEGE()

RDB$SYSTEM_PRIVILEGE Examples

Other Functions

Aggregate Functions

FILTER Clause for Aggregate Functions

General-purpose Aggregate Functions

AVG()

AVG Examples

COUNT()

COUNT Examples

LIST()

LIST Examples

MAX()

MAX Examples

MIN()

MIN Examples

SUM()

SUM Examples

General-purpose Aggregate Functions

Statistical Aggregate Functions

CORR()

CORR Examples

COVAR_POP()

COVAR_POP Examples

COVAR_SAMP()

COVAR_SAMP Examples

STDDEV_POP()

STDDEV_POP Examples

STDDEV_SAMP()

STDDEV_SAMP Examples

VAR_POP()

VAR_POP Examples

VAR_SAMP()

VAR_SAMP Examples

Statistical Aggregate Functions

Linear Regression Aggregate Functions

REGR_AVGX()

REGR_AVGY()

REGR_COUNT()

REGR_INTERCEPT()

REGR_INTERCEPT Examples

REGR_R2()

REGR_SLOPE()

REGR_SXX()

REGR_SXY()

REGR_SYY()

Linear Regression Aggregate Functions

Window (Analytical) Functions

Aggregate Functions as Window Functions

Partitioning

Ordering

Window Frames

Named Windows

Ranking Functions

CUME_DIST()

CUME_DIST Examples

DENSE_RANK()

DENSE_RANK Examples

NTILE()

NTILE Examples

PERCENT_RANK()

PERCENT_RANK Examples

RANK()

RANK Examples

ROW_NUMBER()

ROW_NUMBER Examples

Ranking Functions

Navigational Functions

FIRST_VALUE()

LAG()

LAG Examples

LAST_VALUE()

LEAD()

NTH_VALUE()

Navigational Functions

Aggregate Functions Inside Window Specification

System Packages

RDB$BLOB_UTIL

Function IS_WRITABLE

Function NEW_BLOB

Function OPEN_BLOB

Function READ_DATA

Function SEEK

Procedure CANCEL_BLOB

Procedure CLOSE_HANDLE

Examples

RDB$BLOB_UTIL

RDB$PROFILER

Function START_SESSION

Procedure CANCEL_SESSION

Procedure DISCARD

Procedure FINISH_SESSION

Procedure FLUSH

Procedure PAUSE_SESSION

Procedure RESUME_SESSION

Procedure SET_FLUSH_INTERVAL

Example

RDB$PROFILER

RDB$TIME_ZONE_UTIL

Function DATABASE_VERSION

Procedure TRANSITIONS

RDB$TIME_ZONE_UTIL

Context Variables

CURRENT_CONNECTION

CURRENT_DATE

CURRENT_ROLE

CURRENT_TIME

CURRENT_TIMESTAMP

CURRENT_TRANSACTION

CURRENT_USER

DELETING

GDSCODE

INSERTING

LOCALTIME

LOCALTIMESTAMP

NEW

'NOW'

OLD

RESETTING

ROW_COUNT

SQLCODE

SQLSTATE

'TODAY'

'TOMORROW'

UPDATING

'YESTERDAY'

USER

Transaction Control

Transaction Statements

SET TRANSACTION

Transaction Name

Transaction Parameters

Access Mode

Lock Resolution Mode

WAIT Mode

NO WAIT Mode

Isolation Level

SNAPSHOT Isolation Level Sharing Snapshot Transactions

SNAPSHOT TABLE STABILITY Isolation Level

READ COMMITTED Isolation Level

Variants of READ COMMITTED

NO AUTO UNDO

RESTART REQUESTS

AUTO COMMIT

IGNORE LIMBO

RESERVING

Options for RESERVING Clause

Lock Resolution Mode

Isolation Level

SNAPSHOT Isolation Level Sharing Snapshot Transactions

SNAPSHOT TABLE STABILITY Isolation Level

READ COMMITTED Isolation Level

Variants of READ COMMITTED

NO AUTO UNDO

RESTART REQUESTS

AUTO COMMIT

IGNORE LIMBO

RESERVING

Options for RESERVING Clause

Isolation Level

NO AUTO UNDO

RESTART REQUESTS

AUTO COMMIT

IGNORE LIMBO

RESERVING

Options for RESERVING Clause

NO AUTO UNDO

RESTART REQUESTS

AUTO COMMIT

IGNORE LIMBO

RESERVING

Options for RESERVING Clause

RESTART REQUESTS

AUTO COMMIT

IGNORE LIMBO

RESERVING

Options for RESERVING Clause

AUTO COMMIT

IGNORE LIMBO

RESERVING

Options for RESERVING Clause

IGNORE LIMBO

RESERVING

Options for RESERVING Clause

RESERVING

COMMIT

COMMIT Options

ROLLBACK

ROLLBACK Options

ROLLBACK TO SAVEPOINT

SAVEPOINT

RELEASE SAVEPOINT

Internal Savepoints

Savepoints and PSQL

Transaction Statements

Security

User Authentication

Specially Privileged Users

POSIX Hosts

The SYSDBA User on POSIX

The root User

Windows Hosts

The Database Owner Users with the USER_MANAGEMENT System Privilege

Specially Privileged Users

RDB$ADMIN Role

Granting the RDB$ADMIN Role in the Security Database

Doing the Same Task Using gsec

Using the RDB$ADMIN Role in the Security Database

Using gsec with RDB$ADMIN Rights

Granting the RDB$ADMIN Role in a Regular Database

Using the RDB$ADMIN Role in a Regular Database

AUTO ADMIN MAPPING

Auto Admin Mapping in Regular Databases

Auto Admin Mapping in the Security Database

RDB$ADMIN Role

Administrators

Fine-grained System Privileges

List of Valid System Privileges

Fine-grained System Privileges

SQL Statements for User Management

CREATE USER

Who Can Create a User

CREATE USER Examples

CREATE USER

ALTER USER

Who Can Alter a User?

ALTER USER Examples

ALTER USER

CREATE OR ALTER USER

CREATE OR ALTER USER Examples

CREATE OR ALTER USER

DROP USER

Who Can Drop a User?

DROP USER Example

DROP USER

SQL Privileges

The Object Owner

ROLE

CREATE ROLE

Who Can Create a Role

CREATE ROLE Examples

CREATE ROLE

ALTER ROLE

Who Can Alter a Role

ALTER ROLE Examples

ALTER ROLE

DROP ROLE

Who Can Drop a Role

DROP ROLE Examples

DROP ROLE

Statements for Granting Privileges

GRANT

The TO Clause

Packaging Privileges in a ROLE Object

Cumulative Roles

Default Roles

The User PUBLIC

The WITH GRANT OPTION Clause

The GRANTED BY Clause

Alternative Syntax Using AS username

Privileges on Tables and Views

Examples of GRANT <privilege> on Tables

The EXECUTE Privilege

Examples of Granting the EXECUTE Privilege

The USAGE Privilege

Examples of Granting the USAGE Privilege

DDL Privileges

Examples of Granting DDL Privileges

Database DDL Privileges

Examples of Granting Database DDL Privileges

Assigning Roles

The WITH ADMIN OPTION Clause

Examples of Role Assignment

GRANT

Statements for Revoking Privileges

REVOKE

The DEFAULT Clause

The FROM Clause

Revoking the GRANT OPTION

Removing the Privilege to One or More Roles

Revoking Privileges That Were GRANTED BY

Revoking ALL ON ALL

Examples using REVOKE

REVOKE

Mapping of Users to Objects

The Mapping Rule

CREATE MAPPING

Who Can Create a Mapping

CREATE MAPPING examples

CREATE MAPPING

ALTER MAPPING

Who Can Alter a Mapping

ALTER MAPPING examples

ALTER MAPPING

CREATE OR ALTER MAPPING

CREATE OR ALTER MAPPING examples

CREATE OR ALTER MAPPING

DROP MAPPING

Who Can Drop a Mapping

DROP MAPPING examples

DROP MAPPING

Database Encryption

Encrypting a Database

Decrypting a Database

SQL Security

Management Statements

Data Type Behaviour

SET BIND (Data Type Coercion Rules)

SET BIND Examples

SET DECFLOAT

SET DECFLOAT ROUND

SET DECFLOAT TRAPS

Data Type Behaviour

Connections Pool Management

ALTER EXTERNAL CONNECTIONS POOL

Clauses of ALTER EXTERNAL CONNECTIONS POOL

How the Connection Pool Works

New Connections

Who Can Alter the External Connections Pool

Connections Pool Management

Changing the Current Role

SET ROLE

SET ROLE Examples

SET TRUSTED ROLE

SET TRUSTED ROLE Examples

Changing the Current Role

Session Timeouts

SET SESSION IDLE TIMEOUT

Idle Session Timeouts

How the Idle Session Timeout Works

Setting the Idle Session Timeout

Determining the Timeout that is In Effect

Setting the Idle Session Timeout

Determining the Timeout that is In Effect

Determining the Timeout that is In Effect

SET STATEMENT TIMEOUT

Statement Timeouts

How the Statement Timeout Works

Setting a Statement Timeout

Determining the Statement Timeout that is In Effect

Setting a Statement Timeout

Determining the Statement Timeout that is In Effect

Determining the Statement Timeout that is In Effect

Session Timeouts

Time Zone Management

SET TIME ZONE

SET TIME ZONE Examples

Time Zone Management

Optimizer Configuration

SET OPTIMIZE

Optimizer Configuration

Reset Session State

ALTER SESSION RESET

Error Handling

Reset Session State

Debugging

SET DEBUG OPTION

Debugging

Supplementary Information

The RDB$VALID_BLR Field

A Note on Equality

Exception Codes and Messages

SQLSTATE Error Codes and Descriptions

SQLCODE and GDSCODE Error Codes and Descriptions

Reserved Words and Keywords

Reserved words

Keywords

System Tables

RDB$AUTH_MAPPING

RDB$BACKUP_HISTORY

RDB$CHARACTER_SETS

RDB$CHECK_CONSTRAINTS

RDB$COLLATIONS

RDB$CONFIG

RDB$DATABASE

RDB$DB_CREATORS

RDB$DEPENDENCIES

RDB$EXCEPTIONS

RDB$FIELDS

RDB$FIELD_DIMENSIONS

RDB$FILES

RDB$FILTERS

RDB$FORMATS

RDB$FUNCTIONS

RDB$FUNCTION_ARGUMENTS

RDB$GENERATORS

RDB$INDEX_SEGMENTS

RDB$INDICES

RDB$KEYWORDS

RDB$LOG_FILES

RDB$PACKAGES

RDB$PAGES

RDB$PROCEDURES

RDB$PROCEDURE_PARAMETERS

RDB$PUBLICATIONS

RDB$PUBLICATION_TABLES

RDB$REF_CONSTRAINTS

RDB$RELATIONS

RDB$RELATION_CONSTRAINTS

RDB$RELATION_FIELDS

RDB$ROLES

RDB$SECURITY_CLASSES

RDB$TIME_ZONES

RDB$TRANSACTIONS

RDB$TRIGGERS

RDB$TRIGGER_TYPE Value

RDB$TRIGGERS

RDB$TRIGGER_MESSAGES

RDB$TYPES

RDB$USER_PRIVILEGES

RDB$VIEW_RELATIONS

Monitoring Tables

MON$ATTACHMENTS

Using MON$ATTACHMENTS to Kill a Connection

MON$ATTACHMENTS

MON$COMPILED_STATEMENTS

MON$CALL_STACK

MON$CONTEXT_VARIABLES

MON$DATABASE

MON$IO_STATS

MON$MEMORY_USAGE

MON$RECORD_STATS

MON$STATEMENTS

Using MON$STATEMENTS to Cancel a Query

MON$STATEMENTS

MON$TABLE_STATS

MON$TRANSACTIONS

Security tables

SEC$DB_CREATORS

SEC$GLOBAL_AUTH_MAPPING

SEC$USERS

SEC$USER_ATTRIBUTES

Plugin tables

PLG$PROF_CURSORS

PLG$PROF_PSQL_STATS

PLG$PROF_PSQL_STATS_VIEW

PLG$PROF_RECORD_SOURCES

PLG$PROF_RECORD_SOURCE_STATS

PLG$PROF_RECORD_SOURCE_STATS_VIEW

PLG$PROF_REQUESTS

PLG$PROF_SESSIONS

PLG$PROF_STATEMENTS

PLG$PROF_STATEMENT_STATS_VIEW

PLG$SRP

PLG$USERS

Character Sets and Collations

License notice

Document History

Firebird ODBC/JDBC Driver 2.0 Manual

ODBC/JDBC Driver for Firebird Client Applications

About the Firebird ODBC driver

Features Supported

About the Firebird ODBC driver

Installing the Driver

Downloading the Driver

Getting the Right Firebird Client Library

Installing the Driver on Windows

Installing the Driver on Linux

Unpacking the Files

Building from Sources

Installing the Binary Package

Installing the Driver on Linux

Firebird ODBC Configuration

Configuring a DSN on Windows

The DSN Settings

The Services Button

Configuring a DSN on Windows

Configuring a DSN on Linux

Testing the Configuration

Configuring a DSN on Linux

Connecting to Firebird from Applications

Connection Parameters

Read Sequence of the Keys

Connection Examples

DBNAME for Embedded Connections

DBNAME Using Aliases

Connection Parameters

Developing with the Firebird ODBC/JDBC Driver

Multithreading

Transactions

Locking

Transaction Request Syntax

What the Options Mean

Named Transactions and Transaction Workspaces

Ending Explicit Transactions

Ending Explicit Transactions

Two Phase Commit Transactions

More Transactions

MS DTC Transactions

Transactions

Password Security

Cursors

ODBC Cursor Library

Cursors

Stored Procedures

ARRAY Data Type

Usage with Clarion

Firebird Events

Priming the Driver to Listen for Events

The Services Interface

Exploring the ODBC Services Console

Showing Logs from the Interface

Exploring the ODBC Services Console

Using the Services API

Examples of Services Use

More Ways to Create a Database

Using the Services API

Examples

Licence Notices

Documentation Licence Software Licence

Document History

Firebird 5.0.1 Release Notes

General Notes

Compatibility with Older Versions

Sub-release V.5.0.1

V.5.0.1 Improvements

Sub-release V.5.0.1

Bug Reporting

Documentation

New In Firebird 5.0

Summary of New Features

Complete In Firebird 5.0 Release Candidate 2 Complete In Firebird 5.0 Release Candidate 1 Complete In Firebird 5.0 Beta 1

Summary of New Features

Changes in the Firebird Engine

Quick Links

Support for parallel operations

Inline minor ODS upgrade

More cursor-related details in the plan output

Denser compression of records

Compiled statement cache

SQL and PSQL profiler

RDB$BLOB_UTIL package

Changes to the Firebird API and ODS

ODS (On-Disk Structure) Changes

New Minor ODS Number

New System Tables

New Columns in System Tables

ODS (On-Disk Structure) Changes

Application Programming Interfaces

Main API Extensions

Extensions to various getInfo() Methods

Services API Extensions

Application Programming Interfaces

Reserved Words and Changes

New Keywords in Firebird 5.0

Non-reserved

New Keywords in Firebird 5.0

Configuration Additions and Changes

Parameters for Parallel Operations

MaxParallelWorkers

ParallelWorkers

Parameters for Parallel Operations

Other Parameters

MaxStatementCacheSize

OnDisconnectTriggerTimeout

DefaultProfilerPlugin

OptimizeForFirstRows

OuterJoinConversion

SubQueryConversion

Other Parameters

Changed configuration parameters

Replication Configuration Additions and Changes

cascade_replication

Allow macros in replication.conf

Replication Configuration Additions and Changes

Removed configuration parameters

RemotePipeName

TcpLoopbackFastPath

Removed configuration parameters

Security

System privilege PROFILE_ANY_ATTACHMENT

Trace events before a valid security context is established

Management Statements

SET OPTIMIZE

Data Definition Language (DDL)

Quick Links

Support for partial indices

COMMENT ON MAPPING

Data Manipulation Language (DML)

Quick Links

SKIP LOCKED clause

Support for WHEN NOT MATCHED BY SOURCE in the MERGE statement

Support multiple rows for DML RETURNING

Allow parenthesized query expressions

Support PLAN and ORDER BY on MERGE

Support PLAN, ORDER BY and ROWS on UPDATE OR INSERT

OPTIMIZE FOR Clause

Changes to literals

Full SQL standard character string literal syntax

Full SQL standard binary string literal syntax

Changes to literals

Improvements to IN

New Expressions and Built-in Functions

UNICODE_CHAR and UNICODE_VAL

QUARTER added to EXTRACT, FIRST_DAY and LAST_DAY

New Expressions and Built-in Functions

Procedural SQL (PSQL)

Allow subroutines to access variables/parameters defined at the outer/parent level

Monitoring Command-line Utilities

Monitoring

isql

Unify display of system procedures and functions packages with other system objects

Display statement BLR

Replication information added to SHOW output

Display per-table statistics

isql

gbak

Parallel backup/restore

gbak

gfix

Parallel sweep and ICU dependencies rebuild

ODS upgrade

gfix

Compatibility Issues

Migrating the security database from Firebird 4.0 to 5.0

SQL

Multi-row RETURNING behaviour

SQL

Removal of WNET protocol

Removal of QLI

Removal of fbclient_bor.lib

Bugs Fixed

Firebird 5.0.1 Release: Bug Fixes

Core Engine

Server Crashes/Hangups

Utilities

isql

gbak

Builds/Packaging

Firebird 5.0.1 Release: Bug Fixes

Firebird 5.0 Release Candidate 2: Bug Fixes

Core Engine

Server Crashes/Hangups

Utilities

isql

gbak

Firebird 5.0 Release Candidate 2: Bug Fixes

Firebird 5.0 Release Candidate 1: Bug Fixes

Core Engine

Server Crashes/Hangups

Utilities

gbak

nbackup

Firebird 5.0 Release Candidate 1: Bug Fixes

Firebird 5.0 Beta 1 Release: Bug Fixes

Core Engine

Server Crashes/Hangups

Utilities

gbak

Firebird 5.0 Beta 1 Release: Bug Fixes

Firebird 5.0 Project Teams

Licence Notice

Writing Firebird UDRs in Pascal

Introduction

Firebird API

CLOOP

Constants

Life time management

Firebird API

UDR announcements

External functions

External Procedures

Placing External Procedures and Functions Inside Packages

External triggers

UDR announcements

Structure UDR

Registering Functions

Function factory

Function instance

Registration of procedures

Factory of procedures

Procedure instance

Store a choice procedure

Registering triggers

Trigger Factory

trigger instance

Structure UDR

Messages

Working with the message buffer using a structure

Working with the message buffer using IMessageMetadata

Methods of the IMessageMetadata interface

Getting and Using IMessageMetadata

Messages

Factories

Method newItem

Creating instances of UDRs depending on their declaration

setup method

Generic factories

Factories

Working with the BLOB type

Reading data from BLOB

Data recording in Blob

Helper for working with the Blob type

Working with the BLOB type

Connection and transaction context

License notice

Document history

Firebird Database Cache Buffer

Introduction

The Firebird Cache

Appendices

Document history

License notice

Practical Migration Guide to Firebird 5.0

Converting the database to the new format

Transfer of database aliases Transfer of user list Setting up trusted authentication Application-level incompatibilities

Removed network protocol WNET

Mass Migration Framework for Firebird Conclusion

Converting the database to the new format

Firebird Shell Scripts

Introduction

ChangeDBAPassword

CreateAliasDB

createAliasDB.sh Parameters

CreateAliasDB

Fb_config

fb_config Options

Fb_config

ChangeRunUser

ChangeRunUser.sh Parameters

ChangeRunUser

RestoreRootRunUser

ChangeGDSLibraryCompatibleLink

Document history

License notice

Firebird's gbak Backup and Restore Utility

Introduction

Command-line Options

Common Options

-?

-CRYPT

-D[IRECT_IO]

-FE[TCH_PASSWORD]

-KEYHOLDER

-KEYNAME

-M[ETA_DATA]

-PAR[ALLEL]

-PAS[SWORD]

-ROLE

-SE[RVICE]

-SKIP_D[ATA]

-INCLUDE[_DATA]

-ST[ATISTICS]

-TR[USTED]

-USER

-V[ERIFY]

-VERBI[NT]

-Y

-Z

Backup Switches

-B[ACKUP_DATABASE]

-CO[NVERT]

-E[XPAND]

-FA[CTOR]

-G[ARBAGE_COLLECT]

-IG[NORE]

-L[IMBO]

-NOD[BTRIGGERS]

-NT

-OL[D_DESCRIPTIONS]

-T[RANSPORTABLE]

-ZIP

Restore Switches

-C[REATE_DATABASE]

-R[ECREATE_DATABASE] [O[VERWRITE]]

-REP[LACE_DATABASE]

-BU[FFERS]

-FIX_FSS_D[ATA]

-FIX_FSS_M[ETADATA]

-I[NACTIVE]

-K[ILL]

-MO[DE]

-N[O_VALIDITY]

-O[NE_AT_A_TIME]

-P[AGE_SIZE]

-REPLICA

-USE_[ALL_SPACE]

Command-line Options

Backup Mode

Speeding up the Backup

Backup Mode

Restore Mode

Restore Or Recreate?

Malformed String Errors During Restores

Speeding up the Restore

Restore Mode

Security Of Backups

Backup Restore Recipes

Backup Restore Prerequisites

A Simple Backup Restore

Metadata Only

Splitting The Backup

Changing The ODS

Changing The Cache Size

Changing The Page Size

Creating A Read-Only Database Clone

Creating a Database Clone Without a Backup File.

Backup Restore With Without Shadow Files.

Remote Backups Restores

Remote Backups and Restores Using SSH

Using External Tools

Backup Restore Recipes

Gbak Caveats

Gbak Default Mode

Normal Versus Privileged Users

Silent Running?

Gbak Log File Cannot Be Overwritten

Use of stdin or stdout Filenames

Gbak Caveats

Document history

License notice

Firebird's gstat Database Statistics Reporting Tool

Introduction

Command-line Switches

-A[LL]

-D[ATA]

-E[NCRYPTION]

-H[EADER]

-I[NDEX]

-N[OCREATION]

-S[YSTEM]

-R[ECORD]

-RO[LE]

-T[ABLE]

-TR[USTED]

-U[SERNAME]

-P[ASSWORD]

-FE[TCH_PASSWORD]

-Z

-?

Command-line Switches

Gstat Examples And Interpretation

Database Header

Analyse Entire Database

Analyse Data Pages Only

Analyse Index Pages Only

Selecting Tables To Analyse

Including The System Tables Indices

Record Version Details

If You Have Database Corruption

Gstat Examples And Interpretation

Gstat Caveats

The -t[able] Switch Can Cause Problems

The Shadow Count Seems Wrong

Gstat Caveats

Document history

License notice

Firebird's isql Interactive SQL Utility

Introduction

Audience

Version

Introduction

Overview

Invoking Isql

Commandline Switches

Starting An Isql Session

Ending an Isql Session

Getting Help

Connecting To A Database

Connection Strings

URL-like Syntax

Legacy Connection Strings

Using Database Aliases

Connecting To A Database

Creating A Database

Setting The ISC_USER And ISC_PASSWORD Environment Variables

SQL Dialects

Terminator Character Isql Prompts

Error Handling And Exception Support

Transaction Handling

Script Handling

Isql Commands

BLOBDUMP

BLOBVIEW

EDIT

HELP

ADD

COPY

INput

OUTput

SET

SHELL

SHOW

EXIT

QUIT

Isql Commands

Isql Set Commands

SET

SET AUTOddl

SET BAIL

SET BLOBdisplay

SET COUNT

SET MAXROWS or SET ROWCOUNT

SET ECHO

SET EXEC_PATH_DISPLAY

SET EXPLAIN

SET HEADING

SET KEEP_TRAN_params

SET LIST

SET LOCAL_TIMEOUT

SET NAMES

SET PER_TABle_stats

SET PLAN

Effects of Various Plan-Related Commands

SET PLANONLY

SET SQLDA_DISPLAY

SET SQL DIALECT

SET STATs

SET TIME

SET TERM

SET TRANSACTION

SET WARNINGS or SET WNG

SET WIDTH

Isql Set Commands

Isql Show commands

SHOW CHECKs

SHOW COLLATIONs or SHOW COLLATEs

SHOW COMMENTs

SHOW DATABASE or SHOW DB

SHOW DEPENdencies or SHOW DEPENdency

SHOW DOMAINs

SHOW EXCEPtions

SHOW FILTERs

SHOW FUNCtions

SHOW GENerators

SHOW GRANTs

SHOW INDexes or SHOW INDICES

SHOW MAPping

SHOW PROCedures

SHOW PACKages

SHOW PUBlications

SHOW ROLEs

SHOW SECCLAsses

SHOW SECURITY CLAsses

SHOW SEQuences

SHOW SQL DIALECT

SHOW SYStem

SHOW SCHEmas

SHOW TABLEs

SHOW TRIGgers

SHOW VERsion

SHOW USERS

SHOW VIEWs

Isql Show commands

Document History

License Notice

Firebird's nbackup tool

Introduction

Nbackup features — an overview

Advantages of nbackup

Limitations of nbackup

Nbackup features — an overview

Functions and parameters

Making and restoring backups

Full backups

Making full backups

A word on the inner workings

Restoring a full backup

Incremental backups

Making incremental backups

Important performance notice

Restoring incremental backups

Non-connecting links

Backing up raw-device databases

Suppressing database triggers (Firebird 2.1+)

Direct I/O (Firebird 2.1.4+)

Informational options (Firebird 2.5+)

Backups on remote machines (Firebird 2.5+)

A practical application

Read on?

Making and restoring backups

Locking and unlocking

Locking the database and making the backup yourself

Restoring a backup made after nbackup -L

Under the hood

Locking raw-device databases

Locking/Unlocking database in case of Virtual Machines Backup

Locking and unlocking

Setting the delta file

Backup history

Technical background information

Document history

License notice

Firebird Null Guide: NULL behaviour and pitfalls in Firebird SQL

What is NULL?

NULL as a default state

What is NULL?

NULL support in Firebird SQL

Disallowing NULL

Testing for NULL

Assigning NULL

Testing DISTINCTness (Firebird 2+)

The NULL literal

NULL support in Firebird SQL

NULL in operations

Mathematical and string operations

Boolean operations

More logic (or not)

NULL in operations

Internal functions and directives

Internal functions

FIRST, SKIP and ROWS

Internal functions and directives

Predicates

The IN predicate

With an empty list

With a NULL test expression

With NULLs in the list

IN() results

IN() in CHECK constraints

The ANY, SOME and ALL quantifiers

Result values

EXISTS and SINGULAR

EXISTS

SINGULAR

Predicates

Searches

Sorts

Aggregate functions

The GROUP BY clause

Counting frequencies

=====================

Counting frequencies

The HAVING clause

Aggregate functions

Conditional statements and loops

IF statements

CASE expression

WHILE loops

FOR loops

Conditional statements and loops

Keys and unique indices

Primary keys

Unique keys and indices

Foreign keys

Keys and unique indices

CHECK constraints

SELECT DISTINCT

User-Defined Functions (UDFs)

NULL <=> non-NULL conversions you didn't ask for

Descriptors

Improvements in Firebird 2

Being prepared for undesired conversions

More on UDFs

User-Defined Functions (UDFs)

Converting to and from NULL

Substituting NULL with a value

The COALESCE function

Firebird 1.0: the *NVL functions

Converting values to NULL

Firebird 1.5 and up: the NULLIF function

Firebird 1.0: the *nullif UDFs

Converting to and from NULL

Altering populated tables

Adding a non-nullable field to a populated table

Adding a NOT NULL field

False reporting of NULLs as zeroes

Adding a CHECKed column

Using domains to add a non-nullable field

Making existing columns non-nullable

Making an existing column NOT NULL

Adding a CHECK constraint to an existing column

Making non-nullable columns nullable again

Removing a NOT NULL constraint

Removing a domain-based NOT NULL constraint

Removing a CHECK constraint

Removing a domain-based CHECK constraint

Altering populated tables

Changing the nullability of a domain

Adding and removing domain-level CHECK constraints

Altering a domain's NOT NULL setting

Changing the nullability of a domain

Testing for NULL and equality in practice

Testing for NULL — if it matters

Equality tests

Firebird 2.0 and up

Earlier Firebird versions

Summary of (in)equality tests

Finding out if a field has changed

Testing for NULL and equality in practice

Summary

NULL-related bugs in Firebird

Bugs that crash the server

EXECUTE STATEMENT with NULL argument

EXTRACT from NULL date

FIRST and SKIP with NULL argument

LIKE with NULL escape

Other bugs

NULLs in NOT NULL columns

Illegal NULLs returned as 0, '', etc.

Primary key with NULL entries

SUBSTRING results described as non-nullable

Gbak -n restoring NOT NULL

IN, = ANY and = SOME with indexed subselect

ALL with indexed subselect

SELECT DISTINCT with wrong NULLS FIRST|LAST ordering

UDFs returning values when they should return NULL

UDFs returning NULL when they should return a value

SINGULAR inconsistent with NULL results

NULL-related bugs in Firebird

Document history

License notice

Firebird 5 Quick Start Guide

About this guide

The Firebird licenses

Installing Firebird

Installation kits

Installing the Firebird server

Before installation

Installation drives

Installation script or program

Server modes

Installing on Windows

Use the Guardian?

Installing on Linux and other Unix-like platforms

Installing multiple servers

Testing your installation

Pinging the server

Making sure that the Firebird server is running

Server check: Linux and other Unices

Server check: Windows, running as service

Server check: Windows, running as application

Server check: Windows, running as service

Server check: Windows, running as application

Server check: Windows, running as application

Performing a client-only install

Windows

Linux and some other POSIX clients

Installing Firebird

Default disk locations

Linux

Windows

Default disk locations

Server configuration and management

User management

Changing the SYSDBA password

Adding Firebird user accounts

The security database

Appointing co-administrators

Differences between co-administrators and SYSDBA

Security

Administration tools

Server configuration and management

Working with databases

Connection strings

Local connection strings

TCP/IP connection strings

XNET connection strings

NetBEUI connection strings

Third-party programs

Connecting to an existing database

Connecting with isql

Connecting with a GUI client

Creating a database using isql

Starting isql

The CREATE DATABASE statement

Creating a database as a non-privileged user

Firebird SQL

Division of an integer by an integer

Things to know about strings

String delimiter symbol

Apostrophes in strings

Concatenation of strings

Double-quoted identifiers

Apostrophes in strings

Concatenation of strings

Double-quoted identifiers

Concatenation of strings

Double-quoted identifiers

Double-quoted identifiers

Expressions involving NULL

The DISTINCT keyword comes to the rescue!

More about NULLs

More about NULLs

Working with databases

Protecting your data

Backup

How to corrupt a database

Disabling forced writes

Disabling forced writes on Windows

Disabling forced writes on Linux

Disabling forced writes on Linux

Restoring a backup to a running database

Allowing users to log in during a restore

Protecting your data

How to get help

How to give help

The Firebird Project

Document History

License notice

Firebird Wire Protocol

Introduction Protocol versions

Common responses

Generic response

SQL response

Fetch response

Slice response

Dummy response

Common requests Establishing connection

Attachment to database or service Connection rejection Connection handshake

Detach Disconnect

Common responses

Databases

Attach database

Create database

Drop Detach and disconnect

Database information request Cancellation

Databases

Transactions

Start transaction

Commit transaction

Rollback transaction

Commit retaining

Rollback retaining

Prepare

Simple prepare

Reconnect transaction

Transaction information request

Transactions

Statements

Allocate

Deviations for protocol version 11

Free

Deviations for protocol version 11

Prepare

Deviations for protocol version 11

Describe

Describe bind (input parameters)

Execute

Rows affected by query execution

Fetch

Set cursor name

Statement information request

Statements

Blobs

Create/Open

Deviations for protocol version 11

Get segment

Deviations for protocol version 11

Put segment

Deviations for protocol version 11

Batch segments

Deviations for protocol version 11

Seek

Deviations for protocol version 11

Cancel

Deviations for protocol version 11

Close

Deviations for protocol version 11

Blobs

Arrays

Get slice

Put slice

Arrays

Batches

Create

Send messages

Execute batch

Release batch

Cancel batch

Sync batch

Set default blob parameters

Register existing blob

Stream of BLOB data Batch information request

Batches

Services

Attach

Detach

Start

Query service

Services

Events

Connection request

Queue events

Cancel events Event notification

Events

Reading row data

External Data Representation (XDR)

Data types

Revision history