FirebirdSQL logo
  • Anglaiskeyboard_arrow_down

SET EXPLAIN

Added in

Firebird 3.0

SET EXPLAIN [ON | OFF]

This command extends the [isql-set-plan] option to report the explained plan instead of the standard one.

If plan output is not currently enabled, then set explain on turns the plan output on.

SQL> set explain on;

SQL> select emp_no, first_name, last_name
CON> from employee
CON> where emp_no = 107;

Select Expression
    -> Filter
        -> Table "EMPLOYEE" Access By ID
            -> Bitmap
                -> Index "RDB$PRIMARY7" Unique Scan

 EMP_NO FIRST_NAME      LAST_NAME
======= =============== ====================
    107 Kevin           Cook
See also

[isql-set-plan], [isql-set-planonly]

Isql Show commands

As explained in [isql-commands], there are a number of individual show commands within isql.The general format of the show commands is:

SHOW [<object> [name]]

The object is always required, and the name is required to display details of a specific object.Without a name, the commands will usually display all objects of the requested type.

Unfortunately, unlike the set commands, there is no handy drill down into the various show commands using the help command.However, if you execute show on its own, you will be given a little more assistance.For example, isql of Firebird 4.0 shows:

SQL> show;
Valid options are:
CHECKs                   COMMENTs                 COLLATEs
COLLATIONs               DOMAINs                  DB
DATABASE                 DEPENdency               DEPENdencies
EXCEPtions               FILTERs                  FUNCtions
GENerators               GRANTs                   INDexes
INDICES                  MAPping                  PROCedures
PACKages                 PUBlications             ROLEs
SYStem                   SEQuences                SECURITY CLAsses
SECCLAsses               SCHEmas                  TABLEs
TRIGgers                 USERS                    VIEWs
Command error: show

The upper case letters indicate what you must type as an absolute minimum.

The show commands are detailed and described below.Where possible, examples from the employee database are shown.

SHOW CHECKs

SHOW CHECKs table_name

This command displays all user-defined check constraints defined for a specific table.Unlike other show commands, there is no option to display a list of all check constraints in the database.You must always provide a table name as part of the command.

SQL> show check employee;
CONSTRAINT INTEG_30:
  CHECK ( salary >= (SELECT min_salary FROM job WHERE
                        job.job_code = employee.job_code AND
                        job.job_grade = employee.job_grade AND
                        job.job_country = employee.job_country) AND
            salary <= (SELECT max_salary FROM job WHERE
                        job.job_code = employee.job_code AND
                        job.job_grade = employee.job_grade AND
                        job.job_country = employee.job_country))

SHOW GENerators

SHOW {GENerators | SEQuences} [name]

SHOW GENerators and [isql-show-sequences] are identical.Generators are the old Firebird term for what are more commonly known as sequences in the SQL standard and other databases.You are encouraged to use the term sequence rather than generator, but isql considers them aliases.

The first form of the commands above list all user-defined sequences in the current database, while the second form displays details of a specific sequence.

SQL> show sequences;
Generator CUST_NO_GEN, current value: 1015, initial value: 1, increment: 1
Generator EMP_NO_GEN, current value: 145, initial value: 1, increment: 1

SQL> show sequence emp_no_gen;
Generator EMP_NO_GEN, current value: 145, initial value: 1, increment: 1

SHOW GRANTs

SHOW GRANTs [{object_name | role_name}]

This command displays a list of all grants in the current database if the first form of the command is used.The second form drills down and displays only those details for the selected object, which may be a table, procedure, etc.Alternatively, if a role name is provided, only a list of users who have been granted that role will be displayed.

SQL> show grants;

/* Grant permissions for this database */
GRANT ALL ON COUNTRY TO PUBLIC WITH GRANT OPTION
GRANT ALL ON CUSTOMER TO PUBLIC WITH GRANT OPTION
...
GRANT EXECUTE ON PROCEDURE ADD_EMP_PROJ TO PUBLIC WITH GRANT OPTION
GRANT EXECUTE ON PROCEDURE ALL_LANGS TO PUBLIC WITH GRANT OPTION
...

SQL> show grants employee;
GRANT ALL ON EMPLOYEE TO PUBLIC WITH GRANT OPTION

SQL> show grants ship_order;
GRANT EXECUTE ON PROCEDURE SHIP_ORDER TO PUBLIC WITH GRANT OPTION

SQL> show grants default_user;
GRANT DEFAULT_USER TO SYSDBA

SHOW INDexes or SHOW INDICES

SHOW {INDexes | INDICES} [{table_name | index_name}]

You may specify either indexes (or abbreviated forms starting with ind) or indices, they are treated as aliases by isql.The first form of this command lists all user-defined indices in the current database.The second form of the command lists the indices of a specific table as determined by the table_name parameter.The final form of the command displays details of a specific index.

SQL> show indices;
RDB$PRIMARY1 UNIQUE INDEX ON COUNTRY(COUNTRY)
CUSTNAMEX INDEX ON CUSTOMER(CUSTOMER)
...
SALESTATX INDEX ON SALES(ORDER_STATUS, PAID)

SQL> show indices employee;
NAMEX INDEX ON EMPLOYEE(LAST_NAME, FIRST_NAME)
RDB$FOREIGN8 INDEX ON EMPLOYEE(DEPT_NO)
RDB$FOREIGN9 INDEX ON EMPLOYEE(JOB_CODE, JOB_GRADE, JOB_COUNTRY)
RDB$PRIMARY7 UNIQUE INDEX ON EMPLOYEE(EMP_NO)

SQL> show index namex;
NAMEX INDEX ON EMPLOYEE(LAST_NAME, FIRST_NAME)

SHOW MAPping

Added in

Firebird 3.0

SHOW MAPping [name]

This command shows the authentication mapping rules defined in this database or in the security database.

SQL> show mapping;

