FirebirdSQL logo

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.