Open a collection with 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.

Cursor attribute	Control flag	Valid operations
Read-only cursor	MI_COLL_READONLY	Fetch only
Sequential (nonscrolling) cursor	MI_COLL_NOSCROLL	Fetch forward only (MI_CURSOR_NEXT) through the collection elements Any fetch operation that moves the cursor position backward in the cursor is not valid.

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