Using SQL and ISQL Commands

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 n-computer. 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.

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:

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 cat and a date commands. The escape character is needed so that ISQL does not interpret the first semicolon as a command terminator.

   isql=> !cat isqlpro\;date;

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.