FirebirdSQL logo

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.

Script Handling

A batch of DDL and/or DML statements in a text file is known as a script.Scripts can be used to create and alter database objects.These are referred to as Data Definition Language (DDL) scripts.Scripts that manipulate data by selecting, inserting, updating, deleting or performing data conversions, are called Data Manipulation Language (DML) scripts.

One of the most important tasks handled by isql is to process scripts.It can handle both DDL and DML Scripts, but they should be included in separate scripts to avoid data integrity problems.This script processing feature of isql allows the linking of one script to another using the isql command INPUT <filespec>.Scripts statements are executed in order that they appear in the script file.The default setting in isql for AUTODDL is set to ON.You may use the [isql-set-autoddl] command to control where or when DDL statements will be committed.

Note

The AUTODDL setting only affects DDL statements.It doesn’t commit DML statements.If you mix DDL and DML statements within the same interactive session, then the AUTODDL commits do not commit your DML changes.For example:

SQL> set autoddl on;

SQL> insert into test(a) values (666);
SQL> commit;

SQL> select * from test;

           A
============
         666

SQL> insert into test(a) values (999);
SQL> select * from test;

           A
============
         666
         999

SQL> create table another_test(b integer);
SQL> rollback;

SQL> select * from test;

           A
============
         666

Scripts can redirect their output to a log file using the OUTPUT file_name command.This can be entered directly at the isql prompt, or as part of a script file itself.

Isql Commands

Isql commands affect the running of isql itself and do not affect the database or data in any way.These commands are used to display help, run scripts, create listings and so on.You can easily see a list of the available commands by typing the help command which will produce the following output:

SQL> help;
Frontend commands:
BLOBDUMP <blobid> <file>   -- dump BLOB to a file
BLOBVIEW <blobid>          -- view BLOB in text editor
EDIT     [<filename>]      -- edit SQL script file and execute
EDIT                       -- edit current command buffer and execute
HELP                       -- display this menu
INput    <filename>        -- take input from the named SQL file
OUTput   [<filename>]      -- write output to named file
OUTput                     -- return output to stdout
SET      <option>          -- (Use HELP SET for complete list)
SHELL    <command>         -- execute Operating System command in sub-shell
SHOW     <object> [<name>] -- display system information
    <object> = CHECK, COLLATION, DATABASE, DOMAIN, EXCEPTION, FILTER, FUNCTION,
               GENERATOR, GRANT, INDEX, PACKAGE, PROCEDURE, ROLE, SQL DIALECT,
               SYSTEM, TABLE, TRIGGER, VERSION, USERS, VIEW
EXIT                       -- exit and commit changes
QUIT                       -- exit and roll back changes

All commands may be abbreviated to letters in CAPitals

Each of these commands will be discussed in the following sections.Note the last line of output from the help command.It explains that each of the commands may be abbreviated to just those letters displayed in capital letters.In the following discussion, the optional characters will be displays, as above, in lower case letters.For example, the input command will be shown as INput to indicate that the characters 'put' are optional.