Executing a cursor function

A cursor function can return one or more rows of return values to the application. To execute a cursor function, you must associate the EXECUTE FUNCTION statement with a function cursor and use the FETCH...INTO DESCRIPTOR statement to save the return value or values in an sqlda structure.

About this task

To use an sqlda structure to hold cursor-function return values:

Procedure

  1. Declare a function cursor for the user-defined function.

    Use the DECLARE statement to associate the EXECUTE FUNCTION statement with a function cursor.

  2. Use the OPEN statement to execute the function and open the cursor.
  3. Use the FETCH...USING DESCRIPTOR statement to retrieve the return values from the cursor into the sqlda structure.

    For more information, see Put column values into an sqlda structure.

  4. Retrieve the row data from the sqlda structure into host variables with C-language statements that access the sqldata field for each select-list column.

    For more information, see Assign and obtain values from an sqlda structure.

  5. Release memory allocated to the sqldata fields and the sqlda structure.

    For more information, see Free memory allocated to an sqlda structure.

Results

Only an external function that is defined as an iterator function can return more than one row of data. Therefore, you must define a function cursor to execute an iterator function dynamically. Each row of data consists of only one value because an external function can only return a single value. For each row, the sqlda structure contains only one sqlvar_struct structure with the single return value.

An SPL function whose RETURN statement includes the WITH RESUME keywords returns can return one or more rows of data. Therefore, you must define a function cursor to execute these SPL functions dynamically. Each row of data can consist of one or more values because an SPL function can return one or more values at one time. For each row, the sqlda structure contains an sqlvar_struct structure for each return value.