Opening a Collection

Once you have a collection structure for a collection, you can open the collection with one of the functions in Table 20.

Both of the functions in Table 20 are constructor functions for a collection descriptor. Use this collection descriptor in calls to DataBlade API functions that access the collection.

When one of the functions in Table 20 opens a collection, it creates a collection cursor, which is an area of memory that serves as a holding place for collection elements. This cursor has an associated cursor position, which points to one element of the collection cursor. When these functions complete, the cursor position points to the first element of the collection.

The difference between the mi_collection_open( ) and mi_collection_open_with_options( ) functions is the open mode that they create for the collection cursor.

Using mi_collection_open( )

When you open a collection with mi_collection_open( ), you obtain an update scroll cursor to hold the collection elements. Therefore, you can perform the following operations on a collection opened with mi_collection_open( ).

Cursor Attribute

Valid Operations

Read/write cursor

Insert, delete, update, fetch

Scroll cursor

Fetch forward and backward through the collection elements

All Fetch operations are valid. (See Table 21)

Figure 15 shows an example of using the mi_collection_open( ) function to create and open a LIST collection with INTEGER elements.

/*
 * Create and open a collection 
*/
MI_CONNECTION *conn;
MI_COLL_DESC *coll_desc;
MI_COLLECTION *coll_ptr;
MI_TYPEID *type_id;
...
type_id = mi_typestring_to_id(conn, "list(integer not null)");
coll_ptr = mi_collection_create(conn, type_id);
coll_desc = mi_collection_open(conn, coll_ptr);

Figure 16 shows the cursor position after the mi_collection_open( ) call.

Figure 16. Collection Cursor After the Collection Is Opened

Using mi_collection_open_with_options( )

When you open a collection with mi_collection_open_with_options( ), you can override the cursor characteristics that mi_collection_open( ) uses. The control argument of mi_collection_open_with_options( ) can create a collection cursor with any of the cursor characteristics in the following table.

Most collections need the capabilities of the read/write scroll cursor that mi_collection_open( ) creates. However, the database server can perform a special optimization for a collection from a collection subquery if you use a read-only sequential cursor to hold the collection subquery. It can fetch each row of the subquery on demand. That is, you can fetch the elements one at a time with mi_collection_fetch( ). You can use mi_collection_open( ) or mi_collection_open_with_options( ) to create some other type of cursor for a collection subquery. However, if a collection subquery resides in some other type of cursor, the database server fetches all the rows of the subquery and puts them in the collection cursor.

To create a collection subquery, preface the query with the MULTISET keyword. For example, the following SQL statement creates a collection subquery of order numbers for customer 120 and then sends them to the check_orders( ) user-defined function (which expects a MULTISET argument):

SELECT check_orders(
   MULTISET(SELECT ITEM order_num FROM orders 
      WHERE customer_num = 120))
FROM customer
WHERE customer_num = 120;

To have the database server perform the collection-subquery optimization, use the following call to mi_collection_open_with_options( ) when you open a collection subquery:

mi_collection_open_with_options(conn, coll_ptr,
   (MI_COLL_READONLY | MI_COLL_NOSCROLL));