Firebird SQL Dialect in LibreOffice Base

This article provides an overview of the SQL dialect used by the embedded Firebird database engine in LibreOffice Base. It covers the specific dialect version, its adherence to standard SQL, key syntax characteristics, and how to properly execute native commands within the LibreOffice Base interface.

The Native SQL Dialect: Firebird Dialect 3

The internal embedded database engine in LibreOffice Base utilizes Firebird SQL Dialect 3.

Historically, the Firebird relational database management system supports two primary SQL dialects to maintain backward compatibility: * Dialect 1: A legacy mode designed for compatibility with older InterBase 5.x databases, utilizing older data type conventions and standard date handling. * Dialect 3: The modern, native standard for Firebird databases (specifically Firebird 3.0.x embedded in modern LibreOffice releases).

LibreOffice Base initializes and operates its embedded Firebird databases exclusively in Dialect 3 to take advantage of contemporary relational database standards.

Core Features of Firebird Dialect 3

Firebird Dialect 3 closely aligns with ANSI/ISO SQL standards (specifically SQL:2003 and SQL:2008 specifications). Key characteristics include:

• Delimited Identifiers: Object names (tables, views, columns) that are case-sensitive or contain special characters must be enclosed in double quotes (e.g., "CustomerID", "Order Date").
• String Literals: String values must be enclosed in single quotation marks (e.g., 'Active'). Double quotes cannot be used for string literals.
• Data Types: Full support for modern SQL data types, including:
  • BIGINT (64-bit integer)
  • BOOLEAN (native TRUE, FALSE, UNKNOWN)
  • TIMESTAMP, DATE, and TIME (strict separation and storage precision)
  • NUMERIC and DECIMAL with up to 18-digit precision stored internally as 64-bit integers
  • BLOB (Binary Large Objects for files, images, or large text blocks)
• Paging and Limiting Results: Supports both standard SQL windowing syntax and Firebird-specific syntax:
  • Standard: OFFSET 10 ROWS FETCH FIRST 10 ROWS ONLY
  • Firebird-specific: ROWS 1 TO 10 or FIRST 10 SKIP 0

Executing Native SQL in LibreOffice Base

By default, LibreOffice Base parses SQL statements through its internal GUI parser before passing them to the Firebird engine. To execute advanced native Firebird features—such as Data Definition Language (DDL) scripts, stored procedures, or complex triggers:

1. Open the SQL Query editor or go to Tools > SQL… in LibreOffice Base.
2. Enable the Run SQL command directly option (represented by the “SQL” icon in the Query Design view).
3. This disables the LibreOffice graphical parser and transmits the SQL statement directly to the embedded Firebird engine in its native Dialect 3 syntax.