FirebirdSQL logo

SQL Dialects

Firebird supports three SQL dialects in each client and database server.These SQL dialects are differentiated in the context of the date-time format, and the precision of a numerical data type.The dialects serve to instruct the Firebird server on how to process features implemented in legacy Borland Interbase databases, earlier than version 6.0.Dialects are set up at runtime and can be changed for the client at connection time or with a SET SQL DIALECT command.

Note

Dialect 2 is only used when converting a dialect 1 database to a dialect 3 database.

The following table illustrates the differences between the dialects.

Table 1. SQL Dialects
SQL Dialect 1 Dialect 2 Dialect 3

Date

Date & Time (Timestamp)

ERROR Message

Date only

Time Stamp

Timestamp (v.6.x only)

Timestamp

Timestamp

Time

Error message

Error message

Time only

<"quoted item">

String

Error message

Symbol only

Precision: 1/3 =

0.3333333…​ (double precision)

0

0

Numeric 11

double precision

64 bit int

64 bit int

Note

Currently, it is possible to create databases in Dialect 1 and 3 only, however it is recommended that you use Dialect 3 exclusively, since Dialect 1 is deprecated.Dialect 2 cannot be used to create a database since it only serves to convert Dialect 1 to Dialect 3.

When connecting to a database using isql, the utility takes on the dialect of the database, unless you specify otherwise.Dialects cannot be set as a parameter of a CREATE DATABASE statement.So, when creating a database using isql, the database will be in the dialect that is current in isql at the time the CREATE DATABASE statement is issued.You may set the dialect using the isql utility in two ways:

  • When you start isql type:

    isql -sql_dialect n

    (where n refers to the dialect number)`

  • Within a SQL script or isql session, type:

    SQL> SET SQL DIALECT n;
    Note

    Prior to Firebird 2.0 when isql disconnected from a database, either by dropping it or by trying to connect to a non-existent database, it remembered the SQL dialect of the previous connection, which lead to some inappropriate warning messages.This has been fixed in 2.0.

Terminator Character

The default terminator symbol in isql is the semicolon (‘;’).Statements will only be executed if they end with a semicolon.However, you can configure isql to use a different symbol — any printable character, or characters, from the first 127 characters of the ASCII subset — by using the [isql-set-term] command.

Note

The default terminator maybe changed in all instances except in the case of Procedural SQL or PSQL.PSQL does not accept any terminator other than a semicolon.

To change the terminator character to a tilde (‘~’) enter the following code:

SQL> SET TERM ~ ;

You must terminate this command with the current terminator!Changing the terminator is useful if you wish to type in a PSQL function as the following example shows.Because PSQL will only accept the semicolon as a terminator, isql needs to know which semicolon is being used for the PSQL code and which is being used to terminate the SQL commands being entered.

SQL> set term ~ ;

SQL> create procedure test_proc (iInput integer = 666)
CON> returns (oOutput integer)
CON> as
CON> begin
CON>   oOutput = iInput;
CON>   suspend;
CON> end~

SQL> set term ; ~

SQL> commit;

SQL> select * from test_proc;

     OOUTPUT
============
         666

You can see that within the code for the procedure itself, the terminator is the semicolon.However, outside the actual procedure code, the terminator is the tilde (‘~’).Isql is processing a single CREATE PROCEDURE command, but within that one SQL statement, there are multiple embedded PSQL statements:

oOutput = iInput;
suspend;

These have the semicolon terminator, as required by PSQL.The end of the CREATE PROCEDURE command is indicated by the use of the tilde as the terminator:

end~

You can, if desired, change the terminator because you prefer something other than a semicolon.You don’t have to be writing PSQL code to change it.

SQL> -- Change terminator from ; to +
SQL> set term + ;

SQL> select count(*) from employee+

       COUNT
============
          42

SQL> -- Change terminator from + to 'fred'
SQL> set term fred +

SQL> select count(*) from employee fred

       COUNT
============
          42

SQL> -- Change back from 'fred' to ;
SQL> set term ; fred

However, you must be careful not to pick a terminator character that will cause SQL statements to fail due to the terminator being used at some point within the SQL statement.

SQL> select 600+60+6 as The_Beast from rdb$database;

            THE_BEAST
=====================
                  666


SQL> set term + ;
SQL> select 600+60+6 as The_Beast from rdb$database+

Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Unexpected end of command - line 1, column 8
...

SQL> set term ; +

The presence of the terminator within an expression has caused the "unexpected end of command" error.The SQL parser within the Firebird database engine has determined that "select 600" is not a valid statement.For this reason, it is best to always choose a character, or characters, that will not confuse the parser.

SQL> set term #;

SQL> select 600+60+6 as The_Beast from rdb$database#

            THE_BEAST
=====================
                  666

Isql Prompts

The SQL> prompt

As shown above, the normal isql prompt for input is the SQL> prompt.This indicates that the previous command has been completed and isql is now waiting for a new command to process.

The CON> prompt

The CON> or Continuation prompt is displayed if users press kbd:[Enter] without ending a SQL statement with a terminator.For example:

SQL> HELP
CON>

Whenever you see the CON> prompt, you may either continue entering the remainder of the statement or command, or enter a terminator to terminate the statement.When you press kbd:[Enter], the statement will be executed in the latter case.

Error Handling And Exception Support

Exception handling is a programming construct designed to handle an occurrence that disrupts the normal execution of a program.These are called errors.Exceptions are user-defined named error messages, written specifically for a database and stored in that database for use in stored procedures and triggers.

For example, if it is ascertained in a trigger that the value in a table is incorrect, the exception is fired.This leads to a rollback of the total transaction that the client application is attempting to commit.Exceptions can be interleaved, and shared among the different modules of an application, and even among different applications sharing a database.They provide a simple way to standardize the handling of preprogrammed input errors.

Exceptions are database objects, like tables, views and domains, and are part of the database’s metadata.They can be created, modified and dropped like all other Firebird objects using isql.

In isql, error messages comprise the SQLCODE variable and the Firebird status array.The following table provides some examples:

Table 1. ISQL Error Codes and Messages
SQLCODE Message Meaning

<0

SQLERROR

Error occurred: statement did not execute

0

SUCCESS

Successful execution

+1 to +99

SQLWARNING

System warning or information message

+100

NOT FOUND

No qualifying rows found, or end of current active set of rows reached

Transaction Handling

The Firebird architecture allows high transaction concurrency.Transaction save points (nested transactions) are also supported.All Firebird transactions are ACID compliant.ACID is explained below:

Atomicity

ensures that transactions either complete in their entirety or not at all, even if the system fails halfway through the process.

Consistency

ensures that only valid data will be written to the database.If a transaction is executed that violates the database’s consistency rules, the entire transaction will be rolled back and the database will be restored to a state consistent with those rules.If a transaction successfully executes, it will take the database from one state that is consistent with the rules to another state that is also consistent with the rules, without necessarily preserving consistency at all intermediate levels.

Isolation

ensures that transactions are isolated from one another, even if several transactions are running concurrently.Concurrency refers to a state within the database where two or more tasks are running simultaneously.This way, a transaction’s updates are concealed from the rest until that transaction commits.Transactions in Firebird are isolated within separate contexts defined by client applications passing transaction parameters.

Durability

ensures that once a transaction commits, its changes survive within the database, even if there is a subsequent system crash.

There are several parameters available to configure transactions to ensure consistency within the database.These parameters invoke the concept of concurrency.To ensure data integrity, there are four configurable parameters affecting concurrency: isolation level, lock resolution mode, access mode, and table reservation.

  • Isolation Level: A transaction isolation level defines the interaction and visibility of work performed by simultaneously running transactions.There are four transaction isolation levels according to the SQL standard:

    READ UNCOMMITTED

    A transaction sees changes done by uncommitted transactions.

    READ COMMITTED

    A transaction sees only data committed before the statement has been executed.

    REPEATABLE READ

    A transaction sees during its lifetime only data committed before the transaction has been started.

    SERIALIZABLE

    This is the strictest isolation level, which enforces transaction serialization.Data accessed in the context of a serializable transaction cannot be accessed by any other transaction.

In isql, a transaction is begun as soon as the utility is started.The transaction is begun in SNAPSHOT isolation, with a lock resolution set to WAIT.Since the Firebird isql utility accepts DDL, DML and other commands, transactions are handled accordingly, in the following ways:

  • DDL statements are committed when issued at the SQL prompt in two ways:

    • Automatically, if SET AUTODDL is on, which is the default, and if the statement completed successfully.To turn it off, issue a SET AUTODDL OFF statement at the isql prompt, or on the commandline with -⁠n(oautocommit).

    • When COMMIT statements are included in the script.

  • DML statements are never committed automatically.You must issue a COMMIT statement to commit any DML changes to the database.

  • You can use various SHOW commands in isql to query database metadata.Metadata is stored in system tables.When a SHOW command is issued, it operates in a separate transaction from user statements.They run as READ COMMITTED background statements and show all committed metadata changes immediately.

Users can specify the access mode and level of isolation for the next transaction, and explicitly commit the current transaction by using the SET TRANSACTION statement.Executing SET TRANSACTION starts a new transaction.

Here is an example:

-- Use Firebird defaults
SQL> SET TRANSACTION;
-- Use read committed
SQL> SET TRANSACTION READ COMMITTED;

If a transaction is already active when you run SET TRANSACTION, isql will prompt if you want to commit the current transaction.Choosing ‘y’ will commit the current transaction, while ‘n’ will roll back the current transaction.

If the [isql-set-keep-tran-params] option is on isql will remember the last SET TRANSACTION statement executed explicitly, and use it when it needs to implicitly start a transaction.

Note

The set transaction statement is not shown in isql's SET HELP because it is a Firebird statement, and not an isql command, consult the Language Reference for your version for the syntax and further details.