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;
```

docnext count = 7

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.

Starting An Isql Session