First Guide to SQL Anywhere Studio
   PART 2. Getting Results with SQL Anywhere Studio
     CHAPTER 5. Using Interactive SQL       

Entering commands

The Command window appears at the bottom of the Interactive SQL screen. It is a standard edit control for typing Interactive SQL commands. If more lines are typed than will fit in this window, the window automatically scrolls. You can scroll the window using the cursor keys or the scroll bar on the right side of the window. This window can also be made larger and maximized to full screen size in the standard fashion for the operating system.

Commands are executed by pressing the execute key (f9) or you can click Execute.

Multiple commands can be entered at once by separating them with semicolons. Commands can be stored to an ASCII file or loaded from an ASCII file by choosing File->Save or Open from the menu.

  Command recall

Before using Command Recall check the system tray to make sure the Standalone Database Server is running.

1. Type the following in the command window:

   SELECT * FROM department

2. Press F9.

The contents of the department database table are displayed in the Data window. As you execute commands with Interactive SQL, they are saved in a command history. To recall commands, choose Command->Recall from the menu bar. This activates the command recall window.

The command recall window displays the first line of the last 15 commands executed. Use the cursor up and down keys to scroll through the commands.

Position the cursor on the first command that you executed, which was:

SELECT *
FROM department

and press the enter key. The cursor returns to the command window with the selected command in it. You can now re-execute that command or modify it to make a new command.

More recall keys 

The following keys can also be used to recall previous commands:

Key sequence	Description
ctrl+r	Brings up the command recall window
ctrl+p	Cycles backwards through previously executed commands. Retrieved commands are placed into the command window
ctrl+n	Cycles forward through previously executed commands

  Canceling an Interactive SQL command

The Stop button is used to cancel a command.

A Stop operation stops current processing and prompts for the next command. If a command file was being processed, you are prompted for an action to take (Stop command file, Continue, or Exit Interactive SQL). These actions can be controlled with the Interactive SQL ON_ERROR option (see ON_ERROR option).

Reported errors 

When an abort is detected, one of three different errors will be reported depending upon when the abort is detected.

1. If the abort is detected when Interactive SQL is processing the request (as opposed to the database engine), then the following message is displayed:

   ISQL command terminated by user

   Interactive SQL stops processing immediately and the database transaction is left alone.

2. If the cancel is detected by the database engine while processing a standard data manipulation command (SELECT, INSERT, DELETE, and UPDATE) and the engine is not running in bulk operations mode, then the following message is displayed.

   Statement interrupted by user.

   The effects of the current command are undone, but the rest of the transaction is left intact.

3. If the abort is detected while the database engine is processing a data definition command (CREATE, DROP, ALTER, etc.), the following message appears:

   Terminated by user -- transaction rolled back

   Since data definition commands all perform a COMMIT automatically before the command starts, the effect of the ROLLBACK is to just cancel the current command.

   This message also occurs when the database engine is running in bulk operations mode executing a command that modifies the database (INSERT, UPDATE, and DELETE). In this case, ROLLBACK cancels not only the current command, but everything that has been done since the last COMMIT. In some cases, it may take a considerable amount of time for the database engine to perform the automatic ROLLBACK.