Firebird Documentation Index → Isql - Interactive SQL → Using Isql Interactively
Firebird Home Firebird Home Prev: Script HandlingFirebird Documentation IndexUp: Isql - Interactive SQLNext: Command Line Switches

Using Isql Interactively

Isql Commands
Isql Show commands
Isql Set Commands

The Firebird isql utility can be used interactively to:

To perform these functions, isql accepts three kinds of commands at the prompt:

Isql Commands

Isql commands affect the running of isql itself and do not affect the database or data in any way. These commands are used to display help, run scripts, create listings and so on. You can easily see a list of the available commands by typing the help command which will produce the following output: 

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, 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

Each of these commands will now be discussed. Note the last line of output from the help command. It explains that each of the commands may be abbreviated to just those letters displayed in capital letters. In the following discussion, the optional characters will be wrapped in square brackets. For example, the input command will be shown as in[put] to indicate that the characters 'put' are optional.

Blobdump

Blobview

Edit

Help

The help command has been discussed above.

Input

Output

Shell

Exit

Quit

Isql Show commands

As explained in the help command, there are a number of individual show commands within isql. These are as follows:

Show Check

Show Collation

Show Database

Show Domain

Show Exception

Show Filter

Show Function

Show Generator/Sequence

Show Grant

Show Index

Show Procedure

Show Role

Show SQL Dialect

Show System

Show Table

Show Trigger

Show Version

Show Users

Show View

Isql Set Commands

As explained in the help command, you may enter the help set command to drill down into the various options available for the set command. These are all discussed below. Note that the output from the help set command does not include the set transaction command. The help set command produces the following output: 

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 ROWCOUNT [<n>]     -- limit select stmt to <n> rows, zero is no limit
    SET ECHO               -- toggle command echo on/off
    SET HEADING            -- toggle display of query column titles
    SET LIST               -- toggle column or table display format
    SET NAMES <csname>     -- set name of runtime character set
    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

The last line of the above output indicates that these commands can be abbreviated to the letters in capitals. Unfortunately, other than the set autoddl command, none of the others appear to have a short form.

Set

The set command, with no parameters, displays the current settings, as the following example from Firebird 2.5 shows: 

SQL> set;

Print statistics:        OFF
Echo commands:           OFF
List format:             OFF
List Row Count:          OFF
Select rowcount limit:   0
Autocommit DDL:          ON
Access Plan:             OFF
Access Plan only:        OFF
Display BLOB type:       1
Column headings:         ON
Terminator:              ;
Time:                    OFF
Warnings:                ON
Bail on error:           OFF

Set Autoddl

set auto[ddl] [on | off];

This command sets whether all DDL statements executed will be automatically committed or not. The command without any parameters acts as a toggle and turns autoddl off if it is currently on and vice versa. You may supply a specific parameter to make your intentions clear. The parameter must be one of on or off. The set command, with no parameters, will display the current setting. The default in isql is equivalent to set autoddl on.

Set Bail

Set Blob

Set Count

Set Rowcount

Set Echo

Set Heading

Set List

Set List Rowcount CHECK THIS ONE!!!

Set Names

Set Plan

Set Planonly

Set SQLDA_Display

This is a hidden command which is not mentioned in the output from the help set command.

Set SQL Dialect

Set Stats

Set Time

Set Term

Set Transaction

This is another hidden command which is not mentioned in the output from the help set command.

Set Width

Normally the width of a character column in a table defines the width of the output when that column is selected. Using the set width command allows the user to define a wider or narrower output column width.

The format of the command is set width column_or_alias width; The setting remains until changed to a new width, or until cancelled by the set width column_or_alias; command - no width supplied means use the default width setting for this column.

The following example shows the width of the last_name column being amended. The first SELECT shows the default setting which is a wdith of 20 characters (count the '=' in the headings) which is the definition of the last_name column in the employee table. The second shows the width being reduced to 10 characters. 

SQL> select first 10 emp_no, last_name
CON> from employee
CON> order by last_name;

 EMP_NO LAST_NAME            
======= ==================== 
     34 Baldwin              
    105 Bender               
     28 Bennet               
     83 Bishop               
    109 Brown                

SQL> set width last_name 10;

SQL> select first 10 emp_no, last_name
CON> from employee
CON> order by last_name;

 EMP_NO LAST_NAME  
======= ========== 
     34 Baldwin    
    105 Bender     
     28 Bennet     
     83 Bishop     
    109 Brown

Emp_no is a smallint data type. Unfortunately, it doesn't appear to be possible to change the width on non-character columns line integer, smallint etc. The set width emp_no 10; command, for example, has no effect, as shown below, which also demonstrates turning off a previous width setting for the last_name column: 

SQL> set width last_name;
SQL> set width emp_no 10;

SQL> select first 10 emp_no, last_name
CON> from employee
CON> order by last_name;

 EMP_NO LAST_NAME            
======= ==================== 
     34 Baldwin              
    105 Bender               
     28 Bennet               
     83 Bishop               
    109 Brown
Prev: Script HandlingFirebird Documentation IndexUp: Isql - Interactive SQLNext: Command Line Switches
Firebird Documentation Index → Isql - Interactive SQL → Using Isql Interactively