WIN_ADMINS USING PLUGIN WIN_SSPI FROM PREDEFINED_GROUP DOMAIN_ANY_RID_ADMINS TO ROLE RDB$ADMIN

*** Global mapping ***
WIN_ADMINS_GLOBAL USING PLUGIN WIN_SSPI FROM PREDEFINED_GROUP DOMAIN_ANY_RID_ADMINS TO ROLE RDB$ADMIN

SQL> show mapping WIN_ADMINS;

WIN_ADMINS USING PLUGIN WIN_SSPI FROM PREDEFINED_GROUP DOMAIN_ANY_RID_ADMINS TO ROLE RDB$ADMIN

SHOW PROCedures

SHOW PROCedures [name]

This command lists all user-defined procedures created in the current database.The second form of the command shows the details and source code for a specific procedure.See also the [isql-show-functions] and [isql-show-triggers] commands.

SQL> show procedures;
ADD_EMP_PROJ; Dependencies: EMPLOYEE_PROJECT (Table), UNKNOWN_EMP_ID (Exception)
ALL_LANGS; Dependencies: JOB (Table), SHOW_LANGS (Procedure)

SQL> show procedure all_langs;
Procedure text:
=============================================================================
BEGIN
        FOR SELECT job_code, job_grade, job_country FROM job
                INTO :code, :grade, :country

        DO
        BEGIN
            FOR SELECT languages FROM show_langs
                    (:code, :grade, :country) INTO :lang DO
                SUSPEND;
            /* Put nice separators between rows */
            code = '=====';
            grade = '=====';
            country = '===============';
            lang = '==============';
            SUSPEND;
        END
    END
=============================================================================
Parameters:
CODE                              OUTPUT VARCHAR(5)
GRADE                             OUTPUT VARCHAR(5)
COUNTRY                           OUTPUT VARCHAR(15)
LANG                              OUTPUT VARCHAR(15)

Since Firebird 5.0, isql lists procedures and packaged procedures in the same way, where the latter are displayed in the form <package-name>.<procedure-name>.Isql of Firebird 3.0 and 4.0 displayed procedures and packaged procedures differently.

SHOW PACKages

Added in

Firebird 3.0

SHOW PACKages [name]

This command lists all user-defined packages in the current database if the first form is used, or drills down to display the definition of the package.

SQL> show packages;
TEST

SQL> show package test;
TEST
Header source:
BEGIN
  PROCEDURE P1(I INT) RETURNS (O INT); -- public procedure
END

Body source:
BEGIN
  FUNCTION F1(I INT) RETURNS INT; -- private function
  PROCEDURE P1(I INT) RETURNS (O INT)
  AS
  BEGIN
  END
  FUNCTION F1(I INT) RETURNS INT
  AS
  BEGIN
    RETURN 0;
  END
END

SHOW PUBlications

Added in

Firebird 5.0

SHOW PUBlications [name]

This command lists all user-defined publications in the current database if the first form is used, or drills down to display the definition of the publication.

Currently, Firebird only has one system-defined publication, RDB$DEFAULT, and no option to create user-defined publications, so the output of show publications is always the same:

SQL> show publications;
There is no publications in this database

SQL> show system publications;
RDB$DEFAULT

SQL> show publication rdb$default;
RDB$DEFAULT: Disabled

SHOW ROLEs

SHOW ROLEs [name]

This command lists all roles in the current database if the first form is used, or drills down to display a list of all users who have been granted a specific role if the second form of the command is used.

SQL> show roles;
DEFAULT_USER

SQL> show role default_user;
Role DEFAULT_USER is granted to:
SYSDBA

SHOW SECCLAsses

SHOW SECCLAsses object_name

This command displays details about the security classes for a given object.The object_name passed to the command need not be a table name, the command works for tables, procedures, etc.

SQL> show secclasses employee;
Table's main sec class SQL$466
Table's default sec class SQL$DEFAULT58

SHOW SECURITY CLAsses

Although this command is listed in the output of SHOW;, it is not actually available.Use [isql-show-secclasses].

SHOW COLLATIONs or SHOW COLLATEs

Added in

Firebird 2.0

SHOW {COLLATIONs | COLLATEs} [name]

These commands display a list of all user-defined collations in the current database.The first form of the commands display a list of all collations while a specific collation may be displayed by providing the collation name.

SQL> show collations;
UNICODE_ENUS_CI, CHARACTER SET UTF8, FROM EXTERNAL ('UNICODE'), PAD SPACE,
CASE INSENSITIVE, 'COLL-VERSION=58.0.6.48'
UNICODE_ENUS_CS, CHARACTER SET UTF8, FROM EXTERNAL ('UNICODE'), PAD SPACE,
'COLL-VERSION=58.0.6.48'

SQL> show collation unicode_enus_ci;
UNICODE_ENUS_CI, CHARACTER SET UTF8, FROM EXTERNAL ('UNICODE'), PAD SPACE,
CASE INSENSITIVE, 'COLL-VERSION=58.0.6.48'

You can see from the output above, which is not part of the employee database, does appear to display all relevant information in the first form of the command.There does not appear to be much reason to drill down into a specific collation — at least, not for to this example.Some lines in the above have had to be split over two to allow it to fit on the page.

SHOW SEQuences

The Firebird specific name, generator, has been updated to match the SQL standard term sequence.The show sequences command is identical to [isql-show-generators] (above) and the output is identical.

SHOW SQL DIALECT

SHOW SQL DIALECT

This command shows the dialect of the current database as well as the dialect of the current connection.

SQL> show SQL Dialect;
        Client SQL dialect is set to: 3 and database SQL dialect is: 3

SHOW SYStem

SHOW SYStem [<object_type>]

<object_type> :: =
  { TABLEs | COLLATIONs | COLLATEs | ROLEs
  | FUNCtions | PROCedures | PACKages | PUBLications }

