4.1 Example - Using a Descriptor to Return Data

Table of Contents Previous Next


4 Using Descriptors : 4.1 Example - Using a Descriptor to Return Data

The code sample begins by including the prototypes and type definitions for the C stdio and stdlib libraries, SQL data type symbols, and the SQLCA (SQL communications area) structure:
The application includes a forward-declaration for a function named print_meta_data() that will print the metadata found in a descriptor:
The application uses a PREPARE statement to syntax check the string provided by the user:
and a DESCRIBE statement to move the metadata for the query into the SQL descriptor.
If the column count is zero, the end user did not enter a SELECT statement; the application uses an EXECUTE IMMEDIATE statement to process the contents of the statement:
If the statement entered by the user is a SELECT statement (which we know because the column count is non-zero), the application declares a variable named row.
Then, uses a FETCH to retrieve the next row from the cursor into the descriptor:
The application confirms that the FETCH did not fail; if the FETCH fails, the application has reached the end of the result set, and breaks the loop:
The application interrogates the row descriptor (row_desc) to copy the column value (:val), null indicator (:ind) and column name (:name) into the host variables declared above. Notice that you can retrieve multiple items from a descriptor using a comma-separated list.
If the null indicator (ind) is negative, the column value is NULL; if the null indicator is greater than 0, the column value is too long to fit into the val host variable (so we print <truncated>); otherwise, the null indicator is 0 (meaning NOT NULL) so we print the value. In each case, we prefix the value (or <null> or <truncated>) with the name of the column.
The print_meta_data() function extracts the metadata from a descriptor and prints the name, data type, and length of each column:
The application then defines an array of character strings that map data type values (numeric) into data type names. We use the numeric value found in the descriptor to index into this array. For example, if we find that a given column is of type 2, we can find the name of that type (NUMERIC) by writing types[2].
The application retrieves the column count from the descriptor. Notice that the program refers to the descriptor using a host variable (desc) that contains the name of the descriptor. In most scenarios, you would use an identifier to refer to a descriptor, but in this case, the caller provided the descriptor name, so we can use a host variable to refer to the descriptor.
If the numeric type code matches a 'known' type code (that is, a type code found in the types[] array), it sets type_name to the name of the corresponding type; otherwise, it sets type_name to "unknown".

4 Using Descriptors : 4.1 Example - Using a Descriptor to Return Data

Table of Contents Previous Next