FirebirdSQL logo

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.