Firebird Isql Interactive SQL Utility | Connecting To A Database

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.