Table of Contents Previous Next


5 Building and Executing Dynamic SQL Statements : 5.4 Example - Executing a Query With an Unknown Number of Variables

The next example demonstrates executing a query with an unknown number of input parameters and/or columns in the result set. This type of query may occur when you prompt the user for the text of the query, or when a query is assembled from a form on which the user chooses from a number of conditions (i.e., a filter).
The code sample begins by including the prototypes and type definitions for the C stdio and stdlib libraries. In addition, the program includes the sqlda.h and sqlcpr.h header files. sqlda.h defines the SQLDA structure used throughout this example. sqlcpr.h defines a small set of functions used to interrogate the metadata found in an SQLDA structure.
Next, the program declares pointers to two SQLDA structures. The first SQLDA structure (params) will be used to describe the metadata for any parameter markers found in the dynamic query text. The second SQLDA structure (results) will contain both the metadata and the result set obtained by executing the dynamic query.
Next, the program declares three host variables; the first two (username and password) are used to connect to the database server; the third host variable (stmtTxt) is a NULL-terminated C string containing the text of the query to execute. Notice that the values for these three host variables are derived from the command-line arguments. When the program begins execution, it sets up an error handler and then connects to the database server:
Next, the program calls the sqlald()function to allocate the memory required for each descriptor. Each descriptor contains (among other things):
When you allocate an SQLDA descriptor, you specify the maximum number of columns you expect to find in the result set (for SELECT-list descriptors) or the maximum number of parameters you expect to find the dynamic query text (for bind-variable descriptors) - in this case, we specify that we expect no more than 20 columns and 20 parameters. You must also specify a maximum length for each column (or parameter) name and each indicator variable name - in this case, we expect names to be no more than 64 bytes long.
See Section 7.4 for a complete description of the SQLDA structure.
After allocating the SELECT-list and bind descriptors, the program prepares the dynamic statement and declares a cursor over the result set.
Next, the program calls the bindParams() function. The bindParams() function examines the bind descriptor (params) and prompt the user for a value to substitute in place of each parameter marker found in the dynamic query.
Finally, the program opens the cursor (using the parameter values supplied by the user, if any) and calls the displayResultSet() function to print the result set produced by the query.
The bindParams() function determines whether the dynamic query contains any parameter markers, and, if so, prompts the user for a value for each parameter and then binds that value to the corresponding marker. The DESCRIBE BIND VARIABLE statement populates the params SQLDA structure with information describing each parameter marker.
If the statement contains no parameter markers, params->F will contain 0. If the statement contains more parameters than will fit into the descriptor, params->F will contain a negative number (in this case, the absolute value of params->F indicates the number of parameter markers found in the statement). If params->F contains a positive number, that number indicates how many parameter markers were found in the statement.
After prompting the user for a value for a given parameter, the program binds that value to the parameter by setting params->T[i] to indicate the data type of the value (see Section 7.3 for a list of type codes), params->L[i] to the length of the value (we subtract one to trim off the trailing new-line character added by fgets()), and params->V[i] to point to a copy of the NULL-terminated string provided by the user.
The displayResultSet() function loops through each row in the result set and prints the value found in each column. displayResultSet() starts by executing a DESCRIBE SELECT LIST statement - this statement populates an SQLDA descriptor (results) with a description of each column in the result set.
If the dynamic statement returns no columns (that is, the dynamic statement is not a SELECT statement), results->F will contain 0. If the statement returns more columns than will fit into the descriptor, results->F will contain a negative number (in this case, the absolute value of results->F indicates the number of columns returned by the statement). If results->F contains a positive number, that number indicates how many columns where returned by the query.
To decode the type code found in results->T, the program invokes the sqlnul() function (see the description of the T member of the SQLDA structure in Section 7.4). This call to sqlnul() modifies results->T[col] to contain only the type code (the nullability flag is copied to null_permitted). This step is necessary because the DESCRIBE SELECT LIST statement encodes the type of each column and the nullability of each column into the T array.
For numeric values (where results->T[col] = 2), the program calls the sqlprc() function to extract the precision and scale from the column length. To compute the number of bytes required to hold a numeric value in string form, displayResultSet() starts with the precision (that is, the maximum number of digits) and adds three bytes for a sign character, a decimal point, and a NULL terminator.
For a value of any type other than date or numeric, displayResultSet() starts with the maximum column width reported by DESCRIBE SELECT LIST and adds one extra byte for the NULL terminator. Again, in a real-world application you may want to include more careful calculations for other data types.
After computing the amount of space required to hold a given column, the program allocates enough memory to hold the value, sets results->L[col] to indicate the number of bytes found at results->V[col], and set the type code for the column (results->T[col]) to 1 to instruct the upcoming FETCH statement to return the value in the form of a NULL-terminated string.
The program executes a FETCH statement to fetch the next row in the cursor into the results descriptor. If the FETCH statement fails (because the cursor is exhausted), control transfers to the end of the loop because of the EXEC SQL WHENEVER directive found before the top of the loop.
The FETCH statement will populate the following members of the results descriptor:
*results->I[col] will indicate whether the column contains a NULL value (-1) or a non-NULL value (0). If the value non-NULL but too large to fit into the space provided, the value is truncated and *results->I[col] will contain a positive value.
results->V[col] will contain the value fetched for the given column (unless *results->I[col] indicates that the column value is NULL).
results->L[col] will contain the length of the value fetched for the given column
Finally, displayResultSet() iterates through each column in the result set, examines the corresponding NULL indicator, and prints the value. The result set is not aligned - instead, each value is separated from the previous value by a comma.

5 Building and Executing Dynamic SQL Statements : 5.4 Example - Executing a Query With an Unknown Number of Variables

Table of Contents Previous Next