This command lists the internal, i.e. system, objects created and used in the current database.The optional parameter restricts the listing to show only the specified object type.This applies from Firebird 2.0 onwards.Prior to version 2.0, the command would only list the system tables — equivalent to the show system tables command.

If no parameter is passed, the listing will display tables, functions (not built-in functions though), collations, roles, procedures, packages, and publications.

Types PROCedures, PACKages and PUBLications are available since Firebird 5.0.

SQL> show system;
Tables:
MON$ATTACHMENTS
...

Functions:
RDB$BLOB_UTIL.IS_WRITABLE
...

Procedures:
RDB$BLOB_UTIL.CANCEL_BLOB
...

Packages:
RDB$BLOB_UTIL
...

Collations:
ASCII
...

Roles:
RDB$ADMIN

Publications:
RDB$DEFAULT

Note that packaged procedures and functions are listed as <package-name>.<routine-name>.

The show system <object_type> can sometimes show more details compared to just show system:

SQL> show system collations;
ASCII, CHARACTER SET ASCII, PAD SPACE, SYSTEM
...
WIN_PTBR, CHARACTER SET WIN1252, PAD SPACE, CASE INSENSITIVE, ACCENT INSENSITIVE, SYSTEM

If you wish to drill down and display details of a specific object, use the corresponding show <object-type> name command, without system.

SQL> show table mon$io_stats;
MON$STAT_ID                     (RDB$STAT_ID) INTEGER Nullable
MON$STAT_GROUP                  (RDB$STAT_GROUP) SMALLINT Nullable
MON$PAGE_READS                  (RDB$COUNTER) BIGINT Nullable
MON$PAGE_WRITES                 (RDB$COUNTER) BIGINT Nullable
MON$PAGE_FETCHES                (RDB$COUNTER) BIGINT Nullable
MON$PAGE_MARKS                  (RDB$COUNTER) BIGINT Nullable

SQL> show collation ascii;
ASCII, CHARACTER SET ASCII, PAD SPACE, SYSTEM

SHOW SCHEmas

Added in

Firebird 3.0

SHOW SCHEmas

This command always returns an error.

SQL> show schemas;
Command error: show schemas

SHOW TABLEs

SHOW TABLEs [name]

This command lists all user-defined tables in the database if the first form of the command is used, or displays the columns and data types or domains of the table if the second form is used.

SQL> show tables;
COUNTRY
CUSTOMER
...

SQL> show table country;
COUNTRY                         (COUNTRYNAME) VARCHAR(15) Not Null
CURRENCY                        VARCHAR(10) Not Null
CONSTRAINT INTEG_2:
  Primary key (COUNTRY)

You will note that if there are comments defined for a table, this command will not display them.You must use the [isql-show-comments] command, but be aware that you will then be given all comments in the database.There is no command to extract the comments for a single object, unless you query the system tables directly.

SQL> comment on table country is 'This table holds details about countries.';
SQL> commit;

SQL> show comments;
...
COMMENT ON TABLE COUNTRY IS This table holds details about countries.;
...

SQL> show table country;
COUNTRY                         (COUNTRYNAME) VARCHAR(15) Not Null
CURRENCY                        VARCHAR(10) Not Null
CONSTRAINT INTEG_2:
  Primary key (COUNTRY)

SQL> select rdb$description
CON> from rdb$relations
CON> where rdb$relation_name = 'COUNTRY';

  RDB$DESCRIPTION
=================
            6:1e7
==============================================================================
RDB$DESCRIPTION:
This is a table holding details about countries.
==============================================================================

The output from the final query above is not ideal, but at least it’s much less displayed information when there are lots of comments in your database.

SHOW TRIGgers

SHOW TRIGgers [name]

This command lists all user-defined triggers in the current database.The second form of the command shows the details and source code for a specific trigger.See also the [isql-show-procedures] and [isql-show-functions] commands.

SQL> show triggers;
SET_CUST_NO; Table: CUSTOMER
SAVE_SALARY_CHANGE; Table: EMPLOYEE
SET_EMP_NO; Table: EMPLOYEE
POST_NEW_ORDER; Table: SALES

SQL> show trigger set_cust_no;

Triggers on Table CUSTOMER:
SET_CUST_NO, Sequence: 0, Type: BEFORE INSERT, Active
Trigger text:
=============================================================================
AS
BEGIN
    if (new.cust_no is null) then
    new.cust_no = gen_id(cust_no_gen, 1);
END
=============================================================================

SHOW VERsion

SHOW VERsion

This command displays details about the Firebird software, your database and the on-disk structure (ODS) in use.

SQL> show version;
ISQL Version: WI-V5.0.0.1306 Firebird 5.0
Server version:
Firebird/Windows/AMD/Intel/x64 (access method), version "WI-V5.0.0.1306 Firebird 5.0"
on disk structure version 13.1

Depending on the connection protocol, the output may include multiple version lines.The example above is an embedded connection, so only the database engine information is shown.

SHOW USERS

SHOW USERS

This command shows a list of users in the security database, and the number of connections they have to the current database.

Note

Older versions of isql show only connected users, and will report each connection separately.

 
SQL> show users;
Users in the database
  1  NORMAN
  8 #SYSDBA

Only users with administrator privileges will be able to see information on other users.

SHOW VIEWs

SHOW VIEWs [name]

The first form of this command displays a list of all views in the current database.Drilling down using the second form of the command will display the columns and source code for a specific view.

SQL> show views;
PHONE_LIST

SQL> show view phone_list;
EMP_NO                          (EMPNO) SMALLINT Not Null
FIRST_NAME                      (FIRSTNAME) VARCHAR(15) Not Null
LAST_NAME                       (LASTNAME) VARCHAR(20) Not Null
PHONE_EXT                       VARCHAR(4) Nullable
LOCATION                        VARCHAR(15) Nullable
PHONE_NO                        (PHONENUMBER) VARCHAR(20) Nullable
View Source:
==== ======
SELECT
    emp_no, first_name, last_name, phone_ext, location, phone_no
    FROM employee, department
    WHERE employee.dept_no = department.dept_no

