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

Length of

DECFLOAT Use with Standard Functions Special Functions for

Length of

DECFLOAT Use with Standard Functions Special Functions for

DECFLOAT Use with Standard Functions Special Functions for

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

Package

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

Character Indexes

Character Types in Detail

BINARY

CHAR

VARBINARY

VARCHAR

NCHAR

Character Data Types

Boolean Data Type

BOOLEAN

The

BOOLEAN

Use of Boolean Against Other Data Types

Boolean Data Type

Binary Data Types

BLOB

BLOB

Binary Data Types

Array Types

Specifying Explicit Boundaries for Dimensions

Adding More Dimensions PSQL Source for

Array Types

Special Data Types

SQL_NULL

Special Data Types

Conversion of Data Types

Explicit Data Type Conversion

Casting to a Domain

Casting to

Conversions Possible for the

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

Conditional Expressions

CASE

Simple

Searched

Searched

NULL

Expressions Returning

NULL

Subqueries

Correlated Subqueries

Scalar Results

Expressions

Predicates

Conditions

Comparison Predicates

Other Comparison Predicates

BETWEEN

LIKE

Wildcards

Using the Examples using

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

SIMILAR TO

IS [NOT] DISTINCT FROM

Boolean

IS [NOT] NULL

IS [NOT] DISTINCT FROM

Boolean

IS [NOT] NULL

Boolean

IS [NOT] NULL

IS [NOT] NULL

Existential Predicates

EXISTS

IN

SINGULAR

Quantified Subquery Predicates

ALL

ANY

Predicates

Data Definition (DDL) Statements

DATABASE

CREATE DATABASE

Using a Database Alias

Creating a Database on a Remote Server

Optional Parameters for

Specifying the Database Dialect

Who Can Create a Database

Examples Using

CREATE DATABASE

ALTER DATABASE

Who Can Alter the Database

Parameters for

Examples of

ALTER DATABASE

DROP DATABASE

Who Can Drop a Database

Example of

DROP DATABASE

SHADOW

CREATE SHADOW

AUTO | MANUAL

Options for

Who Can Create a Shadow

Examples Using

CREATE SHADOW

DROP SHADOW

Who Can Drop a Shadow

Example of

DROP SHADOW

DOMAIN

CREATE DOMAIN

Type-specific Details

Who Can Create a Domain

CREATE DOMAIN

CREATE DOMAIN

ALTER DOMAIN

ALTER DOMAIN

What

Who Can Alter a Domain

ALTER DOMAIN

ALTER DOMAIN

DROP DOMAIN

Who Can Drop a Domain

Example of

DROP DOMAIN

TABLE

CREATE TABLE

Character Columns

Setting a

Domain-based Columns

Identity Columns (Autoincrement)

GENERATED ALWAYS

GENERATED BY DEFAULT

START WITH

INCREMENT

Computed Columns

Defining an Array Column

Constraints

Names for Constraints and Their Indexes

Named Constraints

The

The

PRIMARY KEY

The

NULL

FOREIGN KEY

Foreign Key Actions

CHECK

NOT NULL

SQL SECURITY

Replication Management

Who Can Create a Table

CREATE TABLE

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

The

The

The

Renaming a Column: the

Changing the Data Type of a Column: the

Changing the Position of a Column: the

The

The

The

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

DROP TABLE

Who Can Drop a Table?

Example of

DROP TABLE

RECREATE TABLE

Example of

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

ALTER INDEX

Who Can Alter an Index?

Use of

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

CREATE OR ALTER VIEW

Example of

CREATE OR ALTER VIEW

DROP VIEW

Who Can Drop a View?

Example

DROP VIEW

RECREATE VIEW

Example of

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

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

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

DROP TRIGGER

Who Can Drop a Trigger?

Example of

DROP TRIGGER

RECREATE TRIGGER

Example of

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

ALTER PROCEDURE

CREATE OR ALTER PROCEDURE

CREATE OR ALTER PROCEDURE

CREATE OR ALTER PROCEDURE

