Informix DataBlade API Programmer's Manual Creating Special-Purpose UDRs

An iterator function is a user-defined function that returns to its calling SQL statement several times, each time returning a value. The database server gathers these returned values together in an active set. To access a value in the active set, you must obtain it from a database cursor. Therefore, an iterator function is a cursor function because it must be associated with a cursor when it is executed.

Tip: This section describes how to create an iterator function that is written in C. For general information on how to create functions, see "Extending Informix Dynamic Server 2000."

The database server might execute an iterator function many times. It groups these iterations into the iterator-status values and puts the iterator status for a given iteration in the MI_FPARAM structure. Within an iterator function, you examine the MI_FPARAM structure for an iterator status to determine which actions the iterator function must take.

Tip: The Informix BladeSmith development tool, which is part of the DataBlade Developers Kit, automatically generates C source code for an iterator function as well as the SQL statements to register the iterator function. For more information, see the "DataBlade Developers Kit User's Guide."

The following table lists the different points at which the database server calls an iterator function and the associated iterator-status values.

To implement an iterator function with a C user-defined function, follow these steps:

1. Declare the iterator function so that its return value has a data type that is compatible with one of the items in the active set.

For example, to return an active set of integer values, declare the iterator function to return the mi_integer data type.

2. Include an MI_FPARAM structure as the last parameter of the C declaration of the iterator function.

The MI_FPARAM structure holds the iterator status, the iterator-completion flag, and the user-state pointer.

3. Within the iterator function, obtain the iterator status from the MI_FPARAM structure with the mi_fp_request() function.

This function returns the iterator-status constant (SET_INIT, SET_RETONE, or SET_END) that the database server has set for the distinct groups of iterations of the iterator function.

4. For each of the iterator-status values, take the appropriate actions within the iterator function.

   Iterator-Status Value	For More Information
   SET_INIT	Initializing the Iterations
   SET_RETONE	Returning One Active-Set Item
   SET_END	Releasing Iteration Resources

5. Register the iterator function as a user-defined function with the ITERATOR routine modifier in the CREATE FUNCTION statement.

Omit the MI_FPARAM parameter from the parameter list when you register the iterator function. For more information on how to register a user-defined routine, see Registering a C UDR.

The Fibonacci series is a list of numbers for which each value is the sum of the previous two. For example, the Fibonacci series up to a stop value of 20 is as follows:

0, 1, 1, 2, 3, 5, 8, 13

Figure 13-1 is an implementation of an iterator function called fibGen(). This function builds an active set that contains a Fibonacci series of numbers up to a specified stop value.

Figure 13-1 The fibGen() Iterator Function

The database server calls this fibGen() iterator function at the following execution points:

• Once, to initialize the calculation of the Fibonacci series of numbers

At this point, the database server has set the iterator status to SET_INIT and fibGen() calls the fibGen_init() function (see Figure 13-2 on page 13-10).

• Repeatedly, to calculate each number in the series until a number exceeds the stop value

As long as the number is less than the stop value, the database server sets the iterator status to SET_RETONE and fibGen() calls the fibGen_retone() function (see Figure 13-3 on page 13-11).

• Once, to deallocate resources that the iterator function uses

At this point, the database server has set the iterator status to SET_END and fibGen() calls the fibGen_end() function (see Figure 13-4 on page 13-13).

Tip: For end users to be able to use an iterator function within an SQL statement, you must register the iterator function with the ITERATOR routine modifier of the CREATE FUNCTION statement. For more information, see Calling an Iterator Function from an SQL Statement.

When the iterator function reaches the last item, call the mi_fp_setisdone() function to set the iterator-completion flag of the MI_FPARAM structure to one (1). This flag indicates to the database server that it has reached the end condition for the iterator function. The database server no longer needs to continue calling the iterator function with the SET_RETONE iterator-status value. Instead, it calls the iterator function one more time, with the SET_END status value.

Important: Make sure that you include a call to the mi_fp_setisdone() function within your iterator function that sets the iterator-completion flag to one (1). Without this call, the database server never reaches an end condition for the iteration, which causes it to iterate the function in an infinite loop.

In Figure 13-1 on page 13-6, the fibGen() iterator function determines if it has reached an end condition after it calls fibGen_retone(). It makes this determination as follows:

