Implementing an iterator function with a C user-defined function
About this task
To implement an iterator function with a C user-defined
function:
Procedure
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.
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.
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.
For each of the iterator-status values, take the appropriate
actions within the iterator function.
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, see Register a C UDR.
Example
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
The
following code fragment is an implementation of an iterator function
named fibGen(). This function builds an active
set that contains a Fibonacci series of numbers up to a specified
stop value.
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 The fibGen_init() initialization
function).
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 The fibGen_retone()
value-return function).
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 The fibGen_end() iterator-end function).
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 Call 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. 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. 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 The fibGen() iterator
function, 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. 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.