SQL Language Enhancements

Table of Contents

Changes affecting All SQL
Data Definition Language (DDL)
Data Manipulation Language (DML)
Stored Procedure and Trigger Language (PSQL)
New Reserved Words

A large number of additions and enhancements were made to the various subsets of Firebird's SQL language.

Changes affecting All SQL

(1.5) Enhancement to single-line comment marker
(1.0) CURRENT_USER and CURRENT_ROLE

(1.5) Enhancement to single-line comment marker

Dmitry Yemanov

Single-line comments can be in any position in the line, not just the first. For use in scripts, DSQL, stored procedures and triggers.

So, in 1.5, the "--" marker can be used for a comment at the end of a line statement in a script, stored procedure, trigger or DSQL statement. It can thus be used to "comment out" unwanted parts of statements. All characters from the "--" marker until the next carriage return or line feed will be ignored.

  ...
  WHERE COL1 = 9 OR COL2 = 99 -- OR COL3 = 999

Previous implementation (v.1.0, Claudio Valderrama)

  -- This is a comment

This new marker can be used for "commenting out" a single line of code in a script, DDL/DML statement, stored procedure or trigger. The logic is to ignore characters is as follows:

Skip '--' if it is found as the first character pair following an end-of-line marker (LF on Linux/Unix, CRLF on Windows)
Continue skipping characters until the next end-of-line marker

This logic is NOT intended for mixing with the block comment logic ( /* a comment */ ). In other words, don't use the '--' style of commenting within a block comment and don't use the block-style of commenting within a '--' line.

Interactive isql sessions

Keep this in mind when working in an interactive isql session. isql will accept pieces of a statement in separate continuation segments, displaying the 'CON>' prompt until it receives the terminator symbol (normally ';'). If you type a '--' pair at the start of a continuation line, the ignoring logic will finish at the end-of-line marker that is printed to the screen or your OUTPUT file when you press Enter.

There is potential for errors if you subsequently add a continuation, expecting it to be ignored.

The problem with isql arises because it has its own special commands that should be parsed only by isql. If they are not recognized due to tricky placement of "--", then they are passed to the engine. Obviously, the engine doesn't understand isql's SET and SHOW commands and rejects them.

(1.0) CURRENT_USER and CURRENT_ROLE

These two new context variables have been added to reference the USER and (if implemented1) the ROLE of the current connection context.

Examples

  CREATE GENERATOR GEN_USER_LOG;
  CREATE DOMAIN INT_64 AS NUMERIC(18,0);
  COMMIT;
  CREATE TABLE USER_LOG(
    LOG_ID  INT_64 PRIMARY KEY NOT NULL,
    OP_TIMESTAMP TIMESTAMP,
    LOG_TABLE VARCHAR(31),
    LOG_TABLE_ID INT_64,
    LOG_OP CHAR(1),
    LOG_USER VARCHAR(8),
    LOG_ROLE VARCHAR(31));

  COMMIT;

  CREATE TRIGGER ATABLE_AI FOR ATABLE
  ACTIVE AFTER INSERT POSITION O AS
  BEGIN
    INSERT INTO USER_LOG VALUES(
      GEN_ID(GEN_USER_LOG, 1),
      CURRENT_TIMESTAMP,
      'ATABLE',
      NEW.ID,
      'I',
      CURRENT_USER,
      CURRENT_ROLE);
  END

CURRENT_USER is a DSQL synonym for USER that appears in the SQL standard. They are identical. There is no advantage of CURRENT_USER over USER.

If you insist on using an InterBase v.4.x or 5.1 database with Firebird, ROLE is not supported, so current_role will be NONE (as mandated by the SQL standard in absence of an explicit role) even if the user passed a role name.
If you use IB 5.5, IB 6 or Firebird, the ROLE passed is verified. If the role does not exist, it is reset to NONE without returning an error.

This means that in FB you can never get an invalid ROLE returned by CURRENT_ROLE, because it will be reset to NONE. This is in contrast with IB, where the bogus value is carried internally, although it is not visible to SQL.