CHAPTER 4: Command Reference

EXEC SQL BEGIN DECLARE SECTION;

No meaning.

1. The declaration section may be located anywhere allowed for variable declarations by the host language syntax.

2. More than one declaration section may be used in a given program file. If more than one declaration section is used, the variable names across declaration sections should be unique.

3. It is recommended that the declaration section be global to the file containing the Empress SQL Precompiler statements.

4. Every BEGIN DECLARE SECTION must have a matching END DECLARE SECTION.

EXEC SQL BEGIN DECLARE SECTION;
   int i;
   short j;
   float f;
   char str [10];
EXEC SQL END DECLARE SECTION;

EXEC SQL :var = [CALL] expr;

Aggregate functions and operators can not be called using this statement.

EXEC SQL CANCEL TRANSACTION;

No meaning.

1. CANCEL TRANSACTION rolls back all transactions on all databases.

2. If your program is within a retrieval loop, the current record will no longer be valid. However, the next FETCH statement will return the proper next record.

EXEC SQL CLOSE cursor_name;

where:

EXEC SQL CLOSE_TABLE [table {, table}];

No meaning.

If no table name is specified in the CLOSE_TABLE command, all open tables will be closed.

The following command will close all open tables:

   EXEC SQL CLOSE_TABLE;

The following command will close only the named tables, loans and personnel:

   EXEC SQL CLOSE_TABLE loans, personnel;

EXEC SQL COMMIT TRANSACTION;

No meaning.

EXEC SQL DATABASE IS database;

No meaning.

1. The database name may include a full file specification. In this case, the database name must be enclosed in quotes.

2. DATABASE IS cannot be used in PSM routines and triggers.

EXEC SQL DECLARE cursor_name CURSOR

   FOR SELECT [ |DISTINCT |] [attr {, attr}]
                |UNIQUE   |

   FROM table {, table}

   [where_clause]

   [sort_clause]

where:

No meaning.

1. In order to open and use a cursor it must first be declared. After the cursor is declared it must be opened before it can be used.

2. A cursor may be declared only once within a program.

3. If you want to modify as well as read the selected rows, the cursor must be declared FOR UPDATE or FOR DEFERRED.

4. Access to selected rows can be ordered using the SORT option.

The command:

   EXEC SQL DECLARE c1 CURSOR FOR
      SELECT FROM personnel, loans
      WHERE personnel.name='Jones'
         AND personnel.name = loans.name;

declares a cursor called c1 that acts on selected records from the personnel and loans table. The criterion for record selection is defined in the WHERE clause. This context will consist of personnel records where the name attribute is equal to Jones and personnel and loans records where the name attributes are equivalent.

1. All of the rows that satisfy the WHERE clause will be deleted unless a cursor is specified in the WHERE clause. If a cursor is specified, only the current row will be deleted. If no row is current, no record will be deleted and the appropriate SQLCODE returned.

2. A variable SQLCNT can be used for checking the number of deleted records.

The command:

   EXEC SQL DELETE FROM loans WHERE amount < 50;

will delete from the loans table all records in which the attribute amount has a value less than 50.

The command:

   EXEC SQL DELETE FROM loans WHERE CURRENT OF c;

will delete the current record from the context associated with the cursor named 'c'. If no record is current, none will be deleted.

EXEC SQL END DECLARE SECTION;

No meaning.

Every BEGIN DECLARE SECTION must have a matching END DECLARE SECTION.

EXEC SQL EXIT;

No meaning.

1. You should always execute EXIT before leaving your program.

2. No further SQL operations may be done, including INIT.

3. EXIT cannot be used in PSM routines and triggers.

EXEC SQL FETCH [|NEXT |[AGAIN][FROM]] cursor_name
                |PRIOR|

     attr {, attr} INTO variable {, variable};

If the next record in the context is not accessible (i.e., it is locked), it will be made the current record but the values of its attributes will not be read into the program variables. You can attempt to re-read these attributes with the FETCH_AGAIN command.

The command:

   EXEC SQL FETCH c1
      name, credit_limit
      INTO :name, :crelim;

will make the next available record in the context associated with the cursor named c1 the current record, retrieve the values for the attributes name and credit_limit, and store them in the program variables :name and :crelim.

EXEC SQL FETCH_AGAIN cursor_name attr {, attr}
     INTO variable {, variable};

FETCH_AGAIN does not affect the status of the current record. After a FETCH_AGAIN the current record remains the current record.

The command:

   EXEC SQL FETCH_AGAIN c1
      name, credit_limit
      INTO :name, :crelim;

will attempt to retrieve the values for the attributes name and credit_limit and store them into the program variables :name and :crelim from the current record in the context associated with the cursor named c1.

EXEC SQL INCLUDE SQLCA

No meaning.

You should have an INCLUDE SQLCA statement as the first Precompiler statement of your program.

