Calling an Iterator Function from an SQL Statement

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

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

Registering the Iterator 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.

The following CREATE FUNCTION statement registers the fibGen( ) iterator function, which Figure 89 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 or Linux 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.

Executing the Iterator Function

After you register an iterator function (and assign it the appropriate privileges), users who have the 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 an interactive database utility (such as 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.