# CHAPTER 7: Dynamic SQL Command Reference

The syntax of dynamic SQL commands for the Empress SQL Precompiler for C are presented in this section. Each SQL command statement must begin with the keywords `EXEC SQL`, and must end with a semicolon (;). A statement can extend over more than one line.

- ALLOCATE DESCRIPTOR
- AUTOMATIC MEMORY
- CLOSE CURSOR
- DEALLOCATE DESCRIPTOR
- DEALLOCATE PREPARE
- DECLARE CURSOR
- DELETE
- DESCRIBE INOUT
- DESCRIBE INPUT
- DESCRIBE OUTPUT
- EXECUTE
- EXECUTE IMMEDIATE
- MEMORY ALLOCATE
- MEMORY REALLOCATE
- FETCH
- FETCH_AGAIN
- FREE
- GET DESCRIPTOR
- INCLUDE SQLDA
- OPEN
- PREPARE
- SET DESCRIPTOR
- TRIGGER ABORT
- UPDATE

## ALLOCATE DESCRIPTOR

Creates a descriptor with the specified name.

Syntax |
    EXEC SQL ALLOCATE DESCRIPTOR descriptor_name
         [WITH MAX (occurrences [, max_name_length])];
where:
`descriptor_name` | is a Precompiler identifier for a descriptor.
`occurrences` | is an integer literal or variable specifying the maximum number of descriptor item areas.
`max_name_length` | is an integer literal or variable specifying the maximum length of an item name.
SQLCODE |
`0` | OK.
`-86` | Overflow of table of allocated descriptors.
`-87` | Attempt to reallocate a descriptor.
Note | A descriptor cannot be used unless it is first allocated. The default value for occurrences is 10. The default value for max_name_length is 32.
Example 1 | The following command creates a descriptor containing 15 item areas, and the maximum length of an item name is 40 characters:
    EXEC SQL ALLOCATE DESCRIPTOR out_desc WITH MAX (15, 40);
Example 2 | The following command creates a descriptor containing item areas as specified by the value of integer variable `:occ_num`, and maximum length of an item name is `:max_num` characters:
    EXEC SQL ALLOCATE DESCRIPTOR out_desc WITH MAX (:occ_num, :max_num);

## AUTOMATIC MEMORY

Specify the memory allocation option.

Syntax |
    EXEC SQL AUTOMATIC MEMORY |ON [NOCHECK]|;
                              |     OFF    |
SQLCODE |
`0` | OK.
Note | When using pointer variables to fetch attribute values, user has the option of either allocating the space for the data explicitly, or letting Empress allocate the space. If `OFF` is specified(which is default), user should allocate space. If `ON` is specified, Empress will allocate space for pointer variables. User is responsible for deallocating space. With `NOCHECK` option, user will not be warned for not deallocating space.
Example | The following command requests Empress to allocate space for pointer variables and not warn for deallocating space:
    EXEC SQL AUTOMATIC MEMORY ON NOCHECK;

## CLOSE CURSOR

Closes the cursor specified.

Syntax |
    EXEC SQL CLOSE cursor_name;
where:
`cursor_name` | is a Precompiler identifier for the cursor.
SQLCODE |
`0` | OK.
`-73` | Close cursor error.
Note | A cursor must be closed before it can be re-opened.
Example | The following command makes cursor `c1` inactive. The records in its context cannot be retrieved unless the cursor is opened again:
       EXEC SQL CLOSE c1;

## DEALLOCATE DESCRIPTOR

Discards the specified descriptor.

Syntax |
    EXEC SQL DEALLOCATE DESCRIPTOR descriptor_name;
where:
`descriptor_name` | is a Precompiler identifier for a descriptor.
SQLCODE |
`0` | OK.
`-88` | Use of a deallocated descriptor.
Note | The memory used by the descriptor is freed. After a descriptor has been deallocated, it cannot be used, unless re-allocated again.
Example | The following command discards the descriptor `out_desc`, and releases the space used by the descriptor:
       EXEC SQL DEALLOCATE DESCRIPTOR out_desc;

## DEALLOCATE PREPARE

Discard the specified statement name.

Syntax |
    EXEC SQL DEALLOCATE PREPARE statement_name;
where:
`statement_name` | is a Precompiler identifier for a statement name.
SQLCODE |
`0` | OK.
`-60` | Undefined prepared statement name.
Note | Database resources used by the statement name are freed.
Example | The following command discards the statement name `s1`, and releases the space used by the statement name:
       EXEC SQL DEALLOCATE PREPARE s1;