EXEC SQL INIT;

1. INIT must be the first executable Empress Precompiler command that you use in a program, but this rule is not enforced by the Precompiler.

2. INIT may be called several times during a program; subsequent invocations have to be proceeded by EXIT call.

3. INIT cannot be used in PSM routines and triggers.

EXEC SQL INSERT INTO table
     (attr {, attr}) VALUES (value {, value});

where:

|program variable|
|constant        |

1. If an attribute in the table is not named in the list of attributes in the INSERT command, a NULL will be inserted for it.

2. You can use a control variable to insert a NULL at runtime. If the value is :variable:control_variable, at runtime control_variable is tested and if a NULL is inserted, else the value contained in the variable is inserted.

The command:

   EXEC SQL INSERT INTO personnel
      (name, number, credit_limit)
       VALUES ("Mosca", 10, 1500);

will insert a new row into the personnel table setting the value of the attribute name to Mosca, the attribute number to 10 and the attribute credit_limit to 1500.

EXEC SQL OPEN cursor_name;

1. Opening a cursor causes the SELECT statement defined in its declaration to be initialized.

2. A cursor can be opened many times within a single program. However, a cursor must be closed before it can be re-opened.

For every executed command, the Precompiler verifies that all of the required tables have been opened and, if so, that they have been opened in the correct mode (read, update or dirty read). If the tables are not open, they will be opened in a mode corresponding to the requirements of your SQL command.

The command:

   EXEC SQL OPEN_TABLE
      loans FOR READ,
      personnel FOR UPDATE;

will open the loans table for read operations and the personnel table for update operations.

EXEC SQL SELECT attr {, attr} FROM table {, table}
   INTO variable {, variable}
   [where_clause];

1. SELECT retrieves one row of data from the specified table or view. If a WHERE clause is used, the first row that satisfies the WHERE clause is selected. If you want to access all the rows that satisfy the WHERE clause, you must use a cursor. (See OPEN CURSOR command.)

2. On retrieval, the value of the first selected attribute will be stored as the contents of the first named program variable. The value of the second selected attribute will be stored as the contents of the second named program variable and so on.

3. In a SELECT which includes a join, the attribute which forms the join cannot be specified using a program variable on the right-hand side of the join.

The command:

   EXEC SQL SELECT name, date FROM personnel
      INTO :var1, :var2 WHERE name='Jones';

will retrieve the values of the attributes name and date from the personnel table. The values will be taken from the first record that meets the conditions of the WHERE clause (i.e., that has the value Jones in the attribute name). They will then be stored in the program variables :var1 and :var2, respectively.

Transactions may be started from within retrieval loops.

EXEC SQL UPDATE table SET attr = value {, attr = value}

     |where_clause                |;
     |WHERE CURRENT OF cursor_name|

1. All of the rows that satisfy the WHERE clause will be modified unless a cursor is specified in the WHERE clause. If a cursor is used, only the current row will be modified.

2. A value to be entered can be: a constant, a user variable, or NULL for deleting the contents of an attribute.

3. A value and an attribute must be of the same type, or the Precompiler will return an error code.

4. You can use a control variable to insert a NULL at runtime. If the value is :variable : ctrl, at runtime ctrl is tested. If it is greater than 0, a NULL or the value contained in the variable is inserted. (See the section on "Control Variables" in this chapter for more information.)

5. A variable SQLCNT can be used for checking the number of updated records.

The command:

   EXEC SQL UPDATE personnel
      SET credit_limit=100, date='19851119'
      WHERE name='Jones';

will update the attributes credit_limit and date with the supplied values, for all of the records in the personnel table where the attribute name contains the value Jones.

The command:

   EXEC SQL UPDATE personnel
      SET credit_limit=100, date='19851119'
      WHERE CURRENT OF c;

will update only the current record in the context associated with the cursor named c.

where operand is:

No meaning.

1. MATCH is a dual-case pattern match, while SMATCH is single-case. The "!" preceding a match keyword selects all records which do not match.

2. Note that NOT is not equivalent to "!". "!" applies to an operator, but NOT applies to an expression. When NOT is used in front of an operator its effect is as if it were in front of the expression.

3. For more information on the WHERE clause and its components, see the chapter on WHERE clauses in the Empress SQL: Reference manual.

No meaning.

1. The action specified for SQLERROR will be used every time an error condition is detected. The action specified for SQLWARNING will be used whenever some exception condition is detected. The action specified for NOT FOUND will be used whenever a row could not be returned or found.

2. CONTINUE and GOTO are the actions to be taken in the case of an error condition, exception condition or row-not-found condition.

3. You can have several WHENEVER clauses in a program to change the action to be taken.

4. The WHENEVER statement will apply to all errors encountered from the statement to the next WHENEVER statement with the same operator (SQLERROR, SQLWARNING or NOT FOUND) or to the end of the procedure.