HP 3000 Manuals

ALLBASE/ISQL Reference Manual

Using ISQL Files and Buffers 

ISQL has a command router that determines whether a command is an SQL
statement or an ISQL command and then routes it to SQLCore for execution.
The ISQL commands that entail database access, such as data loading
operations, are converted into SQL statements by the ISQL command
manager.  The command router also looks up synonyms when a synonym file
is used.

As Figure 2-1 illustrates, ISQL's command router that accepts commands
from the following sources: 

   *   The terminal.
   *   Files containing commands.
   *   The command buffer.
   *   The command history buffer.


          Figure 2-1.  Command Sources 

Entering Interactive Commands from the Terminal 

You submit one or more commands when you see the input prompt.  ISQL
processes the commands, then displays the input prompt again.

Files Containing Commands 

A command file is a file that contains one or more commands you want to
execute using ISQL. Command files are useful when you want to enter
lengthy or repetitive commands.  Any SQL statement you can enter from a
terminal can be put in a command file, and any ISQL command except REDO
can be put in a command file.  Commands in a command file can contain
parameters.  To execute commands in a command file, you by issue the
START command.  Control returns to the terminal when ISQL stops
processing commands from the file.

To maintain command files, you can use the following ISQL commands: 

   *   EDIT invokes that editor named in the EDITOR option of the SET
       command. 
   *   STORE saves the current contents of the command buffer in a file. 
   *   ERASE purges files. 
   *   LIST FILE displays files at the terminal.
   *   RENAME changes the names of files.[REV BEG]
   *   RECALL FILE places up to 32K bytes of a command file into the
       command buffer.[REV END]
   *   START executes the commands in the command file. 

Figure 2-2 summarizes the ISQL commands related to command file creation,
maintenance, and use.  Refer to "Command Buffer" and "Command History
Buffer" later in this chapter for an explanation of the buffer areas.


          Figure 2-2.  ISQL Commands Related to Command Files 

