isql commands

<< Script handling | Firebird Interactive SQL Utility | isql SET commands >>

isql commands

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 displays, as above, in lower case letters. For example, the INPUT command will be shown as INput to indicate that the characters 'put' are optional.


BLOBDUMP

 SQL> BLOBDUMP blob_id filename;

This command allows you to copy a BLOB from the database into an external file. It is the responsibility of the user to ensure that the correct file type is used - don't call an image file something.txt when it should be a jpeg for example.

Blobdump requires two parameters, a blob id and a filename. The latter is simple but the former is more convoluted.

You are required to pass the blob id as a pair of hexadecimal numbers, separated by a colon. The first number is the relation id number for the table in question and the second is a sequential number within the database. You will see this pair of numbers when you select any BLOB column's data from a table - it is displayed above the BLOB contents, assuming that the display of BLOBs is turned on. See the SET BLOBDISPLAY command below for details. 

 SQL> set blobdisplay off;

 SQL> select proj_id, proj_desc
 CON> from project
 CON> where proj_id = 'MKTPR';

 PROJ_ID         PROJ_DESC
 ======= =================
 MKTPR               85:10

 SQL> blobdump 85:10 project.jpg;

 SQL> blobdump 85:10 project.txt;

The blob id required in the above example is the 85:10 value. You will notice that I have dumped this BLOB to both a jpeg and a text file. isql gave no errors for the fact that I attempred to dump the BLOB to a jpeg file when the BLOB in question is text. Attempting to open the jpeg file with any image viewers will, however, result in an error. The text file opens happily in any of the assorted text viewers or editors installed on the system.


BLOBVIEW

 SQL> BLOBVIEW blob_id;

This command is similar to BLOBDUMO above, but only requires the blob id parameter as the BLOB data will be displayed in an editor. 

 SQL> blobview 85:10;

The contents of the selected BLOB are displayed in an external editor. When the editor is closed, control returns to isql. You cannot use isql while the editor is open.

Note: BLOBVIEW may return an “Invalid transaction handle” error after you close the editor. This is a known bug. To correct the situation, start a transaction manually, with the command SET TRANSACTION;.


EDIT

 SQL> EDIT [filename];

This command allows you to edit an existing file. This may be a file of SQL commands to be used by the isql INput command (see below) or any other text file. The file must however already exist.

If no filename is supplied, a history of all your previous commands will be displayed for editing. Please note that when you exit from the editor in this case, the commands left in the buffer at the end of the edit will be executed as a script file.


HELP

The help command has been discussed above.


ADD

 SQL> ADD table_name;

This command, when passed a table name, prompts you for each column's data and adds a row to the table. You may add as many rows as you wish as the command continues until either an error occurs or the RETURN key is pressed with no data. If you wish to set a column to NULL, type it in exactly as shown. 

 SQL> add country;

 Enter data or NULL for each column. RETURN to end.
 Enter COUNTRY>Scotland
 Enter CURRENCY>GBP

 Enter COUNTRY>

 SQL> commit;

COPY

 SQL> COPY from_table_name to_table_name [other_database];

The copy command allows you to copy most of the structure of a table to a new table, in the current database or to a different one. Unfortunately it has a couple of problems:

  • It shells out to the command line to do the work, and connects to the receiving database using an application named isql. If, like me, your system has renamed isql to isql-fb, you will actually end up running the wrong isql application and confusing error messages will be the only result.
  • It assumes that isql will be on the $PATH or .
  • You need to define ISC_USER and ISC_PASSWORD for the child isql process to login to the receiving database to create the table. This is very insecure.
  • Because of the need for ISC_USER and ISC_PASSWORD, the receiving database must be running on the same server as the source database.
  • The data in the table is not copied to the receiving database. Only the following parts of the table's structure is copied.
    • Domains required to recreate the table. This only applies if the copy is to another database.
    • The table itself will be created.
    • Primary key constraint, if there is one.
    • The index used to support the primary key constraint, if there is one.
  • Not all of the table structure is actually copied. Missing are:
    • Foreign key constraints.
    • Check constraints.
    • Indices other than the primary key index.
    • Triggers.
    • All of the table's data.

