| INFORMIX-ESQL/C Programmer's Manual Using an sqlda Structure |
|
|
For an introduction on how to handle unknown columns in an unknown select list, see page 15-26. This section describes how to use an sqlda structure to handle a SELECT statement.
Important: If the SELECT statement has input parameters of an unknown number and type in the WHERE clause, your program must also handle these input parameters with an sqlda structure.
For more information, see Handling a Parameterized SELECT Statement.
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.
The demo3.ec sample program, in this section, executes a dynamic SELECT statement with the following conditions:
The demo4 sample program (Executing a SELECT That Returns Multiple Rows) 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.
The program must include the ESQL/C sqlda.h header file to provide a definition for the sqlda structure.
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.
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.
This line declares the demo3cursor for the prepared statement identifier, demo3id.
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:
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.
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.
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.
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 Handling an Unknown Column List).
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.
Important: The demo3 program assumes that the returned columns are of character data type. If the program did not make this assumption, it would need to check the sqltype and sqllen fields to determine the appropriate data type for the host variable that holds the sqldata value.
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").
After all the rows are fetched, the CLOSE statement closes the demo3cursor cursor.
These FREE statements release the resources that are allocated for the demo3id prepared statement and the demo3cursor database cursor.
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.
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).
Tip: The ESQL/C demonstration programs unload.ec and dyn_sql.ec (described in The dyn_sql Program) also use sqlda to describe columns of a select list. Also refer to the PREPARE statement in the "Informix Guide to SQL: Syntax."
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.