Handling an Unknown Select List
For an introduction on how to handle unknown columns in an unknown select list, see page 15-24. This section describes how to use an sqlda structure to handle a SELECT statement.
To use an sqlda structure to handle unknown select-list columns
1. Declare a variable to hold the address of an sqlda structure. For more information, see "Defining an sqlda Structure".
2. 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".
3. Use the DESCRIBE...INTO statement to perform two tasks:
a. Allocate an sqlda structure. The address of the allocated structure is stored in the sqlda pointer that you declare. For more information, see "Allocating Memory for the sqlda Structure".
b. Determine the number and data types of select-list columns. The DESCRIBE statement fills an sqlvar_struct structure for each column of the select list. For more information, see "Initializing the sqlda Structure".
4. Examine the sqltype and sqllen fields of sqlda for each select-list column to determine the amount of memory that you need to allocate for the data. For more information, see "Allocating Memory for Column Data".
5. Save the number of select-list columns stored in the sqld field in a host variable.
6. Declare and open a cursor and then use the FETCH...USING DESCRIPTOR statement to fetch column values, one row at a time, into an allocated sqlda structure. See "Putting Column Values into an sqlda Structure".
7. Retrieve the row data from the sqlda structure into host variables with C-language statements that access the sqldata field for each select-list column. For more information, see "Assigning and Obtaining Values from an sqlda Structure".
8. Release memory allocated to the sqldata fields and the sqlda structure. For more information, see "Freeing Memory Allocated to an sqlda Structure".
The following sections demonstrate how to use an sqlda structure to handle a SELECT statement that returns many rows and one that returns only one row.
Executing a SELECT That Returns Multiple Rows
The demo3.ec sample program, in this section, executes 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 DESCRIPTOR statement.
The OPEN statement does not need to include the USING clause.
The FETCH statement includes the USING DESCRIPTOR clause to store the return values in an sqlda structure.
The demo4 sample program (page 16-15) assumes these same conditions. While demo4 uses a system-descriptor area to define the select-list columns, demo3 uses an sqlda structure. The demo3 program does not perform exception handling.
1 #include <stdio.h>
2 EXEC SQL include sqlda;
3 EXEC SQL include sqltypes;
4 main()
5 {
6 struct sqlda *demo3_ptr;
7 struct sqlvar_struct *col_ptr;
8 static char data_buff[1024];
9 int pos, cnt, size;
10 EXEC SQL BEGIN DECLARE SECTION;
11 short int i, desc_count;
12 char demoquery[80];
13 EXEC SQL END DECLARE SECTION;
14 printf("DEMO3 Sample ESQL program running.\n\n");
15 EXEC SQL connect to 'stores7';
Line 2
The program must include the ESQL/C sqlda.h header file to provide a definition for the sqlda structure.
Lines 6 to 13
Lines 6 and 7 declare sqlda variables that are needed by the program. The demo3_ptr variable points to the sqlda structure that will hold the data that is fetched from the database. The col_ptr variable points to an sqlvar_struct structure so that the code can step through each of the sqlvar_struct structures in the variable-length portion of sqlda. Neither of these variables is declared as an ESQL/C host variable. Lines 10 to 13 declare host variables to hold the data that is obtained from the user and the data that is retrieved from the sqlda structure.
16 /* These next four lines have hard-wired both the query and
17 * the value for the parameter. This information could have
18 * been entered from the terminal and placed into the strings
19 * demoquery and a query value string (queryvalue), respectively.
20 */
21 sprintf(demoquery, "%s %s",
22 "select fname, lname from customer",
23 "where lname < 'C' ");
24 EXEC SQL prepare demo3id from :demoquery;
25 EXEC SQL declare demo3cursor cursor for demo3id;
26 EXEC SQL describe demo3id into demo3_ptr;
Lines 16 to 24
These lines assemble the character string for the SELECT statement (in demoquery) and prepare it as the demo3id statement identifier. For more information on these steps, see "Assembling and Preparing the SQL Statement".
Line 25
This line declares the demo3cursor for the prepared statement identifier, demo3id.
Line 26
The DESCRIBE statement describes the select-list columns for the prepared statement that is in the demo3id statement identifier. For this reason, you must prepare the statement before you use DESCRIBE. This DESCRIBE includes the INTO clause to specify the sqlda structure to which demo3_ptr points as the location for these column descriptions. The DESCRIBE...INTO statement also allocates memory for an sqlda structure and stores the address of this structure in the demo3_ptr variable.
The demo3 program assumes that the following SELECT statement is assembled at runtime and stored in the demoquery string:
After the DESCRIBE statement in line 26, the components of the sqlda structure contain the following:
- The sqlda component, demo3_ptr->sqld, has the value
2, since two columns were selected from the customer table.
- The component demo3_ptr->sqlvar[0], an sqlvar_struct structure, contains information on the fname column of the customer table. The demo3_ptr->sqlvar[0].sqlname component, for example, gives the name of the first column (fname).
- The component demo3_ptr->sqlvar[1], an sqlvar_struct structure, contains information on the lname column of the customer table.
27 desc_count = demo3_ptr->sqld;
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 prsqlda(i, demo3_ptr->sqlvar[i-1]);
32 printf("\n\n");
Lines 27 and 28
Line 27 assigns the number of select-list columns that are found by the DESCRIBE statement to the desc_count host variable. Line 28 displays this information to the user.
Lines 29 to 32
This for loop goes through the sqlvar_struct structures for the columns of the select list. It uses the desc_count host variable to determine the number of these structures that are initialized by the DESCRIBE statement. For each sqlvar_struct structure, the prsqlda() function (line 31) displays information such as the data type, length, and name. For a description of prsqlda(), see the description of lines 75 to 81.
33 for(col_ptr=demo3_ptr->sqlvar, cnt=pos=0; cnt < desc_count;
34 cnt++, col_ptr++)
35 {
36 /* Allow for the trailing null character in C
37 character arrays */
38 if(col_ptr->sqltype==SQLCHAR)
39 col_ptr->sqllen += 1;
40 /* Get next word boundary for column data and
41 assign buffer position to sqldata */
42 pos = rtypalign(pos, col_ptr->sqltype);
43 col_ptr->sqldata = &data_buff[pos];
44 /* Determine size used by column data and increment
45 buffer position */
46 size = rtypmsize(col_ptr->sqltype, col_ptr->sqllen);
47 pos += size;
48 }
49 EXEC SQL open demo3cursor;
Lines 33 to 48
This second for loop allocates memory for the sqldata fields and sets the sqldata fields to point to this memory.
Lines 40 to 47 examine the sqltype and sqllen fields of sqlda for each select-list column to determine the amount of memory you need to allocate for the data. The program does not use malloc() to allocate space dynamically. Instead, it uses a static data buffer (the data_buff variable defined on line 8) to hold the column data. The ESQL/C rtypalign() function (line 42) returns the position of the next word boundary for the column data type (in
col_ptr->sqltype). Line 43 then assigns the address of this position within the data_buff data buffer to the sqldata field (for columns that receive values returned by the query).
The ESQL/C rtypmsize() function (line 46) returns the number of bytes required for the SQL data type that is specified by the sqltype and sqllen fields. Line 47 then increments the data buffer pointer (pos) by the size required for the data. For more information, see "Allocating Memory for Column Data".
Line 49
The database server executes the SELECT statement when it opens the demo3cursor cursor. If the WHERE clause of your SELECT statement contains input parameters, you also need to specify the USING DESCRIPTOR clause of OPEN (see page 17-30).
50 for (;;)
51 {
52 EXEC SQL fetch demo3cursor using descriptor demo3_ptr;
53 if (strncmp(SQLSTATE, "00", 2) != 0)
54 break;
55 /* Print out the returned values */
56 for (i=0; i<desc_count; i++)
57 printf("Column: %s\tValue:%s\n", demo3_ptr->sqlvar[i].sqlname,
58 demo3_ptr->sqlvar[i].sqldata);
59 printf("\n");
60 }
61 if (strncmp(SQLSTATE, "02", 2) != 0)
62 printf("SQLSTATE after fetch is %s\n", SQLSTATE);
63 EXEC SQL close demo3cursor;
Lines 50 to 60
This inner for loop executes for each row that is fetched from the database. The FETCH statement (line 52) includes the USING DESCRIPTOR clause to specify the sqlda structure to which demo3_ptr points as the location of the column values. After this FETCH, the column values are stored in the specified sqlda structure.
The if statement (lines 53 and 54) tests the value of the SQLSTATE variable to determine the success of the FETCH. If SQLSTATE indicates any status other than success, line 54 executes and ends the for loop. Lines 56 to 60 display the contents of the sqlname and sqldata fields for each column of the select list.
Lines 61 and 62
Outside the for loop, the program tests the SQLSTATE variable again so that it can notify the user in the event of a successful execution, a runtime error, or a warning (class code not equal to "02").
Line 63
After all the rows are fetched, the CLOSE statement closes the demo3cursor cursor.
64 EXEC SQL free demo3id;
65 EXEC SQL free demo3cursor;
66 /* No need to explicitly free data buffer in this case because
67 * it wasn't allocated with malloc(). Instead, it is a static char
68 * buffer
69 */
70 /* Free memory assigned to sqlda pointer. */
71 free(demo3_ptr);
72 EXEC SQL disconnect current;
73 printf("\nDEMO3 Sample Program Over.\n\n");
74 }
75 prsqlda(index, sp)
76 short int index;
77 register struct sqlvar_struct *sp;
78 {
79 printf(" Column %d: type = %d, len = %d, data = %s\n",
80 index, sp->sqltype, sp->sqllen, sp->sqldata, sp->sqlname);
81 }
Lines 64 and 65
These FREE statements release the resources that are allocated for the demo3id prepared statement and the demo3cursor database cursor.
Lines 66 to 71
At the end of the program, free the memory allocated to the sqlda structure. Because this program does not use malloc() to allocate the data buffer, it does not use the free() system call to free the sqldata pointers. Although the allocation of memory from a static buffer is straightforward, it has the disadvantage that this buffer remains allocated until the program ends. For more information, see "Freeing Memory Allocated to an sqlda Structure".
The free() system call (line 71) frees the sqlda structure to which demo3_ptr points.
Lines 75 to 81
The prsqlda() function displays information about a select-list column. It reads this information from the sqlvar_struct structure whose address is passed into the function (sp).
Executing a Singleton SELECT
The demo3 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 DESCRIPTOR statement instead of the FETCH...USING DESCRIPTOR. You will still need to use the DESCRIBE statement to define the select-list columns. For more information, see "Putting Column Values into an sqlda Structure".
|