• If this number is greater than the user-specified stop value (in the fib_endval field of the user-state information), the end condition was reached.

The fibGen() function calls the mi_fp_setisdone() function to set the iterator-completion flag to 1. The function then exits with a return value of zero (0). However, this last return value of 0 is not returned as part of the active set. The database server calls the next iteration of fibGen() with an iterator-status value of SET_END.

• If the next Fibonacci number is less than or equal to the stop value, the end condition was not reached.

The function returns this next number in the Fibonacci series to the active set. The database server calls the next iteration of fibGen() with an iterator-status value of SET_RETONE.

The first time that the database server calls the iterator function, the database server passes it an MI_FPARAM structure with the iterator status set to SET_INIT and the user-state pointer set to NULL. When the iterator function obtains this iterator-status value, it can perform the following initialization tasks for the iterator function:

• Allocate memory for the structure in which you save the user-state information with a DataBlade API memory-management function such as mi_dalloc().

Make sure that this memory has a memory duration of PER_COMMAND so that the user state remains for the duration of all iterations of the iterator function. If the memory has the default PER_ROUTINE memory duration, the database server automatically deallocates it after only one iteration of the iteration function. For more information, see Determining Memory Duration.

• Save the user-state pointer in the MI_FPARAM structure with the mi_fp_funcstate() function.

Each subsequent call to the iterator function uses the same MI_FPARAM structure, so each iteration can reuse the cached user-state memory. The iterator function must save enough information to return values one at a time on demand. For more information, see Saving a User State.

You can perform these initialization tasks directly in the iterator function or you can declare a separate iterator-initialization function, which the iterator function calls when it receives the SET_INIT iterator-status value. Declare the iterator-initialization function to return the same data type as the main iterator function. However, the database server ignores the return value of this function; it does not put this return value in the active set.

Figure 13-2 implements an iterator-initialization function, call fibGen_init(), which the fibGen() iterator function (Figure 13-1 on page 13-6) calls when it obtains the SET_INIT iterator-status value.

Figure 13-2 The fibGen_init() Initialization Function

The fibGen_init() function returns an mi_integer value (0) because the main iterator function, fibGen(), returns an active set of mi_integer values. However, the database server does not return this value as part of the active set. Once fibGen_init() completes, the database server calls the next iteration of fibGen() with an iterator-status value of SET_RETONE, to return the first item of the active set.

When the iterator status is SET_RETONE, the iterator function can return one item of the active set. When the iterator function obtains this iterator-status value, it can perform the iteration tasks needed to generate one item of the active set.

You can perform these iterator tasks directly in the iterator function or you can declare a separate iterator-value-return function, which the iterator function calls when it receives the SET_RETONE iterator-status value. Declare the iterator-value-return function to return the same data type as the main iterator function. The database server puts the return value of this function in the active set.

Figure 13-3 implements an iterator-value-return function, called fibGen_retone(), which the fibGen() iterator function (Figure 13-1 on page 13-6) calls each time it obtains the SET_RETONE iterator status.

Figure 13-3 The fibGen_retone() Value-Return Function

Each item in the active set that the fibGen() function generates is one call to the fibGen_retone() function. The fibGen_retone() function returns one number of the Fibonacci series. It uses the mi_fp_funcstate() function to obtain the user-state pointer from the MI_FPARAM structure. This user-state pointer points to a fibstate structure (which the fibGen_init() function in Figure 13-2 on page 13-10 allocated).

From the information in the fibstate structure, the fibGen_retone() function determines the next Fibonacci number and stores it in the next variable. The function then updates the fibstate structure for the next iteration of fibGen(). Finally, the function returns one item of the active set: the value of next.

Once the mi_fp_setisdone() function sets the iterator-completion flag to 1, the database server calls the iterator function one last time with the iterator-status value in the MI_FPARAM structure set to SET_END. When the iterator function obtains this iterator-status value, it can perform any tasks needed to deallocate resources that the iterator function has allocated.

Important: Only free resources that you have allocated. Do not attempt to free resources that the database server has allocated (such as MI_FPARAM).

