SQL Statements
DATABASE
Use the DATABASE statement to select an accessible database as the current database.
Syntax
Usage
You can use the DATABASE statement to select any database on your database server. To select a database on another Universal Server database server, specify the name of the database server with the database name.
If you specify the name of the current database server or another database server with the database name, the database server name cannot be uppercase.
Issuing a DATABASE statement when a database is already open closes the current database before opening the new one. Closing the current database releases any cursor resources held by the database server, which invalidates any cursors you have declared up to that point. If the user identity was changed through a SET SESSION AUTHORIZATION statement, the original user name is restored.
The current user (or PUBLIC) must have the Connect privilege on the database specified in the DATABASE statement. The current user cannot have the same user name as an existing role in the database.
You cannot include the DATABASE statement in a multistatement PREPARE operation.
You can determine the type of database a user selects by checking the warning flag after a DATABASE statement in the sqlca structure.
If the database has transactions, the second element of the sqlwarn structure (sqlca.sqlwarn.sqlwarn1) contains a W after the DATABASE statement executes. 
 If the database is ANSI compliant, the third element of the sqlwarn structure (sqlca.sqlwarn.sqlwarn2) contains a W after the DATABASE statement executes.
If the database is an INFORMIX-Universal Server database, the fourth element of the sqlwarn structure (sqlca.sqlwarn.sqlwarn3) contains a W after the DATABASE statement executes.
If the database is running in secondary mode, the seventh element of the sqlwarn structure (sqlca.sqlwarn.sqlwarn6) contains a W after the DATABASE statement executes.
EXCLUSIVE Keyword
The EXCLUSIVE keyword opens the database in exclusive mode and prevents access by anyone but the current user. To allow others access to the database, you must execute the CLOSE DATABASE statement and then reopen the database without the EXCLUSIVE keyword.
The following statement opens the stores7 database on the training database server in exclusive mode:
If another user has already opened the database, exclusive access is denied, an error is returned, and no database is opened.
References
See the CLOSE DATABASE and CONNECT statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussions of database design in Chapter 8 and implementing the data model in Chapter 9.
DEALLOCATE COLLECTION
Use the DEALLOCATE DESCRIPTOR statement to release memory for an INFORMIX-ESQL/C collection variable that was previously allocated with the ALLOCATE COLLECTION statement.
Syntax
Usage
The DEALLOCATE COLLECTION statement frees all the memory that is associated with the ESQL/C collection variable that variable name identifies. You must explicitly release memory resources for a collection variable with DEALLOCATE COLLECTION. Otherwise, deallocation does not occur automatically until the end of the program.
The following example shows how to deallocate resources with the DEALLOCATE COLLECTION statement for the untyped collection variable, a_set:
The DEALLOCATE COLLECTION statement releases resources for both typed and untyped collection variables.
If you deallocate a nonexistent collection variable or a variable that is not an ESQL/C collection variable, an error results. Once you deallocate a collection variable, you can use the ALLOCATE COLLECTION to reallocate resources and you can then reuse a collection variable.
References
See the ALLOCATE COLLECTION and DEALLOCATE ROW statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussion of collection data types in Chapter 10. In the INFORMIX-ESQL/C Programmer's Manual, see the discussion of complex data types.
DEALLOCATE DESCRIPTOR
Use the DEALLOCATE DESCRIPTOR statement to free a system-descriptor area that was previously allocated with the ALLOCATE DESCRIPTOR statement.
Syntax
Usage
The DEALLOCATE DESCRIPTOR statement frees all the memory that is associated with the system-descriptor area that descriptor or descriptor variable identifies. It also frees all the item descriptors (including memory for data values in the value descriptors).
The following examples show the DEALLOCATE DESCRIPTOR statement for INFORMIX-ESQL/C. The first line shows an embedded-variable name, and the second line shows a quoted string that identifies the allocated system-descriptor area.
You can reuse a descriptor or descriptor variable after it is deallocated. Deallocation occurs automatically at the end of the program.
If you deallocate a nonexistent descriptor or descriptor variable, an error results.
You cannot use the DEALLOCATE DESCRIPTOR statement to deallocate an sqlda structure. You can use it only to free the memory that is allocated for a system-descriptor area.
References
See the ALLOCATE DESCRIPTOR, DECLARE, DESCRIBE, EXECUTE, FETCH, GET DESCRIPTOR, OPEN, PREPARE, PUT, and SET DESCRIPTOR statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussion of dynamic SQL in Chapter 5. In the INFORMIX-ESQL/C Programmer's Manual, see the discussion of dynamic SQL.
DEALLOCATE ROW
Use the DEALLOCATE ROW statement to release memory for an INFORMIX-ESQL/C row variable that was previously allocated with the ALLOCATE ROW statement.
Syntax
Usage
The DEALLOCATE ROW statement frees all the memory that is associated with the ESQL/C row variable that variable name identifies. You must explicitly release memory resources for a row variable with DEALLOCATE ROW. Otherwise, deallocation does not occur automatically until the end of the program.
The following example shows how to deallocate resources for the row variable, a_row, with the DEALLOCATE ROW statement:
The DEALLOCATE COLLECTION statement releases resources for both typed and untyped row variables.
If you deallocate a nonexistent row variable or a variable that is not an ESQL/C row variable, an error results. Once you deallocate a row variable, you can use the ALLOCATE ROW to reallocate resources, and you can then reuse a row variable.
References
See the ALLOCATE ROW and DEALLOCATE COLLECTION statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussion of row types in Chapter 10. In the INFORMIX-ESQL/C Programmer's Manual, see the discussion of complex data types.
DECLARE
Use the DECLARE statement to define a cursor, which associates rows with a SELECT, INSERT, or EXECUTE FUNCTION statement.
Syntax
Usage
A cursor is an identifier that you associate with a group of rows. The DECLARE statement associates the cursor with one of the following database objects:
The DECLARE statement assigns an identifier to the cursor, specifies its uses, and directs the preprocessor to allocate storage to hold the cursor. The DECLARE statement must precede any other statement that refers to the cursor during the execution of the program.
The amount of available memory in the system limits the number of open cursors and prepared statements that you can have at one time in one process. Use FREE statement id or FREE statement id variable to release the resources that a prepared statement holds; use FREE cursor id or FREE cursor variable to release resources that a cursor holds.
A program can consist of one or more source-code files. By default, the scope of a cursor is global to a program, so a cursor declared in one file can be referenced from another file. In a multiple-file program, if you want to limit the scope of cursors to the files in which they are declared, you must preprocess all the files with the -local command-line option. See your SQL API product manual for more information, restrictions, and performance issues when you preprocess with the -local option.
A host variable used in place of the cursor name or statement identifier must be a character data type. The following ESQL/C code defines a char host variable called cursname:
Other ESQL/C character data types are also valid to hold cursor names and statement identifiers.
To declare multiple cursors, use a single statement identifier. For instance, the following INFORMIX-ESQL/C example does not return an error:
If you include the -ansi compilation flag (or if DBANSIWARN is set), warnings are generated for statements that use dynamic cursor names or dynamic statement identifier names and statements that use derived tables. Some error checking is performed at runtime. The following list indicates the typical checks:
Checks for multiple declarations of a cursor of the same name are performed at compile time only if the cursor or statement is an identifier. For example, the code in the first example below results in a compile error. The code in the second example does not result in a compile error because it uses a host variable to hold the cursor name.
Results in error
Runs successfully
Overview of Cursor Types
With the DECLARE statement, you can declare the following types of cursors:
Any of these cursor types can have cursor characteristics: sequential, scroll, and hold. These characteristics determine the structure of the cursor. For more information, see "Cursor Characteristics". In addition, a select or function cursor can have a cursor mode: read-only or update. For more information, see "Cursor Modes".
The following table summarizes types of cursors that are available.
The following sections describe each of these cursor types.
Select or Function Cursor
When an SQL statement returns more than one group of values to an ESQL/C program, you must declare a cursor to save the multiple groups, or rows, of data and to access these rows one at a time. You must associate the following SQL statements with cursors:
A select cursor is a data structure that represents a specific location within the active set of rows that the SELECT statement retrieved.
The function cursor represents the columns or values that a user-defined function (and external function or an SPL function) returns. Function cursors behave the same as select cursors, which are enabled as update cursors.
When you associate a SELECT or EXECUTE FUNCTION statement with a cursor, the statement can include an INTO clause. However, if you prepare the SELECT or EXECUTE FUNCTION statement, you must omit the INTO clause in the PREPARE statement and use the INTO clause of the FETCH statement to retrieve the values from the collection cursor.
A select or function cursor enables you to scan returned rows of data and to move data row by row into a set of receiving variables, as the following steps describe:
1. Use a DECLARE statement to define a cursor and associate the SELECT statement or the EXECUTE FUNCTION statement with the cursor.
2. Open the cursor with the OPEN statement. The database server processes the query until it locates or constructs the first row of the active set.
3. Retrieve successive rows of data from the cursor with the FETCH statement.
4. Close the cursor with the CLOSE statement when the active set is no longer needed.
5. Free the cursor with the FREE statement. The FREE statement releases the resources that are allocated for a select or function cursor.
Cursor Modes
You can declare a select or function cursor with one of two cursor modes:
You cannot specify both the FOR UPDATE option and the FOR READ ONLY option in the same DECLARE statement because these options are mutually exclusive.
Read-Only Cursor
In a database that is not ANSI compliant, data in a select cursor or function cursor is read only. That is, you cannot directly update the data that is within a select or function cursor. Such a cursor is called a read-only cursor. To update data in a read-only cursor, you must copy the data out of the read-only cursor, perform the modifications on the copy, and then explicitly update the row with an UPDATE statement and a WHERE clause to identify the row you are updating.
In an ANSI-compliant database, you can directly update the data that is within a select cursor because a select cursor and a function cursor are, by default, update cursors. (For more information, see "Update Cursor".) If you want a select or function cursor to be for read only, you must declare a read-only cursor with the FOR READ ONLY option of the DECLARE statement. The FOR READ ONLY keywords state explicitly that a select or function cursor cannot be used to modify data. The database server can use less stringent locking for a read-only cursor than for an update cursor.
The following example declares a read-only cursor:
The SELECT statement for the cursor must conform to all of the restrictions for read-only cursors listed in "Subset of the SELECT Statement Associated with Cursors".
In a database that is not ANSI compliant, a select cursor and a select cursor with the FOR READ ONLY option are the same. The only advantage of specifying the FOR READ ONLY keywords explicitly is for better program documentation. The following example declares a read-only cursor in a non-ANSI database:
If you want to make it clear in the program code that this cursor is a read-only cursor, you can specify the FOR READ ONLY option as the following example shows:
Update Cursor
In a database that is not ANSI compliant, you cannot directly update the data that is within a select cursor or function cursor because these cursors are, by default, read-only cursors. (For more information, see "Read-Only Cursor".) To update data in a select or function cursor, you must declare an update cursor with the FOR UPDATE option of the DECLARE statement.
The following example declares an update cursor:
In an ANSI-compliant database, a select cursor and a select cursor with the FOR UPDATE option are the same. You can use a select cursor to update or delete data as long as the cursor was not declared with the FOR READ ONLY option and it follows the restrictions on update cursors that are described in "Subset of the SELECT Statement Associated with Cursors".
The following example declares an update cursor in an ANSI-compliant database:
If you want to make it clear in the program documentation that this cursor is an update cursor, you can specify the FOR UPDATE option as the following example shows:
The SELECT statement for the cursor must conform to all of the restrictions for update cursors listed in "Subset of the SELECT Statement Associated with Cursors".
In an update cursor, you can update or delete rows in the active set. After you create an update cursor, you can update or delete the currently selected row by using an UPDATE or DELETE statement with the WHERE CURRENT OF clause. The words CURRENT OF refer to the row that was most recently fetched; they take the place of the usual test expressions in the WHERE clause.
An update cursor lets you perform updates that are not possible with the UPDATE statement because the decision to update and the values of the new data items can be based on the original contents of the row. Your program can evaluate or manipulate the selected data before it decides whether to update. The UPDATE statement cannot interrogate the table that is being updated.
Locking with an update cursor
Use the FOR UPDATE keywords to notify the database server that updating is possible and cause it to use more stringent locking than with a select cursor. You declare an update cursor to let the database server know that the program might update (or delete) any row that it fetches as part of the SELECT statement. The update cursor employs promotable locks for rows that the program fetches. Other programs can read the locked row, but no other program can place a promotable or write lock. Before the program modifies the row, the row lock is promoted to an exclusive lock.
Although it is possible to declare an update cursor with the WITH HOLD keywords, the only reason to do so is to break a long series of updates into smaller transactions. You must fetch and update a particular row in the same transaction. (For more information on hold cursors, see page 1-318.)
If an operation involves fetching and updating a very large number of rows, the lock table that the database server maintains can overflow. The usual way to prevent this overflow is to lock the entire table that is being updated. If this action is impossible, an alternative is to update through a hold cursor and to execute COMMIT WORK at frequent intervals. However, you must plan such an application very carefully because COMMIT WORK releases all locks, even those that are placed through a hold cursor.
Using FOR UPDATE with a list of columns
When you declare an update cursor, you can limit the update to specific columns by including the OF keyword and a list of columns.You can modify only those named columns in subsequent UPDATE...WHERE CURRENT OF statements. The columns need not be in the select list of the SELECT clause.
The following example declares an update cursor and specifies that this cursor can update only the fname and lname columns in the customer_notansi table:
By default, a select cursor in a database that is ANSI compliant is an update cursor. Therefore, the FOR UPDATE keywords are optional. However, if you want an update cursor to be able to modify only some of the columns in a table, you must specify these columns in the FOR UPDATE option. The following example declares an update cursor and specifies that this cursor can update only the fname and lname columns in the customer_ansi table:
The principal advantage to specifying columns is documentation and preventing programming errors. (The database server refuses to update any other columns.) An additional advantage is speed, when the SELECT statement meets the following criteria:
If the columns that you intend to update are part of the index that is used to process the SELECT statement, the database server must keep a list of each row that is updated to ensure that no row is updated twice. When you use the OF keyword to specify the columns that can be updated, the database server determines whether to keep the list of updated rows. If the database server determines that the list is unnecessary, then eliminating the work of keeping the list results in a performance benefit. If you do not use the OF keyword, the database server keeps the list of updated rows, although it might be unnecessary.
This column restriction applies only to UPDATE...WHERE CURRENT OF statements. The OF column clause has no effect on subsequent DELETE statements that use a WHERE CURRENT OF clause. (A DELETE statement removes the contents of all columns.)
The following example contains INFORMIX-ESQL/C code that uses an update cursor with a DELETE statement to delete the current row. Whenever the row is deleted, the cursor remains between rows. After you delete data, you must use a FETCH statement to advance the cursor to the next row before you can refer to the cursor in a DELETE or UPDATE statement.
Subset of the SELECT Statement Associated with Cursors
Not all SELECT statements can be associated with an update cursor or a read-only cursor. If the DECLARE statement includes the FOR UPDATE clause or the FOR READ ONLY clause, you must observe certain restrictions on the SELECT statement that is included in the DECLARE statement (either directly or as a prepared statement).
If the DECLARE statement includes the FOR UPDATE clause, the SELECT statement must conform to the following restrictions:
If the DECLARE statement includes the FOR READ ONLY clause, the SELECT statement must conform to the following restrictions:
For a complete description of SELECT syntax and usage, see the SELECT statement on page 1-596.
Insert Cursor
When you associate an INSERT statement with a cursor, the cursor is called an insert cursor. An insert cursor is a data structure that represents the rows that the INSERT statement is to add to the database. The insert cursor simply inserts rows of data; it cannot be used to fetch data. To create an insert cursor, you associate a cursor with a restricted form of the INSERT statement. The INSERT statement must include a VALUES clause; it cannot contain an embedded SELECT statement.
Create an insert cursor if you want to add multiple rows to the database in an INSERT operation. An insert cursor allows bulk insert data to be buffered in memory and written to disk when the buffer is full, as the following steps describe:
1. Use a DECLARE statement to define an insert cursor for the INSERT statement.
2. Open the cursor with the OPEN statement. The database server creates the insert buffer in memory and positions the cursor at the first row of the insert buffer.
3. Put successive rows of data into the insert buffer with the PUT statement.
4. The database server writes the rows to disk only when the buffer is full. You can use the CLOSE, FLUSH, or COMMIT WORK statement to flush the buffer when it is less than full. This topic is discussed further under the PUT and CLOSE statements.
5. Close the cursor with the CLOSE statement when the insert cursor is no longer needed. You must close an insert cursor to insert any buffered rows into the database before the program ends. You can lose data if you do not close the cursor properly.
6. Free the cursor with the FREE statement. The FREE statement releases the resources that are allocated for an insert cursor.
An insert cursor increases processing efficiency (compared with embedding the INSERT statement directly). This process reduces communication between the program and the database server and also increases the speed of the insertions.
Cursor Characteristics
Structurally, you can declare a cursor with the following cursor characteristics:
A select or function cursor can be either a sequential or scroll cursor. An insert cursor can only be a sequential cursor. Select, function, and insert cursors can optionally be hold cursors. The following sections explain these structural characteristics.
Sequential Cursor
If you use only the CURSOR keyword in a DECLARE statement, you create a sequential cursor, which can fetch only the next row in sequence from the active set. The sequential cursor can read through the active set only once each time it is opened. If you are using a sequential cursor for a select cursor, on each execution of the FETCH statement, the database server returns the contents of the current row and locates the next row in the active set.
The following INFORMIX-ESQL/C example creates a read-only sequential cursor in a database that is not ANSI compliant and an update sequential cursor in an ANSI-compliant database:
In addition to select and function cursors, insert cursors can also have the sequential cursor characteristic. To create an insert cursor, you associate a sequential cursor with a restricted form of the INSERT statement. (For more information, see "Insert Cursor".) The following example contains INFORMIX-ESQL/C code that declares a sequential insert cursor:
Scroll Cursor
When you specify the SCROLL keyword in a DECLARE statement, you create a scroll cursor, which can fetch rows of the active set in any sequence. The following example creates a scroll cursor for a SELECT:
You can create scroll cursors for select and function cursors but not for insert cursors. Scroll cursors cannot be declared as FOR UPDATE.
To implement a scroll cursor, the database server creates a temporary table to hold the active set. With the active set retained as a table, you can fetch the first, last, or any intermediate rows as well as fetch rows repeatedly without having to close and reopen the cursor. See the FETCH statement on page 1-411 for a discussion of these abilities.
The database server retains the active set for a scroll cursor in a temporary table until the cursor is closed. On a multiuser system, the rows in the tables from which the active-set rows were derived might change after a copy is made in the temporary table. (For information about temporary tables, see the INFORMIX-Universal Server Administrator's Guide.) If you use a scroll cursor within a transaction, you can prevent copied rows from changing either by setting the isolation level to Repeatable Read or by locking the entire table in share mode during the transaction. (See the SET ISOLATION statement on page 1-742 and the LOCK TABLE statement on page 1-525.)
Hold Cursor
If you use the WITH HOLD keywords in a DECLARE statement, you create a hold cursor. A hold cursor allows uninterrupted access to a set of rows across multiple transactions. Ordinarily, all cursors close at the end of a transaction. A hold cursor does not close; it remains open after a transaction ends.
You can use the WITH HOLD keywords to declare select and function cursors sequential and scroll), and insert cursors. These keywords follow the CURSOR keyword in the DECLARE statement. The following example creates a sequential hold cursor for a SELECT:
You can use a select hold cursor as the following ESQL/C code example shows. This code fragment uses a hold cursor as a master cursor to scan one set of records and a sequential cursor as a detail cursor to point to records that are located in a different table. The records that the master cursor scans are the basis for updating the records to which the detail cursor points. The COMMIT WORK statement at the end of each iteration of the first WHILE loop leaves the hold cursor c_master open but closes the sequential cursor c_detail and releases all locks. This technique minimizes the resources that the database server must devote to locks and unfinished transactions, and it gives other users immediate access to updated rows.
When you associate a hold cursor with an insert cursor, you can use transactions to break a long series of PUT statements into smaller sets of PUT statements. Instead of waiting for the PUT statements to fill the buffer and trigger an automatic write to the database, you can execute a COMMIT WORK statement to flush the row buffer. If you use a hold cursor, the COMMIT WORK statement commits the inserted rows but leaves the cursor open for further inserts. This method can be desirable when you are inserting a large number of rows, because pending uncommitted work consumes database server resources.
Use either the CLOSE statement to close the hold cursor explicitly or the CLOSE DATABASE or DISCONNECT statements to close it implicitly. The CLOSE DATABASE statement closes all cursors.
Associating a Cursor With a Prepared Statement
The PREPARE statement lets you assemble the text of an SQL statement at runtime and pass the statement text to the database server for execution. If you anticipate that a dynamically prepared SELECT statement or EXECUTE FUNCTION statement that returns values could produce more than one row of data, the prepared statement must be associated with a cursor. (See the PREPARE statement on page 1-541 for more information about preparing SQL statements.)
The result of a PREPARE statement is a statement identifier (statement id or id variable), which is a data structure that represents the prepared statement text. You declare a cursor for the statement text by associating a cursor with the statement identifier.
You can associate a sequential cursor with any prepared SELECT or EXECUTE FUNCTION statement. You cannot associate a scroll cursor with a prepared INSERT statement or with a SELECT statement that was prepared to include a FOR UPDATE clause.
After a cursor is opened, used, and closed, a different statement can be prepared under the same statement identifier. In this way, it is possible to use a single cursor with different statements at different times. The cursor must be redeclared before you use it again.
The following example contains INFORMIX-ESQL/C code that prepares a SELECT statement and declares a sequential cursor for the prepared statement text. The statement identifier st_1 is first prepared from a SELECT statement that returns values; then the cursor c_detail is declared for st_1.
If you want use a prepared SELECT statement to modify data, add a FOR UPDATE clause to the statement text that you wish to prepare, as the following INFORMIX-ESQL/C example shows:
Associating a Cursor With a Collection Variable
The DECLARE statement allows you to declare a cursor for an ESQL/C collection variable. Such a cursor is called a collection cursor. You use a collection variable to access the elements of a collection (SET, MULTISET, LIST) column. Use a cursor when you want to access one or more elements in a collection variable.
You can declare the following types of cursors for a collection variable:
Include the Collection Derived Table clause with the SELECT statement that you associate with the cursor.
Include the Collection Derived Table clause with the INSERT statement that you associate with the cursor.
The Collection Derived Table clause identifies the collection variable for which to declare the cursor. For more information on the Collection Derived Table clause, see page 1-831.
A Select Cursor for a Collection Variable
To declare a select cursor for a collection variable, include the Collection Derived Table clause with the SELECT statement that you associate with the cursor. A select cursor allows you to select one or more elements from the collection variable. The DECLARE for this select cursor has the following restrictions:
The DECLARE statement cannot include the FOR READ ONLY clause that specifies the read-only cursor mode.
The DECLARE statement cannot specify the SCROLL or WITH HOLD cursor characteristics.
The SELECT statement that you associate with the cursor also has some restrictions:
These columns cannot use the following syntax:
When you declare a select cursor for a collection variable, the Collection Derived Table clause of the SELECT statement must contain the name of the collection variable. You cannot specify an input parameter (the question-mark (?) symbol) for the collection variable. For example, the following DECLARE statement declares a select cursor for a collection variable:
To select the element(s) from the collection variable, use the FETCH statement with the INTO clause. For more information, see "Fetching From a Collection Cursor".
If you want to modify the elements of the collection variable, declare the select cursor as an update cursor with the FOR UPDATE keywords. You can then use the WHERE CURRENT OF clause of the DELETE and UPDATE statements to delete or update elements of the collection. For more information, see the DELETE and UPDATE statements in this manual.
A collection cursor that includes a SELECT statement with the Collection Derived Table clause allows you to access the elements in a collection variable. To select elements, follow these steps:
1. Create a client collection variable in your ESQL/C program.
2. Declare the collection cursor for the SELECT statement with the DECLARE statement and open this cursor with the OPEN statement.
3. Fetch the element(s) from the collection cursor with the FETCH statement and the INTO clause.
4. If necessary, perform any updates or deletes on the fetched data and save the modified collection variable in the collection column.
Once the collection variable contains the correct elements, you can use the UPDATE statement or the INSERT statement on a table name to save the contents of the collection variable in a collection column (SET, MULTISET, or LIST).
5. Close the collection cursor with the CLOSE statement.
For a code example that uses a collection cursor for a SELECT statement, see "Fetching From a Collection Cursor". For more information on how to use ESQL/C collection variables, see the discussion of complex data types in the INFORMIX-ESQL/C Programmer's Manual.
An Insert Cursor For a Collection Variable
To declare an insert cursor for a collection variable, include the Collection Derived Table clause with the INSERT statement that you associate with the cursor. An insert cursor allows you to insert one or more elements in the collection. The insert cursor must be a sequential cursor; the DECLARE statement cannot specify the WITH HOLD cursor characteristic.
When you declare an insert cursor for a collection variable, the Collection Derived Table clause of the INSERT statement must contain the name of the collection variable. You cannot specify an input parameter (the question-mark (?) symbol) for the collection variable. However, you can use an input parameter in the VALUES clause of the INSERT statement. This parameter indicates that the collection element is to be provided later by the FROM clause of the PUT statement. For example, the following DECLARE statement declares an insert cursor for the a_set collection variable:
To insert the element(s) into the collection variable, use the PUT statement with the FROM clause. For more information, see "Inserting into a Collection Cursor".
A collection cursor that includes an INSERT statement with the Collection Derived Table clause allows you to insert many elements into a collection variable. To insert elements, follow these steps:
1. Create a client collection variable in your ESQL/C program.
2. Declare the collection cursor for the INSERT statement with the DECLARE statement and open the cursor with the OPEN statement.
3. Put the element(s) into the collection cursor with the PUT statement and the FROM clause.
4. Once the collection variable contains all the elements, you then use the UPDATE statement or the INSERT statement on a table name to save the contents of the collection variable in a collection column (SET, MULTISET, or LIST).
5. Close the collection cursor with the CLOSE statement.
For a code example that uses a collection cursor for an INSERT statement, see "Inserting into a Collection Cursor". For more information on how to use ESQL/C collection variables, see the discussion on complex data types INFORMIX-ESQL/C Programmer's Manual.
Using Cursors within Transactions
To roll back a modification, you must perform the modification within a transaction. A transaction in a database that is not ANSI compliant begins only when the BEGIN WORK statement is executed.
In ANSI-compliant databases, transactions are always in effect.
The database server enforces the following guidelines for insert and update cursors. These guidelines ensure that modifications can be committed or rolled back properly:
The database server lets you open and close a hold cursor for an update outside a transaction; however, you should fetch all the rows that pertain to a given modification and then perform the modification all within a single transaction. You cannot open and close hold or update cursors outside a transaction.
The following example produces an error when the database server tries to execute the UPDATE statement:
Results in error
The following example does not produce an error when the database server tries to execute the UPDATE statement:
Runs successfully
When you update a row within a transaction, the row remains locked until the cursor is closed or the transaction is committed or rolled back. If you update a row when no transaction is in effect, the row lock is released when the modified row is written to disk.
If you update or delete a row outside a transaction, you cannot roll back the operation.
In a database that uses transactions, you cannot open an insert cursor outside a transaction unless it was also declared with hold.
References
See the CLOSE, DELETE, EXECUTE FUNCTION, FETCH, FREE, INSERT, OPEN, PREPARE, PUT, SELECT, and UPDATE statements in this manual.
In the Informix Guide to SQL: Tutorial, see the discussions of cursors and data modification in Chapter 5 and Chapter 6, respectively.
DELETE
Use the DELETE statement to delete one or more rows from a table, or one or more elements in an SPL or INFORMIX-ESQL/C collection variable.
Syntax
Usage
Use the DELETE statement to remove either of the following types of objects:
For information on how to delete an element from a collection variable, see "Deleting from a Collection Variable". The other sections of this DELETE statement describe how to remove a row in a table.
If you use the DELETE statement without a WHERE clause, all the rows in the table are deleted.
If you use the DELETE statement to remove rows of a supertable, rows from both the supertable and its subtables can be deleted. To delete rows from the supertable only, you must use the ONLY keyword prior to the table name, as the following example shows:
If you use the DELETE statement outside a transaction in a database that uses transactions, each DELETE statement that you execute is treated as a single transaction.
Each row affected by a DELETE statement within a transaction is locked for the duration of the transaction; therefore, a single DELETE statement that affects a large number of rows locks the rows until the entire operation is complete. If the number of rows affected is very large, you might exceed the limits your operating system places on the maximum number of simultaneous locks. If this occurs, you can either reduce the scope of the DELETE statement or lock the entire table before you execute the statement.
If you specify a view name, the view must be updatable. See "Updating Through Views" for an explanation of an updatable view.
If you omit the WHERE clause while you are working within the SQL menu, DB-Access prompts you to verify that you want to delete all rows from a table. You do not receive a prompt if you run the DELETE statement within a command file.
Statements are always within an implicit transaction in an ANSI-compliant database; therefore, you cannot have a DELETE statement outside a transaction.
Deleting Rows That Contain Opaque Data Types
Some opaque data types require special processing when they are deleted. A For example, if an opaque data type contains spatial or multirepresentational data, it might provide a choice of how to store the data: inside the internal structure or, for very large objects, in a smart large object.
This processing is accomplished by calling a user-defined support function called destroy(). When you use the DELETE statement to delete a row that contains one of these opaque types, the database server automatically invokes the destroy() function for the type. The destroy() support function can decide how remove the data, regardless of where it is stored. For more information on the destroy() support function, see the Extending INFORMIX-Universal Server: Data Types manual.
Deleting Rows That Contain Collection Data Types
When a row contains a column that is a collection data type (LIST, MULTISET, or SET), you can search for a particular element in the collection, and delete the row or rows in which the element is found. For example, the following statement deletes any rows from the new_tab table in which the set_col column contains the element jimmy smith:
Using Cascading Deletes
Use the ON DELETE CASCADE option of the REFERENCES clause on either the CREATE TABLE or ALTER TABLE statement to specify that you want deletes to cascade from one table to another. For example, the stock table contains the column stock_num as a primary key. The catalog and items tables each contain the column stock_num as foreign keys with the ON DELETE CASCADE option specified. When a delete is performed from the stock table, rows are also deleted in the catalog and items tables, which are referred through the foreign keys.
If a cascading delete is performed without a WHERE clause, all rows in the parent table (and subsequently, the affected child tables) are deleted.
WHERE Clause
Use the WHERE clause to specify one or more rows that you want to delete. The WHERE conditions are the same as the conditions in the SELECT statement. For example, the following statement deletes all the rows of the items table where the order number is less than 1034:
If you include a WHERE clause that selects all rows in the table, DB-Access gives no prompt and deletes all rows.
Deleting and the WHERE Clause
If you delete from a table in an ANSI-compliant database with a DELETE that contains a WHERE clause and no rows are found, that database server issues a warning. You can detect this warning condition in either of the following ways:
The database server also sets SQLSTATE and SQLCODE to these values if the DELETE ... WHERE ... is a part of a multistatement prepare and the database server returns no rows.
In a database that is not ANSI compliant, the database server does not return a warning when it finds no matching rows for the WHERE clause of a DELETE statement. The SQLSTATE code is `00000' and the SQLCODE code is zero (0). However, if the DELETE ... WHERE ... is a part of a multistatement prepare, and no rows are returned, the database server does issue a warning. It sets SQLSTATE to `02000' and SQLCODE value to 100.
For additional information about the SQLSTATE code, see the GET DIAGNOSTICS statement in this manual. For information about the SQLCODE code, see the description of the sqlca structure in the Informix Guide to SQL: Tutorial.
WHERE CURRENT OF Clause
You can use the WHERE CURRENT OF clause to delete either of the following objects:
You access both of these objects with an update cursor. An update cursor is a sequential cursor that is associated with a SELECT statement but can modify and delete the contents of the cursor. For more information on the update cursor, see page 1-310.
To use the WHERE CURRENT OF clause, you must have previously used the DECLARE statement with the FOR UPDATE clause to define the cursor name for the update cursor. (See the DECLARE statement on page 1-303.)
Before you can use the WHERE CURRENT OF clause, you must declare a cursor with the FOREACH statement. (See the FOREACH statement on page 2-27.)
All select cursors are potentially update cursors in ANSI-compliant databases. You can use the WHERE CURRENT OF clause with any select cursor.
Deleting the Current Row
 When you specify a table or view name in the FROM clause of the SELECT, the DECLARE statement defines a cursor that populates an active set with the rows of the specified tables or views. The DELETE....WHERE CURRENT OF statement deletes the current row of the active set of a cursor. When you use the WHERE CURRENT OF clause, the DELETE statement removes the row of the active set at the current position of the cursor. After the deletion, no current row exists; you cannot use the cursor to delete or update a row until you reposition the cursor with a FETCH statement.
