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 attempted 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.