DROP PROCEDURE

Who Can Drop a Procedure

DROP PROCEDURE

DROP PROCEDURE

RECREATE PROCEDURE

RECREATE PROCEDURE

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

CREATE FUNCTION

ALTER FUNCTION

Who Can Alter a Function

Examples of

ALTER FUNCTION

CREATE OR ALTER FUNCTION

Examples of

CREATE OR ALTER FUNCTION

DROP FUNCTION

Who Can Drop a Function

Examples of

DROP FUNCTION

RECREATE FUNCTION

Examples of

RECREATE FUNCTION

EXTERNAL FUNCTION

DECLARE EXTERNAL FUNCTION

DECLARE EXTERNAL FUNCTION

Clauses and Keywords

Who Can Create an External Function Examples using

DECLARE EXTERNAL FUNCTION

ALTER EXTERNAL FUNCTION

Who Can Alter an External Function

Examples using

ALTER EXTERNAL FUNCTION

DROP EXTERNAL FUNCTION

Who Can Drop an External Function

Example using

DROP EXTERNAL FUNCTION

PACKAGE

CREATE PACKAGE

Statement Terminators

SQL Security

Procedure and Function Parameters

Who Can Create a Package

Examples of

CREATE PACKAGE

ALTER PACKAGE

Who Can Alter a Package

Examples of

ALTER PACKAGE

CREATE OR ALTER PACKAGE

Examples of

CREATE OR ALTER PACKAGE

DROP PACKAGE

Who Can Drop a Package

Examples of

DROP PACKAGE

RECREATE PACKAGE

Examples of

RECREATE PACKAGE

PACKAGE BODY

CREATE PACKAGE BODY

Who Can Create a Package Body

Examples of

CREATE PACKAGE BODY

ALTER PACKAGE BODY

Who Can Alter a Package Body

Examples of

ALTER PACKAGE BODY

DROP PACKAGE BODY

Who Can Drop a Package Body

Examples of

DROP PACKAGE BODY

RECREATE PACKAGE BODY

Examples of

RECREATE PACKAGE BODY

FILTER

DECLARE FILTER

Specifying the Subtypes

Parameters

Who Can Create a

Examples of

DECLARE FILTER

DROP FILTER

Who Can Drop a

DROP FILTER

DROP FILTER

SEQUENCE

CREATE SEQUENCE

Who Can Create a Sequence?

Examples of

CREATE SEQUENCE

ALTER SEQUENCE

Who Can Alter a Sequence?

Examples of

ALTER SEQUENCE

CREATE OR ALTER SEQUENCE

Example of

CREATE OR ALTER SEQUENCE

DROP SEQUENCE

Who Can Drop a Sequence?

Example of

DROP SEQUENCE

RECREATE SEQUENCE

Example of

RECREATE SEQUENCE

SET GENERATOR

Who Can Use a

Example of

SET GENERATOR

EXCEPTION

CREATE EXCEPTION

Who Can Create an Exception

CREATE EXCEPTION

CREATE EXCEPTION

ALTER EXCEPTION

Who Can Alter an Exception

ALTER EXCEPTION

ALTER EXCEPTION

CREATE OR ALTER EXCEPTION

CREATE OR ALTER EXCEPTION

CREATE OR ALTER EXCEPTION

DROP EXCEPTION

Who Can Drop an Exception

DROP EXCEPTION

DROP EXCEPTION

RECREATE EXCEPTION

RECREATE EXCEPTION

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

CHARACTER SET

ALTER CHARACTER SET

Who Can Alter a Character Set

ALTER CHARACTER SET

ALTER CHARACTER SET

Comments

COMMENT ON

Who Can Add a Comment

Examples using

COMMENT ON

Data Manipulation (DML) Statements

SELECT

FIRST

Characteristics of

Examples of FIRST/SKIP

The

Examples of

The

Selecting

Examples

Selecting

Selecting

LATERAL

Example using a derived table A more useful example

Example using a derived table A more useful example

Selecting

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

The

Examples

The

