FirebirdSQL logo

Script Handling

A batch of DDL and/or DML statements in a text file is known as a script.Scripts can be used to create and alter database objects.These are referred to as Data Definition Language (DDL) scripts.Scripts that manipulate data by selecting, inserting, updating, deleting or performing data conversions, are called Data Manipulation Language (DML) scripts.

One of the most important tasks handled by isql is to process scripts.It can handle both DDL and DML Scripts, but they should be included in separate scripts to avoid data integrity problems.This script processing feature of isql allows the linking of one script to another using the isql command INPUT <filespec>.Scripts statements are executed in order that they appear in the script file.The default setting in isql for AUTODDL is set to ON.You may use the [isql-set-autoddl] command to control where or when DDL statements will be committed.

Note

The AUTODDL setting only affects DDL statements.It doesn’t commit DML statements.If you mix DDL and DML statements within the same interactive session, then the AUTODDL commits do not commit your DML changes.For example:

SQL> set autoddl on;

SQL> insert into test(a) values (666);
SQL> commit;

SQL> select * from test;

           A
============
         666

SQL> insert into test(a) values (999);
SQL> select * from test;

           A
============
         666
         999

SQL> create table another_test(b integer);
SQL> rollback;

SQL> select * from test;

           A
============
         666

Scripts can redirect their output to a log file using the OUTPUT file_name command.This can be entered directly at the isql prompt, or as part of a script file itself.

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, PACKAGE, 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 be discussed in the following sections.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

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 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 [isql-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.

SHELL

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>