INFORMIX-ESQL/C Programmer's Manual Using an sqlda Structure

For an introduction on how to handle unknown return values from a user-defined function, see Determining Return Values Dynamically. This section describes how to use an sqlda structure to save values that a dynamically executed user-defined function returns.

1. Declare a variable to hold the address of an sqlda structure.

For more information, see Defining an sqlda Structure.

2. Assemble and prepare an EXECUTE FUNCTION statement.

The EXECUTE FUNCTION statement cannot contain the INTO clause. For more information, see Assembling and Preparing the SQL Statement.

3. Use the DESCRIBE...INTO statement to perform two tasks:

1. Allocate an sqlda structure. The address of the allocated structure is stored in the sqlda pointer that you declare. For more information, see Allocating Memory for the sqlda Structure.
2. Determine the number and data types of function return values. The DESCRIBE statement fills an sqlvar_struct structure for each return value. For more information, see Initializing the sqlda Structure.

1. After the DESCRIBE statement, you can test the SQLCODE variable (sqlca.sqlcode) for the defined constant SQ_EXECPROC to check for a prepared EXECUTE FUNCTION statement.

The SQ_EXECPROC constant is defined in the sqlstype.h header file. For more information, see Determining the Statement Type.

2. Examine the sqltype and sqllen fields of sqlda for each return value to determine the amount of memory that you need to allocate for the data. For more information, see Allocating Memory for Column Data.
3. Execute the EXECUTE FUNCTION statement and store the return values in the sqlda structure.

The statement you use to execute a user-defined function depends on whether the function is a noncursor function or a cursor function. The following sections discuss how to execute each type of function.

4. Deallocate any memory you allocated to the sqlda structure.

See Freeing Memory Allocated to an sqlda Structure.

A noncursor function returns only one row of return values to the application. Use the EXECUTE...INTO DESCRIPTOR statement to execute the function and save the return value(s) in an sqlda structure.

An external function that is not explicitly defined as an iterator function returns only a single row of data. Therefore, you can use EXECUTE...INTO DESCRIPTOR to execute most external functions dynamically and save their return values into an sqlda structure. This single row of data consists of only one value because an external function can only return a single value. The sqlda structure contains only one item descriptor with the single return value.

An SPL function whose RETURN statement does not include the WITH RESUME keywords returns only a single row of data. Therefore, you can use EXECUTE...INTO DESCRIPTOR to execute most SPL functions dynamically and save their return values into an sqlda structure. An SPL function can return one or more values at one time so the sqlda structure contains one or more item descriptors.

Warning: Because you usually do not know the number of returned rows that a user-defined function returns, you cannot guarantee that only one row will be returned. If you do not use a cursor to execute cursor function, ESQL/C generates a runtime error. Therefore, it is a good practice to always associate a user-defined function with a function cursor.

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(s) in an sqlda structure.

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 Putting 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 Assigning and Obtaining Values from an sqlda Structure.

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

For more information, see Freeing Memory Allocated to an sqlda Structure.

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 row 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.