## DECLARE CURSOR

Declares a cursor by associating a cursor name to a query.

Syntax |
    EXEC SQL DECLARE cursor_name  [ | SENSITIVE  | ] [ SCROLL ] CURSOR FOR
                                    | INSENSITIVE|
                                    | ASENSITIVE |

         |statement              | [FOR UPDATE];
         |STATEMENT_NAME variable|
where:
`cursor_name` | is a Precompiler identifier for the cursor.
`statement` | is either a Precompiler identifier for a prepared `SELECT` statement, or a string literal or variable containing a parameterless `SELECT` statement.
`variable` | is a Precompiler identifier.

SQLCODE

`0` | OK.
`-69` | Overflow of table of declared cursors.
`-70` | Attempt to redefine a cursor.

Note

1. A cursor name cannot be declared more than once in a program.

2. An `INSENITIVE` cursor is a cursor that effectively causes a seperate copy of its defined context to be created; the cursor accesses that copy, rather than the original context, so any changes made to the original context by other processes won't be visible to this cursor.

3. A `SENSITIVE` cursor works directly on its context, it makes no copy, so other changes made to the context might be visible to this cursor.

4. An `ASENSITIVE` cursor may or may not make a copy of its context, whether other changes to its context table will be visible is implementation-defined. The default is an `ASENSITIVE` cursor.

5. If `SCROLL` is specified, all forms of `FETCH` are allowed (e.g. `NEXT`, `PRIOR`, `FIRST`, `LAST`, `ABSOLUTE`, `RELATIVE`), otherwise, only `FETCH NEXT` is allowed.

6. The statement must be a query (`SELECT` statement).

7. If statement is an identifier, it must be a statement name which has previously been defined in a `PREPARE` statement.

8. If statement is a string literal or variable, it must contain a query which does not have parameters.

9. If the selected records will be modified, the cursor must be declared `FOR UPDATE`.

Example 1

Cursor `c1` is declared for the prepared statement `q1`, which selects an attribute from all records in table `books`.

       EXEC SQL PREPARE q1 FROM "SELECT * FROM books";
       EXEC SQL DECLARE c1 CURSOR FOR q1;

Example 2

Cursor `c2` is declared for a query specified as a string literal. It selects all attributes from records dated yesterday.

       EXEC SQL DECLARE c2 CURSOR FOR
          "SELECT * FROM weather WHERE w_date = today - 1 day";

Example 3

Cursor `c3` is declared for a query specified as a string variable. Note how the query can be built dynamically, although parameters are not allowed in statements which are not prepared.

       strcpy (str, "SELECT * FROM ");
       strcat (str, "table1");
       EXEC SQL DECLARE c3 CURSOR FOR :str;

## DELETE

Delete the current record in a dynamic cursor.

Syntax |
    EXEC SQL DELETE FROM table WHERE CURRENT OF cursor_name;
where:
`table_name` | is the name of the table to delete from.
`cursor_name` | is a Precompiler identifier for the cursor.
SQLCODE |
`0` | OK.
`1` | Record is locked.
`-78` | Attempt to delete from a cursor opened in read mode.
`-79` | Cursor must be made up of a single table.
`-80` | Wrong table name.
`-81` | Cursor contains an aggregate function.
`-82` | Internal error detected.
Note | The cursor must not reference more than one table. The cursor must not contain aggregate functions.
Example | The following command deletes from table `acid_tests`, the current record in cursor `ctests`:
       EXEC SQL DELETE FROM acid_tests WHERE CURRENT OF ctests;

## DESCRIBE INOUT

Associates selected items in a query with item areas in a descriptor.

Syntax |
    EXEC SQL DESCRIBE [INOUT] statement_name

        |USING| SQL DESCRIPTOR descriptor_name;
        |INTO |
where:
`statement_name` | is a Precompiler identifier for a prepared SQL statement.
`descriptor_name` | is a Precompiler identifier for a descriptor.
SQLCODE |
`0` | OK.
`-88` | Use of a deallocated descriptor.
`-89` | Wrong statement name.
`-90` | Descriptor contains fewer areas than required.
`-91` | Descriptor must be associated with the current cursor.
`-102` | Two or more opened cursors based on the same statement.
Note | The descriptor must be allocated before it can be described. The cursor for the query should be opened before a descriptor is described for output. The command fails if the number of selected items in the query exceeds the maximum number of items areas in descriptor. After it is described for output, the number of selected items in the query can be determined by reading the `COUNT` field. If there is more than one opened cursor based on the specified statement, the command fails, because Empress does not know which cursor the descriptor should be associated with.
Example | This statement associates selected items from query `select1` to item areas in descriptor `inout_desc`.
       EXEC SQL DESCRIBE INOUT select1 INTO SQL DESCRIPTOR inout_desc;

## DESCRIBE INPUT

Associates parameters in a prepared SQL statement with item areas in a descriptor.

Syntax |
    EXEC SQL DESCRIBE INPUT statement_name

        |USING| SQL DESCRIPTOR descriptor_name;
        |INTO |
where:
`statement_name` | is a Precompiler identifier for a prepared SQL statement.
`descriptor_name` | is a Precompiler identifier for a descriptor.
SQLCODE |
`0` | OK.
`-88` | Use of a deallocated descriptor.
`-89` | Wrong statement name.
`-90` | Descriptor contains fewer areas than required.
`-101` | Attempt to describe a statement with no parameters.
Note | The descriptor must be allocated before it can be described. The command fails if the number of parameters in the prepared SQL statement exceeds the maximum number of items areas in descriptor. After it is described for input, the number of parameters in the prepared statement can be determined by reading the `COUNT` field.
Example | This statement associates parameters in the prepared statement `update1` to item areas in descriptor `in_desc`.
       EXEC SQL DESCRIBE INPUT update1 INTO SQL DESCRIPTOR in_desc;

## DESCRIBE OUTPUT

Associates selected items in a query with item areas in a descriptor.

Syntax |
    EXEC SQL DESCRIBE [OUTPUT] statement_name

        |USING| SQL DESCRIPTOR descriptor_name;
        |INTO |
where:
`statement_name` | is a Precompiler identifier for a prepared SQL statement.
`descriptor_name` | is a Precompiler identifier for a descriptor.
SQLCODE |
`0` | OK.
`-88` | Use of a deallocated descriptor.
`-89` | Wrong statement name.
`-90` | Descriptor contains fewer areas than required.
`-91` | Descriptor must be associated with the current cursor.
`-102` | Two or more opened cursors based on the same statement.
Note | The descriptor must be allocated before it can be described. The cursor for the query should be opened before a descriptor is described for output. The command fails if the number of selected items in the query exceeds the maximum number of items areas in descriptor. After it is described for output, the number of selected items in the query can be determined by reading the `COUNT` field. If there is more than one opened cursor based on the specified statement, the command fails, because Empress does not know which cursor the descriptor should be associated with.
Example | This statement associates selected items from query `select1` to item areas in descriptor `out_desc`.
       EXEC SQL DESCRIBE OUTPUT select1 INTO SQL DESCRIPTOR out_desc;

## EXECUTE

Executes a prepared SQL statement, replacing parameters in the statement by values held by variables or descriptor areas.

Syntax |
    EXEC SQL EXECUTE statement_name

       [USING |variable {, variable}         |];
              |SQL DESCRIPTOR descriptor_name|
where:
`statement_name` | is a Precompiler identifier for a prepared SQL statement.
`descriptor_name` | is a Precompiler identifier for a descriptor.
`variable` | is a variable, which may optionally be followed by a control variable.
SQLCODE |
`0` | OK.
`-60` | Undefined prepared statement name.
`-61` | Incorrect number of actual parameters.
`-64` | Wrong data type in `USING` clause.
`-65` | Error detected by the SQL processor.
`-66` | Attempt to execute a `SELECT` statement.
`-67` | Signal caught during execution of a dynamic statement.
`-68` | Unidentified error detected during execution of a dynamic statement.
`-88` | Use of a deallocated descriptor.
`-96` | Output descriptor used.
`-97` | Descriptor has not been prepared for specified statement.
Note | The statement cannot be a query (`SELECT` statement). The statement cannot be a transaction command (such as `START TRANSACTION`). The statement name must already be defined by a `PREPARE` statement. The `USING` clause allows parameters in the prepared statement to be replaced by values held by variables or held in descriptor areas. The number of variables in the `USING` clause must match the number of parameters in the prepared statement. If a descriptor is used, it must be an input descriptor described for the specified statement. If the statement is a call statement or is an assign statement, then after execution, the result will be returned if there is any.
Example 1 | In the example below, a SQL statement to create a named index on an attribute of table messages is first prepared. The statement is then executed, with a `USING` clause to specify the name of the index, and the attribute to index on.
       EXEC SQL PREPARE index1 FROM "CREATE INDEX ? ON messages (?)";
       EXEC SQL EXECUTE index1 USING :idx_name, :idx_attr;
Example 2 | This command will execute the prepared statement `ps1`, substituting its parameters with data values contained in descriptor `desc1`.
       EXEC SQL EXECUTE ps1 USING SQL DESCRIPTOR desc1;

## EXECUTE IMMEDIATE

Executes a SQL statement which does not contain parameters.

Syntax |
    EXEC SQL EXECUTE IMMEDIATE sql_statement;
where:
`sql_statement` | is a string literal or character array variable containing the SQL statement to be executed.
SQLCODE |
`0` | OK.
`-65` | Error detected by the SQL processor.
`-66` | Attempt to execute a `SELECT` statement.
`-67` | Signal caught during execution of a dynamic statement.
`-68` | Unidentified error detected during execution of a dynamic statement.
Note | The statement cannot be a query (`SELECT` statement). The statement cannot be a transaction command (such as `START TRANSACTION`).
Example 1 | The following command will create a table with the attributes specified:
       EXEC SQL EXECUTE IMMEDIATE "CREATE messages
            (sender    CHAR(50,1),
             receiver  CHAR(50,1),
             timestamp TIME(2),
             message   TEXT(20,20,20,1))";
Example 2 | This command will execute a statement contained in string variable `str_var`.
       EXEC SQL EXECUTE IMMEDIATE :str_var;

## MEMORY ALLOCATE

Allocates space (memory) for pointer variables.

Syntax |
    EXEC SQL MEMORY ALLOCATE variable(|size    |);
                                      |variable|
where:
`variable` | is a pointer variable.
`size` | is a size of memory allocated to a pointer variable.
SQLCODE |
`0` | OK.
`-1` | Memory allocation failed.
Note | This statement requires `EXEC SQL MEMORY FREE` to be used in order to free the allocated space.
Example |
    ...
    char* ptr;
    ...
    EXEC SQL MEMORY ALLOCATE :ptr (20);
    ...
    EXEC SQL FETCH cursor1 into :ptr;
    ...
    EXEC SQL MEMORY FREE :ptr;
    ...

## MEMORY REALLOCATE

Changes the size of space (memory) allocated for pointer variables.

Syntax |
    EXEC SQL MEMORY REALLOCATE variable(|size    |);
                                        |variable|
where:
`variable` | is a pointer variable.
`size` | is a modified size of memory reallocated to a pointer variable.
SQLCODE |
`0` | OK.
`-1` | Memory reallocation failed.
Note | This statement requires `EXEC SQL MEMORY FREE` to be used in order to free the reallocated space.
Example |
    ...
    char* ptr;
    ...
    EXEC SQL MEMORY ALLOCATE :ptr (20);
    ...
    EXEC SQL MEMORY REALLOCATE :ptr (40);
    ...
    EXEC SQL FETCH cursor1 into :ptr;
    ...
    EXEC SQL MEMORY FREE :ptr;
    ...

## FETCH

Places the cursor on the designated record in the defined context, makes that record current, and retrieves values of selected items into variables or descriptor areas.

Syntax 1 |
    EXEC SQL FETCH [|NEXT           |[AGAIN][FROM]] cursor_name
                    |PRIOR          |
                    |FIRST          |
                    |LAST           |
                    |ABSOLUTE offset|
                    |RELATIVE offset|
         INTO |variable {, variable }        |;
              |SQL DESCRIPTOR descriptor_name|
Syntax 2 |
    EXEC SQL FETCH  | emptrig_cursor_old |  [attr {, attr}]
                    | emptrig_cursor_new |
         INTO |variable {, variable }        |;
              |SQL DESCRIPTOR descriptor_name|
where:
`cursor_name` | is a Precompiler identifier for a defined cursor.
`offset` | is an integer literal or a variable specifying the number of records.
`variable` | is a variable, which may optionally be followed by a control variable.
`descriptor_name` | is a Precompiler identifier for a descriptor.
`emptrig_cursor_old` | is a reserved Precompiler identifier for a cursor used in a trigger.
`emptrig_cursor_new` | is a reserved Precompiler identifier for a cursor used in a trigger.
`attr` | is the name of an attribute.
SQLCODE |
`0` | OK.
`1` | Locked record.
`100` | No more records.
`-74` | Wrong number of variables/descriptor areas.
`-76` | Wrong type of variable.
`-77` | Internal conversion error.
`-88` | Use of a deallocated descriptor.
`-97` | Descriptor has not been prepared for the specified statement.
`-98` | Input descriptor used.
`-99` | Attempt to use an inactive cursor.
SQLWARN1 |
`''` | Without truncation
'`W`' | Truncation performed
Note | If the fetched record is not accessible (i.e., it is locked), it will become the current record, but the values of the items selected will not be read into the variables or descriptor areas. If n is the offset, `FETCH ABSOLUTE` offset moves the cursor to the n-th record (counting backward from the end if n is a negative number). If n is the offset, `FETCH RELATIVE` offset moves the cursor to the n-th record from the current record (counting backward if n is a negative number). `FETCH FIRST` moves the cursor to the first record. `FETCH LAST` moves the cursor to the last record. The number of variables in the `INTO` clause must be equal to the number of items selected. If variables are used, conversion is automatically performed from the data type of the selected items to the data type of the host variables. This allows numeric values to be retrieved in external (string) format. If a descriptor is used, it must be an output descriptor described for the statement used to define this cursor. A control variable (or the `DA_CNTRL` field of a descriptor area) will be set to a negative value if the item fetched is `NULL`. When fetching into an array variable, truncation may occur if the array size is smaller than the data to be read. In such a case, `SQLWARN1` is set to '`W`', and the control variable is set to a number indicating the real length of the data, if that length can be represented by a C short. For reserved cursor identifiers `emptrig_cursor_old` and `emptrig_cursor_new`, `DECLARE CURSOR`, `OPEN CURSOR` and `CLOSE CURSOR` commands cannot be used. Furthermore, `FETCH` options, such as `NEXT`, `PRIOR`, `ABSOLUTE` and `RELATIVE` also cannot be used.
Example 1 | The next record in the context selected by cursor `c1` is made current, and the values of selected items are copied into variables `v1` and `v2`.
       EXEC SQL FETCH c1 INTO :v1, :v2;
Example 2 | The next record in cursor `c2` is made current, and the values of selected items are copied into descriptor `d2`.
       EXEC SQL FETCH c2 INTO SQL DESCRIPTOR d2;

## FETCH_AGAIN

Reads the current record and retrieves values of selected items into variables or descriptor areas.

Syntax |
    EXEC SQL FETCH_AGAIN cursor_name

       [INTO |variable {, variable }        |];
             |SQL DESCRIPTOR descriptor_name|
where:
`cursor_name` | is a Precompiler identifier for a defined cursor.
`descriptor_name` | is a Precompiler identifier for a descriptor.
`variable` | is a variable, which may optionally be followed by a control variable.
SQLCODE |
`0` | OK.
`1` | Locked record.
`100` | No more records.
`-74` | Wrong number of variables/descriptor areas.
`-76` | Wrong type of variable.
`-77` | Internal conversion error.
`-88` | Use of a deallocated descriptor.
`-97` | Descriptor has not been prepared for the specified statement.
`-98` | Input descriptor used.
`-99` | Attempt to use an inactive cursor.
SQLWARN1 |
`''` | Without truncation
'`W`' | Truncation performed
Note | Normally used after a `FETCH` command encountered a locked record, to attempt to re-read that record. The number of variables in the `INTO` clause must be equal to the number of items selected. If variables are used, conversion is automatically performed from the data type of the selected items to the data type of the host variables. This allows numeric values to be retrieved in external (string) format. If a descriptor is used, it must be an output descriptor described for the statement used to define this cursor. A control variable (or the `DA_CNTRL` field of a descriptor area) will be set to a negative value if the item fetched is `NULL`. When fetching into an array variable, truncation may occur if the array size is smaller than the data to be read. In such a case, `SQLWARN1` is set to '`W`', and the control variable is set to a number indicating the real length of the data, if that length can be represented by a C short.
Example 1 | The current record in the context selected by cursor `c1` is read again, and the values of selected items are copied into variables `v1` and `v2`.
       EXEC SQL FETCH_AGAIN c1 INTO :v1, :v2;
Example 2 | The current record in cursor `c2` is read again, and the values of selected items are copied into descriptor `d2`.
       EXEC SQL FETCH_AGAIN c2 INTO SQL DESCRIPTOR d2;

## FREE

Free space that has been automatically allocated to pointer variables. When the `AUTOMATIC MEMORY` option is `ON`, Empress will allocate space to pointer variables when retrieving data. It is programmer's responsibility to deallocate these spaces.

Syntax |
    EXEC SQL MEMORY FREE variable {,variable};
where:
`variable` | is a variable.
SQLCODE |
`0` | OK.
Note | This statement is a replacement for `mpdfree` in the previous versions. `EXEC SQL MEMORY FREE` must not be passed a variable which does not have space allocated to it.
Example |
    ...
    char* ptr;
    ...
    EXEC SQL AUTOMATIC MEMORY ON;
    ...
    EXEC SQL FETCH cursor1 into :ptr;
    ...
    EXEC SQL FREE :ptr;
    ...

## GET DESCRIPTOR

Reads values of fields in a descriptor.

Syntax |
    EXEC SQL GET DESCRIPTOR descriptor_name
|  |  | `|var = COUNT |VALUE item var = field_name {, var = field_name}` | `|; |`
where:
`descriptor_name` | is a Precompiler identifier for a descriptor.
`item` | is an integer constant or variable specifying the item area to access.
`field_name` | is one of the following identifiers:
|  |  | `DA_TYPE` | Code for data type
|  |  | `DA_NAME` | Name of item
|  |  | `DA_NLEN` | Length of name
|  |  | `DA_DATA` | Data value
|  |  | `DA_CNTRL` | Equivalent to a control variable
`var` | is an embedded variable
SQLCODE |
`0` | OK.
`-76` | Wrong type of variable.
`-77` | Internal conversion error.
`-88` | Use of a deallocated descriptor.
`-92` | Wrong specification of descriptor area.
`-94` | Invalid descriptor.
`-95` | Wrong data type of variable.
4. Note | If the descriptor was described for input, and data has not been assigned to the fields, only the `COUNT` field contains valid information. If the descriptor was described for output and used for fetching, all fields contain valid information. The `COUNT` field holds an integer value indicating the number of active item areas in the descriptor. The `DA_TYPE` field holds an integer value representing the data type of the attribute or selected item. The following constants are used to identify the data type:
   |  |  | `DTGCHAR` | Generic character data type
   |  |  | `DTGINTEGER` | Generic integer data type
   |  |  | `DTGFLOAT` | Generic float data type
   |  |  | `DTGTEXT` | Empress `TEXT` data type
   |  |  | `DTGBULK` | Empress `BULK` data type
   |  |  | `DTGDATE` | Empress `DATE` data type
   |  |  | `DTGTIME` | Empress `TIME` data type
   |  |  | `DTGDECIMAL` | `DECIMAL` and `DOLLAR` data type
The `DA_NAME` field holds a character string specifying the name of the selected item. If the selected item is an attribute, `DA_NAME` holds the name of the attribute. If the selected item is an expression, `DA_NAME` holds the string "`EXPRESSION_n`" (where n is an integer). If the selected item had a `PRINT` clause, `DA_NAME` contains the header specified. The `DA_NLEN` field holds an integer value indicating the number of characters in the name of the selected item. The `DA_DATA` field holds the actual data obtained by the `FETCH` statement. The variable used to get the contents of this field must be of a data type which is appropriate for `DA_TYPE`. Empress will attempt to perform data type conversions if possible (e.g., when retrieving `DTGDECIMAL` data using a C float variable). The `DA_CNTRL` field is used as a control variable. If it is negative, then the data retrieved is `NULL`. The variable used to access this field must be a C short.
Example 1 | Integer variable `num` is assigned the number of active item areas in descriptor `d1`. Since `d1` is associated to statement `in1`, which contains two parameters, `num` will be assigned the integer value 2.
       EXEC SQL PREPARE in1 FROM "INSERT INTO t1 (a, b) VALUES (?, ?)";
       EXEC SQL DESCRIBE INPUT in1 INTO SQL DESCRIPTOR d1;
       EXEC SQL GET DESCRIPTOR d1 :num = COUNT;
Example 2 | In the example below, a descriptor is used to describe a query where the attributes to be retrieved are unknown. The `COUNT` field is used to find out the number of selected items. After fetching into a descriptor, the name of the first attribute is assigned to variable `aname`, the value of the attribute is assigned to variable `aval`, the data type code is assigned to variable `atype`, and variable `anul` is used to check for `NULL` values.
       EXEC SQL PREPARE sel1 FROM "SELECT * FROM tab1";
       EXEC SQL DESCRIBE OUTPUT sel1 INTO SQL DESCRIPTOR d2;
       EXEC SQL GET DESCRIPTOR d2 :num = COUNT;
       EXEC SQL DECLARE c1 CURSOR FOR sel1;
       EXEC SQL OPEN c1;
       EXEC SQL FETCH c1 INTO SQL DESCRIPTOR d2;
       EXEC SQL GET DESCRIPTOR d2 VALUE 1 :aname=DA_NAME, :aval=DA_DATA,
                :atype=DA_TYPE, :anul=DA_CNTRL;

## INCLUDE SQLDA

Includes the definition of SQL descriptors. This statement must be placed in the global section of your program before you can use SQL descriptors.

Syntax |
    EXEC SQL INCLUDE SQLDA;
SQLCODE | No meaning.
Note | This statement is not required if descriptors are not used. It is usually placed after the `INCLUDE SQLCA` statement.

## OPEN

Opens the named cursor for retrieving multiple records, replacing parameters in the `SELECT` statement by values held by variables or descriptor areas.

Syntax |
    EXEC SQL OPEN cursor_name

       [USING |variable {, variable}         |];
              |SQL DESCRIPTOR descriptor_name|
where:
`cursor_name,` | is a Precompiler identifier for a defined cursor
`descriptor_name` | is a Precompiler identifier for a descriptor.
`variable` | is a variable, which may optionally be followed by a control variable.
SQLCODE |
`0` | OK.
`-60` | Undefined prepared statement name.
`-61` | Incorrect number of actual parameters.
`-64` | Wrong data type in `USING` clause.
`-71` | Attempt to open a previously opened cursor.
`-72` | Open cursor error.
`-88` | Use of a deallocated descriptor.
`-90` | Descriptor contains fewer areas than required.
`-96` | Output descriptor used.
`-97` | Descriptor has not been prepared for specified statement.
`-100` | Attempt to open a cursor based on non-`SELECT` statement.
Note | The cursor name must already be defined. The `USING` clause allows parameters in a prepared statement to be replaced by values held by variables or held in descriptor areas. The number of variables in the `USING` clause must match the number of parameters in the prepared statement. If a descriptor is used, it must be an input descriptor described for the statement used to define this cursor.
Example 1 | Opens cursor `c1`. Since a `USING` clause is not specified, the query related to the cursor must not have parameters.
       EXEC SQL OPEN c1;
Example 2 | Opens cursor `c2`, substituting three variables into the query statement, which must have exactly three parameters.
       EXEC SQL OPEN c2 USING :v1, :v2, :v3;
Example 3 | Opens cursor `c3`
       EXEC SQL OPEN c3 USING SQL DESCRIPTOR d3;

## PREPARE

Prepares a SQL statement for later use. It associates a statement name to the SQL statement which may contain parameters represented by question marks (`?`).

Syntax |
`EXEC SQL PREPARE statement_name FROM` | `|sql_statement |call_statement` | `|; |`
where:
`statement_name` | is a Precompiler identifier used for later reference to this statement.
`sql_statement` | is a string literal or character array variable containing the SQL statement to be executed.
`call_statement` | is:
`CALL` | `|expr |procedure_name [([expr {, expr}])]` | `|; |`
SQLCODE |
`0` | OK.
`-62` | Invalid `PREPARE` statement.
`-63` | Overflow of table of prepared statements.
Note | A SQL statement containing parameters must be prepared before use. The statement name may have been defined previously. If this is the case, the old definition is lost, and the statement name is associated with the new SQL statement.
Example 1 | This command prepares a `DROP` statement for later execution. A parameter is used to allow the records to be deleted to be specified at execution time.
        EXEC SQL PREPARE drop_t1 FROM
          "DROP FROM books WHERE title MATCH ?";
Example 2 | The following statements illustrate how a SQL statement can be built dynamically before preparation:
       strcpy (sqls, "SELECT ");
       strcat (sqls, "title, publisher, price ");
       strcat (sqls, "FROM books");
       EXEC SQL PREPARE query1 FROM :sqls;
Example 3 | This command prepares a `CALL` statement to the procedure named `proc` for later execution:
       SQL EXEC PREPARE call_s1 FROM "CALL proc (?, ?, ?)";

## SET DESCRIPTOR

Assigns values to fields in a descriptor, for the purpose of assigning values to parameters in a prepared statement.

Syntax |
    EXEC SQL SET DESCRIPTOR descriptor_name
         VALUE item field_name = val  {, field_name = val};
where:
`descriptor_name` | is a Precompiler identifier for a descriptor.
`item` | is an integer constant or variable specifying the item area to access.
`field_name` | is one of the following identifiers:
|  |  | `DA_DATA` | Data value
|  |  | `DA_CNTRL` | Equivalent to a control variable
`val` | val is a literal value or variable
SQLCODE |
`0` | OK.
`-88` | Use of a deallocated descriptor.
`-92` | Wrong specification of descriptor area.
`-93` | Attempt to update an output descriptor.
Note | The descriptor must be described for input. If it was described for output, its fields cannot be assigned values. The `DA_DATA` field should be assigned the actual value which will replace a parameter in the prepared statement. Empress will attempt to perform data type conversion if necessary (e.g., when assigning a C double variable to a `DOLLAR` attribute, or assigning a character string to a `DATE` attribute). The `DA_CNTRL` field is used as a control variable. If it is assigned a negative value, then the data is `NULL`. The variable used for this field must be a C short.
Example | The statements below would be equivalent to the following SQL statement: `INSERT INTO tab1 (name, code) VALUES ('John', null)`
       EXEC SQL PREPARE in1 FROM "INSERT INTO tab1 (?, ?) VALUES (?, ?)";
       EXEC SQL DESCRIBE INPUT in1 INTO SQL DESCRIPTOR d1;
       EXEC SQL SET DESCRIPTOR d1 VALUE 1 DA_DATA="name";
       EXEC SQL SET DESCRIPTOR d1 VALUE 2 DA_DATA="code";
       i = 3;
       strcpy (str, "ÕJohnÕ");
       EXEC SQL SET DESCRIPTOR d1 VALUE :i DA_DATA=:str;
       EXEC SQL SET DESCRIPTOR d1 VALUE 4 DA_CNTRL=-1;
       EXEC SQL EXECUTE in1 USING SQL DESCRIPTOR d1;

## TRIGGER ABORT

Aborts the trigger PSM procedure.

Syntax | `EXEC SQL TRIGGER ABORT;`
SQLCODE | No meaning.
Note | It can only be used in trigger PSM procedure.

## UPDATE

Update the current record in a dynamic cursor.

Syntax |
    EXEC SQL UPDATE |table_name| SET |attr = variable {, attr = variable}|
                    |CURRENT   |     |attr {, attr} VALUES attr {, attr} |

         WHERE CURRENT OF |cursor_name       |;
                          |emptrig_cursor_new|
where:
`table_name` | is the name of the table.
`attr` | is the name of an attribute to update.
`variable` | is a variable, which may optionally be followed by a control variable.
`cursor_name` | is a Precompiler identifier for the cursor.
`emptrig_cursor_new` | is a reserved Precompiler identifier for the cursor used in a trigger.
SQLCODE |
`0` | OK.
`1` | Record is locked.
`-78` | Attempt to update a cursor opened in read mode.
`-79` | Cursor must be made up of a single table.
`-80` | Wrong table name.
`-81` | Cursor contains an aggregate function.
`-83` | Wrong attribute name.
`-84` | Failed `UPDATE` statement.
`-85` | Wrong data type of variable.
Note | The cursor must not reference more than one table. The cursor must not contain aggregate functions. `table_name` or a keyword `CURRENT` can be used to reference to a table. `emptrig_cursor_old` cannot be used in `UPDATE` command. If `UPDATE` command is used in PSM procedure which is fired in `AFTER INSERT`, `AFTER UPDATE` or `AFTER DELETE` trigger, it will not take any effect. Reserved Precompiler cursor identifiers `emptrig_cursor_old` and `emptrig_cursor_new` can be used only in a trigger PSM procedure.
Example 1 | The command below updates table `tab1`, assigning the string '`Jerry`' to attribute name for the current record in cursor `ct1`.
       strcpy (name_var, "Jerry");
       EXEC SQL UPDATE tab1 SET name = :name_var WHERE CURRENT OF ct1;
Example 2 | The command below updates table `acid_tests`, assigning the string '`black`' to attribute `color_change`, and integer value 7 to attribute `reaction_time` for the current record in cursor `c1`.
       strcpy (tab, "acid_tests");
       strcpy (att1, "color_change");
       strcpy (att2, "reaction_time");
       strcpy (str1, "black");
       val2 = 7;
       EXEC SQL UPDATE :tab SET :att1 = :str1, :att2 = :val2
       WHERE CURRENT OF c1;