Deleting a Collection Element
 You declare a collection cursor when you associate a cursor with SELECT statement that includes a Collection Derived Table clause. You use one of the following statements to declare a collection cursor:
For more information, see the FOREACH statement on page 2-27.
A collection cursor is an update cursor by default. However, you can optionally specify the FOR UPDATE clause with the SELECT statement. With an update cursor, you can use the DELETE...WHERE CURRENT OF statement to delete the current element of a collection cursor. For more information, see "Deleting from a Collection Variable".
 Deleting from a Collection Variable
The DELETE statement with the Collection Derived Table clause allows you to delete elements from a collection variable. The Collection Derived Table clause identifies the collection variable in which to delete the elements. For more information, see "Collection Derived Table".
In an INFORMIX-ESQL/C program, declare a host variable of type collection for a collection variable. This collection variable can be typed or untyped.
In an SPL routine, declare a variable of type COLLECTION, LIST, MULTISET, or SET for a collection variable. This collection variable can be typed or untyped.
To delete elements, follow these steps:
1. Create a collection variable in your SPL routine or ESQL/C program.
2. Optionally, select a collection column into the collection variable with the SELECT statement (without the Collection Derived Table clause).
3. Delete elements of the collection variable with the DELETE statement and the Collection Derived Table Clause.
4. After the collection variable contains the correct elements, use the INSERT or UPDATE statement on a table name to save the collection variable in the collection column (SET, MULTISET, or LIST).
The DELETE statement and the Collection Derived Table clause allow you to perform the following operations on a collection variable:
You must declare an update cursor for the collection variable and use DELETE with the WHERE CURRENT OF clause. For more information on how to use an update cursor with ESQL/C, see the DECLARE statement on page 1-303. For more information on how to use an update cursor with SPL, see "FOREACH".
The application or SPL routine must position the update cursor on the element to be deleted and then use DELETE...WHERE CURRENT OF to delete this value. For more information on the WHERE CURRENT OF clause of DELETE, see page 1-331.
Use the DELETE statement (without the WHERE CURRENT OF clause). No cursor is required to delete all elements of a collection.
For example, the following DELETE statement removes all elements in the a_list ESQL/C collection variable:
You could also use the following statements in an SPL routine:
A DELETE of an element or elements in a collection variable cannot include a WHERE clause.
The collection variable stores the elements of the collection. However, it has no intrinsic connection with a database column. Once the collection variable contains the correct elements, you must then save the variable into the collection column with one of the following SQL statements:
Suppose that the set_col column of a row in the table1 table is defined as a SET and for one row contains the values {1,8,4,5,2}. The following ESQL/C code fragment uses an update cursor and a DELETE statement with a WHERE CURRENT OF clause to delete the element whose value is 4:
After the DELETE statement executes, this collection variable contains the elements {1,8,5,2}. The UPDATE statement at the end of this code fragment saves the modified collection into the set_col column of the database. Without this UPDATE statement, the collection column never has element 4 deleted.
For information on how to use collection host variables in an ESQL/C program, see the discussion of complex data types in the INFORMIX-ESQL/C Programmer's Manual.
You can also delete the element with the value 4 from the set {1,8,4,5,2} with an SPL routine, as the following example shows.
This SPL routine defines two variables, a and b, each to hold a SET of SMALLINT values. The first SELECT statement selects a SET column from one row of table1 into b. Then, the routine declares a cursor that selects one element at a time from b into a. When the cursor is positioned on the element with the value 4, the DELETE statement deletes that element from b. Last, the UPDATE statement updates the row of table1 with the new collection that is stored in b.
For information on how to use collection variables in an SPL routine, see Chapter 14 of the Informix Guide to SQL: Tutorial.
 Deleting a Row Variable
