A sample program that executes an SPL function

=======================================================================
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 - 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 about these steps, see Assemble and prepare the SQL statement.

Line 27

The ALLOCATE DESCRIPTOR statement allocates the spdesc system-descriptor area. For more information, see Allocate memory for a system-descriptor area.

Lines 28 - 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 was prepared. For more information, see Determine the 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 - 40

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.

Lines 41 - 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 Free memory allocated to a system-descriptor area.