Handling an Unknown Select List
For an introduction to how to handle unknown columns in an unknown select list, see page 15-24. This section describes how to use a system-descriptor area to handle a SELECT statement.
To use a system-descriptor area to handle unknown select-list columns:
1. Prepare the SELECT statement (with the PREPARE statement) to give it a statement identifier. The SELECT statement cannot include an INTO TEMP 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 types of the select-list columns with the DESCRIBE...USING SQL DESCRIPTOR statement. DESCRIBE fills an item descriptor for each column in the select list. For more information on DESCRIBE, see "Initializing the System-Descriptor Area".
4. Save the number of select-list columns in a host variable with the GET DESCRIPTOR statement to obtain the value of the COUNT field.
5. Declare and open a cursor and then use the FETCH...USING SQL DESCRIPTOR statement to fetch column values, one row at a time, into an allocated system-descriptor area. See "Putting Column Values into a System-Descriptor Area".
6. Retrieve the row data from the system-descriptor area into host variables with the GET DESCRIPTOR statement to access the DATA field. For more information on GET DESCRIPTOR, see "Assigning and Obtaining Values from a System-Descriptor Area".
7. Deallocate the system-descriptor area with the DEALLOCATE DESCRIPTOR statement. For more information, see "Freeing Memory Allocated to a System-Descriptor Area".
The following sections demonstrate how to handle a SELECT statement that returns many rows and one that returns only one row.
Executing a SELECT That Returns Multiple Rows
The demo4.ec sample program, shown in this section, shows how to execute a dynamic SELECT statement with the following conditions:
The SELECT must be associated with a cursor, executed with the OPEN statement, and have its return values retrieved with the FETCH...USING SQL DESCRIPTOR statement.
The OPEN statement does not need to include the USING clause.
The FETCH statement includes the USING SQL DESCRIPTOR clause to store the return values in an sqlda structure.
This demo4 program is a version of the demo3 sample program (page 17-18) that uses a system-descriptor area to hold select-list columns. The demo4 program does not include exception handling.
1 #include <stdio.h>
2 EXEC SQL define NAME_LEN 15;
3 main()
4 {
5 EXEC SQL BEGIN DECLARE SECTION;
6 int i;
7 int desc_count;
8 char demoquery[80];
9 char colname[19];
10 char result[ NAME_LEN + 1 ];
11 EXEC SQL END DECLARE SECTION;
Lines 5 to 11
These lines declare host variables to hold the data that is obtained from the user and the column values that are retrieved from the system-descriptor area.
12 printf("DEMO4 Sample ESQL program running.\n\n");
13 EXEC SQL connect to 'stores7';
14 /* These next three lines have hard-wired both the query and
15 * the value for the parameter. This information could have been
16 * been entered from the terminal and placed into the strings
17 * demoquery and the query value string (queryvalue), respectively.
18 */
19 sprintf(demoquery, "%s %s",
20 "select fname, lname from customer",
21 "where lname < 'C' ");
22 EXEC SQL prepare demo4id from :demoquery;
23 EXEC SQL declare demo4cursor cursor for demo4id;
24 EXEC SQL allocate descriptor 'demo4desc' with max 4;
25 EXEC SQL open demo4cursor;
Lines 14 to 22
These lines assemble the character string for the statement (in demoquery) and prepare it as the demo4id statement identifier. For more information on these steps, see "Assembling and Preparing the SQL Statement".
Line 23
This line declares the demo4cursor cursor for the prepared statement identifier, demo4id. All non-singleton SELECT statements must have a declared cursor.
Line 24
To be able to use a system-descriptor area for the select-list columns, you must first allocate it. This ALLOCATE DESCRIPTOR statement allocates the demo4desc system-descriptor area with four item descriptors.
Line 25
The database server executes the SELECT statement when it opens the demo4cursor cursor. If the WHERE clause of your SELECT statement contains input parameters, you also need to specify the USING SQL DESCRIPTOR clause of the OPEN statement. (See page 16-36.)
26 EXEC SQL describe demo4id using sql descriptor 'demo4desc';
27 EXEC SQL get descriptor 'demo4desc' :desc_count = COUNT;
28 printf("There are %d returned columns:\n", desc_count);
29 /* Print out what DESCRIBE returns */
30 for (i = 1; i <= desc_count; i++)
31 prsysdesc(i);
32 printf("\n\n");
Line 26
The DESCRIBE statement describes the select-list columns for the prepared statement in the demo4id statement identifier. For this reason, the DESCRIBE must follow the PREPARE. This DESCRIBE includes the USING SQL DESCRIPTOR clause to specify the demo4desc system-descriptor area as the location for these column descriptions.
Lines 27 and 28
Line 27 uses the GET DESCRIPTOR statement to obtain the number of select-list columns found by the DESCRIBE. This number is read from the COUNT field of the demo4desc system-descriptor area and saved in the desc_count host variable. Line 28 displays this information to the user.
Lines 29 to 31
This for loop goes through the item descriptors for the columns of the select list. It uses the desc_count host variable to determine the number of item descriptors initialized by the DESCRIBE statement. For each item descriptor, the for loop calls the prsysdesc() function (line 31) to save information such as the data type, length, and name of the column in host variables. See lines 58 to 77 for a description of prsysdesc().
33 for (;;)
34 {
35 EXEC SQL fetch demo4cursor using sql descriptor 'demo4desc';
36 if (strncmp(SQLSTATE, "00", 2) != 0)
37 break;
38 /* Print out the returned values */
39 for (i = 1; i <= desc_count; i++)
40 {
41 EXEC SQL get descriptor 'demo4desc' VALUE :i
42 :colname=NAME, :result = DATA;
43 printf("Column: %s\tValue:%s\n ", colname, result);
44 }
45 printf("\n");
46 }
Lines 33 to 46
This inner for loop executes for each row fetched from the database. The FETCH statement (line 35) includes the USING SQL DESCRIPTOR clause to specify the demo4desc system-descriptor area as the location of the column values. After this FETCH executes, the column values are stored in the specified system-descriptor area.
The if statement (lines 36 and 37) tests the value of the SQLSTATE variable to determine if the FETCH was successful. If SQLSTATE contains a class code other than "00", then the FETCH generates a warning ("01"), the NOT FOUND condition ("02"), or an error (> "02"). In any of these cases, line 37 ends the for loop.
Lines 39 to 45 access the fields of the item descriptor for each column in the select list. After each FETCH statement, the GET DESCRIPTOR statement (lines 41 and 42) loads the contents of the DATA field into a host variable of the appropriate type and length. The second for loop (lines 39 to 44) ensures that GET DESCRIPTOR is called for each column in the select list.
47 if(strncmp(SQLSTATE, "02", 2) != 0)
48 printf("SQLSTATE after fetch is %s\n", SQLSTATE);
49 EXEC SQL close demo4cursor;
50 /* free resources for prepared statement and cursor*/
51 EXEC SQL free demo4id;
52 EXEC SQL free demo4cursor;
53 /* free system-descriptor area */
54 EXEC SQL deallocate descriptor 'demo4desc';
55 EXEC SQL disconnect current;
56 printf("\nDEMO4 Sample Program Over.\n\n");
57 }
Lines 47 and 48
Outside the for loop, the program tests the SQLSTATE variable again so that it can notify the user in the event of successful execution, a runtime error, or a warning (class code not equal to "02").
Line 49
After all the rows are fetched, the CLOSE statement closes the demo4cursor cursor.
Lines 50 to 54
These FREE statements release the resources that are allocated for the prepared statement (line 51) and the database cursor (line 52).
The DEALLOCATE DESCRIPTOR statement (line 54) releases the memory allocated to the demo4desc system-descriptor area. For more information, see "Freeing Memory Allocated to a System-Descriptor Area".
58 prsysdesc(index)
59 EXEC SQL BEGIN DECLARE SECTION;
60 PARAMETER int index;
61 EXEC SQL END DECLARE SECTION;
62 {
63 EXEC SQL BEGIN DECLARE SECTION;
64 int type;
65 int len;
66 int nullable;
67 char name[40];
68 EXEC SQL END DECLARE SECTION;
69
70 EXEC SQL get descriptor 'demo4desc' VALUE :index
71 :type = TYPE,
72 :len = LENGTH,
73 :nullable = NULLABLE,
74 :name = NAME;
75 printf(" Column %d: type = %d, len = %d, nullable=%d, name = %s\n",
76 index, type, len, nullable, name);
77 }
Lines 58 to 77
The prsysdesc() function displays information about a select-list column. It uses the GET DESCRIPTOR statement to access one item descriptor from the demo4desc system-descriptor area.
The GET DESCRIPTOR statement (lines 70 to 74) accesses the TYPE, LENGTH, NULLABLE, and NAME fields from an item descriptor in demo4desc to provide information about a column. It stores this information in host variables of appropriate lengths and data types. The VALUE keyword indicates the number of the item descriptor to access.
Executing a Singleton SELECT
The demo4 program, described in the previous section, assumes that the SELECT statement returns more than one row and therefore the program associates the statement with a cursor. If you know at the time that you write the program that the dynamic SELECT always returns just one row, you can omit the cursor and use the EXECUTE...INTO SQL DESCRIPTOR statement instead of the FETCH...USING SQL DESCRIPTOR. You need to use the DESCRIBE statement to define the select-list columns. For more information, see "Putting Column Values into a System-Descriptor Area".
|
