HP 3000 Manuals HP 3000 Manuals
Using SQL and ISQL Commands
When you are in ISQL, you can issue both ISQL and SQL commands. However,
you can use ISQL commands only when you are in ISQL. SQL statements can
be used in application programs also.
You can also invoke other programs from ISQL and issue commands from
them. For more information, refer to "Using Other Programs within ISQL"
in this chapter.
Terminating Commands
All commands entered through ISQL must be terminated with a semicolon.
When accepting commands, ISQL does not process anything you enter until
it detects a semicolon followed by a carriage return. Notice in the
example below that, when accepting commands from a terminal and a
semicolon is not the last character on the line, ISQL displays a
line-continuation prompt (>) whenever you press Return:
isql=> UPDATE STATISTICS FOR TABLE PurchDB.Parts; UPDATE
> STATISTICS FOR TABLE PurchDB.SupplyPrice; UPDATE STATISTICS FOR
> TABLE PurchDB.Orders
> ;
isql=>
A command can span multiple lines. Conversely, you can submit multiple
commands on a single line.
When your input to ISQL is only a single character and you are executing
commands interactively or from a command file, you can omit the
semicolon, as follows:
A transaction is in progress. Commit work (Y/N)> N
END OF PROGRAM
Prompting Mode
ISQL provides a special mode of command entry called prompting mode.
Prompting mode is available to you when you submit ISQL commands from a
terminal. In prompting mode, you can enter part of an ISQL command, and
ISQL prompts you for subsequent options and parameters:
isql=> LIST;
Option (s[et], f[ile], i[nstall], or h[istory])> File;
File name> MyFile;
The contents of the named file are displayed.
Terminate both your original input and your response to each prompt with
a semicolon and Return.
In prompting mode, ISQL skips prompts when you provide more input at one
time:
isql=> LIST;
Option (s[et], f[ile], i[nstall], or h[istory])> FILE MyFile;
The contents of the named file are displayed.
isql=> LIST FILE;
File name> MyFile;
The contents of the named file are displayed.
ISQL accepts responses to its prompts either in the current language or
in NATIVE-3000. Refer to the section on "Native Language Support" in the
"Introduction" for an explanation of how to set the current language.
Aborting Commands
Respond to an ISQL prompt with a slash (/) or two slashes (//) anytime
you want to abort the command you are currently entering. For example:
isql=> ERASE;
File name> /
isql=> SELECT * FROM
> //
isql=> EXIT;
A transaction is in progress. Commit work (Y/N)> /
isql=>
Special Characters in Commands
Table 2-1 lists characters that have special meaning to ISQL.
Table 2-1. Special Characters in ISQL
---------------------------------------------------------------------------------------------
| | |
| Special | Meaning to ISQL |
| Character | |
| | |
---------------------------------------------------------------------------------------------
| | |
| ' | Delimiter |
| | |
---------------------------------------------------------------------------------------------
| | |
| " | Delimiter |
| | |
---------------------------------------------------------------------------------------------
| | |
| ; | Command terminator |
| | |
---------------------------------------------------------------------------------------------
| | |
| & | Parameter indicator |
| | |
---------------------------------------------------------------------------------------------
| | |
| -- | Comment marker |
| | |
---------------------------------------------------------------------------------------------
| | |
| /*...*/ | Comment marker |
| | |
---------------------------------------------------------------------------------------------
| | |
| \ | Escape character (initially set to \, but |
| | modifiable with the SET command) |
| | |
---------------------------------------------------------------------------------------------
When you enter SQL statements with embedded quotation marks, you need to
double all embedded quotation marks so that ISQL does not interpret the
embedded quotation marks as closing delimiters. For example:
isql=> UPDATE PurchDB.Vendors SET VendorRemarks =
> 'Don''t use this vendor unless it''s an emergency!'
> WHERE VendorNumber = 9006;
Object names included in double quotes cannot be split across lines.
You can insert comments into command files in the following ways:
* Using two hyphens (--).
* Using slashes and asterisks (/* and */).
When using the hyphens, you prefix the comment with two hyphens on each
line. The comment terminates at the end of the current line.
When using the slash and asterisk, enter /* before the comment starts and
*/ at the end of the comment. In this case, comments can span lines; you
only need to put the */ at the end of the comment.
The following example shows a command file using both types of comment
markers:
/* The following statement is used when we have a rush
order for Graphics Monitor Adaptors.*/
SELECT * FROM PurchDB.SupplyPrice --This statement selects values
WHERE PartNumber = '1723-AD-01' --from the SupplyPrice table.
AND DeliveryDays <30;
In an ISQL command, when you want special characters to have a literal
meaning to ISQL, precede the character with the escape character
currently defined in the ESCAPE option of the SET command. The escape
character is initially set to a backslash (\).
In the following example, the ISQL CHANGE command is used to edit a
command placed in the command buffer with the HOLD command. The escape
character (\) is needed so that ISQL interprets the single quotation
marks being inserted as quotation marks, not delimiters. The slashes (/)
are delimiters for the CHANGE command.
isql=> HOLD SELECT * FROM PurchDB.Parts WHERE PartNumber<1300;
isql=> CHANGE /1300/\'1300\'/;
SELECT * FROM PurchDB.Parts WHERE PartNumber<'1300';
isql=>
In the following example, the ISQL system interrupt character (:) is
used to submit a FILE command containing a semicolon. The escape
character is needed so that ISQL does not interpret the first semicolon
as a command terminator.
isql=> :FILE ISQLLP\;DEV=PP;
If any of the special characters in Table 2-1 is replaced by another
character in the character set of a native language you are using, you
must type the replacement character instead. For example, in the
Japanese Kana 8 character set, the ASCII "\" is replaced by the yen
symbol. Therefore, ISQL users whose current language is Katakana or
Japanese must type a yen symbol wherever ISQL syntax requires a "\".
Parameters in Commands
You can include parameters in your commands by using an ampersand (&) and
a digit from 1 through 100. Using a parameter lets you assign different
values to parts of a command each time you execute the command. For
example, you might use a parameter for a table name or a column name.
When you submit the command for execution, ISQL prompts you for parameter
values, as follows:
isql=> SELECT &1 FROM &2;
SELECT &1 FROM &2;
Value for parameter &1> *;
Value for parameter &2> PurchDB.Parts;
Query result is displayed.
Parameters are convenient to use in commands submitted from files or from
the command buffer. Command files and the command buffer are discussed
in the section "Using ISQL Files and Buffers" below. As in the following
example, when you use the START command to execute the commands in the
file or buffers, ISQL prompts you for parameter values:
isql=> START MyFile;
UPDATE STATISTICS FOR TABLE &1;
Value for parameter &1> PurchDB.Parts;
Command file processing continues.
Alternatively, you can submit parameter values in the START command
itself as shown here:
isql=> START MyFile (PurchDB.Parts);
UPDATE STATISTICS FOR TABLE &1;
Command file processing continues.
Within any single command file or command buffer, each parameter number
assumes one value. This feature lets you use the same parameter more
than once, but you can supply its value only once. For example:
isql=> SELECT &1 FROM &2 WHERE &1 = &3;
SELECT &1 FROM &2 WHERE &1 = &3;
Value for parameter &1> SalesPrice;
Value for parameter &2> PurchDB.Parts;
Value for parameter &3> 1000;
Query result is displayed.