A sample program that uses a dynamic SELECT statement

=======================================================================
1. #include <stdio.h>
2. EXEC SQL include sqltypes;
3. 
4. EXEC SQL define NAME_LEN 15;
5. EXEC SQL define MAX_IDESC 4;
6. main()
7. {
8. EXEC SQL BEGIN DECLARE SECTION;
9.    int i;
10.    int desc_count;
11.    char demoquery[80];
12.   char queryvalue[2];
13.     char result[ NAME_LEN + 1 ];
14. EXEC SQL END DECLARE SECTION;
15.    printf("Modified DEMO4 Sample ESQL program running.\n\n");
16.    EXEC SQL connect to 'stores7';
=======================================================================

Lines 8 - 14

These lines declare host variables to hold the data obtained from the user and the column values retrieved from the system descriptor.

=======================================================================
17.    /* These next three lines have hard-wired both the query and
18.     * the value for the parameter. This information could have
19.     * been entered from the terminal and placed into the strings
20.     * demoquery and queryvalue, respectively.
21.     */
22.    sprintf(demoquery, "%s %s",
23.       "select fname, lname from customer",
24.       "where lname < ? ");
25.    EXEC SQL prepare demoid from :demoquery;
26.    EXEC SQL declare democursor cursor for demoid;
27.    EXEC SQL allocate descriptor 'demodesc' with max MAX_IDESC;
=======================================================================

Lines 17 - 25

The lines assemble the character string for the statement (in demoquery) and prepare it as the demoid statement identifier. The question mark (?) indicates the input parameter in the WHERE clause. For more information about these steps, see Assemble and prepare the SQL statement.

Line 26

This line declares the democursor cursor for the prepared statement identifier demoid. All non-singleton SELECT statements must have a declared cursor.

Line 27

To be able to use a system-descriptor area for the input parameters, you must first allocate the system-descriptor area. This ALLOCATE DESCRIPTOR statement allocates the demodesc system-descriptor area. For more information about ALLOCATE DESCRIPTOR, see Allocate memory for a system-descriptor area.

=======================================================================
28.    /*    This section of the program must evaluate :demoquery
29.     *    to count how many question marks are in the where 
30.     *   clause and what kind of data type is expected for each 
31.     *   question mark.
32.     *    For this example, there is one parameter of type 
33.     *   char(15). It would then obtain the value for 
34.     *   :queryvalue. The value of queryvalue is hard-wired in 
35.     *   the next line. 
36.     */
37.    sprintf(queryvalue, "C");
38.    desc_count = 1;
39.    if(desc_count > MAX_IDESC)
40.       {
41.       EXEC SQL deallocate descriptor 'demodesc';
42.       EXEC SQL allocate descriptor 'demodesc' with max :desc_count;
43.       }
44.    /* number of parameters to be held in descriptor is 1 */
45.    EXEC SQL set descriptor 'demodesc' COUNT = :desc_count; 
=======================================================================

Lines 28 - 38

These lines simulate the dynamic entry of the input parameter value. Although the parameter value is hard-coded here (line 37), the program would more likely obtain the value from user input. Line 38 simulates code that would determine how many input parameters exist in the statement string. If you did not know this value, you would need to include C code to parse the statement string for the question mark (?) character.

Lines 39 - 43

This if statement determines if the demodesc system-descriptor area contains enough item descriptors for the parameterized SELECT statement. It compares the number of input parameters in the statement string (desc_count) with the number of item descriptors currently allocated (MAX_IDESC). If the program has not allocated enough item descriptors, the program deallocates the existing system-descriptor area (line 41) and allocates a new one (line 42); it uses the actual number of input parameters in the WITH MAX clause to specify the number of item descriptors to allocate.

Lines 44 and 45

This SET DESCRIPTOR statement stores the number of input parameters in the COUNT field of the demodesc system-descriptor area.

=======================================================================
46.    /* Put the value of the parameter into the descriptor */
47.    i = SQLCHAR;
48.    EXEC SQL set descriptor 'demodesc' VALUE 1 
49.          TYPE = :i, LENGTH = 15, DATA = :queryvalue;
50.    /* Associate the cursor with the parameter value */
51.    EXEC SQL open democursor using sql descriptor :demodesc;
52.    /*Reuse the descriptor to determine the contents of the Select-
        * list*/
53.    EXEC SQL describe qid using sql descriptor 'demodesc';
54.    EXEC SQL get descriptor 'demodesc' :desc_count = COUNT;
55.    printf("There are %d returned columns:\n", desc_count);
56.    /* Print out what DESCRIBE returns */
57.    for (i = 1; i <= desc_count; i++)
58.       prsysdesc(i);
59.    printf("\n\n");
=======================================================================

Lines 47 - 49

This SET DESCRIPTOR statement sets the TYPE, LENGTH (for a CHAR value), and DATA fields for each of the parameters in the WHERE clause. The program only calls SET DESCRIPTOR once because it assumes that the SELECT statement has only one input parameter. If you do not know the number of input parameters at compile time, put the SET DESCRIPTOR in a loop for which the desc_count host variable controls the number of iterations.

Lines 50 and 51

The database server executes the SELECT statement when it opens the democursor cursor. This OPEN statement includes the USING SQL DESCRIPTOR clause to specify the demodesc system-descriptor area as the location of the input-parameter values.

Lines 52 - 59

The program also uses the demodesc system-descriptor area to hold the columns that are returned by the SELECT statement. The DESCRIBE statement (line 53) examines the select list to determine the number and data types of these columns. The GET DESCRIPTOR statement (line 54) then obtains the number of described columns from the COUNT field of demodesc. Lines 55 - 58 then display the column information for each returned column. For more information about how to use a system-descriptor area to receive column values, see Handling an unknown select list.

=======================================================================
60.    for (;;)
61.       {
62.       EXEC SQL fetch democursor using sql descriptor 'demodesc';
63.       if (sqlca.sqlcode != 0) break;
64.       for (i = 1; i <= desc_count; i++)
65.          {
66.          EXEC SQL get descriptor 'demodesc' VALUE :i :result = DATA;
67.          printf("%s ", result);
68.          }
69.       printf("\n");
70.       }
71.    if(strncmp(SQLSTATE, "02", 2) != 0)
72.       printf("SQLSTATE after fetch is %s\n", SQLSTATE);
73.    EXEC SQL close democursor;
74.    EXEC SQL free demoid; /* free resources for statement */
75.    EXEC SQL free democursor; /* free resources for cursor */
76.    /* free system-descriptor area */
77.    EXEC SQL deallocate descriptor 'demodesc';
78.    EXEC SQL disconnect current;
79.    printf("\nModified DEMO4 Program Over.\n\n");
80. }
=======================================================================

Lines 60 - 70

These lines access the fields of the item descriptor for each column in the select list. After each FETCH statement, the GET DESCRIPTOR statement loads the contents of the DATA field into the result host variable.

Line 73

After all the rows are fetched, the CLOSE statement frees the resources allocated to the active set of the democursor cursor.

Lines 74 - 77

The FREE statement on line 74 frees the resources allocated to the demoid statement identifier while the FREE statement on line 75 frees the resources to the democursor cursor. The DEALLOCATE DESCRIPTOR statement frees the resources allocated to the demodesc system-descriptor area. For more information, see Free memory allocated to a system-descriptor area.