HAVING

The

The

Simple Plans

Composite Plans

UNION

ORDER BY

Sorting Direction

Collation Order

NULLs Position

Ordering

Examples of

ROWS

Replacing of

Mixing

ROWS

Examples of

OFFSET

Examples of

FOR UPDATE [OF]

WITH LOCK

Usage with a

How the engine deals with

Caveats using

Examples using explicit locking

OPTIMIZE FOR

INTO

Common Table Expressions (“

Recursive CTEs

CTE Notes Example

Recursive CTEs

Full

SELECT

INSERT

INSERT …​ VALUES

INSERT …​ SELECT

INSERT …​ DEFAULT VALUES

OVERRIDING

The

Inserting into

INSERT

UPDATE

Using an alias

The

The

The

The

The

The

RETURNING Example (DSQL)

Updating

UPDATE

UPDATE OR INSERT

The

The

Example of

UPDATE OR INSERT

DELETE

Aliases

WHERE

PLAN

ORDER BY

SKIP LOCKED

RETURNING

DELETE

MERGE

The

The

Examples of

MERGE

EXECUTE PROCEDURE

“Executable” Stored Procedure Examples of

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

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

Database Triggers

DDL Triggers

Semantics

The

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

CHARACTER SET

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

IF …​ THEN …​ ELSE

IF

WHILE …​ DO

WHILE …​ DO

BREAK

LEAVE

LEAVE

CONTINUE

CONTINUE

EXIT

SUSPEND

SUSPEND

EXECUTE STATEMENT

Parameterized Statements

Special Rules for Parameterized Statements

Examples of

Examples of

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

Caveats with

FOR SELECT

The Undeclared Cursor

Examples using

FOR EXECUTE STATEMENT

FOR EXECUTE STATEMENT

OPEN

OPEN

FETCH

FETCH

CLOSE

CLOSE

IN AUTONOMOUS TRANSACTION

IN AUTONOMOUS TRANSACTION

POST_EVENT

POST_EVENT

RETURN

RETURN

Writing the Body Code

Trapping and Handling Errors

System Exceptions

Custom Exceptions

EXCEPTION

WHEN …​ DO

Scope of a

Examples using

Trapping and Handling Errors

Built-in Scalar Functions

Context Functions

RDB$GET_CONTEXT()

The

The

Examples

RDB$SET_CONTEXT()

Context Functions

Mathematical Functions

ABS()

ACOS()

ACOSH()

ASIN()

ASINH()

ATAN()

ATAN2()

ATANH()

CEIL()

COS()

COSH()

COT()

EXP()

FLOOR()

LN()

LOG()

LOG10()

MOD()

PI()

POWER()

RAND()

ROUND()

ROUND

SIGN()

SIN()

SINH()

SQRT()

TAN()

TANH()

TRUNC()

Mathematical Functions

String and Binary Functions

ASCII_CHAR()

ASCII_VAL()

BASE64_DECODE()

Example of

BASE64_ENCODE()

Example of

BIT_LENGTH()

BIT_LENGTH

BLOB_APPEND()

BLOB_APPEND

CHAR_LENGTH()

CHAR_LENGTH

CRYPT_HASH()

Examples of

HASH()

Examples of

HEX_DECODE()

Example of

HEX_ENCODE()

Example of

LEFT()

LOWER()

LOWER

LPAD()

LPAD

OCTET_LENGTH()

OCTET_LENGTH

OVERLAY()

OVERLAY

POSITION()

POSITION

REPLACE()

REPLACE

REVERSE()

REVERSE

RIGHT()

RPAD()

RPAD

SUBSTRING()

Positional

Regular Expression

TRIM()

TRIM

UNICODE_CHAR()

UNICODE_VAL()

UPPER()

UPPER

String and Binary Functions

Date and Time Functions

DATEADD()

Examples of

DATEDIFF()

DATEDIFF

EXTRACT()

Returned Data Types and Ranges

MILLISECOND

WEEK

WEEK

FIRST_DAY()

Examples of

LAST_DAY()

Examples of

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

GEN_UUID()

GEN_UUID

UUID_TO_CHAR()

UUID_TO_CHAR

UUID Functions

Functions for Sequences (Generators)

GEN_ID()

GEN_ID

Functions for Sequences (Generators)

Conditional Functions

COALESCE()

COALESCE

DECODE()

DECODE

IIF()

IIF

MAXVALUE()

MAXVALUE

MINVALUE()

MINVALUE

NULLIF()

NULLIF

Conditional Functions

Special Functions for

COMPARE_DECFLOAT()

NORMALIZE_DECFLOAT()

Examples of

QUANTIZE()

Examples of

TOTALORDER()

Special Functions for

Cryptographic Functions

DECRYPT()

DECRYPT

ENCRYPT()

ENCRYPT

RSA_DECRYPT()

RSA_DECRYPT

RSA_ENCRYPT()

RSA_ENCRYPT

RSA_PRIVATE()

RSA_PRIVATE

RSA_PUBLIC()

RSA_PUBLIC

RSA_SIGN_HASH()

RSA_SIGN_HASH

RSA_VERIFY_HASH()

RSA_VERIFY_HASH

Cryptographic Functions

Other Functions

MAKE_DBKEY()

Examples of

RDB$ERROR()

Example of RDB$ERROR

RDB$GET_TRANSACTION_CN()

RDB$GET_TRANSACTION_CN

RDB$ROLE_IN_USE()

RDB$ROLE_IN_USE

RDB$SYSTEM_PRIVILEGE()

RDB$SYSTEM_PRIVILEGE

Other Functions

Aggregate Functions

FILTER

General-purpose Aggregate Functions

AVG()

AVG

COUNT()

COUNT

LIST()

LIST

MAX()

MAX

MIN()

MIN

SUM()

SUM

General-purpose Aggregate Functions

Statistical Aggregate Functions

CORR()

CORR

COVAR_POP()

COVAR_POP

COVAR_SAMP()

COVAR_SAMP

STDDEV_POP()

STDDEV_POP

STDDEV_SAMP()

STDDEV_SAMP

VAR_POP()

VAR_POP

VAR_SAMP()

VAR_SAMP

Statistical Aggregate Functions

Linear Regression Aggregate Functions

REGR_AVGX()

REGR_AVGY()

REGR_COUNT()

REGR_INTERCEPT()

REGR_INTERCEPT

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

DENSE_RANK()

DENSE_RANK

NTILE()

NTILE

PERCENT_RANK()

PERCENT_RANK

RANK()

RANK

ROW_NUMBER()

ROW_NUMBER

Ranking Functions

Navigational Functions

FIRST_VALUE()

LAG()

LAG

LAST_VALUE()

LEAD()

NTH_VALUE()

Navigational Functions

Aggregate Functions Inside Window Specification

System Packages

RDB$BLOB_UTIL

Function

Function

Function

Function

Function

Procedure

Procedure

Examples

RDB$BLOB_UTIL

RDB$PROFILER

Function

Procedure

Procedure

Procedure

Procedure

Procedure

Procedure

Procedure

Example

RDB$PROFILER

RDB$TIME_ZONE_UTIL

Function

Procedure

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

NO WAIT

Isolation Level

SNAPSHOT Sharing Snapshot Transactions

SNAPSHOT TABLE STABILITY

READ COMMITTED

Variants of

NO AUTO UNDO

RESTART REQUESTS

AUTO COMMIT

IGNORE LIMBO

RESERVING

Options for

Lock Resolution Mode

Isolation Level

SNAPSHOT Sharing Snapshot Transactions

SNAPSHOT TABLE STABILITY

READ COMMITTED

Variants of

NO AUTO UNDO

RESTART REQUESTS

AUTO COMMIT

IGNORE LIMBO

RESERVING

Options for

Isolation Level

NO AUTO UNDO

RESTART REQUESTS

AUTO COMMIT

IGNORE LIMBO

RESERVING

Options for

NO AUTO UNDO

RESTART REQUESTS

AUTO COMMIT

IGNORE LIMBO

RESERVING

Options for

RESTART REQUESTS

AUTO COMMIT

IGNORE LIMBO

RESERVING

Options for

AUTO COMMIT

IGNORE LIMBO

RESERVING

Options for

IGNORE LIMBO

RESERVING

Options for

RESERVING

COMMIT

COMMIT

ROLLBACK

ROLLBACK

ROLLBACK TO SAVEPOINT

SAVEPOINT

RELEASE SAVEPOINT

Internal Savepoints

Savepoints and PSQL

Transaction Statements

Security

User Authentication

Specially Privileged Users

POSIX Hosts

The

The

Windows Hosts

The Database Owner Users with the

Specially Privileged Users

RDB$ADMIN

Granting the

Doing the Same Task Using

Using the

Using

Granting the

Using the

AUTO ADMIN MAPPING

Auto Admin Mapping in Regular Databases

Auto Admin Mapping in the Security Database

RDB$ADMIN

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

CREATE USER

ALTER USER

Who Can Alter a User?

ALTER USER

ALTER USER

CREATE OR ALTER USER

CREATE OR ALTER USER

CREATE OR ALTER USER

DROP USER

Who Can Drop a User?

DROP USER

DROP USER

SQL Privileges

The Object Owner

ROLE

CREATE ROLE

Who Can Create a Role

CREATE ROLE

CREATE ROLE

ALTER ROLE

Who Can Alter a Role

ALTER ROLE

ALTER ROLE

DROP ROLE

Who Can Drop a Role

DROP ROLE

DROP ROLE

Statements for Granting Privileges

GRANT

The

Packaging Privileges in a

Cumulative Roles

Default Roles

The User

The

The

Alternative Syntax Using

Privileges on Tables and Views

Examples of

The

Examples of Granting the

The

Examples of Granting the

DDL Privileges

Examples of Granting DDL Privileges

Database DDL Privileges

Examples of Granting Database DDL Privileges

Assigning Roles

The

Examples of Role Assignment

GRANT

Statements for Revoking Privileges

REVOKE

The

The

Revoking the

Removing the Privilege to One or More Roles

Revoking Privileges That Were

Revoking

Examples using

REVOKE

Mapping of Users to Objects

The Mapping Rule

CREATE MAPPING

Who Can Create a Mapping

CREATE MAPPING

CREATE MAPPING

ALTER MAPPING

Who Can Alter a Mapping

ALTER MAPPING

ALTER MAPPING

CREATE OR ALTER MAPPING

CREATE OR ALTER MAPPING

CREATE OR ALTER MAPPING

DROP MAPPING

Who Can Drop a Mapping

DROP MAPPING

DROP MAPPING

Database Encryption

Encrypting a Database

Decrypting a Database

SQL Security

Management Statements

Data Type Behaviour

SET BIND

SET BIND

SET DECFLOAT

SET DECFLOAT ROUND

SET DECFLOAT TRAPS

Data Type Behaviour

Connections Pool Management

ALTER EXTERNAL CONNECTIONS POOL

Clauses of

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

SET TRUSTED ROLE

SET TRUSTED ROLE

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

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

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

RDB$TRIGGERS

RDB$TRIGGER_MESSAGES

RDB$TYPES

RDB$USER_PRIVILEGES

RDB$VIEW_RELATIONS

Monitoring Tables

MON$ATTACHMENTS

Using

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

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

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 Release Candidate 2 Release Notes

General Notes

Compatibility with Older Versions

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

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

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

Support for

Support multiple rows for DML RETURNING

Allow parenthesized query expressions

Support

Support

OPTIMIZE FOR

Changes to literals

Full SQL standard character string literal syntax

Full SQL standard binary string literal syntax

Changes to literals

Improvements to

New Expressions and Built-in Functions

UNICODE_CHAR

QUARTER

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

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

Bugs Fixed

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

Firebird Database Cache Buffer

Practical Migration Guide to Firebird 5.0