INFORMIX-ESQL/C Programmer's Manual Using a System-Descriptor Area

For an introduction on how to handle unknown return values from a user-defined function, see page 15-29. This section describes how to use a system-descriptor area to save values that a dynamically executed user-defined function returns.

1. Assemble and prepare an EXECUTE FUNCTION statement.

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

2. Allocate a system-descriptor area with the ALLOCATE DESCRIPTOR statement.

For more information, see Allocating Memory for a System-Descriptor Area.

3. Determine the number and data type(s) of the return value(s) with the DESCRIBE...USING SQL DESCRIPTOR statement.

The DESCRIBE...USING SQL DESCRIPTOR statement fills an item descriptor for each value that the user-defined function returns. For more information on DESCRIBE, see Initializing the System-Descriptor Area.

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

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

5. Execute the EXECUTE FUNCTION statement and store the return values in the system-descriptor area.

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.

6. Deallocate the system-descriptor area with the DEALLOCATE DESCRIPTOR statement.

See Freeing Memory Allocated to a System-Descriptor Area.

A noncursor function returns only one row of return values to the application. Use the EXECUTE...INTO SQL DESCRIPTOR statement to execute the function and save the return value(s) in a system-descriptor area.

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 SQL DESCRIPTOR to execute most external functions dynamically. This single row of data consists of only one value because external function can only return a single value. The system-descriptor area 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 SQL DESCRIPTOR to execute most SPL functions dynamically. An SPL function can return one or more values at one time so the system-descriptor area 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.

The following program fragment dynamically executes an SPL function called items_pct. This SPL function calculates the percentage that the items of a given manufacturer represent out of the total price of all items in the items table. It accepts one argument, the manu_code value for the chosen manufacturer, and it returns the percentage as a decimal value. Figure 16-3 shows the items_pct SPL function.

create function items_pct(mac char(3)) returning decimal;

	define tp money;

	define mc_tot money;

	define pct decimal;

	let tp = (select sum(total_price) from items);

	let mc_tot = (select sum(total_price) from items

			where manu_code = mac);

	let pct = mc_tot / tp;

	return pct;

end function;

The following program fragment uses a system-descriptor area to dynamically execute an SPL function that returns more than one set of return values.

#include <stdio.h>

#include <ctype.h>

EXEC SQL include sqltypes;

EXEC SQL include sqlstype;

EXEC SQL include decimal;

EXEC SQL include datetime;

extern char statement[80];

main()

{

EXEC SQL BEGIN DECLARE SECTION;

int sp_cnt, desc_count;

char dyn_stmnt[80], rout_name[30];

EXEC SQL END DECLARE SECTION;

int whenexp_chk();

printf("Sample ESQL program to execute an SPL function running.\n\n");

EXEC SQL whenever sqlerror call whenexp_chk;

EXEC SQL connect to 'stores7';

printf("Connected to stores7 database.\n");

/* These next five lines hard-wire the execute function

* statement. This information could have been entered

* by the user and placed into the string dyn_stmnt.

*/

stcopy("items_pct(\"HSK\")", rout_name);

sprintf(dyn_stmnt, "%s %s",

"execute function", rout_name);

The call to sprintf() (line 24) assembles the character string for the EXECUTE FUNCTION statement that executes the items_pct() SPL function.

EXEC SQL prepare spid from :dyn_stmnt;

EXEC SQL allocate descriptor 'spdesc';

EXEC SQL describe spid using sql descriptor 'spdesc';

if(SQLCODE != SQ_EXECPROC)

{

printf("\nPrepared statement is not EXECUTE FUNCTION.\n");

exit();

}

The PREPARE statement then creates the spid statement identifier for the EXECUTE FUNCTION statement. For more information on these steps, see Assembling and Preparing the SQL Statement.

The ALLOCATE DESCRIPTOR statement allocates the spdesc system-descriptor area. For more information, see Allocating Memory for a System-Descriptor Area.

The DESCRIBE statement determines the number and data types of values that the items_pct SPL function returns. This DESCRIBE includes the USING SQL DESCRIPTOR clause to specify the spdesc system-descriptor area as the location for these descriptions.

On line 28, the program tests the value of the SQLCODE variable (sqlca.sqlcode) against the constant values defined in the sqlstype.h file to verify that the EXECUTE FUNCTION statement was prepared. (For more information, see Determining the Statement Type.)

EXEC SQL get descriptor 'spdesc' :sp_cnt = COUNT;

if(sp_cnt == 0)

{

sprintf(dyn_stmnt, "%s %s", "execute procedure", rout_name);

EXEC SQL prepare spid from :dyn_stmnt;

EXEC SQL execute spid;

}

else

{

EXEC SQL declare sp_curs cursor for spid;

EXEC SQL open sp_curs;

while(getrow("spdesc"))

disp_data(:sp_cnt, "spdesc");

EXEC SQL close sp_curs;

EXEC SQL free sp_curs;

}

To obtain the number of return values in a host variable, the GET DESCRIPTOR statement retrieves the value of the COUNT field into a host variable. This value is useful when you need to determine how many values the SPL routine returns. If the SPL routine does not return values, that is, the value of COUNT is zero, the SPL routine is a procedure, not a function. Therefore, the program prepares an EXECUTE PROCEDURE statement (line 38) and then uses the EXECUTE statement (line 39) to execute the procedure. The EXECUTE statement does not need to use the system-descriptor area because the SPL procedure does not have any return values.

If the SPL routine does return values, that is, if the value of COUNT is greater than zero, the program declares and opens the sp_curs cursor for the prepared SPL function.

A while loop (lines 45 and 46) executes for each set of values that is returned by the SPL function. This loop calls the getrow() function to fetch one set of values into the spdesc system-descriptor area. It then calls the disp_data() function to display the returned values. For descriptions of the getrow() and disp_data() functions, see Guide to the dyn_sql.ec File.

After all the sets of return values are returned, the CLOSE statement (line 47) closes the sp_curs cursor and the FREE statement (line 48) releases the resources allocated to the cursor.

EXEC SQL free spid;

EXEC SQL deallocate descriptor 'spdesc';

EXEC SQL disconnect current;

}

This FREE statement releases the resources allocated for the prepared statement. The DEALLOCATE DESCRIPTOR statement releases the memory allocated to the spdesc system-descriptor area. For more information, see Freeing Memory Allocated to a System-Descriptor Area.

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 SQL DESCRIPTOR statement to save the return value(s) in a system-descriptor area.

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 SQL DESCRIPTOR statement to retrieve the return values from the cursor into the system-descriptor area.

For more information, see Putting Column Values into a System-Descriptor Area.

4. Use the GET DESCRIPTOR statement to retrieve the return values from the system-descriptor area into host variables

The DATA field of each item descriptor contains the return values. For more information, see Assigning and Obtaining Values from a System-Descriptor Area.

5. Deallocate the system-descriptor area with the DEALLOCATE DESCRIPTOR statement.

For more information, see Freeing Memory Allocated to a System-Descriptor Area.

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 system-descriptor area contains only one item descriptor with the single return value.

An SPL function whose RETURN statement includes the WITH RESUME keywords 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 consists of one or more values because an SPL function can return one or more values at one time. For each row, the system-descriptor area contains an item descriptor for each return value.