If you wish to copy to a different database, then the other database must be on the same server as the current one.

You cannot, for example, connect to a database on a server named tux, and copy a table to a database running on the server tuxrep. The COPY command has no way to allow you to pass a username and/or password and, equally, setting ISC_USER and ISC_PASSWORD only affects databases on the current server. 

 tux> $ export ISC_USER=SYSDBA
 tux> $ export ISC_PASSWORD=secret
 tux> isql employee

 Database: employee, User: sysdba

 SQL> -- MAke a copy of the employee table into this database.
 SQL> copy employee employee_2;

 SQL> -- Compare table structures...
 SQL> show table employee;

 EMP_NO                     (EMPNO) SMALLINT Not Null
 FIRST_NAME                 (FIRSTNAME) VARCHAR(15) Not Null
 LAST_NAME                  (LASTNAME) VARCHAR(20) Not Null
 PHONE_EXT                  VARCHAR(4) Nullable
 HIRE_DATE                  TIMESTAMP Not Null DEFAULT 'NOW'
 DEPT_NO                    (DEPTNO) CHAR(3) Not Null
 CHECK                      (VALUE = '000' OR
                            (VALUE > '0' AND VALUE <= '999') OR VALUE IS NULL)
 JOB_CODE                   (JOBCODE) VARCHAR(5) Not Null
 CHECK                      (VALUE > '99999')
 JOB_GRADE                  (JOBGRADE) SMALLINT Not Null
 CHECK                      (VALUE BETWEEN 0 AND 6)
 JOB_COUNTRY                (COUNTRYNAME) VARCHAR(15) Not Null
 SALARY                     (SALARY) NUMERIC(10, 2) Not Null DEFAULT 0
 CHECK                      (VALUE > 0)
 FULL_NAME                  Computed by: (last_name || ', ' || first_name)

 CONSTRAINT INTEG_28:
   Foreign key (DEPT_NO) References DEPARTMENT (DEPT_NO)
 CONSTRAINT INTEG_29:
   Foreign key (JOB_CODE, JOB_GRADE, JOB_COUNTRY)
   References JOB (JOB_CODE, JOB_GRADE, JOB_COUNTRY)
 CONSTRAINT INTEG_27:
   Primary key (EMP_NO)
 CONSTRAINT INTEG_30:
   CHECK ( salary >= (SELECT min_salary FROM job WHERE
                         job.job_code = employee.job_code AND
                         job.job_grade = employee.job_grade AND
                         job.job_country = employee.job_country) AND
           salary <= (SELECT max_salary FROM job WHERE
                         job.job_code = employee.job_code AND
                         job.job_grade = employee.job_grade AND
                         job.job_country = employee.job_country))