The DELETE statement does not support a row variable in the Collection Derived Table clause. A row variable must have a value for each field. For more information, see "Updating a Row Variable".
References
See the DECLARE, INSERT, OPEN, and SELECT statements in Chapter 1 of this manual. See the FOREACH statement in Chapter 2 of this manual.
In the Informix Guide to SQL: Tutorial, see the discussions of cursors and data modification in Chapter 5 and Chapter 6, respectively, and the discussion of stored routines in Chapter 14. In the Guide to GLS Functionality, see the discussion of the GLS aspects of the DELETE statement.
For information on how to access row and collections with ESQL/C host variables, see the chapter on complex data types in the INFORMIX-ESQL/C Programmer's Manual.
DESCRIBE
Use the DESCRIBE statement to obtain information about a prepared statement before you execute it. The information can be stored in a system-descriptor area or in an sqlda structure.
Syntax
Usage
The DESCRIBE statement allows you to determine, at runtime, the following information about a prepared statement:
With this information, you can write code to allocate memory to hold retrieved values and display or process them after they are fetched.
Describing the Statement Type
The DESCRIBE statement takes a statement identifier from a PREPARE statement as input. When the DESCRIBE statement executes, the database server sets the value of the SQLCODE (the sqlcode field of the sqlca) to indicate the statement type (that is, the keyword with which the statement begins). If the prepared statement text contains more than one SQL statement, the DESCRIBE statement returns the type of the first statement in the text.
SQLCODE is set to zero to indicate a SELECT statement without an INTO TEMP clause. This situation is the most common. For any other SQL statement, SQLCODE is set to a positive integer. See the discussion on exception handling in the INFORMIX-ESQL/C Programmer's Manual for more information about possible SQLCODE values after a DESCRIBE statement.
You can test the number against the constant names that are defined. In INFORMIX-ESQL/C, the constant names are defined in the sqlstype.h header file. A printed list of the possible values and their constant names appears in the INFORMIX-ESQL/C Programmer's Manual.
The DESCRIBE statement uses the SQLCODE field differently than any other statement, possibly returning a nonzero value when it executes successfully. You can revise standard error-checking routines to accommodate this behavior, if desired.
Checking for Existence of a WHERE Clause
If the DESCRIBE statement detects that a prepared statement contains an UPDATE or DELETE statement without a WHERE clause, the DESCRIBE statement sets the following sqlca variable to W.
Without a WHERE clause, the update or delete action is applied to the entire table. Check this variable to avoid unintended global changes to your table.
Describing SELECT, EXECUTE FUNCTION, or INSERT
If the prepared statement text includes a SELECT statement without an INTO TEMP clause, an EXECUTE FUNCTION statement, or an INSERT statement, the DESCRIBE statement also returns a description of each column or expression that is included in the SELECT, EXECUTE FUNCTION, or INSERT list. You can store these descriptions in one of the following dynamic-management structures:
These dynamic-management structures provide the following information:
USING SQL DESCRIPTOR Clause
If the prepared statement contains parameters for which the number of parameters or parameter data types is to be supplied at runtime, you can describe these input values in a system-descriptor area. A system-descriptor area describes the data type and memory location of one or more values.
You can also use an sqlda structure to dynamically supply parameters. However, a system-descriptor area conforms to the X/Open standards.
The USING SQL DESCRIPTOR clause lets you store the description of a SELECT, INSERT, or EXECUTE FUNCTION list in a system-descriptor area that an ALLOCATE DESCRIPTOR statement creates. You can obtain information about the resulting columns of a prepared statement through a system-descriptor area.
The following example shows the use of a system-descriptor area in a DESCRIBE statement. In the first example system-descriptor area is a quoted string; in the second example, it is a host variable name.
The DESCRIBE...USING SQL DESCRIPTOR statement performs the following tasks on a system-descriptor area:
If the column has an opaque data type, DESCRIBE...USING SQL DESCRIPTOR sets the EXTYPEID, EXTYPENAME, EXTYPELENGTH, EXTYPEOWNERLENGTH, and EXTYPEOWNERNAME fields of the item descriptor.
After a DESCRIBE statement is executed, the SCALE and PRECISION fields contain the scale and precision of the column, respectively. If SCALE and
PRECISION are set in the SET DESCRIPTOR statement, and TYPE is set to DECIMAL or MONEY, the LENGTH field is modified to adjust for the scale and precision of the decimal value. If TYPE is not set to DECIMAL or MONEY, the values for SCALE and PRECISION are not set, and LENGTH is unaffected.
You can modify the system-descriptor-area information with the SET DESCRIPTOR statement. You must modify the system-descriptor area to show the address in memory that is to receive the described value. You can change the data type to another compatible type. This change causes data conversion to take place when the data is fetched.
You can use the system-descriptor area in statements that support a USING SQL DESCRIPTOR clause, such as EXECUTE, FETCH, OPEN, and PUT.
For further information, refer to the discussion of the system-descriptor area in the INFORMIX-ESQL/C Programmer's Manual.
INTO sqlda pointer Clause
If the prepared statement contains parameters for which the number of parameters or their data types is to be supplied at runtime, you can describe these input values in an sqlda structure. An sqlda structure describes the data type and memory location of one or more values.
The INTO sqlda pointer clause lets you allocate memory for an sqlda structure and store its address in an sqlda pointer. The DESCRIBE statement fills in the allocated memory with descriptive information for a SELECT, INSERT, or EXECUTE FUNCTION list.
The DESCRIBE statement sets the sqlda.sqld field to the number of values in the SELECT, INSERT, or EXECUTE FUNCTION list. The sqlda structure also contains an array of data descriptors (sqlvar structures), one for each value in the SELECT, INSERT, or EXECUTE FUNCTION list. After a DESCRIBE statement is executed, the sqlda.sqlvar structure has the sqltype, sqllen, and sqlname fields set.
If the column has an opaque data type, DESCRIBE...INTO sets the sqlxid, sqltypename, sqltypelen, sqlownerlen, and sqlownername fields of the item descriptor.
The DESCRIBE statement allocates memory for an sqlda pointer once it is declared in a program. However, the application program must designate the storage area of the sqlda.sqlvar.sqldata fields.
See the INFORMIX-ESQL/C Programmer's Manual for further information on the sqlda structure.
Describing a Collection Variable
The DESCRIBE statement can provide this information about a collection variable when you use the USING SQL DESCRIPTOR or INTO clause.
You must perform the DESCRIBE statement after you open the select or insert cursor. Otherwise, DESCRIBE cannot get information about the collection variable because it is the OPEN...USING statement that specifies the name of the collection variable to use.
The following ESQL/C code fragment shows how to dynamically select the elements of the :a_set collection variable into a system-descriptor area called desc1:
References
See the ALLOCATE DESCRIPTOR, DEALLOCATE DESCRIPTOR, DECLARE, EXECUTE, FETCH, GET DESCRIPTOR, OPEN, PREPARE, PUT, and SET DESCRIPTOR statements in this manual for further information about using dynamic management statements.
In the Informix Guide to SQL: Tutorial, see the discussion of the DESCRIBE statement in Chapter 5.
For further information about how to use a system-descriptor area or an sqlda pointer with a FETCH or an INSERT statement, refer to the INFORMIX-ESQL/C Programmer's Manual.
DISCONNECT
The DISCONNECT statement terminates a connection between an application and a database server.
Syntax
Usage
The DISCONNECT statement lets you terminate a connection to a database server. If a database is open, it closes before the connection drops. Even if you made a connection to a specific database only, that connection to the database server is terminated by the DISCONNECT statement.
You cannot use the PREPARE statement for the DISCONNECT statement.
If you disconnect a specific connection using connection name or conn_nm variable, DISCONNECT generates an error if the specified connection is not a current or dormant connection.
A DISCONNECT statement that does not terminate the current connection does not change the context of the current environment (the connection context).
DEFAULT Option
Use the DEFAULT option to identify the default connection for a DISCONNECT statement. The default connection is one of the following connections:
You can use DISCONNECT to disconnect the default connection. See "DEFAULT Option" and "Implicit Connection with DATABASE Statements" for more information.
If the DATABASE statement does not specify a database server, as shown in the following example, the default connection is made to the default database server:
If the DATABASE statement specifies a database server, as shown in the following example, the default connection is made to that database server:
In either case, the DEFAULT option of DISCONNECT disconnects this default connection. See "DEFAULT Option" and "Implicit Connection with DATABASE Statements" for more information about the default database server and implicit connections.
CURRENT Keyword
Use the CURRENT keyword with the DISCONNECT statement as a shorthand form of identifying the current connection. The CURRENT keyword replaces the current connection name. For example, the DISCONNECT statement in the following excerpt terminates the current connection to the database server mydbsrvr:
When a Transaction is Active
When a transaction is active, the DISCONNECT statement generates an error. The transaction remains active, and the application must explicitly commit it or roll it back. If an application terminates without issuing a DISCONNECT statement (because of a system crash or program error, for example), active transactions are rolled back.
Disconnecting in a Thread-Safe Environment
If you issue the DISCONNECT statement in a thread-safe ESQL/C application, keep in mind that an active connection can only be disconnected from within the thread in which it is active. Therefore, one thread cannot disconnect the active connection of another thread. The DISCONNECT statement generates an error if such an attempt is made.
However, once a connection becomes dormant, any other thread can disconnect this connection unless an ongoing transaction is associated with the dormant connection (the connection was established with the WITH CONCURRENT TRANSACTION clause of CONNECT). If the dormant connection was not established with the WITH CONCURRENT TRANSACTION clause, DISCONNECT generates an error when it tries to disconnect it.
See the SET CONNECTION statement on page 1-685 for an explanation of connections in a thread-safe ESQL/C application.
Specifying the ALL Option
Use the keyword ALL to terminate all connections established by the application up to that time. For example, the following DISCONNECT statement disconnects the current connection as well as all dormant connections:
The ALL keyword has the same effect on multithreaded applications that it has on single-threaded applications. Execution of the DISCONNECT ALL statement causes all connections in all threads to be terminated. However, the DISCONNECT ALL statement fails if any of the connections is in use or has an ongoing transaction associated with it. If either of these conditions is true, none of the connections is disconnected.
References
See the CONNECT, SET CONNECTION, and DATABASE statements in this manual.
For information on multithreaded applications, see the INFORMIX-ESQL/C Programmer's Manual.
|
An element in a collection variable 
