Handling Unknown Return Values
For an introduction on how to handle unknown return values from a user-defined function, see page 15-27. This section describes how to use a system-descriptor area to save values that a dynamically executed user-defined function returns.
To use a system-descriptor area to handle unknown function return values
1. Assemble and prepare an EXECUTE FUNCTION statement.
2. Allocate a system-descriptor area with the ALLOCATE DESCRIPTOR statement.
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.
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.
Executing a Noncursor Function
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.
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.
The following program fragment uses a system-descriptor area to dynamically execute an SPL function that returns more than one set of return values.
1 #include <stdio.h>
2 #include <ctype.h>
3 EXEC SQL include sqltypes;
4 EXEC SQL include sqlstype;
5 EXEC SQL include decimal;
6 EXEC SQL include datetime;
7 extern char statement[80];
8 main()
9 {
10 EXEC SQL BEGIN DECLARE SECTION;
11 int sp_cnt, desc_count;
12 char dyn_stmnt[80], rout_name[30];
13 EXEC SQL END DECLARE SECTION;
14 int whenexp_chk();
15 printf("Sample ESQL program to execute an SPL function running.\n\n");
16 EXEC SQL whenever sqlerror call whenexp_chk;
17 EXEC SQL connect to 'stores7';
18 printf("Connected to stores7 database.\n");
19 /* These next five lines hard-wire the execute function
20 * statement. This information could have been entered
21 * by the user and placed into the string dyn_stmnt.
22 */
23 stcopy("items_pct(\"HSK\")", rout_name);
24 sprintf(dyn_stmnt, "%s %s",
25 "execute function", rout_name);
Lines 19 to 25
The call to sprintf() (line 24) assembles the character string for the EXECUTE FUNCTION statement that executes the items_pct() SPL function.
26 EXEC SQL prepare spid from :dyn_stmnt;
27 EXEC SQL allocate descriptor 'spdesc';
28 EXEC SQL describe spid using sql descriptor 'spdesc';
29 if(SQLCODE != SQ_EXECPROC)
30 {
31 printf("\nPrepared statement is not EXECUTE FUNCTION.\n");
32 exit();
33 }
Line 26
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".
Line 27
The ALLOCATE DESCRIPTOR statement allocates the spdesc system-descriptor area. For more information, see "Allocating Memory for a System-Descriptor Area".
Lines 28 to 33
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 has been prepared. (For more information, see "Determining Statement Type".)
34 EXEC SQL get descriptor 'spdesc' :sp_cnt = COUNT;
35 if(sp_cnt == 0)
36 {
37 sprintf(dyn_stmnt, "%s %s", "execute procedure", rout_name);
38 EXEC SQL prepare spid from :dyn_stmnt;
39 EXEC SQL execute spid;
40 }
41 else
42 {
43 EXEC SQL declare sp_curs cursor for spid;
44 EXEC SQL open sp_curs;
45 while(getrow("spdesc"))
46 disp_data(:sp_cnt, "spdesc");
47 EXEC SQL close sp_curs;
48 EXEC SQL free sp_curs;
49 }
Lines 34 to 40
To save the number of return values in a host variable, the GET DESCRIPTOR statement obtains the value of the COUNT field. 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.
Lines 41 to 49
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.
50 EXEC SQL free spid;
51 EXEC SQL deallocate descriptor 'spdesc';
52 EXEC SQL disconnect current;
53 }
Lines 50 and 51
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".
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 SQL DESCRIPTOR statement to save the return value(s) in a system-descriptor area.
To use a system-descriptor area to hold cursor-function return values
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.
4. Use the GET DESCRIPTOR statement to retrieve the return values from the system-descriptor area into host variables
5. Deallocate the system-descriptor area with the DEALLOCATE DESCRIPTOR statement.
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 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 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.
|
