Interactive Mode - Firebird

Interactive isql can be run locally or from a remote client.

  • From a remote client, a valid user name and password are always required to run isql.
  • If you are connecting locally, you can set the operating system variables ISC_USER and ISC_PASSWORD and avoid the need to enter them on commands. For more information about these variables, refer to Chapter Configuring Firebird.

Default Text Editor

Some isql commands access your system’s default text editor.

  • On UNIX, Linux, and some other POSIX platforms, the default editor is defined by one or the other of the two environment variables EDITOR and VISUAL. The installation default is usually vi, vim, or emacs, but you can set it to another preferred console (not X) text editor.
  • On Windows, the story is similar. The default editor is defined by the environment variable EDITOR. On current versions of Windows, it is Notepad.exe, but you can set it to be any text editor you prefer. On very old Windows versions, the default editor was probably mep.exe or edit.exe.

Starting isql

To start isql, open a command shell and cd to the Firebird /bin directory of your Firebird server or client installation. Key in the following command at the shell prompt and press the Enter key:

isql [database_name] [-u[ser] <user-name> -pas[sword] <password>]

database_name is optional. If you include it, isql will open a connection to the database and start its shell already connected. It must be either the full path to the database from where you are or, in version 1.5 and later, a valid alias.

The switches –user <user-name> and –password <password> are optional when you are starting isql without a connection to the database and required when you are starting isql remotely. If the ISC_USER and ISC_PASSWORD environment variables are not set, they will also be required when you start isql locally.

On POSIX:

./isql

On Windows:

isql

starts the program.

./isql -user TEMPDBA -password osoweary [on POSIX] isql -user TEMPDBA -password osoweary [on Windows]

starts the program and stores the supplied user name and password without authenticating them.

isql hotchicken:/data/mydatabase.fdb -user TEMPDBA -password osoweary

starts the program on a Windows client and connects to the database on a POSIX server, provided the user name and password are valid on the server.

./isql /data/mydatabase.fdb

starts the program locally on a Linux server and connects to the database, provided the environment variables ISC _USER and ISC _PASSWORD are set and are available to your Linux user profile.

If you are logged into the database when isql starts up, you will see a console display similar to Figure. The appearance of the surrounding shell depends on the operating system. The isql shell is the same on all platforms.

Console display when isql starts logged-in

Console display when isql starts logged-in

If you did not enter a database path or you used a user name and password that are not defined on the server, you will see something similar to Figure.

Console display when isql starts not logged-in

Console display when isql starts not logged-in

If there were errors in the command string, or authentication problems, you might also see some error messages. If you can see the SQL> prompt, you are in the isql shell and you can connect to your database from there.

Connecting to a Database

To connect to a database from the SQL> prompt in the isql shell, use the following examples as syntax patterns. Notice that the syntax inside the isql shell is different from that used when passing the connection parameters from the system shell.

CONNECT 'HOTCHICKEN:L:DATAEXAMPLESEMPLOYEE.FDB' USER 'SYSDBA' PASSWORD 'masterkey';

connects to a remote or local server named HOTCHICKEN.

CONNECT 'L:DATAEXAMPLESEMPLOYEE.FDB';

connects to a local server where isql already knows your Firebird user name and password—either because you entered them correctly when you started isql or because isql is executing in a shell that can see the environment variables ISC_USER and ISC_PASSWORD.

CONNECT 'HOTCHICKEN:EMP3' USER 'SYSDBA' PASSWORD 'masterkey';

is equivalent to the first example, in Firebird 1.5 and later, using an alias stored in aliases.conf on the server, that points to the path.

CONNECT 'L:/DATA/EXAMPLES/EMPLOYEE.FDB';

is equivalent to the second example—slashes may be forward or backward in isql.

Server and Path Names

On Windows, do not confuse server names and shared disk resource names. The client/server network layer does not recognize mapped drives or shares. A drive identifier must always point to the actual drive letter of a hard drive or partition on the server host machine.

User Authentication

Regardless of whether you log in from the command line or do so when connecting inside the isql shell, authentication will fail if the server does not recognize the user name or the password. For example, Figure displays what you will see if your CONNECT statement fails.

Failed authentication

Failed authentication

If this happens to you, double-check that you have spelled the username and password correctly, and that the password is correct for case. Passwords are case sensitive; user names are not.

The Terminator Character

The default statement terminator is the semicolon (;), which is used for all of the examples in this chapter. You can change the terminator to any character or group of characters with the SET TERM[INATOR] command. For example, to change it to “!!”, use this statement:

SQL> SET TERM !!; SQL>

The Continuation Prompt