SHOW COMMENTs

SHOW COMMENTs

This command displays all comments that have been created, on various objects, in the current database.There is no option to display a specific comment.Each comment is listed along with the object type and name, to which it has been applied.

SQL> show comments;
COMMENT ON DATABASE IS This is the demonstration EMPLOYEE database.;
COMMENT ON TABLE EMPLOYEE IS The EMPLOYEE table has details of our employees.;

The actual comment text is shown between the word 'IS' and the trailing semicolon.

SHOW DATABASE or SHOW DB

SHOW {DATABASE | DB}

The show database (or show db) command displays details about the current database.The ODS version, shown in the following examples, is only displayed from Firebird version 2.0 onwards, and other options depend on the type of connection and the Firebird version.

SQL> show database;
Database: employee
        Owner: SYSDBA
PAGE_SIZE 8192
Number of DB pages allocated = 346
Number of DB pages used = 319
Number of DB pages free = 27
Sweep interval = 20000
Forced Writes are OFF
Transaction - oldest = 236
Transaction - oldest active = 237
Transaction - oldest snapshot = 237
Transaction - Next = 244
ODS = 13.1
Database not encrypted
Embedded connection
Creation date: Jan 11, 2024 12:08:41
Replica mode: NONE
Default Character set: NONE
Publication: Disabled

No parameters, such as a specific database name, are required and if supplied, will be ignored.The details displayed will always be for the current database.

SQL> show database testing_db;
Database: employee
        Owner: SYSDBA
PAGE_SIZE 8192
...
Publication: Disabled

You will note from the above that the details displayed are still for the employee database.

SHOW DEPENdencies or SHOW DEPENdency

SHOW {DEPENdencies | DEPENdency} object_name

These commands display all dependencies for the specified object name supplied as a parameter.The object name supplied need not necessarily be a table name, it could be a function or procedure name, a sequence name etc.

The output listed is a comma-separated list of the other objects in the database upon which the supplied object is dependent.In other words, a procedure would fail to compile if any of the listed dependencies was to be removed, for example.

SQL> show dependencies SET_CUST_NO;
        [SET_CUST_NO:Trigger]
CUSTOMER:Table<-CUST_NO, CUST_NO_GEN:Generator
+++

The listing above shows that SET_CUST_NO is a trigger and that it is dependent on two separate objects, the CUST_NO column of table CUSTOMER, and the sequence (generator) named CUST_NO_GEN.If you display the trigger itself, you will see both of those objects mentioned:

SQL> show trigger set_cust_no;

Triggers on Table CUSTOMER:
SET_CUST_NO, Sequence: 0, Type: BEFORE INSERT, Active
Trigger text:
=============================================================================
AS
BEGIN
    if (new.cust_no is null) then
    new.cust_no = gen_id(cust_no_gen, 1);
END
=============================================================================

Sometimes, the output can be a little confusing.You may see various objects in the list that don’t appear to be relevant.The RDB$DEPENDENCIES table, where the data comes from, also holds details of system objects upon which a given object will depend.

SHOW DOMAINs

SHOW DOMAINs [name]

This command displays domain information.A domain is a user-defined data type, global to the database.It is used to define the format and range of columns, upon which the actual column definitions in tables are based.

Firebird tables are defined by the specification of columns, which store appropriate information in each column using data types.

A data type is an elemental unit when defining data, which specifies the type of data stored in tables, and which operations may be performed on this data.It can also include permissible calculative operations and maximum data size.Examples of data types include: numerical (numeric, decimal, integer), textual (char, varchar, nchar, nvarchar), date (date, time, timestamp) and blobs (binary large objects).

As with many show commands, there are two forms.The first displays a list of all known user-defined domains in the database while the second allows you to display the details of a specific domain.

SQL> show domain;
ADDRESSLINE
BUDGET
...

SQL> show domain addressline;
ADDRESSLINE                     VARCHAR(30) Nullable

SHOW EXCEPtions

SHOW EXCEPtions [name]

This command displays all user-defined exceptions in the current database.Details of the exception’s error message and objects which use the exception — those which are dependant upon the exception — are also shown.You may display individual exception’s details with the second form of the command.

SQL> show exceptions;
CUSTOMER_CHECK; Msg: Overdue balance -- can not ship.; Used by: SHIP_ORDER (Stored procedure)
CUSTOMER_ON_HOLD; Msg: This customer is on hold.; Used by: SHIP_ORDER (Stored procedure)
...

SQL> show exception customer_on_hold;
CUSTOMER_ON_HOLD; Msg: This customer is on hold.; Used by: SHIP_ORDER (Stored procedure)

SHOW FILTERs

SHOW FILTERs [name]

This command displays a list of all user-defined BLOB filters declared in the current database using the declare filter command.The second form of the command shows the full details of a specific filter.

SQL> show filter;
FUNNEL
...

SQL> show filter funnel;
BLOB Filter: FUNNEL
        Input subtype: 2 Output subtype: 1
        Filter library is myfilterlib
        Entry point is blr2asc

SHOW FUNCtions

SHOW FUNCtions [name]

This command lists all user-defined external and stored functions declared in the current database.External functions are those defined and coded in various UDF libraries.

The second form of the command shows the details of a specific function.

SQL> show functions;
ADDDAY
ADDHOUR
...

SQL> show function addyear;

Function ADDYEAR:
Function library is fbudf
Entry point is addYear
Returns  TIMESTAMP
Argument 1: TIMESTAMP
Argument 2: INTEGER

Since Firebird 5.0, isql lists functions and packaged functions in the same way, where the latter are displayed in the form <package-name>.<function-name>.Isql of Firebird 3.0 and 4.0 displayed functions and packaged functions differently.

Document History

The exact file history is recorded in the firebird-documentation git repository; see https://github.com/FirebirdSQL/firebird-documentation

Revision History

1.4

4 May 2024

MR

Wrong name for SET option EXEC_DISPLAY_PATH should be EXEC_PATH_DISPLAY (#209)

1.3

2 Apr 2024

MR

Protocol names are lowercase (#205)

1.2

24 Feb 2024

  • Replaced isql -⁠help (invalid switch) with isql -⁠? (valid switch)

  • Added word-joiner in commandline switches between minus (-) and first character to ensure they aren’t broken up on word wrap

  • Include isql name in the document title, making it the same as on https://firebirdsql.org/en/reference-manuals/

1.1

17 Jan 2024

MR

  • Misc. copy-editing

  • Moved SET KEEP_TRAN_params for correct alphabetical ordering

  • Replaced Firebird 4.0 Language Reference link with Firebird 5.0 Language Reference link

  • Documented SET PER_TABle_stats introduced in Firebird 5.0.Adapted from the original text by Vlad Khorsun in docs/README.isql_enhancements.txt.

  • Documented new options for SHOW SYStem

  • Documented SHOW PUBlications

  • Replaced (most) output examples with output of Firebird 5.0

  • Removed SQL> prefix and ; terminator from syntax documentation

1.0

24 Dec 2021

MR

  • Reversed order of this document history to put the most recent change first

  • Documented SET EXEC_PATH_DISPLAY introduced in Firebird 4.0.1.Adapted from the original text by Adriano dos Santos Fernandes in docs/README.isql_enhancements.txt.

0.9

12 Jun 2021

MR

0.8

24 May 2021

MR

  • Added missing Firebird 3.0 and 4.0 features (SET MAXROWS, SET EXPLAIN, SET KEEP_TRAN_PARAMS, SHOW MAPPING, SHOW PACKAGES, SHOW SCHEMAS)

  • Used consistent code formatting in section titles of commands

  • Added command aliases to section titles

  • Updated example output of most commands with output of Firebird 4.0

  • General copy-editing

0.7

08 May 2021

MR

Documented SET LOCAL_TIMEOUT

0.6

20 Jun 2020

MR

Conversion to AsciiDoc, minor copy-editing

0.5

10 Apr 2012

ND

More tidying up.Plus:

0.4

15 Feb 2012

ND

General tidy up.Changes to formatting.Corrected some Docbook "misuse".Spelling & punctuation corrections.Lists compacted.Corrected <screen> overflow in pdf rendering.Etc.

0.3

20 Oct 2009

ND

Converted from a chapter in the Command Line Utilities manual to stand alone manual in its own right.

Changed title to Firebird Interactive SQL Utility to bring it in line with the other utility manuals.

Many other updates to bring this manual into line with the others and to incorporate Firebird 2 changes etc.

0.2

5 Jul 2008

PV

Changed title to Isql - Firebird Interactive SQL Utility to bring it in line with the other manuals.Added titleabbrev and edition info.Moved Audience and Version sections into Introduction.Removed Related Documentation section.Fixed typos, interpunction (still more to do here).Replaced most emphasises and all citetitles with more appropriate tags.Gave IDs to manual and all (sub)sections.Added manual History and License Notice.

0.1

Overview

The isql utility is a text-mode client tool located in the bin directory of the Firebird installation.It provides a commandline interface for interactive access to a Firebird database.It accepts DSQL statements along with a group of ISQL-specific commands to query and interact with the Firebird database.Some SET commands can be incorporated in DDL scripts to perform batch executions within isql.It also accepts DDL, DML and console commands.

The isql utility can be used in three modes:

  • as an interactive session;

  • directly from the commandline;

  • and as a non-interactive session, using a shell script or batch file.

Different tasks may be performed in each of the modes, as illustrated below:

  • An interactive session can be invoked from the commandline of the operating system shell, and lasts until the session is terminated, using a QUIT or EXIT command.Isql can be used interactively to:

    • Create, update, query, and drop data or metadata

    • Input a script file containing a batch of SQL statements in sequence without prompting

    • Add and modify data

    • Grant user permissions

    • Perform database administrative functions

  • Directly from the commandline, with individual options and without starting an interactive session.Commands execute, and upon completion, return control automatically to the operating system.

  • In a non-interactive session, the user employs a shell script or batch file to perform database functions.

Note

Because other applications in the Linux environment, for example, MySQL, also use isql as a utility name, you are advised to run the Firebird utility from its own directory, or provide the absolute file path if you have another relational database, besides Firebird, installed on your machine.
Note

Some affected distributions have renamed Firebird’s isql to isql-fb.There may be similar changes in other distributions.

In addition, not all distributions install Firebird to the same location.OpenSuse, for example, installs just about everything to /opt/firebird/n.n but Ubuntu and derivatives install it to a number of different locations, but the utilities are in /usr/bin.

For the remainder of this document, we assume that the utility is called isql.

License Notice

The contents of this Documentation are subject to the Public Documentation License Version 1.0 (the “License”); you may only use this Documentation if you comply with the terms of this License.Copies of the License are available at https://www.firebirdsql.org/pdfmanual/pdl.pdf (PDF) and https://www.firebirdsql.org/manual/pdl.html (HTML).

The Original Documentation is titled Firebird Interactive SQL Utility.

The Initial Writer of the Original Documentation is: Kamala Vadlamani.

Copyright © 2006.All Rights Reserved.Initial Writer contact: kamala dot vadlamani at gmail dot com.

Contributors: Paul Vinkenoog, Norman Dunbar, Mark Rotteveel.

Portions created by Paul Vinkenoog are Copyright © 2008.All Rights Reserved.Contributor contact: paul at vinkenoog dot nl.

Portions created by Norman Dunbar are Copyright © 2009, 2011-2013.All Rights Reserved.Contributor contact: NormanDunbar at users dot sourceforge dot net.

Portions created by Mark Rotteveel are Copyright © 2021-2024.All Rights Reserved.Contributor contact: mrotteveel at users dot sourceforge dot net.

Invoking Isql

If you do not have the Firebird bin directory on your path, then either go to the bin subdirectory of your Firebird installation and type isql (Windows) or ./isql (Linux) at the command prompt, or, type the full path to the isql application to execute it.If the bin is on your path, you may start it by typing isql regardless of your operating system.

Example:

$ isql

Use CONNECT or CREATE DATABASE to specify a database
SQL> CONNECT "C:\DATABASES\FIREBIRD\MY_EMPLOYEE.FDB"
CON> user 'SYSDBA' password 'secret';
Tip

Here and elsewhere in this document, we use the ‘$’ to signify the command prompt, it is not part of the command to enter.

For example, on Windows the command prompt might look like “C:\>”, or on Linux “user@HOST:~$"”.The exact prompt depends on the OS defaults and user configuration.

The above is the simplest method of starting isql, and once activated in this way, you must begin by either creating a new database, or connecting to one.The prompt given by isql is a hint as to what you must do next.If you wish to connect to an already existing database, you may pass the database name on the commandline.You should be aware that unless you also pass the username and password as well, you may see an error message telling you that your username or password have not been defined.In this case, you need to supply the username and password, or create two environment variables as discussed below.

The following example shows how to pass the database name plus user credentials on the commandline.

$ isql -user sysdba -password secret employee
Database:  employee, User: SYSDBA
SQL>

In this example, we used a database alias for the employee database.This example comes predefined in the file databases.conf  — or aliases.conf in Firebird 2.5 and older — which normally lives in the directory that Firebird was installed in, but some Linux distributions put it in /etc/firebird/n.n where n.n is the version of the Firebird database server.There is more information on connecting to databases, using full paths or aliases in [isql-connect-database].

Commandline Switches

Commandline switches are arguments that begin with a minus/hyphen (‘-’) character.The following is an example of what happens when you attempt to start isql with the help (“-⁠?”) switch — it displays the list of valid switches with a brief explanation of each.If an invalid switch is specified, isql will also display the usage help.

$ isql -?
usage:    isql [options] [<database>]
        -a(ll)                  extract metadata incl. legacy non-SQL tables
        -b(ail)                 bail on errors (set bail on)
        -c(ache) <num>          number of cache buffers
        -ch(arset) <charset>    connection charset (set names)
        -d(atabase) <database>  database name to put in script creation
        -e(cho)                 echo commands (set echo on)
        -ex(tract)              extract metadata
        -f(etch_password)       fetch password from file
        -i(nput) <file>         input file (set input)
        -m(erge)                merge standard error
        -m2                     merge diagnostic
        -n(oautocommit)         no autocommit DDL (set autoddl off)
        -nod(btriggers)         do not run database triggers
        -now(arnings)           do not show warnings
        -o(utput) <file>        output file (set output)
        -pag(elength) <size>    page length
        -p(assword) <password>  connection password
        -q(uiet)                do not show the message "Use CONNECT..."
        -r(ole) <role>          role name
        -r2 <role>              role (uses quoted identifier)
        -s(qldialect) <dialect> SQL dialect (set sql dialect)
        -t(erminator) <term>    command terminator (set term)
        -tr(usted)              use trusted authentication
        -u(ser) <user>          user name
        -x                      extract metadata
        -z                      show program and server version

Not all of these switches appear in every release of Firebird.Some will be seen in more recent releases.Many of the switches have an equivalent set command, and these will be discussed below.

Using -⁠b(ail)

The commandline switch -⁠b(ail) instructs the isql utility to bail on error, but only when used in a non-interactive mode.

This switch prevents isql from continuing execution after an error has been detected.No further statements will be executed and isql will return an error code to the operating system.

Users will need to use the -⁠e(cho) switch to echo commands to an output file, to isolate the exact statement that caused the error.

When the server provides line and column information, users can see the exact line of the DML in the script that caused the problem.When the server only indicates failure, users can view the first line of the statement that caused the failure, in relation to the entire script.

This feature is also supported in nested scripts.For example, script A includes script B and script B causes a failure, the line number is related to script B.When script B is read completely, isql continues counting the lines related to script A, since each file gets a separate line counter.Script A includes script B when script A uses the INPUT command to load script B.

Lines are counted according to what the underlying IO layer considers separate lines.For ports using EDITLINE, a line is what readline() provides in a single call.The line length limit of 32767 bytes remains uncharged.

Using -⁠ex(tract)

The commandline switch -⁠ex(tract) can be used to extract metadata from the database.In conjunction with the -⁠o(utput) switch, it extracts the information to a specified output file.

The resultant information is the DDL to create the current structure of the database.

Using -⁠m2 and -⁠m(erge)

The commandline switch -⁠m2 — introduced in Firebird 2.0 — can be used to send the statistics and plans to the same output file that receives the input from the -⁠o(utput) switch.

Before Firebird 2.0, when a user specified that the output should be sent to a file, two options existed: the commandline switch -⁠o(utput) with a file name to store the output, or the command OUTput with a file name to store the output.Both these options could be employed either in a batch session or in the interactive isql shell.In both cases, simply passing the command OUTput would return the output to the console.While the console displayed error messages, these were not sent to the output file.

The -⁠m(erge) commandline switch, can be used to incorporate the error messages into the output files.

The -⁠m2 commandline switch ensures that the stats and plan information derived from the SET STATS, SET PER_TABLE_STATS`, SET PLAN and SET PLANONLY commands are also sent to the output file and not just returned to the console.

Note

Neither -⁠m(erge) nor -⁠m2 has an interactive counterpart through a SET command.They are for use only as commandline isql options.
_Using -⁠r2 and -⁠r(ole)

These switches can be used to specify role name.The default switch for this is -⁠r(ole), and are uppercased (i.e. as unquoted identifiers, case-insensitive).With -⁠r2 they are passed to the engine exactly as typed in the commandline, that is case-sensitive (as if they are quoted identifiers).

Using `-⁠o(utput)

The OUTPUT switch allows users to store records of commands to a script file.The TMP setting on a client can be used to control where these script files will be stored, if an absolute file path is not specified.

Starting An Isql Session

To begin an isql session, enter the commandline options and the name of the database in the Linux /Unix shell or Windows command console.For example:

isql [options] [<database_name>]
Note

When invoking isql, you will need to include an appropriate -⁠user and -⁠password in your options, unless users have the ISC_USER and ISC_PASSWORD declared as operating system variables.For example:

isql -user SYSDBA -password masterkey

Isql starts an interactive session if no options are specified.If no database is specified, users must connect to an existing database or create a new one after starting isql.If a database is specified, it starts the interactive session by connecting to the named database, provided the login options are accurate and valid for the specified database.Depending on the options specified, isql starts an interactive or non-interactive session.

Reading an input file and writing to an output file are not considered interactive tasks, therefore the -⁠input or -⁠output commandline options do not initiate an interactive session.Options used to extract DDL statements, such as -⁠a and -⁠x also initiate a non-interactive session.

Isql can be run from either a local or remote client:

  • When connecting using a local client, you may set the environment variables ISC_USER and ISC_PASSWORD.For more information on these, see below.

  • When connecting from a remote client, you will need a valid name and password.

Ending an Isql Session

There are two ways to exit isql.

  • If you wish to roll back all uncommitted work and exit isql type this command at the prompt:

    SQL> QUIT;

  • If you wish to commit all your work before exiting isql, then type in the following command:

    SQL> EXIT;

Getting Help

Isql comes with a basic HELP command.This gives brief details of most of the commands available — unfortunately, some are missing.The help command also allows you to drill down for further information.To activate the help system, simply type HELP at the prompt, as shown below (from Firebird 5.0):

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

Most of these commands have no further level of detail, while the SET command does.To drill down into this extra level, proceed as follows:

SQL> help set;
Set commands:
    SET                    -- display current SET options
    SET AUTOddl            -- toggle autocommit of DDL statements
    SET BAIL               -- toggle bailing out on errors in non-interactive mode
    SET BLOB [ALL|<n>]     -- display BLOBS of subtype <n> or ALL
    SET BLOB               -- turn off BLOB display
    SET COUNT              -- toggle count of selected rows on/off
    SET MAXROWS [<n>]      -- limit select stmt to <n> rows, zero is no limit
    SET ECHO               -- toggle command echo on/off
    SET EXPLAIN            -- toggle display of query access plan in the explained form
    SET HEADING            -- toggle display of query column titles
    SET KEEP_TRAN_params   -- toggle to keep or not to keep text of following successful SET TRANSACTION statement
    SET LIST               -- toggle column or table display format
    SET NAMES <csname>     -- set name of runtime character set
    SET PER_TABle_stats    -- toggle display of detailed per-table statistics
    SET PLAN               -- toggle display of query access plan
    SET PLANONLY           -- toggle display of query plan without executing
    SET SQL DIALECT <n>    -- set sql dialect to <n>
    SET STATs              -- toggle display of performance statistics
    SET TIME               -- toggle display of timestamp with DATE values
    SET TERM <string>      -- change statement terminator string
    SET WIDTH <col> [<n>]  -- set/unset print width to <n> for column <col>

All commands may be abbreviated to letters in CAPitals

If you attempt to drill down into any other command, the output is the same as executing the HELP command on its own.

Note

The output from HELP SET only shows isql SET commands.Server-side SET statements, like SET TRANSACTION and management statements (e.g. SET BIND, SET OPTIMIZE, etc.), are not shown.

Consult the Language Reference for your Firebird version for details on those statements.

Connecting To A Database

A sample database named employee.fdb is located in the examples/empbuild subdirectory of your Firebird installation, with an alias employee.Users can use this database to experiment with Firebird.Note that on some POSIX systems, the example database may not be located in the location given above, or may need to be installed separately.Each Linux distribution, for example, may have decided to relocate some files.

CONNECT <database_name>
  [USER username] [PASSWORD password] [ROLE role_name]

If any of the parameters to the connect command contains spaces, you must wrap that parameter in single or double quotes.Since Firebird 3.0, usernames enclosed in double quotes (‘"’) are case-sensitive, just like other delimited identifiers in Firebird.

If username or password are not supplied, then the current values in the ISC_USER and ISC_PASSWORD environment variables are used instead.There is no environment variable to preset the required role.

It is possible to connect to a database using isql in two ways: locally and remotely.

  • To connect locally, on Windows, use the CONNECT statement with the full file path or an alias (for a local database):

    SQL> CONNECT "C:\DATABASES\FIREBIRD\MY_EMPLOYEE.FDB"

    On Linux, a similar example would be:

    SQL> CONNECT "/databases/firebird/MY_EMPLOYEE.FDB"

    Depending on the Firebird version used, this may result in an embedded connection or an XNET connection (Windows-only).

  • If connecting remotely (using TCP/IP), use the CONNECT statement with the server name and complete file path of the database or, an alias. When using the full path, remember to ensure that the server name is separated from the database path with a colon.

    To connect to a database on a Linux/UNIX server named cosmos:

    SQL> CONNECT 'cosmos:/usr/firebird/examples/employee.gdb';

    To connect to a database on a Windows server named cosmos:

    SQL> CONNECT 'cosmos:C:\DATABASES\FIREBIRD\MY_EMPLOYEE.FDB'
Note

Firebird is slash agnostic and automatically converts either type of slash to suit the relevant operating system.

Connection Strings

The Firebird client library — and by extension, isql — supports a variety of connection strings.

Connection String Syntax
<database_name> ::= [server_spec]{filepath | db_alias}

<server_spec> ::=
    host[/{port | service}]:
  | \\host\
  | <protocol>://[host[:{port | service}]/]

<protocol> ::= inet | inet4 | inet6 | wnet | xnet

All Firebird versions support the first two connection strings.Firebird 3.0 and later also support the third form.Support for the wnet and xnet protocols is only available on Windows.Support for the wnet protocol was removed in Firebird 5.0.

The actual supported connection strings depends on the Firebird client library in use (fbclient.dll/libfbclient.so).

The service refers to a service definition in the services file of your operating system (Windows: %WINDIR%\System32\drivers\etc\services, Linux: /etc/services).Historically, for Firebird it is gds-db or gds_db with value 3050/tcp, however Windows and Linux do not include either entry by default.

URL-like Syntax

Firebird 3.0 introduced a unified URL-like syntax for the remote server specification.In this syntax, the first part specifies the name of the protocol, then a host name or IP address, port number, and path of the primary database file, or an alias.

The following values can be specified as the protocol:

inet

TCP/IP (first tries to connect using the IPv6 protocol, if it fails, then IPv4)

inet4

TCP/IP v4

inet6

TCP/IP v6

wnet

NetBEUI or Named Pipes Protocol (removed in Firebird 5.0)

xnet

local protocol (does not include a host, port and service name)

<protocol>://[host[:{port | service}]/]{filepath | db_alias}

The standard text form of an IPv6 address uses the colon character to separate groups of digits (upto 8 groups of digits).In the connection string, the IPv6 address must be enclosed in square brackets, to resolve the ambiguity with the use of the colon as the separator between the host IP address and database path.For example:

connect '[2014:1234::5]:test';
connect '[2014:1234::5]/3049:/srv/firebird/test.fdb';
Note

For consistency, square brackets can be optionally used around an IPv4 address or domain name.

Legacy Connection Strings

Firebird also has two “legacy” connection strings.

If you use the TCP/IP protocol to create a database, the primary file specification should look like this:

host[/{port|service}]:{filepath | db_alias}

This format is supported by all Firebird versions.

If you use the Named Pipes protocol to create a database on a Windows server, the primary file specification should look like this:

\\host\{filepath | db_alias}

This format is no longer supported since Firebird 5.0.

Using Database Aliases

In the examples above, we have been using the full path to the database file.This has the disadvantage that all clients need to know exactly where the database is to be found, or may cause problems when the database has to be moved.To alleviate these problems, database aliases can be used.

Once Firebird has been installed, a file named databases.conf (Firebird 3.0 and higher) or aliases.conf (Firebird 2.5 and earlier) can be found in the main installation folder.By adding an entry to this folder, the full path to the database can be simplified to an alias.This makes connecting easier, and allows the database to be moved around as necessary without having to change all clients to allow them to connect to the database at the new location.

To create an alias for the database currently known as /databases/firebird/MY_EMPLOYEE.FDB on the cosmos Linux server, we need to add the following to the databases.conf file on the cosmos server.By default, this will be in the folder /opt/firebird.On Linux, this file is owned by the root user and so, must be updated by the root user.On Windows, you need to be either an administrator, a power user or SYSTEM to change the file.

my_employee = /databases/firebird/MY_EMPLOYEE.FDB

There should be no quotes around the path to the database file.

Regardless of the current location of the database file, or if it has its physical filename renamed, etc., all local users can refer to the database simply as my_employee.Remote users will refer to this database as cosmos:my_employee.The following example shows an isql session connecting locally to the database using the alias rather than a full path:

$ /opt/firebird/bin/isql my_employee
Database:  test, User: sysdba

SQL>

Alternatively, a remote connection would be made as follows, specifying the server name and database alias together:

$ isql cosmos:my_employee
Database:  cosmos:my_employee

SQL>

Because the alias is defined on the server where the database resides, the remote client needs to supply the server name and alias (as defined on that server) to connect.

Using the CONNECT command in an existing isql session is equally simple using aliases:

SQL> CONNECT 'cosmos:my_employee';
Database:  cosmos:my_employee

SQL>
Caution

Do not think that aliases hide the full path to the actual database file from your users.Any user is still able to query this information from within the database:

SQL> select MON$DATABASE_NAME from mon$database;

MON$DATABASE_NAME
=================================
/data/databases/firebird/test.fdb

SQL> select RDB$GET_CONTEXT('SYSTEM', 'DB_NAME') from RDB$DATABASE;

RDB$GET_CONTEXT
=================================
/data/databases/firebird/test.fdb

Creating A Database

To create a database interactively using the isql command shell, open a command prompt in Firebird’s bin subdirectory and type isql (Windows) or ./isql (Linux):

$ isql
Use CONNECT or CREATE DATABASE to specify a database

To create a database named monkey.fdb and store it in a directory named test on your C:-drive:

SQL>CREATE DATABASE 'C:\test\monkey.fdb' page_size 8192
CON>user SYSDBA password 'masterkey';

For the full syntax of CREATE DATABASE, refer to the Language Reference of your Firebird version.For example, the Firebird 5.0 Language Reference section on CREATE DATABASE.

Note

In the CREATE DATABASE statement it is mandatory to place quote characters (single or double) around path and password.In Firebird 2.5 and earlier, it is also required to do this for usernames.Since Firebird 3.0, usernames enclosed in double quotes (‘"’) are case-sensitive, just like other delimited identifiers in Firebird.

When running Classic Server on Linux, or when using Firebird 3.0 or higher, if the database does not start with a host name, the database file will be created with the current user as the file owner.This may cause access denied errors for others who may want to connect at a later stage.By prepending the localhost: to the path, or xnet:// on Windows, the user running the server process (e.g. on Linux, user firebird) will create and own the file.

To test the newly created database type:

SQL>SELECT RDB$RELATION_ID FROM RDB$DATABASE;

RDB$RELATION_ID
===============
128

SQL> commit;

To get back to the command prompt type quit or exit.

Note

The above technique, as demonstrated, works, but ideally databases and metadata objects should be created and maintained using data definition scripts.