Running command files

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

Running command files

This section describes how to use the Interactive SQL command window to enter multiple commands at a time and how to process files consisting of a set of commands.

Entering multiple statements

SQL statements can get quite large. You have already seen how to use the editor to enter statements on several lines. The Interactive SQL environment also allows multiple commands to be entered at the same time. This is done by ending each statement with a semi-colon (;).

Example: entering multiple statements

To enter multiple statement in the Interactive SQL Command window

Try entering the following three commands into the Command window.

UPDATE employee
SET dept_id = 400,
    manager_id = 1576
WHERE emp_id = 467;

UPDATE employee
SET dept_id = 400,
    manager_id = 1576
WHERE emp_id = 195;

SELECT *
FROM employee
WHERE emp_id IN ( 195, 467 )

Press the execute key (F9). All three statements are executed. After execution, the commands are left in the Command window.

Saving statements as command files

You can save commands you enter in Interactive SQL to a command file. This keeps a permanent record of the SQL commands so they can be used later if you wish.

To save statements as a command file:

Choose File ->Save As from the menu bar. You are then prompted for a filename.
Type a file name (for example, transfer.sql) and press enter.
The command file can be run using the Interactive SQL READ command, but you should rollback the changes first. Press the escape key to clear the editor and then execute the ROLLBACK WORK command.
Now enter the following command:
```
READ transfer.sql
```
This command executes the command file transfer.sql which contains the three commands that we saved previously. As each command is executed, it flashes up in the Command window.

What are command files?

Command files are just ASCII files containing SQL statements as you see them in the editor. You can use any editor you like to create command files. You can include comment lines along with the SQL statements to be executed. Command files are also commonly called scripts.

Executing command files

You can execute command files in the following ways:

You can use the Interactive SQL READ command to execute command files.

The following statement executes the file temp.sql:
```
READ temp.sql
```
You can load a command file into the Interactive SQL Command window and execute it directly from there.

You load command files back into the Command window by choosing File->->Open. Enter transfer.sql when prompted for the file name.
You can supply a command file as a command line argument for Interactive SQL.

The Command window in Interactive SQL has a limit of 500 lines. For command files larger than this, you should use a generic editor capable of handling large files. The READ command has no limit on the number of lines that can be read.

Command files with parameters

An example of a command file that would take a parameter is a command file to show the department an employee belongs to, providing the employee's name as a parameter.

To create a command file with parameters:

Create a command file as listed below.

The PARAMETERS command is used to give names to the parameters passed to a command file. In this case, we are giving the first parameter the name employee_name. The parameters are then used in the rest of the command file by enclosing them in braces ({}). Save the command file to emp_dept.sql.
```
parameters employee_name;
SELECT emp_lname, dept_name
FROM employee
NATURAL JOIN department
WHERE emp_lname = {employee_name};
```
Run this command file by typing:
```
READ emp_dept.sql
```
You will be prompted for the employee_name. Enter the following value, including the single quotes:
```
'Whitney'
```
You should now see that the employee with surname Whitney is in the R&D department.

Parameters specifies on the READ command

Parameters can also be specified on the READ command. Try the following command:

READ emp_dept.sql 'Whitney'

In this case you have specified the parameter on the READ command, so Interactive SQL will not prompt for it. Interactive SQL will only prompt for parameters that are named in the PARAMETERS command but are not supplied on the READ command.

Note:

The Parameters command is only allowed in command files and cannot be executed from the command window.