If you press Enter without remembering to finish a statement with a terminator, you will see the continuation prompt CON> instead of the isql prompt SQL>:

SQL> SHOW DATABASE CON>

If it was a mistake, simply type the terminator character and press Enter again. However, you can use this feature to make your typing easier to read, for example:

SQL> CREATE TABLE ATABLE (
CON> ID INTEGER NOT NULL,
CON> DATA VARCHAR(20),
CON> DATE_ENTERED DATE
CON> DEFAULT CURRENT_DATE
CON> );
SQL>

Transactions in isql

Transaction management in isql differs according to whether you issue a DDL statement, a SHOW command, or other kinds of statements.

When isql starts, it starts a transaction in SNAPSHOT (concurrency) isolation with a lock resolution setting of WAIT. Unless you run DDL statements or SHOW commands, the transaction stays current until you issue a COMMIT or ROLLBACK statement.

You can start an explicit transaction by committing the current transaction and using a SET TRANSACTION statement to start a new one. For example, to start a READ COMMITTED NO WAIT transaction:

SQL> COMMIT;
SQL> SET TRANSACTION
CON> NO WAIT READ COMMITTED;

When you have finished your task, just issue a COMMIT statement as usual. The next statement will revert to the default configuration.

DDL Statements

Each time you issue a DDL statement, isql starts a special transaction for it and commits it immediately after you press Enter. A new transaction is started immediately afterward. You can change this automatic behavior by issuing the SET AUTODDL OFF command from the SQL prompt before you begin issuing your DDL statements:

SQL> SET AUTODDL OFF;

To switch back to autocommit mode for DDL statements:

SQL> SET AUTODDL ON;

For switching back and forth between autoddl on and off, a short version is available that simply sets autoddl off if it is on, and vice versa:

SQL> SET AUTO;

SHOW Commands

The isql SHOW commands query the system tables. Whenever you invoke a SHOW command, isql commits the existing transaction and starts a new one in READ COMMITTED isolation. This ensures that you always have an up-to-date view of metadata changes as soon as they occur.

Retrieving the Line Buffer

From v.1.5 onward, isql allows you to retrieve the line buffer, in a similar fashion to the way the readline feature works on POSIX platforms. Use the up and down arrow keys to “scroll through” the isql buffer, a line at a time, to retrieve copies of lines you typed previously.

Using Warnings

By default, isql issues warnings for certain conditions, for example:

  • Statements with no effect
  • Ambiguous join specifications in Firebird 1.0.x (in v.1.5 and higher, they will cause exceptions)
  • Expressions that produce different results in different versions of Firebird
  • API calls that will be replaced in future versions
  • When a database shutdown is pending

For toggling the display off and on during an interactive isql session, use SET WARNINGS or its shorthand counterpart, SET WNG.

Exception Handling

Errors in isql are handled and delivered in the same way as they are in a DSQL application. Isql displays an error message consisting of the SQLCODE variable and the text message from the Firebird status array, as shown in Figure.

Example of an error message in isql

Example of an error message in isql

SQL errors with sub-zero SQLCODEs mean the statement has failed to execute. They are all listed in Appendix X. You may also see one of the SQL warning or information messages, namely

0: SUCCESS (successful execution) +1–99: SQLWARNING (system warning or information message)

+100: NOT FOUND (indicates that no qualifying rows were found, or “end of file”; that is, the end of the current active set of rows was detected)

Setting Dialect in isql

If you start isql and attach to a database without specifying a dialect, isql takes on the dialect of the database.

You can set the isql dialect in the following ways:

  • When starting isql:
    bin] isql -s n

    where n is 1, 2, or 3. If you specify the dialect this way, isql retains that dialect after connection unless you explicitly change it.

  • Within an isql session or in an SQL script:

SET SQL DIALECT n;

Isql continues to operate in that dialect unless it is explicitly changed.

The dialect cannot be set as a parameter of a CREATE DATABASE statement.

CAUTION

Dialect Effects

The effects of commands may show some variations according to dialect:

  • A dialect 1 client processes all commands according to the expectations of InterBase 5 language and syntax, with certain variations. For example, if you create a table that specifies a column of type DATE, you will see an information message telling you that “DATE data type is now called TIMESTAMP.”
  • In a dialect 2 client, elements that have different interpretations in dialect 1 and 3 are all flagged with warnings or errors, to assist in migrating databases to dialect 3.
  • A dialect 3 client parses all statements according to native Firebird SQL semantics: double quotes are delimited identifiers and are not recognized as string delimiters, the DATE data type is date-only, and exact numerics with precision greater than 9 are stored as BIGINT (NUMERIC(18,0) in Firebird 1.0.x).

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics