CHAPTER 7: Dynamic SQL Command Reference

EXEC SQL ALLOCATE DESCRIPTOR descriptor_name
     [WITH MAX (occurrences [, max_name_length])];

where:

1. A descriptor cannot be used unless it is first allocated.

2. The default value for occurrences is 10.

3. The default value for max_name_length is 32.

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);

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);

EXEC SQL AUTOMATIC MEMORY |ON [NOCHECK]|;
                          |     OFF    |

1. 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.

2. If OFF is specified(which is default), user should allocate space.

3. 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.

EXEC SQL AUTOMATIC MEMORY ON NOCHECK;

EXEC SQL CLOSE cursor_name;

where:

A cursor must be closed before it can be re-opened.

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;

EXEC SQL DEALLOCATE DESCRIPTOR descriptor_name;

where:

1. The memory used by the descriptor is freed.

2. After a descriptor has been deallocated, it cannot be used, unless re-allocated again.

The following command discards the descriptor out_desc, and releases the space used by the descriptor:

   EXEC SQL DEALLOCATE DESCRIPTOR out_desc;

EXEC SQL DEALLOCATE PREPARE statement_name;

where:

1. Database resources used by the statement name are freed.

The following command discards the statement name s1, and releases the space used by the statement name:

   EXEC SQL DEALLOCATE PREPARE s1;

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


     |statement              | [FOR UPDATE];
     |STATEMENT_NAME variable|

where:

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.

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;

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";

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;

EXEC SQL DELETE FROM table WHERE CURRENT OF cursor_name;

where:

1. The cursor must not reference more than one table.

2. The cursor must not contain aggregate functions.

The following command deletes from table acid_tests, the current record in cursor ctests:

   EXEC SQL DELETE FROM acid_tests WHERE CURRENT OF ctests;

EXEC SQL DESCRIBE [INOUT] statement_name

    |USING| SQL DESCRIPTOR descriptor_name;
    |INTO |

where:

1. The descriptor must be allocated before it can be described.

2. The cursor for the query should be opened before a descriptor is described for output.

3. The command fails if the number of selected items in the query exceeds the maximum number of items areas in descriptor.

4. After it is described for output, the number of selected items in the query can be determined by reading the COUNT field.

5. 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.

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;

EXEC SQL DESCRIBE INPUT statement_name

    |USING| SQL DESCRIPTOR descriptor_name;
    |INTO |

where:

1. The descriptor must be allocated before it can be described.

2. The command fails if the number of parameters in the prepared SQL statement exceeds the maximum number of items areas in descriptor.

3. After it is described for input, the number of parameters in the prepared statement can be determined by reading the COUNT field.

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;

EXEC SQL DESCRIBE [OUTPUT] statement_name

    |USING| SQL DESCRIPTOR descriptor_name;
    |INTO |

where:

1. The descriptor must be allocated before it can be described.

2. The cursor for the query should be opened before a descriptor is described for output.

3. The command fails if the number of selected items in the query exceeds the maximum number of items areas in descriptor.

4. After it is described for output, the number of selected items in the query can be determined by reading the COUNT field.

5. 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.

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;

EXEC SQL EXECUTE statement_name

   [USING |variable {, variable}         |];
          |SQL DESCRIPTOR descriptor_name|

where:

1. The statement cannot be a query (SELECT statement).

2. The statement cannot be a transaction command (such as START TRANSACTION).

3. The statement name must already be defined by a PREPARE statement.

4. The USING clause allows parameters in the prepared statement to be replaced by values held by variables or held in descriptor areas.

5. The number of variables in the USING clause must match the number of parameters in the prepared statement.

6. If a descriptor is used, it must be an input descriptor described for the specified statement.

7. If the statement is a call statement or is an assign statement, then after execution, the result will be returned if there is any.

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;

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;

EXEC SQL EXECUTE IMMEDIATE sql_statement;

where:

1. The statement cannot be a query (SELECT statement).

2. The statement cannot be a transaction command (such as START TRANSACTION).

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))";

This command will execute a statement contained in string variable str_var.

   EXEC SQL EXECUTE IMMEDIATE :str_var;

EXEC SQL MEMORY ALLOCATE variable(|size    |);
                                  |variable|

where:

1. This statement requires EXEC SQL MEMORY FREE to be used in order to free the allocated space.

...
char* ptr;
...
EXEC SQL MEMORY ALLOCATE :ptr (20);
...
EXEC SQL FETCH cursor1 into :ptr;
...
EXEC SQL MEMORY FREE :ptr;
...

EXEC SQL MEMORY REALLOCATE variable(|size    |);
                                    |variable|

where:

1. This statement requires EXEC SQL MEMORY FREE to be used in order to free the reallocated space.

...
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;
...

EXEC SQL FETCH [|NEXT           |[AGAIN][FROM]] cursor_name
                |PRIOR          |
                |FIRST          |
                |LAST           |
                |ABSOLUTE offset|
                |RELATIVE offset|

     INTO |variable {, variable }        |;
          |SQL DESCRIPTOR descriptor_name|

EXEC SQL FETCH  | emptrig_cursor_old |  [attr {, attr}]
                | emptrig_cursor_new |

     INTO |variable {, variable }        |;
          |SQL DESCRIPTOR descriptor_name|

where:

1. 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.

2. 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).

3. 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).

4. FETCH FIRST moves the cursor to the first record.

5. FETCH LAST moves the cursor to the last record.

6. The number of variables in the INTO clause must be equal to the number of items selected.

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

8. If a descriptor is used, it must be an output descriptor described for the statement used to define this cursor.

9. 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.

10. 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.

11. 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.

   EXEC SQL FETCH c1 INTO :v1, :v2;

   EXEC SQL FETCH c2 INTO SQL DESCRIPTOR d2;

EXEC SQL FETCH_AGAIN cursor_name

   [INTO |variable {, variable }        |];
         |SQL DESCRIPTOR descriptor_name|

where:

1. Normally used after a FETCH command encountered a locked record, to attempt to re-read that record.

2. The number of variables in the INTO clause must be equal to the number of items selected.

3. 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.

4. If a descriptor is used, it must be an output descriptor described for the statement used to define this cursor.

5. 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.

6. 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.

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;

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;

EXEC SQL MEMORY FREE variable {,variable};

where:

1. This statement is a replacement for mpdfree in the previous versions.

2. EXEC SQL MEMORY FREE must not be passed a variable which does not have space allocated to it.

...
char* ptr;
...
EXEC SQL AUTOMATIC MEMORY ON;
...
EXEC SQL FETCH cursor1 into :ptr;
...
EXEC SQL FREE :ptr;
...

EXEC SQL GET DESCRIPTOR descriptor_name

where:

1. If the descriptor was described for input, and data has not been assigned to the fields, only the COUNT field contains valid information.

2. If the descriptor was described for output and used for fetching, all fields contain valid information.

3. The COUNT field holds an integer value indicating the number of active item areas in the descriptor.

4. 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

5. 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.

6. The DA_NLEN field holds an integer value indicating the number of characters in the name of the selected item.

7. 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).

8. 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.

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;

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;

EXEC SQL INCLUDE SQLDA;

1. This statement is not required if descriptors are not used.

2. It is usually placed after the INCLUDE SQLCA statement.

EXEC SQL OPEN cursor_name

   [USING |variable {, variable}         |];
          |SQL DESCRIPTOR descriptor_name|

where:

1. The cursor name must already be defined.

2. The USING clause allows parameters in a prepared statement to be replaced by values held by variables or held in descriptor areas.

3. The number of variables in the USING clause must match the number of parameters in the prepared statement.

4. If a descriptor is used, it must be an input descriptor described for the statement used to define this cursor.

Opens cursor c1. Since a USING clause is not specified, the query related to the cursor must not have parameters.

   EXEC SQL OPEN c1;

Opens cursor c2, substituting three variables into the query statement, which must have exactly three parameters.

   EXEC SQL OPEN c2 USING :v1, :v2, :v3;

Opens cursor c3

   EXEC SQL OPEN c3 USING SQL DESCRIPTOR d3;

where:

1. A SQL statement containing parameters must be prepared before use.

2. 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.

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 ?";

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;

This command prepares a CALL statement to the procedure named proc for later execution:

   SQL EXEC PREPARE call_s1 FROM "CALL proc (?, ?, ?)";

EXEC SQL SET DESCRIPTOR descriptor_name
     VALUE item field_name = val  {, field_name = val};

where:

1. The descriptor must be described for input. If it was described for output, its fields cannot be assigned values.

2. 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).

3. 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.

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;

EXEC SQL TRIGGER ABORT;

No meaning.

1. It can only be used in trigger PSM procedure.

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:

1. The cursor must not reference more than one table.

2. The cursor must not contain aggregate functions.

3. table_name or a keyword CURRENT can be used to reference to a table.

4. emptrig_cursor_old cannot be used in UPDATE command.

5. 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.

6. Reserved Precompiler cursor identifiers emptrig_cursor_old and emptrig_cursor_new can be used only in a trigger PSM procedure.

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;

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;