Next, the program declares three host variables; the first two (username
) 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.
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.
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.
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
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()
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
For numeric values (where results->T[col]
), 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.
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.
statement will populate the following members of the results descriptor:
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.
will contain the value fetched for the given column (unless *results->I[col]
indicates that the column value is NULL).
will contain the length of the value fetched for the given column
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.