You can create command files with the editor on your system.  For
example:

     isql=> EDIT LoadDB;[REV BEG]
     TDP/3000     (A.05.03)  HP36578 Editor (c) COPYRIGHT Hewlett-Packard Co. 1990
      HPSPELL (A.01.01)
              MON, JAN 13, 1992,  9:58 AM  (DAY #13 )[REV END]
     /t LOADDB; list all 
         1 /* Command file to load PartsDBE tables                */
         2 /* Command to load table PurchDB.Parts                 */
         3 LOAD FROM EXTERNAL PartData TO PurchDB.Parts
         4 PartNumber             1      16
         5 PartName               17     30     ?
         6 SalesPrice             47     10     ?
         7 END  NO;
         8 COMMIT WORK;
         9 SELECT * FROM PurchDB.Parts;
        10 PAUSE;
     :
     /k LOADDB 
     /e 
     isql=>

You can format commands however you want to in command files, but you
must terminate each ISQL and SQL statement in the file with a semicolon.
You can also insert comments into the command file; enter a /* before the
comment starts and a */ at the end of the comment.  Comments can span
lines, and in this case you only need to put the */ at the end of the
comment.  Do not put information after column 72 in command files if you
want ISQL to process it, because ISQL reads only up to 72 bytes per line
when executing commands from a command file.

You execute command files by using the START command as follows:

     isql=> START LoadDB; 

You can include additional START commands in a command file.  The maximum
number of STARTs permitted (including the first START and all levels of
nesting) is 10.

During execution of command files, what you see at the terminal depends
on the setting of the ECHO option of the SET command and whether you have
included the PAUSE option:

   *   When ECHO is ON, ISQL displays lines at the terminal as they are
       read from the command file.  Both comments and commands in the
       file are displayed.  Regardless of the setting of this option,
       however, normal command output and error messages are displayed at
       the terminal.

   *   When you specify the PAUSE option in your command file following a
       SELECT statement, ISQL temporarily suspends command file
       processing to let you browse through and optionally print
       the query result at your terminal.  The discussion under
       "SELECTSTATEMENT" in chapter 4 shows how to browse through
       displayed query results.  When you enter END; at the terminal,
       ISQL resumes reading lines from the command file.

       If you do not specify the PAUSE option following a SELECT
       statement in a command file, ISQL displays the first group of data
       rows of the query result, then resumes processing commands from
       the file; in this case, you must include END; in your command file
       to terminate the query.

If ISQL encounters an error while processing commands from a command
file, it terminates command file processing if the CONTINUE option value
of the SET command is OFF. If CONTINUE is ON, ISQL continues processing
any subsequent commands in the file.

Creating a Profile File.   

The profile file is a special kind of command file.  When you invoke
ISQL, the commands in your profile file are executed before the input
prompt appears.  Your profile file must reside in your current group and
account and be named ISQLPRO. You can issue the following file equation
to activate a different file as your profile:

     : FILE ISQLPRO=OtherFile.SomeGrp.SomeAcct 

You can create a profile file with an editor on your system, or with ISQL
STORE or EDIT commands as follows:

     isql=> EDIT; 

     Editor is invoked. 
     ...
     /a 
        1     SET ECHO ON;
        2     CONNECT TO 'MYDBE';
        3     //
     /k ISQLPRO 
     /e 
     isql=>

This profile file contains one ISQL and one SQL statement.  The ISQL
command sets the SET ECHO option ON. The SQL statement establishes a DBE
session.

Running ISQL From a Command File.   

You can run ISQL from a command file by specifying the file name as part
of the ISQL command invocation.  Example:

     : RUN ISQL.PUB.SYS;STDIN=COMMANDS.SomeGrp.SomeAcct 

If you specify a command file and if ISQLPRO also exists, the commands in
ISQLPRO are executed first.

Running ISQL in Job Mode.   

You can execute ISQL in job mode by including ISQL and/or SQL commands in
the job file.

The following example illustrates a job file that defines the sample
DBEnvironment.  The ISQL command file named STARTDBE configures the
DBEnvironment.  Tables and other objects are defined in the command file
named CREATABS. The command file named LOADTABS contains ISQL LOAD
commands for loading the tables.  The command file named CREAINDX
contains SQL statements for creating indexes.  The command file named
CREASEC contains the SQL statements that create authorization groups and
grant users and groups specific authorities.

     !JOB JIM,MGR.HPSQL;OUTCLASS=,1
     !ISQL
     SET ECHO ON;
     START STARTDBE;
     START CREATABS;
     COMMIT WORK;
     START LOADTABS;
     START CREAINDX;
     COMMIT WORK;
     START CREASEC;
     COMMIT WORK;
     EXIT;
     TELL JIM,MGR.HPSQL Creation and Loading of PartsDBE is now done!
     !EOJ

When executing ISQL in job mode, the $STDLIST file created by the job
does not contain an echo of the commands executed in ISQL. It does
contains visible escape characters in query results.

The Command Buffer 
[REV BEG]

The command buffer is a 32K byte area for storing one or more commands
for the duration of an ISQL session.  A single command greater than 32K
bytes is truncated and is not executed.  You[REV END] put commands into
the command buffer by using the ISQL RECALL or HOLD command.  You execute
commands in the command buffer by using the ISQL START command.  Figure
2-3 summarizes the ISQL commands that operate on the command buffer.


          Figure 2-3.  ISQL Commands Related to the Command Buffer 

When you first invoke ISQL, the command buffer is empty.  You can use one
of the following commands to put information into the command buffer:

   *   HOLD puts one or more commands you key in at the terminal into the
       command buffer.  To put multiple commands into the command buffer
       with the HOLD command, type the current escape character before
       the semicolon separating commands as shown below: 

            isql=> HOLD UPDATE STATISTICS 
            > FOR TABLE PurchDB.Parts\;INFO PurchDB.Vendors; 

   *   RECALL FILE puts the contents of a file into the command buffer
       and displays it. 

   *   RECALL HISTORY puts a command from the current command history
       buffer into the command buffer and displays it.  The command
       history buffer is discussed in the following section.

You can use the RECALL CURRENT command to identify the commands currently
in the buffer.  For example:

     isql=> RECALL CURRENT; 

     UPDATE STATISTICS FOR TABLE PurchDB.Parts;INFO PurchDB.Vendors;

You can change, store, or execute the contents of the command buffer with
the CHANGE, STORE, and START commands, respectively:

   *   CHANGE replaces one string with another string, which can be null.
       You may replace one or all occurrences of the string in the
       command buffer. 

   *   STORE keeps the contents of the command buffer in a file. 

   *   START executes the command(s) in the command buffer.  The commands
       may contain parameters. 

The Command History Buffer 

When you invoke ISQL, the command history buffer is empty.  As[REV BEG]
you submit commands, ISQL puts the first 32K bytes of each[REV END]
command into this buffer.  As illustrated in Figure 2-4, ISQL and SQL
commands submitted from any source are put into the command history
buffer.  The RECALL HISTORY and LIST HISTORY commands are the only
commands not placed in this buffer.


          Figure 2-4.  ISQL Commands Related to the Command History Buffer 

The command history buffer holds the 10 most recently submitted commands.
Commands in the command history buffer are numbered from 1 through 10,
with 1 being the most recently submitted command.  When you submit
multiple commands at the same time, each command occupies a separate slot
in the history buffer.

The following three commands operate on the commands currently in the
command history buffer: 

   *   LIST HISTORY displays one or all of the commands in the command
       history buffer; you can use an @ to indicate that you want to see
       the entire command history buffer.  For example:

            isql=> LIST HISTORY @; 

            1     SELECT * FROM PurchDB.Parts;
            2     SELECT * FROM PurchDB.Vendors;
            3     SELECT * FROM PurchDB.PartInfo;
            4     CONNECT TO 'PartsDBE.SomeGrp.SomeAcct';
            5
            6
            7
            8
            9
            10

            isql=>

   *   RECALL HISTORY puts one command from the command history buffer
       into the command buffer and displays it.  For example:

            isql=> RECALL HISTORY 2; 

            SELECT * FROM PurchDB.Vendors;

            isql=>

   *   REDO lets you edit any of the commands in the command history
       buffer, then execute the edited command if you desire.  As the
       following example illustrates, you use the REDO I subcommand to
       insert data and the X subcommand to execute the edited command:

            isql=> REDO 2; 
            SELECT * FROM PurchDB.Vendors;
                                         Itatistics 
            SELECT * FROM PurchDB.Vendorstatistics;
            X 

            SELECT statement is executed. 

       Refer to the REDO command in chapter 4, "ISQL Commands," for a
       complete explanation of this command's editing features.

When you submit an ISQL command in prompting mode, as in the following
example, only the part of the command first submitted is stored in the
command history buffer:

     isql=> LOAD FROM INTERNAL; 

     Input file name> InFile; 
     'InFile' cannot be found.  (DBERR 17)
     isql=> RECALL HISTORY 1; 

     LOAD FROM INTERNAL;
     isql=> START; 

     Input file name> IntFile; 
     Table name> PurchDB.Vendors; 
     Number of rows processed is 15
     COMMIT WORK to save to DBEnvironment

     isql=>

Using a Synonym File 

You can equate ISQL and SQL commands to your own command verbs by using a
synonym file.  For example, you can define UNDO to be the synonym for
ROLLBACK WORK. When you enter UNDO, ISQL issues the SQL ROLLBACK WORK
statement.  A synonym file must be named ISQLSYN. You can use the
following file equation to equate ISQLSYN with some other file name:

     : FILE ISQLSYN=ActualSynonymFileName 

Each line in a synonym file should contain an ISQL or SQL command verb,
followed by at least one blank, then a synonym.  There can be as many as
100 synonyms per file.  The ISQL or SQL command can contain as many as 60
bytes, including embedded ASCII blanks.  Synonyms can be as long as 16
bytes, including embedded ASCII blanks.  Multiple-word verbs and synonyms
should be delimited by ASCII double quotation marks as shown below:

     isql=> EDIT; 

     Editor is invoked. 
     ...
     /a 
        1     "COMMIT WORK"   CW
        2     REDO            FIX
        3     "CONNECT TO 'PARTSDBE.SOMEGRP.SOMEACCT'"   OPEN
        4    \\
     /k ISQLPRO 
     /e 
     isql=>

To invoke a synonym that contains embedded blanks, enclose it in double
quotation marks.  For example, if a CONNECT statement is defined in your
synonym file as "OPEN DB", invoke it as follows:

     isql=> "OPEN DB"; 

MPE/iX 5.0 Documentation