Accessing a Collection
ESQL/C supports the SQL collection data types with the ESQL/C collection data type for host variables. A collection data type is a complex data type that contains one or more members called elements. All elements have the same data type. Universal Server supports the following kinds of collections:
To access the elements of a column in a Universal Server table that has a collection type (LIST, MULTISET, or SET) as its data type, perform the following steps:
1. Declare a collection host variable, either typed or untyped.
2. Allocate memory for the collection host variable.
3. Perform any select, insert, update, or delete operations on the collection host variable.
4. Save the contents of the collection host variable into the collection column.
The following sections describe each of these steps in more detail.
Declaring Collection Variables
Use the collection data type to declare host variables for database columns of collection data types (SET, MULTISET, or LIST). To declare a collection host variable, use the collection keyword as the variable data type, as the following syntax shows.
A collection variable can be any SQL collection type: LIST, MULTISET, or SET.
Typed and Untyped Collection Variables
ESQL/C supports the following two collection variables:
The Typed Collection Variable
A typed collection variable provides an exact description of the collection. This declaration specifies the data type of the collection (SET, MULTISET, or LIST) and the element type for the collection variable.
Figure 9-1 shows declarations for three typed collection variables.
Typed collection variables can contain elements with the following data types:
When you specify the element type of the collection variable, use the SQL data types, not the ESQL/C data types. For example, as the declaration for the list1 variable in Figure 9-1 illustrates, use the SQL SMALLINT data type, not the ESQL/C short data type, to declare a LIST variable whose elements are small integers. Similarly, use the SQL syntax for a CHAR column to declare a SET variable whose elements are character strings, as the following example illustrates:
A named row type is not valid as the element type of a collection variable. However, you can specify an element type of unnamed row type, whose fields match those of the named row type.
For example, suppose your database has the named row type, myrow, and the database table, mytable, that are defined as follows:
You can define a collection variable for the col2 column of mytable as follows:
You can declare a typed collection variable whose element type is different from that of the collection column as long as the two data types are compatible. If the database server is able to convert between the two element types, it automatically performs this conversion when it returns the fetched collection.
Suppose you create the tab1 table as follows:
You can declare a typed collection variable whose element type matches (set_int) or one whose element type is compatible (set_float), as follows:
When it executes the first FETCH statement, the ESQL/C client program automatically converts the integer elements in the column to the float values in the set_float host variable. The ESQL/C program only generates a type-mismatch error if you change the host variable after the first fetch. In the preceding code fragment, the second FETCH statement generates a type-mismatch error because the initial fetch has already defined the element type as float.
Use a typed collection variable in the following cases:
Match the declaration of a typed collection variable exactly with the data type of the collection column. You can then use this collection variable directly in SQL statements such as INSERT, DELETE, or UPDATE, or in the collection-derived table clause. (For more information on the collection-derived table clause, see "Using the Collection-Derived Table Clause on Collections".)
In a single declaration line, you can declare several collection variables for the same typed collection, as the following declaration shows:
You cannot declare collection variables for different collection types in a single declaration line.
The Untyped Collection Variable
An untyped collection variable provides a general description of a collection. This declaration includes only the collection keyword and the variable name. The following lines declare three untyped collection variables:
The advantage of an untyped collection host variable is that it provides more flexibility in collection definition. For an untyped collection variable, you do not have to know the definition of the collection column at compile time. Instead, you obtain, at runtime, a description of the collection from a collection column with the SELECT statement.
To obtain the description of a collection column, execute a SELECT statement to retrieve the column into the untyped collection variable. The database server returns the column description (the collection type and the element type) with the column data. ESQL/C assigns this definition of the collection column to the untyped collection variable.
For example, suppose the a_coll host variable is declared as an untyped collection variable, as follows:
The following code fragment uses a SELECT statement to initialize the a_coll variable with the definition of the list_col collection column (which Figure 9-2 defines) before it uses the collection variable in an INSERT statement:
To obtain the description of a collection column, your application must verify that a collection column has data in it before it selects the column. If the table has no rows in it, the SELECT statement returns neither column data nor the column description and ESQL/C cannot assign the column description to the untyped collection variable.
You can use an untyped collection variable to store collections with different column definitions, as long as you select the associated collection column description into the collection variable before you use the variable in an SQL statement.
Client Collections
The ESQL/C application declares the collection variable name, allocates the memory for it with the ALLOCATE COLLECTION statement, and performs operations on the collection data.
To access the elements of a collection variable, specify the variable in the Collection Derived Table clause of a SELECT, INSERT, UPDATE, or DELETE statement. ESQL/C will perform the select, insert, update, or delete operation. ESQL/C does not send these statements to the database server when they include a client collection variable in the collection-derived table clause.
For example, ESQL/C performs the following INSERT operation on the a_multiset collection variable:
When an SQL statement includes a collection variable, it has the following syntax restrictions:
For more information about how to insert into a collection variable, see "Inserting into a Collection Variable". For more information on a collection-derived table, see "Operating on a Collection Variable".)
Managing Memory for Collections
ESQL/C does not automatically allocate or deallocate memory for collection variables. You must explicitly manage the memory that is allocated to a collection variable.
Use the following SQL statements to manage memory for both typed and untyped collection host variables:
This collection variable can be a typed or untyped collection. The ALLOCATE COLLECTION statement sets SQLCODE (sqlca.sqlcode) to zero if the memory allocation was successful and a negative error code if the allocation failed.
Once you free the collection variable with the DEALLOCATE COLLECTION statement, you can reuse the collection variable.
The following code fragment declares the a_set host variable as a typed collection, allocates memory for this variable, then deallocates memory for this variable:
The ALLOCATE COLLECTION statement allocates memory for the collection variable and the collection data. For syntax information for the ALLOCATE COLLECTION and DEALLOCATE COLLECTION statements, refer to their descriptions in the Informix Guide to SQL: Syntax.
Operating on a Collection Variable
Universal Server supports access to a collection column as a whole through the SELECT, UPDATE, INSERT, and DELETE statements. For example, the SELECT statement can retrieve all elements of a collection, and the UPDATE statement can update all elements in a collection to a single value. (For more information, see "Operating on a Collection Column".)
The SELECT, INSERT, UPDATE, and DELETE statements cannot access elements of a collection column in a table. To access elements in a collection column, an ESQL/C application constructs a subtable, called a collection-derived table, in the collection host variable. From collection-derived table, the ESQL/C application to access the elements of the collection variable as rows of a table.
This section discusses the following topics on how to use a collection-derived table in an ESQL/C application to access a collection column:
Using the Collection-Derived Table Clause on Collections
The collection-derived table clause allows you to specify a collection host variable as a table name. This clause has the following syntax:
In this example, coll_var is a collection host variable. It can be either a typed or untyped collection host variable, but it must be declared and have memory allocated in the ESQL/C application before it appears in a collection-derived table clause.
For more information on the syntax of the collection-derived table clause, see the description of the collection-derived table segment in the Informix Guide to SQL: Syntax.
Accessing a Collection Variable
In SQL statements, the ESQL/C application specifies a collection-derived table in place of a table name to perform the following operations on the collection host variable:
Once the collection host variable contains valid elements, you update the collection column with the contents of the host variable. For more information, see "Saving into a Collection Column". For more information on the syntax of the collection-derived table clause, see the description of the collection-derived table segment in the Informix Guide to SQL: Syntax.
Distinguishing Between Columns and Collection Variables
When you use the collection-derived table clause with a collection host variable in an SQL statement (such as SELECT, INSERT, or UPDATE), the statement is not sent to the database server for processing. Instead, ESQL/C processes the statement. Consequently, some of the syntax checking that the database server performs is not done on SQL statements that include the collection-derived table clause.
In particular, the ESQL/C preprocessor cannot distinguish between column names and host variables. Therefore, when you use the collection-derived table clause with an UPDATE or INSERT statement, you must use valid host-variable syntax in:
For more information, see "Distinguishing Between Columns and Row Variables".
Initializing a Collection Variable
You must always initialize an untyped collection variable by selecting a collection column into it. You must execute a SELECT statement, regardless of the operation you wish to perform on the untyped collection variable.
You can initialize a collection variable by selecting a collection column into the collection variable, constructing the SELECT statement as follows:
You can initialize a typed collection variable by executing an INSERT statement that uses the collection derived table syntax. You do not need to initialize a typed collection variable before an INSERT or UPDATE because ESQL/C has a description of the collection variable.
Suppose, for example, that you create the tab_list and tab_set tables with the statements in Figure 9-2.
The following code fragment accesses the set_col column with a typed collection host variable called a_set:
When you use a typed collection host variable, the description of the collection column (the collection type and the element type) should be compatible with the corresponding description of the typed collection host variable. If the data types don't match, the database server will do a cast if it can. The SELECT statement in the preceding code fragment successfully retrieves the set_col column because the a_set host variable has the same collection type (SET) and element type (INTEGER) as the set_col column.
The following SELECT statement succeeds because the database server casts list_col column to a set in a_set host variable and discards any duplicates:
You can select any type of collection into an untyped collection host variable. The following code fragment uses an untyped collection host variable to access the list_col and set_col columns that Figure 9-2 defines:
Both SELECT statements in this code fragment can successfully retrieve collection columns into the a_collection host variable.
Once you have initialized the collection host variable, you can use the collection-derived table clause to select, update, or delete existing elements in the collection or to insert additional elements into the collection. For more information, see the following sections.
Inserting into a Collection Variable
To insert one or more elements into a collection variable, use the INSERT statement with the collection-derived table clause after the INTO keyword. The collection- derived table clause identifies the collection variable in which to insert the elements. Associate the INSERT statement and the collection-derived table clause with a cursor to insert more than one element into a collection variable.
For information on the types of values you can insert into a collection variable, see "Specifying Element Values".
Inserting One Element
The INSERT statement and the collection-derived table clause allow you to insert one element into a collection. ESQL/C inserts the values that the VALUES clause specifies into the collection variable that the collection-derived table clause specifies.
To Insert into SET and MULTISET Collections
For SET and MULTISET collections, the position of the new element is undefined, because the elements of these collections do not have ordered positions. Suppose the table readings has the following declaration:
To access the time_dataset column, the typed ESQL/C host variable time_vals has the following declaration:
The following INSERT statement adds a new MULTISET element of 1,423,231 to time_vals:
For more information on the ifx_int8cvint() function and the INT8 data type, see Chapter 5, "Working with Numeric Data Types."
To Insert into LIST Collections
LIST collections have elements that have ordered positions. If the collection is of type LIST, you can use the AT clause of the INSERT statement to specify the position within the list at which you want to add the new element. Suppose the table rankings has the following declaration:
To access the item_rankings column, the typed ESQL/C host variable rankings has the following declaration:
The following INSERT statement adds a new list element of 9 as the new third element of rankings:
Suppose that before this insert, rankings contained the elements {1,8,4,5,2}. After this insert, this variable contains the elements {1,8,9,4,5,2}.
If you do not specify the AT clause, INSERT adds new elements at the end of a LIST collection. For more information on the AT clause, see the description of the INSERT statement in the Informix Guide to SQL: Syntax.
Inserting More Than One Element
An insert 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 insert cursor for the collection variable with the DECLARE statement and open the cursor with the OPEN statement.
3. Put the element(s) into the collection variable with the PUT statement and the FROM clause.
4. Close the insert cursor with the CLOSE statement, and if you no longer need the cursor, free it with the FREE statement.
5. 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).
The following sections provide information on how to declare an insert cursor for a collection variable, put elements into this cursor, and save the insert cursor into the collection variable. For sample code that inserts several elements into a collection variable, see Figure 9-3.
To Declare an Insert Cursor for a Collection Variable
An insert cursor allows you to insert one or more elements in the collection. To declare an insert cursor for a collection variable, include the collection-derived table clause in the INSERT statement that you associate with the cursor. The insert cursor for a collection variable has the following restrictions:
If you need to use input parameters, you must prepare the INSERT statement and specify the prepared statement identifier in the DECLARE statement.
You can use input parameters to specify the following items:
The following DECLARE statement declares the list_curs insert cursor for the a_list variable:
EXEC SQL prepare ins_stmt from
'insert into table(:a_list) values (?)';
EXEC SQL declare list_curs cursor for ins_stmt;
EXEC SQL open list_curs;
You can then use the PUT statement to specify the values to insert. For a code fragment that includes this statement, see Figure 9-3.
The following DECLARE statement declares the list_curs2 insert cursor for the a_list variable:
EXEC SQL prepare ins_stmt2 from
'insert into table(?) values (?)';
EXEC SQL declare list_curs2 cursor for ins_stmt2;
EXEC SQL open list_curs2 using :a_list;
while (1)
{
EXEC SQL put list_curs2 from :an_element;
.
.
.
}
The USING clause of the OPEN statement specifies the name of the collection variable. You can then use the PUT statement to specify the values to insert. For information on how to use the PUT statement, see "To Put Elements into the Insert Cursor."
After you declare the insert cursor, you can open it with the OPEN statement. You can insert elements into the collection variable once the associated insert cursor is open.
To Put Elements into the Insert Cursor
To put elements, one at a time, into the insert cursor, use the PUT statement and the FROM clause. The PUT statement identifies the insert cursor that is associated with the collection variable. The FROM clause identifies the element value to be inserted into the cursor. The data type of any host variable in the FROM clause must be compatible with the element type of the collection.
To indicate that the collection element is to be provided later by the FROM clause of the PUT statement, use an input parameter in the VALUES clause of the INSERT statement. However, you must prepare the INSERT statement when it contains input parameters. No input parameters can appear in the DECLARE statement.
Figure 9-3 contains a code fragment that demonstrates how to insert elements into the collection variable a_list and then to update the list_col column of the tab_list table (which Figure 9-2 defines) with this new collection.
In Figure 9-3, the first statement that accesses the a_list variable is the OPEN statement. Therefore, at this point in the code, ESQL/C must be able to determine the data type of the a_list variable. Because the a_list host variable is a typed collection variable, ESQL/C can determine the data type from the variable declaration. However, if a_list was declared an untyped collection variable, you would need a SELECT statement before the DECLARE statement executes to return the definition of the associated collection column.
ESQL/C automatically saves the contents of the insert cursor into the collection variable when you put them into the insert cursor with the PUT statement.
To Free Cursor Resources
The CLOSE statement explicitly frees resources assigned to the insert cursor. However, the cursor ID still exists, so you can reopen the cursor with the OPEN statement. The FREE statement explicitly frees the cursor ID. To reuse the cursor, you must redeclare it with the DECLARE statement.
The FLUSH statement does not have an effect on an insert cursor that is associated with a collection variable. For the syntax of the CLOSE statement, see the Informix Guide to SQL: Syntax.
Selecting from a Collection Variable
The SELECT statement with the collection-derived table clause allows you to select elements from a collection variable. The collection-derived table clause identifies the collection variable from which to select the elements. The SELECT statement on a client collection variable (one that has the collection-derived table clause) has the following restrictions:
These columns cannot use the database@server:table.column syntax.
The SELECT statement and the collection-derived table clause allow you to perform the following operations on a collection variable:
Use the SELECT statement with the collection-derived table clause.
Use the SELECT statement with the collection-derived table clause and a row variable.
Associate the SELECT statement and the collection-derived table clause with a cursor to declare a select cursor for the collection variable.
Selecting One Element
The SELECT statement and the collection-derived table clause allow you to select one element into a collection. The INTO clause identifies the variable in which to store the element value that is selected from the collection variable. The data type of the host variable in the INTO clause must be compatible with the element type of the collection.
The following code fragment selects only one element from the set_col column (see Figure 9-2) with a typed collection host variable called a_set:
If the element of the collection is itself a complex type (collection or row type), the collection is a nested collection. For information on how to use a cursor to select elements from a nested collection, see "Selecting Values from a Nested Collection". The following section describes how to use a row variable to select a row element from a collection.
Selecting One Row Element
You can select an entire row element from a collection into a row type host variable. The INTO clause identifies a row variable in which to store the row element that is selected from the collection variable.
The following code fragment selects one row from the set_col column into the row type host variable a_row:
Selecting More Than One Element
A select cursor that includes a SELECT statement with the collection-derived table clause allows you to select many elements from a collection variable. To select elements, follow these steps:
1. Create a client collection variable in your ESQL/C program.
2. Declare the select cursor for the collection variable with the DECLARE statement and open this cursor with the OPEN statement.
3. Fetch the element(s) from the collection variable 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.
5. Close the select cursor with the CLOSE statement, and if you no longer need the cursor, free it with the FREE statement.
To Declare 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. 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.
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. For example, the following DECLARE statement declares a select cursor for the collection variable, a_set:
To select the element(s) from the collection variable, use the FETCH statement with the INTO clause. For more information, see "To Fetch Elements from the Select 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 "Deleting One Element" and "Updating One Element".
To Fetch Elements from the Select Cursor
To fetch elements, one at a time, from a collection variable, use the FETCH statement and the INTO clause. The FETCH statement identifies the select cursor that is associated with the collection variable. The INTO clause identifies the host variable for the element value that is fetched from the collection variable. The data type of the host variable in the INTO clause must be compatible with the element type of the collection.
Figure 9-4 contains a code fragment that selects all elements from the set_col column (see Figure 9-2) into the typed collection host variable called a_set then fetches these elements, one at a time, from the a_set collection variable.
Updating a Collection Variable
Once you have initialized a collection host variable with a collection column (see page 9-16), you can use the UPDATE statement with the collection-derived table clause to update the elements in the collection. The collection-derived table clause identifies the collection variable whose elements are to be updated. The UPDATE statement and the collection-derived table clause allow you to perform the following operations on a collection variable:
Use the UPDATE statement (without the WHERE CURRENT OF clause) and specify a derived column name in the SET clause.
You must declare an update cursor for the collection variable and use UPDATE with the WHERE CURRENT OF clause.
Neither form of the UPDATE statement can include a WHERE clause. For information on the types of values you can specify when you update a collection variable, see "Specifying Element Values".
Updating All Elements
You cannot include a WHERE clause on an UPDATE statement with a collection-derived table clause. Therefore, an UPDATE statement on a collection variable sets all elements in the collection to the value you specify in the SET clause. No update cursor is required to update all elements of a collection.
For example, the following UPDATE changes all elements in the a_list ESQL/C collection variable to a value of 16:
In this example, the derived column list_elmt provides an alias to identify an element of the collection in the SET clause.
Updating One Element
To update a particular element in a collection, declare an update cursor for the collection host variable. An update cursor for a collection variable is a select cursor that has been declared with the FOR UPDATE keywords. The update cursor allows you to sequentially scroll through the elements of the collection and update the current element with the UPDATE...WHERE CURRENT OF statement.
To update elements, follow these steps:
1. Create a client collection variable in your ESQL/C program.
2. Declare the update cursor for the collection variable with the DECLARE statement and the FOR UPDATE clause; open this cursor with the OPEN statement.
By default, a select cursor on a collection variable supports updates. For more information about how to declare a select cursor, see page 9-27.
3. Fetch the element(s) from the collection variable with the FETCH statement and the INTO clause.
4. Update the fetched data with the UPDATE statement and the WHERE CURRENT OF clause.
5. Save the modified collection variable in the collection column.
6. Close the update cursor with the CLOSE statement, and if you no longer need the cursor, free it with the FREE statement.
The application must position the update cursor on the element to be updated and then use UPDATE...WHERE CURRENT OF to update this value.
The ESQL/C program in Figure 9-5 uses an update cursor to update an element in the collection variable, a_set, and then to update the set_col column of the tab_set table (see Figure 9-2).
Specifying Element Values
You can specify any of the following values as elements in a collection variable:
The host variable must contain a value whose data type is compatible with the element type of the collection.
You cannot include complex expressions directly to specific values.
For information on how to insert elements into a collection variable, see "Inserting into a Collection Variable". For information on how to update elements in a collection variable, see "Updating a Collection Variable". The following sections describe the values you can assign to an element in a collection variable.
Literal Values as Elements
You can use a literal value to specify an element of a collection variable. The literal values must have a data type that is compatible with the element type of the collection. For example, the following INSERT statement inserts a literal integer into a SET(INTEGER NOT NULL) host variable called a_set:
The following UPDATE statement uses a derived column name (an_element) to update all elements of the a_set collection variable with the literal value of 19:
The following INSERT statement inserts a quoted string into a LIST(CHAR(5)) host variable called a_set2:
The following INSERT statement inserts a literal collection into a SET(LIST(INTEGER NOT NULL) host variable called nested_coll:
The following UPDATE statement updates the nested_coll collection variable with a new literal collection value:
ESQL/C Host Variables as Elements
You can use an ESQL/C host variable to specify an element of a collection variable. The host variable must be declared with a data type that is compatible with the element type of the collection and must contain a value that is also compatible. For example, the following INSERT statement uses a host variable to insert a single value into the same a_set variable as in the preceding example:
To insert multiple values into a collection variable, you can use an INSERT statement for each value or you can declare an insert cursor and use the PUT statement. For more information, see "Inserting More Than One Element".
The following UPDATE statement uses a host variable to update all elements in the a_set collection to a value of 4:
To update multiple values into a collection variable, you can declare an update cursor and use the WHERE CURRENT OF clause of the UPDATE statement. For more information, see "Updating One Element".
Deleting Elements from a Collection Variable
Once you have initialized a collection host variable with a collection column (see page 9-16), you can use the DELETE statement and the collection-derived table clause to delete an element of a collection variable. The collection-derived table clause identifies the collection variable in which to delete the elements.
The DELETE statement and the collection-derived table clause allow you to perform the following operations on a collection variable:
Use the DELETE statement (without the WHERE CURRENT OF clause).
You must declare an update cursor for the collection variable and use DELETE with the WHERE CURRENT OF clause.
Neither form of the DELETE statement can include a WHERE clause.
Deleting All Elements
You cannot include a WHERE clause on a DELETE statement with a collection-derived table clause. Therefore, a DELETE statement on a collection variable deletes all elements from the collection. No update cursor is required to delete all elements of a collection.
For example, the following DELETE removes all elements in the a_list ESQL/C collection variable:
Deleting One Element
To delete a particular element in a collection, declare an update cursor for the collection host variable. An update cursor for a collection variable is a select cursor that has been declared with the FOR UPDATE keywords. The update cursor allows you to sequentially scroll through the elements of the collection and delete the current element with the DELETE...WHERE CURRENT OF statement.
To delete particular elements, follow the same steps for how to update particular elements (see "Updating One Element"). In these steps, you replace the use of the UPDATE...WHERE CURRENT OF statement with the DELETE...WHERE CURRENT OF statement.
The application must position the update cursor on the element to be deleted and then use DELETE...WHERE CURRENT OF to delete this value. The following ESQL/C code fragment uses an update cursor and a DELETE statement with a WHERE CURRENT OF clause to delete the element from the set_col column of tab_set (see Figure 9-2).
Suppose that in the row with an id_col value of 6, the set_col column contains the values {1,8,4,5,2} before this code fragment executes. After the DELETE...WHERE CURRENT OF statement, 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.
Accessing a Nested Collection
Universal Server supports nested collections as a column type. A nested collection is a collection column whose element type is another collection. For example, the code fragment in Figure 9-6 creates the tab_setlist table whose column is a nested collection.
The setlist_col column is a set, each element of which is a list. This nested collection resembles a two-dimensional array with a y-axis of set elements and an x-axis of list elements.
Selecting Values from a Nested Collection
To select values from a nested collection, you must declare a collection variable and a select cursor for each level of collection. The following code fragment uses the nested collection variable, nested_coll and the collection variable list_coll to select the lowest-level elements in the nested-collection column, setlist_col.
Inserting Values into a Nested Collection
To insert literal values into a collection variable for a nested column, you specify the literal collection for the element type. You do not need to specify the constructor keyword for the actual collection type. The following typed collection host variable can access the setlist_col column of the tab_setlist table:
The following code fragment inserts literal values into the nested_coll collection variable and then updates the setlist_col column (which Figure 9-6 defines):
To insert nonliteral values into a nested collection, you must declare a collection variable and an insert cursor for each level of collection. For example, the following code fragment uses the nested collection variable, nested_coll, to insert new elements into the nested-collection column, setlist_col.
Saving into a Collection Column
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 explicitly save the contents of the variable into the collection column with one of the following statements:
Figure 9-5 shows the UPDATE statement that saves the contents of the a_set variable in the set_col collection column.
Figure 9-3 shows the INSERT statement that saves the contents of the a_list variable in the list_col collection column.
In the INSERT and UPDATE statements, a collection host variable is valid anywhere an ESQL/C host variable is valid. For more information, see "Inserting into and Updating a Collection Column".
Operating on a Collection Column
You can use the SELECT, UPDATE, INSERT, and DELETE statements to access a collection column (SET, MULTISET, or LIST), as follows:
The following sections summarize these SQL statements with collection columns. For more information on how to use these statements with collection columns, see the Informix Guide to SQL: Tutorial.
Selecting from a Collection Column
To select all elements in a collection column, specify the collection column in the select list of the SELECT statement. If you put a collection host variable in the INTO clause of the SELECT statement, you can access these elements from an ESQL/C application. For more information, see "Initializing a Collection Variable".
Inserting into and Updating a Collection Column
The INSERT and UPDATE statements support collection columns as follows:
In the VALUES clause of an INSERT statement or the SET clause of an UPDATE statement, the element values can be in any of the following formats:
To represent literal values for a collection column, you specify a literal-collection value. You create a literal-collection value, introduce the value with the SET, MULTISET, or LIST keyword and provide the field values in a comma-separated list that is enclosed in braces. You surround the entire literal-collection value with quotes (double or single). The following INSERT statement inserts the literal collection of SET {7, 12, 59, 4} into the set_col column in the tab_set table (that Figure 9-2 defines):
The UPDATE statement in Figure 9-7 overwrites the SET values that the previous INSERT added to the tab_set table.
If any character value appears within this literal-collection value, it too must be enclosed in quotes; this condition creates nested quotes. Nested quotes can occur in the following cases:
For a col1 column of type SET(CHAR(5), a literal value would be:
'SET{"abcde"}'
For a col2 column whose data type is LIST(ROW(a INTEGER, b SMALLINT) NOT NULL), a literal value would be:
'LIST{"ROW(80, 3)"}'
Rules for grouping nested quotes can be found in the descriptions of the Literal Collection segment in the Informix Guide to SQL: Syntax.
To specify nested quotes within an SQL statement in an ESQL/C program, you must escape every double quote when it appears within a single-quote string. The following two INSERT statements show how to use escape characters for inner double quotes:
When you embed a double-quoted string inside another double-quoted string, you do not need to escape the inner-most quotation marks, as the following INSERT statement shows:
For more information on the syntax of literal values for collection variables, see "Literal Values as Elements". For more information on the syntax of literal-collection values for collection columns, see the Literal Collection segment in the Informix Guide to SQL: Syntax.
Deleting an Entire Collection
To delete the entire collection in a collection column, specify the table, view, or synonym name after the FROM keyword of the DELETE statement and use the WHERE clause to identify the row or rows that you want to delete.
The following DELETE statement deletes the tab_set row that contains the LIST collection that the UPDATE statement in Figure 9-7 saves:
ifx_cl_card()
The ifx_cl_card() function returns the cardinality of the specified collection type host variable.
Syntax
Usage
The ifx_cl_card() function enables you to determine the number of elements in a collection, whether the collection is empty, and whether the collection is null.
Return Values
Example
This sample program is in the ifx_cl_card.ec file in the demo directory.
Example Output
Accessing Row Types
ESQL/C supports the SQL row types with the ESQL/C row data type for host variables. A row type is a complex data type that contains one or more members called fields. Each field has a name and a data type associated with it. Universal Server supports the following two kinds of row types:
You can use a particular unnamed row type as the data type of one column in the database. You create an unnamed row type with the ROW constructor in the column definition of a CREATE TABLE statement.
The named row type is a template for a row definition. You create a named row type with the CREATE ROW TYPE statement. You can then use a named row type as follows:
The database server checks data type values.
For more information about row types, see the CREATE ROW TYPE statement in the Informix Guide to SQL: Syntax and the Informix Guide to SQL: Reference.
To access a column in a Universal Server table that has a row type (named or unnamed) as its data type, perform the following steps:
1. Declare a row host variable.
2. Allocate memory for the row host variable with the ALLOCATE ROW statement.
3. Perform any select or update operations on the row host variable.
4. Save the contents of the row host variable in the row-type column.
The following sections describe each of these steps in more detail.
Declaring Row Variables
Use the row data type to declare host variables for database columns of named or unnamed row types. To declare a row host variable, use the row keyword as the variable data type, as the following syntax shows.
Typed and Untyped Row Variables
ESQL/C supports the following two row variables:
ESQL/C handles row variables as client-side collection variables.
The Typed Row Variable
A typed row variable provides an exact description of the row. This declaration specifies a field list, which contains the name and data type of each field in the row. Figure 9-8 shows declarations for three typed row variables.
Typed row variables can contain fields with the following data types:
When you specify the field type of a field in the row variable, use the SQL data types, not ESQL/C data types. For example, to declare a row variable with a field that holds small integers, use the SQL SMALLINT data type, not the ESQL/C int data type. Similarly, to declare a field whose values are character strings, use the SQL syntax for a CHAR column, not the C syntax for char variables. For example, the following declaration of the row_var host variable contains a field of small integers and a character field:
Use a typed row variable when you know the exact data type of the row-type column whose data you store in the row variable. Match the declaration of a typed row variable exactly with the data type of the row-type column. You can use this row variable directly in SQL statements such as INSERT, DELETE, or UPDATE. You can also use it in the collection-derived table clause.
The benefit of a typed row variable is that ESQL/C is more restrictive with respect to type matching between the information you put in the typed row variable (with the SELECT statement or with C assignment statements) and the definition of the host variable. If you try to place anything incompatible with the definition into the typed row variable, ESQL/C returns an error.
You can declare several row variables in a single declaration line. However, all variables must have the same field types, as the following declaration shows:
The Untyped Row Variable
An untyped row variable provides a general description of a row. This declaration includes only the row keyword and the variable name. The following lines declare three untyped row variables:
The advantage of an untyped row host variable is that it provides more flexibility in row definition. For an untyped row variable, you do not have to know the definition of the row-type column at compile time. Instead, you obtain, at runtime, a description of the row from a row-type column.
To obtain this description at runtime, execute a SELECT statement that retrieves the row-type column into the untyped row variable. When the database server executes the SELECT statement, it returns the data-type information for the row-type column (the field types of the fields in the row) to the client application. The client application then uses this information to assign a data type to the untyped row variable.
For example, suppose the a_row host variable is declared as an untyped row variable, as follows:
The following code fragment uses a SELECT statement to initialize the a_row variable with data-type information before it uses the row variable in an UPDATE statement:
You can use an untyped row variable to store row types with different data-type information, as long as you select the associated row-type column description into the row variable before you use the variable in an SQL statement.
The untyped row variable has the following restrictions:
Using Named Row Types
ESQL/C supports the specification of a named row type as part of the declaration of a row variable. A named row type has a name associated with the row structure. You create a named row type in the database with the CREATE ROW TYPE statement. If the database contains more than one row type with the same structure but with distinctly different names, the database server cannot properly enforce structural equivalence when it compares named row types. To resolve this ambiguity, specify a row-type name in the declaration of the row variable.
A named ESQL/C row variable can be typed or untyped. A typed named row variable includes a list of declarations for the fields in the row. An untyped named row variable omits the field declarations. For more information, see "Typed and Untyped Collection Variables".
The ESQL/C preprocessor does not check the validity of a row-type name and ESQL/C does not use this name at runtime. ESQL/C just sends this name to the database server to provide information for type resolution. Therefore, ESQL/C treats the a_row variable in the following declaration as an untyped row variable even though a row-type name is specified:
If you specify both the row-type name and a row structure in the declaration (a typed named row variable), the row-type name overrides the structure. For example, suppose the database contains the following definition of the address_t named row type:
In the following declaration, the another_row host variable has line1 and line2 fields of type CHAR(20) (from the address_t row type:), not CHAR(10) as the declaration specifies:
In a Collection-Derived Table
You cannot specify a named row type to declare a row variable that you use in a collection-derived table. ESQL/C does not have information about the named row type, only the database server does. For example, suppose your database has a named row type, r1, and a table, tab1, that are defined as follows:
To access this column, suppose you declare two row variables, as follows:
With these declarations, the following statement succeeds because ESQL/C has the information it needs about the structure of row1:
The following statement fails; however, because ESQL/C does not have the necessary information to determine the correct storage structure of the row2 row variable.
Similarly, the following statement also fails. In this case, ESQL/C treats r1 as a user-defined type instead of a named row type.
You can get around this restriction in either of the following ways:
For this method to work, at least one row must exist in the tab1 table.
An UPDATE statement that uses either the row2 or row2_untyped row variable in its collection-derived table clause can now execute successfully.
Client-Side Rows
When you declare a row variable (typed or untyped), the client application declares the row variable name, allocates the memory, and performs operations on the row. Therefore, a row variable is sometimes called a client-side row.
To access the elements of a row variable, you specify the variable in the collection-derived table clause of a SELECT or UPDATE statement. When either of these statements contain a collection-derived table clause, ESQL/C performs the select or update operation on the row variable; it does not send these statements to the database server for execution. For example, ESQL/C executes the update operation on the row variable, a_row, that the following UPDATE statement specifies:
Because row variables use client-side processing, SQL statements have the following syntax restrictions when they include a row variable:
For more information on the collection-derived table clause, see "Operating on a Row Variable".
Managing Memory for Rows
Once you declare a row variable, ESQL/C recognizes the variable name. For typed row variables, ESQL/C also recognizes the associated data type. However, ESQL/C does not automatically allocate or deallocate memory for row variables. You must explicitly manage memory that is allocated to a row variable. To manage memory for both typed and untyped row host variables, use the following SQL statements:
This row variable can be a typed or untyped row. The ALLOCATE ROW statement sets SQLCODE (sqlca.sqlcode) to zero if the memory allocation was successful and a negative error code if the allocation failed.
Once you free the row variable with the DEALLOCATE ROW statement, you can reuse the row variable.
The following code fragment declares the a_name host variable as a typed row, allocates memory for this variable, then deallocates memory for this variable:
You must explicitly manage the client-side memory for a row variable with the ALLOCATE ROW and DEALLOCATE ROW statements. For syntax information for the ALLOCATE ROW and DEALLOCATE ROW statements, refer to their descriptions in the Informix Guide to SQL: Syntax.
Operating on a Row Variable
The SELECT, UPDATE, INSERT, and DELETE statements allow you to access a row-type column as a whole. Universal Server can also access individual fields in a row-type column, as follows:
In addition, an ESQL/C client application can access individual fields as follows:
Unlike with collection columns, the SELECT statement can access individual members of row-type columns. Therefore, an ESQL/C client application can directly select or update fields in row-type columns of the database. For more information, see "Operating on a Row-Type Column".
With a row host variable, the ESQL/C application accesses the row-type column as a collection-derived table. A collection-derived table allows the application to decompose a row into its fields, which the application can then access individually. The collection-derived table contains a single table row and each column in this table row is a field of the row-type column.
The application first performs the operations on the fields through the row host variable. Once modifications are complete, the application can save the contents of the row variable into a row-type column of the database.
This section discusses the following topics on how to use a collection-derived table in an ESQL/C application to access a row-type column:
Using the Collection-Derived-Table Clause on Row Types
The collection-derived table clause allows you to create a collection-derived table from a row-type column. This clause has the following syntax:
In the preceding syntax, row_var is a row host variable. It can be either a typed or untyped row host variable but it must already be declared.
For more information on the syntax of the collection-derived table clause, see the description of the collection-derived table segment in the Informix Guide to SQL: Syntax.
Accessing a Row Variable
In SQL statements, use a collection-derived table in place of a table name to access the row host variable. You can perform the following operations on the row host variable with the collection-derived table clause:
The insert and delete operations are not supported on row variables. For more information, see page 9-64 and page 9-68.
Once the row host variable contains valid elements, you update the row-type column with the contents of the host variable. For more information, see "Saving into a Row-Type Column". For more information on the syntax of the collection-derived table clause, see the description of the collection-derived table segment in the Informix Guide to SQL: Syntax.
Distinguishing Between Columns and Row Variables
When you use the collection-derived table clause with an SQL statement (such as SELECT, INSERT, or UPDATE), the statement is not sent to the database server for processing. Instead, ESQL/C processes the SQL statement. Therefore, some of the syntax checking that the database server performs is not done on SQL statements that include the collection-derived table clause.
In particular, the ESQL/C preprocessor cannot distinguish between column names and host variables. Therefore, when you use the collection-derived table clause with an UPDATE or INSERT statement values to modify a row host variable, the preprocessor does not check that you correctly specify any host variables. You must ensure that you use valid host-variable syntax for any host variables that contain values in:
If you omit the host-variable symbol (colon (:) or dollar sign ($)), the preprocessor assumes that the name is a column name. For example, the following UPDATE statement omits the colon for the clob_ins host variable in the SET clause:
The ESQL/C preprocessor assumes that clob_ins is a column and does not catch the missing colon for the clob_ins host variable. The client application does not return an error when it executes the UPDATE, because nothing is syntactically wrong with the statement. Therefore, you might think that the UPDATE successfully added the contents of the clob_ins host variable to the clob_fld field of the named_row1 row variable. Instead, ESQL/C has stored a null value in the clob_fld field because no valid value was provided in the SET clause and no column named clob_ins exists.
You must ensure that the syntax for host variables is correct when you use these variables to specify values for row variables.
Initializing a Row Variable
To perform operations on existing fields in a row-type column, you must first initialize the row variable with the field values. To perform this initialization, select the existing fields of the row-type column into a row variable with the SELECT statement, as follows:
Suppose you create the tab_unmrow and tab_nmrow tables with the statements in Figure 9-9.
The following code fragment initializes a typed row host variable called a_rect with the contents of the rectangle column in the row whose area column is 1234:
When you use a typed row host variable, the data types of the row-type column (the field types) must be compatible with the corresponding data types of the typed row host variable. The SELECT statement in the preceding code fragment successfully retrieves the rectangle column because the a_rect host variable has the same field types as the rectangle column.
The following SELECT statement fails because the data types of the fields in the emp_name column and the a_rect host variable do not match:
You can select any row into an untyped row host variable. The following code fragment uses an untyped row host variable to access the emp_name and rectangle columns that Figure 9-9 defines:
Both SELECT statements in this code fragment can successfully retrieve row-type columns into the an_untyped_row host variable. However, ESQL/C does not perform type checking on an untyped row host variable because its elements do not have a predefined data type.
Once you have initialized the row host variable, you can use the collection-derived table clause to select or update existing fields in the row. For more information, see the following sections.
Inserting into a Row Variable
An insert operation does not apply to a row variable because an insert normally adds a row to a table. The row variable represents the row-type value as a single table row in the collection-derived table. Each field in the row type is a column in this table. You cannot add new table rows to the collection-derived table. Therefore, the INSERT statement does not support a row variable in the collection-derived table clause. ESQL/C returns an error if you attempt to perform an insert operation on a row variable.
However, you can use the UPDATE statement to insert new field values into a row variable. For more information, see "Updating a Row Variable".
Selecting from a Row Variable
The SELECT statement and the collection-derived table clause allow you to select a particular field or group of fields in the row variable. The INTO clause identifies the row variable that holds the field value(s) selected from the row-type column. The data type of the host variable in the INTO clause must be compatible with the field type.
For example, Figure 9-10 contains a code fragment that puts the value of the width field (in the row variable myrect) into the rect_width host variable.
The SELECT statement on a row variable (one that contains a collection-derived table clause) has the following restrictions:
These columns cannot use the database@server:table.column syntax.
If the row variable is a nested row, a SELECT statement cannot use dot notation to access the fields of the inner row. Instead, you must declare a row variable for each row type. The code fragment in Figure 9-11 shows how to access the fields of the inner row in the nested_row host variable.
The following SELECT statement is not valid to access the x and y fields of the nested_row variable because it uses dot notation:
An ESQL/C application can use dot notation to access fields of a nested row when a SELECT statement accesses a database column. For more information, see "Selecting Fields of a Row Column",
Updating a Row Variable
Once you have initialized a row host variable with a row-type column (see page 9-62), you can use the UPDATE statement with the collection-derived table clause to update the fields in the row type. The collection-derived table clause identifies the row variable whose fields are to be updated.
The UPDATE statement and the collection-derived table clause allow you to update a particular field or group of fields in the row variable. You specify the new field value(s) in the SET clause. An UPDATE of a field or fields in a row variable cannot include a WHERE clause.
For example, the following UPDATE changes the x and y fields in myrect ESQL/C row variable:
Suppose that after the SELECT statement, the myrect2 variable has the values x=0, y=0, length=8, and width=8. After the UPDATE statement, myrect2 variable has field values of x=3, y=4, length=8, and width=8.
You cannot use a row variable in the collection-derived table clause of an INSERT statement. However, you can use the UPDATE statement and the collection-derived table clause to insert new field values into a row host variable, as long as you specify a value for every field in the row. For example, the following code fragment inserts new field values into the row variable myrect and then inserts this row variable into the database:
For information on the types of values you can specify when you update a row variable, see "Specifying Field Values".
Deleting from a Row Variable
A delete operation does not apply to a row variable because a delete normally removes a row from a table. The row variable represents the row-type value as a single table row in the collection-derived table. Each field in the row type is a column in this table. You cannot remove this single table row from the collection-derived table. Therefore, the DELETE statement does not support a row variable in the collection-derived table clause. ESQL/C returns an error if you attempt to perform a DELETE operation on a row variable.
However, you can use the UPDATE statement to modify existing field values in a row variable. For more information, see "Updating a Row Variable".
Specifying Field Names
ESQL/C is case insensitive with regard to the field names of a row variable. In a SELECT, INSERT, UPDATE, or DELETE statement, ESQL/C always interprets field names of a row variable as lowercase. For example, in the following SELECT statement, ESQL/C interprets the fields to select as x and y (lowercase), even though the SELECT statement specifies them in uppercase:
This behavior is consistent with how the database server handles identifier names in SQL statements. To maintain the case of a field name, specify the field name as a delimited identifier. That is, surround the field name in double quotes and enable the DELIMIDENT environment variable before you compile the program.
ESQL/C interprets the fields to select as X and Y (uppercase) in the following SELECT statement (assuming the DELIMIDENT environment variable is enabled):
For more information on delimited identifiers and the DELIMIDENT environment variable, see "SQL Identifiers".
Specifying Field Values
You can specify any of the following values for fields in a row variable:
You cannot include complex expressions directly to specify field values. However, a constructed row provides support for expressions as field values.
You cannot include complex expressions directly to specific values.
For information on how to update field values into a row variable, see "Updating a Row Variable". The following sections describe the values you can assign to an element in a row variable.
Literal Values as Field Values
You can use a literal value to specify a field value for a row variable. The literal values must have a data type that is compatible with the field type. For example, the following UPDATE statement specifies a literal integer as a field value for the length field of the myrect variable that Figure 9-8 defines:
The following UPDATE statement updates the x- and y-coordinate fields of the myrect variable:
The following UPDATE statement updates a ROW(a INTEGER, b CHAR(5)) host variable called a_row2 with a quoted string:
The following UPDATE statement updates the nested_row host variable (which Figure 9-11 defines) with a literal row:
The following UPDATE statement updates the nested_row variable with a new literal-row value:
Constructed Rows
You can use a constructed row to specify an expression as a field value for a row variable. The constructed expression must use a row constructor and evaluate to a data type that is compatible with the field type of the field. Suppose you have a nested-row variable that is declared as follows:
The following UPDATE statement uses the ROW row constructor to specify expressions in the value for the fld2 field of the a_nested_row variable:
For more information on the syntax of a row constructor, see the Expression segment in the Informix Guide to SQL: Syntax.
ESQL/C Host Variables as Field Values
You can use an ESQL/C host variable to specify a field value for a row variable. The host variable must be declared with a data type that is compatible with the field type of the field and must contain a value that is also compatible. For example, the following UPDATE statement uses a host variable to update a single value into the a_row variable that was declared in "Literal Values as Field Values":
To insert multiple values into a row variable, you can use an UPDATE statement for each value or you can specify all field values in a single UPDATE statement:
The following UPDATE statement performs the same task as the preceding UPDATE:
Saving into a Row-Type Column
The row variable stores the fields of the row type. However, it has no intrinsic connection with a database column. Once the row variable contains the correct field values, you must then explicitly save the contents of the variable into the row-type column with one of the following statements:
In the INSERT and UPDATE statements, a row host variable is valid anywhere that an ESQL/C host variable is valid.
Accessing a Typed Table
You can use a row variable to access the columns of a typed table. A typed table is a table that has been created with the OF TYPE clause of the CREATE TABLE statement. This table obtains the information for its columns from a named row type.
Suppose you create a typed table called names from the full_name named row type that Figure 9-9 defines:
You can access a row of the names typed table with a row variable. The following code fragment declares a_name as a typed row variable and selects a table row of the names table into this row variable:
The last SELECT statement accesses the lname column value of the names row through the row variable, a_name. For more information about typed tables, see the CREATE TABLE statement in the Informix Guide to SQL: Syntax and the Informix Guide to SQL: Tutorial.
Operating on a Row-Type Column
You can use the SELECT, UPDATE, INSERT, and DELETE statements to access a row-type column (named or unnamed), as follows:
The following sections summarize these SQL statements with row-type columns. For more information on how to use these statements with row-type columns, see the Informix Guide to SQL: Tutorial.
Selecting from a Row-Type Column
The SELECT statement allows you to access a row-type column in the following ways:
Selecting the Entire Row-Type Column
To select all fields in a row-type column, specify the row-type column in the select list of the SELECT statement. If you put a row host variable in the INTO clause of the SELECT statement, you can access these fields from an ESQL/C application. For more information, see "Initializing a Row Variable".
Selecting Fields of a Row Column
You can access an individual field in a row-type column with dot notation. Dot notation allows you to qualify an SQL identifier with another SQL identifier. You separate the identifiers with the period (.) symbol. The following code fragment uses a single SELECT statement to perform the same task as the two SELECT statements in Figure 9-10:
For more information on dot notation, see the Column Expression section of the Expression segment in the Informix Guide to SQL: Syntax.
Inserting into and Updating Row-Type Columns
The INSERT and UPDATE statements support row-type columns as follows:
In the VALUES clause of an INSERT statement or the SET clause of an UPDATE statement, the field values can be in any of the following formats:
To represent literal values for a row-type column, you specify a literal-row value. You create a literal-row value or a named or unnamed row type, introduce the value with the ROW keyword and provide the field values in a comma-separated list that is enclosed in parentheses. You surround the entire literal-row value with quotes (double or single). The following INSERT statement inserts the literal row of ROW(0, 0, 4, 5) into the rectangle column in the tab_unmrow table (that Figure 9-9 defines):
The UPDATE statement in Figure 9-12 overwrites the SET values that the previous INSERT added to the tab_unmrow table.
If any character value appears within this literal-row value, it too must be enclosed in quotes; this condition creates nested quotes. Nested quotes can occur in the following cases:
For a row1 column of row type ROW(id INTEGER, name CHAR(5), a literal value would be:
'ROW(6, "dexter")'
For a col2 column whose data type is ROW(a INTEGER, b SET (INTEGER)), a literal value would be:
'ROW(1, "SET{80, 81, 82, 83}")'
Rules for grouping nested quotes can be found in the descriptions of the Literal Row segment in the Informix Guide to SQL: Syntax.
To specify nested quotes within an SQL statement in an ESQL/C program, you must escape every double quote when it appears within a single-quote string. The following two INSERT statements show how to use escape characters for inner quotes:
When you embed a double-quoted string inside another double-quoted string, you do not need to escape the inner-most quotation marks:
For more information on the syntax of literal values for row variables, see "Literal Values as Field Values". For more information on the syntax of literal-row values, see the Literal Row segment in the Informix Guide to SQL: Syntax.
Deleting an Entire Row Type
To delete all fields in a row-type column, specify the table, view, or synonym name after the FROM keyword of the DELETE statement and use the WHERE clause to identify the table row(s) that you want to delete.
The following DELETE statement deletes the row in the tab_unmrow table that contains the row type that the UPDATE statement in Figure 9-12 saves:
|