Triggers on Table EMPLOYEE: 

 SET_EMP_NO, Sequence: 0, Type: BEFORE INSERT, Active
 SAVE_SALARY_CHANGE, Sequence: 0, Type: AFTER UPDATE, Active

 SQL> show table employee_2;

 EMP_NO                     (EMPNO) SMALLINT Not Null
 FIRST_NAME                 (FIRSTNAME) VARCHAR(15) Not Null
 LAST_NAME                  (LASTNAME) VARCHAR(20) Not Null
 PHONE_EXT                  VARCHAR(4) Nullable
 HIRE_DATE                  TIMESTAMP Not Null DEFAULT 'NOW'
 DEPT_NO                    (DEPTNO) CHAR(3) Not Null
 CHECK                      (VALUE = '000' OR
                            (VALUE > '0' AND VALUE <= '999') OR VALUE IS NULL)
 JOB_CODE                   (JOBCODE) VARCHAR(5) Not Null
 CHECK                      (VALUE > '99999')
 JOB_GRADE                  (JOBGRADE) SMALLINT Not Null
 CHECK                      (VALUE BETWEEN 0 AND 6)
 JOB_COUNTRY                (COUNTRYNAME) VARCHAR(15) Not Null
 SALARY                     (SALARY) NUMERIC(10, 2) Not Null DEFAULT 0
 CHECK                      (VALUE > 0)
 FULL_NAME                  Computed by: (last_name || ', ' || first_name)

 CONSTRAINT INTEG_93:
   Primary key (EMP_NO)

 SQL> -- Check indices on both tables...
 SQL> show indices employee;

 NAMEX INDEX ON EMPLOYEE(LAST_NAME, FIRST_NAME)
 RDB$FOREIGN8 INDEX ON EMPLOYEE(DEPT_NO)
 RDB$FOREIGN9 INDEX ON EMPLOYEE(JOB_CODE, JOB_GRADE, JOB_COUNTRY)
 RDB$PRIMARY7 UNIQUE INDEX ON EMPLOYEE(EMP_NO)

 SQL> show indices employee_2;
 RDB$PRIMARY27 UNIQUE INDEX ON EMPLOYEE_2(EMP_NO)

 SQL> -- Check data counts on both tables...
 SQL> select count(*) from employee;

        COUNT
 ============
           42

 SQL> select count(*) from employee_2;

        COUNT
 ============
            0

The COPY command only works provided your isql application is really named isql. In addition, if you have lots of data in the table, you still have to copy it manually as the COPY command will only copy the table structure. Remember that the new table will have no triggers, no foreign keys, no indices - other than the primary key one - and no data.

Note: It is possible that the COPY command will be removed from isql at some future release.


INput

 SQL> INput filename;

This command enables the user to execute a number of commands from a script file rather than manually typing them all into isql at the prompt. The script may contain any mix of DDL and/or DDL commands, along with isql commands to redirect output, change options, etc. 

 SQL> shell;

 $ cat test.sql
 drop table fred;
 commit;

 $ exit;

 SQL> show table fred;

 A                 INTEGER Nullable
 B                 INTEGER Not Null

 SQL> input test.sql;

 SQL> show table fred;
 There is no table FRED in this database

OUTput

 SQL> OUTput [filename];

This command redirects all output that normally is displayed on the screen, to a specific file. If a file name is supplied, all subsequent output goes to that file and is not displayed on screen. If no file name is supplied, OUTput is once more redirected to the screen. 

 SQL> output test.log;

 SQL> show tables;

 SQL> output;

 SQL> shell;

 $ cat test.log

 COUNTRY                CUSTOMER
 DEPARTMENT             EMPLOYEE
 EMPLOYEE_PROJECT       FRED
 JOB                    PROJECT
 PROJ_DEPT_BUDGET       SALARY_HISTORY
 SALES

SET

There are a number of settings and options that can be changed to suit how you wish isql to operate. These settings are changed by the SET command which is discussed below.


SHELL

 SQL> SHELL;

This command allows you to temporarily exit from isql and use a shell session to carry out some further processing. On exiting from the shell, you will return to isql. You cannot use the isql session that activated the shell while the shell session remains open. 

 SQL> shell;

 $ cat test.log

 COUNTRY              CUSTOMER
 DEPARTMENT           EMPLOYEE
 EMPLOYEE_PROJECT     FRED
 JOB                  PROJECT
 PROJ_DEPT_BUDGET     SALARY_HISTORY
 SALES

 $ exit

 SQL>

SHOW

There are a number of settings and options that can be changed to suit how you wish isql to operate. The SHOW command allows you to view the way that these have been set up by the SET commands, or by other options. These are discussed below.


EXIT

 SQL> EXIT;

The EXIT command will commit any uncommitted work and exit from isql.


QUIT

 SQL> QUIT;

The QUIT command will rollback any uncommitted work and exit from isql.

See also:
Structured Query Language: isql
Firebird and InterBase® command-line utilities: isql

back to top of page
<< Script handling | Firebird Interactive SQL Utility | isql SET commands >>