You can perform these deallocation tasks directly in the iterator function or you can declare a separate iterator-end function, which the iterator function calls when it receives the SET_END iterator-status value. Declare the iterator-end function to return the same data type as the main iterator function. However, the database server ignores the return value of this function; it does not put this return value in the active set.

Figure 13-4 implements an iterator-end function, called fibGen_end(), which the fibGen() iterator function (see Figure 13-1 on page 13-6) calls when it obtains the SET_END iterator-status value.

The fibGen_end() function uses the mi_fp_funcstate() function to obtain the user-state pointer from the MI_FPARAM structure. It then calls the mi_free() function to free the resources in the fibstate state structure, which the fibGen_init() function (see Figure 13-2 on page 13-10) has allocated. The fibGen_end() function returns an mi_integer value (0) because the main iterator function, fibGen(), returns an active set of mi_integer values.

For end users to be able to use an iterator function within an SQL statement, take the following steps:

• Register the iterator function with the ITERATOR routine modifier.
• Associate the iterator function with a cursor to execute the function.

Register the iterator function with the CREATE FUNCTION statement. The CREATE FUNCTION must include the ITERATOR routine modifier to tell the database server that it must call the function until the iterator-completion flag is set to 1.

Tip: If your iterator function calls other functions (such as an iteration-initialization function, iterator-value-return function, or iterator-end function) to implement its iterations, you do not have to register these other functions with the CREATE FUNCTION statement.

The following CREATE FUNCTION statement registers the fibGen() iterator function, which Figure 13-1 on page 13-6 defines, in the database:

CREATE FUNCTION fibgen(arg INTEGER)
RETURNING INTEGER
WITH (ITERATOR)
EXTERNAL NAME "$USERFUNCDIR/fib.so"
LANGUAGE C

This statement assumes that the object code for the fibGen() function resides in the UNIX fib.so shared-object file in the directory that the USERFUNCDIR environment variable specifies. It also assumes that USERFUNCDIR was set in the server environment.

For more information on how to register a user-defined function, see Registering a C UDR.

Once you register an iterator function (and assign it the appropriate privileges), users who have Execute privilege can execute it. However, because an iterator function returns an active set of items, you must associate the function with a cursor. The cursor holds the active set, which the application can then access one at a time.

Each iteration of the iterator function returns one item of the active set. To execute an iterator function, you must associate it with a cursor. This EXECUTE FUNCTION statement generates an active set that contains the following Fibonacci values:

0, 1, 1, 2, 3, 5, 8

To obtain these values from within an application, you must associate the EXECUTE FUNCTION statement with a cursor to execute the function.

Once you register the fibGen() function, you can execute the following SQL statement from DB-Access:

EXECUTE FUNCTION  fibgen(10)

From within a DataBlade API module, execute fibGen() with the mi_exec_prepared_statement() function, as follows:

MI_CONNECTION *conn;
mi_string *cmd = "EXECUTE FUNCTION fibgen(10);";
MI_STATEMENT *stmt;
mi_integer error, col_val;
MI_ROW *row;
...
/* Prepare the EXECUTE FUNCTION to execute fibGen() */
stmt = mi_prepare(conn, cmd, NULL);

/* Open the cursor to allocate the active set */
if ( mi_open_prepared_statement(stmt, MI_SEND_READ, 1, 0,
		NULL, NULL, NULL, NULL, NULL, 0, NULL) == MI_OK )

	/* Initialize the fetch direction */
	if ( mi_fetch_statement(stmt, MI_CURSOR_NEXT, 0, 0)
			== MI_OK )

		if ( mi_get_result(conn) == MI_ROWS )
			{
			/* Fetch the items of the active set. 
			  * Process each item of active set until 
			  * last item is found */
			while ( (row = mi_next_row(conn, &error)) 
					!= NULL )
				{
				colval = NULL;

				/* Obtain one number of Fibonacci series */
				mi_value(row, 0, (mi_integer)&col_val,
					sizeof(mi_integer));

				/* Process current Fibonacci number */
				....

				} /* end while */
			} /* end if mi_get_result */

/* Close the cursor to deallocate active set */
mi_close_statement(stmt);

/* Release statement descriptor */
mi_drop_prepared_statement(stmt);

For more information on how to use mi_exec_prepared_statement(), see Executing Prepared SQL Statements.