DB2 CLI stores information (data types, size, pointers, and so on) about columns in a result set, and parameters in an SQL statement. The bindings of application buffers to columns and parameters must also be stored. Descriptors are a logical view of this information, and provide a way for applications to query and update this information.
Many CLI functions make use of descriptors, but the application itself does not need to manipulate them directly.
For instance:
Although no database operations require direct access to descriptors, there are situations where working directly with the descriptors will be more efficient or result in simpler code. For instance, a descriptor that describes a row fetched from a table can then be used to describe a row inserted back into the table.
There are four types of descriptors, as follows:
The only difference between the four types of descriptors described above is how they are used. One of the benefits of descriptors is that a single descriptor can be used to serve multiple purposes. For instance, a row descriptor in one statement can be used as a parameter descriptor in another statement.
As soon as a descriptor exists it is either an application descriptor or an implementation descriptor. This is the case even if the descriptor has not yet been used in a database operation. If the descriptor is allocated by the application using SQLAllocHandle() then it is an application descriptor.
Each descriptor contains both header fields and record fields. These fields together completely describe the column or parameter.
Each header field occurs once in each descriptor. Changing one of these fields affects all columns or parameters.
Many of the following header fields correspond to a statement
attribute. Setting the header field of the descriptor using
SQLSetDescField() is the same as setting the corresponding
statement attribute using SQLSetStmtAttr(). The same holds
true for retrieving the information using SQLGetDescField() or
SQLGetStmtAttr(). If your application does not already have
a descriptor handle allocated then it is more efficient to use the statement
attribute calls instead of allocating the descriptor handle then using the
descriptor calls.
| SQL_DESC_ALLOC_TYPE | SQL_DESC_BIND_TYPEa | ||
| SQL_DESC_ARRAY_SIZEa | SQL_DESC_COUNT | ||
| SQL_DESC_ARRAY_STATUS_PTRa | SQL_DESC_ROWS_PROCESSED_PTRa | ||
| SQL_DESC_BIND_OFFSET_PTRa |
| ||
| |||
For more information about each of these fields see Header Fields in SQLSetDescField().
The descriptor header field SQL_DESC_COUNT is the one-based index of the highest-numbered descriptor record that contains information. DB2 CLI automatically updates this field (and the physical size of the desciptor) as columns or parameters are bound and unbound. The initial value of SQL_DESC_COUNT is 0 when a descriptor is first allocated.
Zero or more descriptor records are contained in a single descriptor. As new columns or parameters are bound, new desciptor records are added to the descriptor. When a column or parameter is unbound, the descriptor record is removed.
Table 8 lists the fields in a descriptor record. They
describe a column or parameter, and occur once in each descriptor
record.
| SQL_DESC_AUTO_UNIQUE_VALUE | SQL_DESC_LOCAL_TYPE_NAME |
| SQL_DESC_BASE_COLUMN_NAME | SQL_DESC_NAME |
| SQL_DESC_BASE_TABLE_NAME | SQL_DESC_NULLABLE |
| SQL_DESC_CASE_SENSITIVE | SQL_DESC_OCTET_LENGTH |
| SQL_DESC_CATALOG_NAME | SQL_DESC_OCTET_LENGTH_PTR |
| SQL_DESC_CONCISE_TYPE | SQL_DESC_PARAMETER_TYPE |
| SQL_DESC_DATA_PTR | SQL_DESC_PRECISION |
| SQL_DESC_DATETIME_INTERVAL_CODE | SQL_DESC_SCALE |
| SQL_DESC_DATETIME_INTERVAL_PRECISION | SQL_DESC_SCHEMA_NAME |
| SQL_DESC_DISPLAY_SIZE | SQL_DESC_SEARCHABLE |
| SQL_DESC_FIXED_PREC_SCALE | SQL_DESC_TABLE_NAME |
| SQL_DESC_INDICATOR_PTR | SQL_DESC_TYPE |
| SQL_DESC_LABEL | SQL_DESC_TYPE_NAME |
| SQL_DESC_LENGTH | SQL_DESC_UNNAMED |
| SQL_DESC_LITERAL_PREFIX | SQL_DESC_UNSIGNED |
| SQL_DESC_LITERAL_SUFFIX | SQL_DESC_UPDATABLE |
For more information about each of these fields see Record Fields in SQLSetDescField().
Deferred fields are created when the descriptor header or a descriptor record is created. The addresses of the defined variables are stored but not used until a later point in the application. The application must not deallocate or discard these variables between the time it associates them with the fields and the time CLI reads or writes them.
The following table lists the deferred fields and the meaning or a null
pointer where applicable:
| Field | Meaning of Null value |
|---|---|
| SQL_DESC_DATA_PTR | The record is unbound. |
| SQL_DESC_INDICATOR_PTR | (none) |
| SQL_DESC_OCTET_LENGTH_PTR (ARD and APD only) |
|
| SQL_DESC_ARRAY_STATUS_PTR (multirow fetch only) | A multirow fetch failed to return this component of the per-row diagnostic information. |
| SQL_DESC_ROWS_PROCESSED_PTR (multirow fetch only) | (none) |
The SQL_DESC_DATA_PTR field in each descriptor record points to a variable that contains the parameter value (for APDs) or the column value (for ARDs). This is a deferred field that defaults to null. Once the column or parameter is bound it points to the parameter or column value. At this point the descriptor record is said to be bound.
A consistency check is performed automatically whenever an application sets the SQL_DESC_DATA_PTR field of the APD or ARD. The check ensures that various fields are consistent with each other, and that appropriate data types have been specified.
To force a consistency check of IPD fields, the application can set the SQL_DESC_DATA_PTR field of the IPD. This setting is only used to force the consistency check. The value is not stored and cannot be retrieved by a call to SQLGetDescField() or SQLGetDescRec().
A consistency check cannot be performed on an IRD.
See Consistency Checks in SQLSetDescRec() for more information on the consistency check.
Descriptors are allocated in one of two ways:
To obtain handles to these implicitly allocated descriptors an application can call SQLGetStmtAttr(), passing the statement handle and an Attribute value of:
An application descriptor on a connection can be explicitly allocated at any time it is connected to the database. This is done by calling SQLSetStmtAttr(), passing the statement handle and an Attribute value of:
In this case the explicitly specified allocated descriptor will be used rather than the implicitly allocated descriptor.
An explicitly allocated descriptor can be associated with more than one statement.
When an application row descriptor is allocated, its fields receive the initial values indicated in the Initialization of Descriptor Fields section of SQLSetDescField(). The SQL_DESC_TYPE field is set to SQL_DEFAULT which provides for a standard treatment of database data for presentation to the application. The application may specify different treatment of the data by setting fields of the descriptor record.
The initial value of the SQL_DESC_ARRAY_SIZE header field is 1. To enable multirow fetch, the application can set this value in an ARD to specify the number of rows in a rowset. See Scrollable Cursors for information about rowsets in a scrollable cursor.
There are no default values for the fields of an IRD. The fields are set when there is a prepared or executed statement.
The following fields in an IPD are undefined until they have been automatically populated by a call to SQLPrepare():
There are times when the application will need to discover information about the parameters of a prepared SQL statement. A good example is when an ad-hoc query is prepared; the application will not know anything about the parameters in advance. If the application enables automatic population of the IPD, by setting the SQL_ATTR_ENABLE_AUTO_IPD statement attribute to SQL_TRUE (using SQLSetStmtAttr()), then the fields of the IPD are automatically populated to describe the parameter. This includes the data type, precision, scale, and so on (the same information that SQLDescribeParam() returns). The application can use this information to determine if data conversion is required, and which application buffer is the most appropriate to bind the parameter to.
Automatic population of the IPD involves some overhead. If it is not necessary for this information to be automatically gathered by the CLI driver then the SQL_ATTR_ENABLE_AUTO_IPD statement attribute should be set to SQL_FALSE. This is the default setting, and the application should return it to this value when it is no longer needed.
When automatic population of the IPD is active, each call to SQLPrepare() causes the fields of the IPD to be updated. The resulting descriptor information can be retrieved by calling the following functions:
Explicitly allocated descriptors can be freed in one of two ways:
An implicitly allocated descriptor cannot be freed by calling SQLFreeHandle() with a HandleType of SQL_HANDLE_DESC.
The following sections describe manipulating descriptors using descriptor handles. The final section, Accessing Descriptors without using a Handle describes how to manipulate descriptor values by calling CLI functions that do not use descriptor handles.
The handle of an explicitly allocated descriptor is returned in the OutputHandlePtr argument when the application calls SQLAllocHandle() to allocate the descriptor.
The handle of an implicitly allocated descriptor is obtained by calling SQLGetStmtAttr() with either SQL_ATTR_IMP_PARAM_DESC or SQL_ATTR_IMP_ROW_DESC.
See SQLGetDescField - Get Single Field Settings of Descriptor Record for information on how to obtain a single field of a descriptor record.
See SQLGetDescRec - Get Multiple Field Settings of Descriptor Record for information on how to obtain the settings of multiple descriptor fields that affect the data type and storage of column or parameter data.
This section deals with how to set the values of descriptor fields using descriptor handles. You can also set many of these fields without using descriptor handles; see Accessing Descriptors without using a Handle for more information.
Two methods can be used to set descriptor fields, one field at a time or multiple fields at a time:
Some fields of a descriptor are read-only, but the others can be set using the function SQLSetDescField(). See the following sections for specific details on each field that can be set:
Record and header fields are set differently using SQLSetDescField():
The application must follow the steps defined in Sequence of Setting Descriptor Fields when setting individual fields of a descriptor. Setting some fields will cause DB2 CLI to automatically set other fields. A consistency check will take place after the application follows the defined steps. This will ensure that the values in the descriptor fields are consistent. See Consistency Check for more information.
If a function call that would set a descriptor fails, the contents of the descriptor fields are undefined after the failed function call.
A predefined set of descriptor fields can be set with one call rather than setting individual fields one at a time. SQLSetDescRec() sets the following fields for a single column or parameter:
(SQL_DESC_DATETIME_INTERVAL_CODE is also defined by ODBC but is not supported by DB2 CLI.)
See SQLSetDescRec - Set Multiple Descriptor Fields for a Column or Parameter Data for more information.
One benefit of descriptors is the fact that a single descriptor can be used for multiple purposes. For instance, an ARD on one statement handle can be used as an APD on another statement handle.
There will be other instances, however, where the application will want to make a copy of the original descriptor, then modify certain fields. In this case SQLCopyDesc() is used to overwrite the fields of an existing descriptor with the values from another descriptor. Only fields that are defined for both the source and target descriptors are copied (with the exception of the SQL_DESC_ALLOC_TYPE field which cannot be changed).
Fields can be copied from any type of descriptor, but can only be copied to an application descriptor (APD or ARD) or an IPD. Fields cannot be copied to an IRD. The descriptor's allocation type will not be changed by the copy procedure (again, the SQL_DESC_ALLOC_TYPE field cannot be changed).
See SQLCopyDesc - Copy Descriptor Information Between Handles for complete details on copying descriptors.
As was mentioned at the beginning of this section on descriptors, many CLI functions make use of descriptors, but the application itself does not need to manipulate them directly. Instead, the application can use a different function which will set or retrieve one or more fields of a descriptor as well as perform other functions. This category of CLI functions are called concise functions. SQLBindCol() is an example of a concise function that manipulates descriptor fields.
In addition to manipulating multiple fields, concise functions are called without explicitly specifying the descriptor handle. The application does not even need to retrieve the descriptor handle to use a concise function.
The following types of concise functions exist::
If required, an application can also use the descriptor calls directly to modify individual details of a binding. In this case the descriptor handle must be retrieved, and the functions SQLSetDescField() or SQLSetDescRec() called to modify the binding.
/* ... */
SQLCHAR * sqlstmt =
"SELECT deptname, location from org where division = ? " ;
/* ... */
/* macro to initalize server, uid and pwd */
INIT_UID_PWD ;
/* allocate an environment handle */
rc = SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv ) ;
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
/* allocate a connect handle, and connect */
rc = DBconnect( henv, &hdbc ) ;
if ( rc != SQL_SUCCESS ) return( terminate( henv, rc ) ) ;
rc = SQLAllocHandle( SQL_HANDLE_STMT, hdbc, &hstmt ) ;
CHECK_HANDLE( SQL_HANDLE_DBC, hdbc, rc ) ;
/* Use SQLGetStmtAttr() to get implicit parameter descriptor handle */
rc = SQLGetStmtAttr ( hstmt,
SQL_ATTR_IMP_PARAM_DESC,
&hIPDdesc,
SQL_IS_POINTER,
NULL);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Use SQLGetStmtAttr() to get implicit row descriptor handle */
rc = SQLGetStmtAttr ( hstmt,
SQL_ATTR_IMP_ROW_DESC,
&hIRDdesc,
SQL_IS_POINTER,
NULL);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Call SQLGetDescField() to see how the header field */
/* SQL_DESC_ALLOC_TYPE is set. */
rc = SQLGetDescField( hIPDdesc,
0, /* ignored for header fields */
SQL_DESC_ALLOC_TYPE,
&desc_smallint, /* The result */
SQL_IS_SMALLINT,
NULL ); /* ignored */
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Print the descriptor information */
printf("The IPD header descriptor field SQL_DESC_ALLOC_TYPE is %s\n",
ALLOCTYPES[desc_smallint]);
/* prepare statement for multiple use */
rc = SQLPrepare(hstmt, sqlstmt, SQL_NTS);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* bind division to parameter marker in sqlstmt */
rc = SQLBindParameter( hstmt,
1,
SQL_PARAM_INPUT,
SQL_C_CHAR,
SQL_CHAR,
10,
0,
division.s,
11,
NULL
) ;
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* bind deptname to first column in the result set */
rc = SQLBindCol(hstmt, 1, SQL_C_CHAR, (SQLPOINTER) deptname.s, 15,
&deptname.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, (SQLPOINTER) location.s, 14,
&location.ind);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Call SQLGetDescField() to see how the descriptor record */
/* field SQL_DESC_PARAMETER_TYPE is set */
rc = SQLGetDescField( hIPDdesc,
1, /* Look at the parameter */
SQL_DESC_PARAMETER_TYPE,
&desc_smallint, /* The result */
SQL_IS_SMALLINT,
NULL ); /* ignored */
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf("The IPD record descriptor field SQL_DESC_PARAMETER_TYPE is %s\n",
PARAMTYPE[desc_smallint]);
strcpy( division.s, "Eastern");
rc = SQLExecute(hstmt);
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf("\nDepartments in %s Division:\n", division.s);
printf("Department Location\n");
printf("-------------- -------------\n");
while ( ( rc = SQLFetch( hstmt ) ) == SQL_SUCCESS )
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf( "%-14.14s %-13.13s \n", deptname.s, location.s ) ;
if ( rc != SQL_NO_DATA_FOUND )
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
/* Print out some implementation row descriptor fields */
/* from the last SQLFetch() above */
for (colCount = 1; colCount <=2; colCount++) {
printf("\nInformation for column %i\n",colCount);
/* Call SQLGetDescField() to see how the descriptor record */
/* field SQL_DESC_TYPE_NAME is set */
rc = SQLGetDescField( hIRDdesc,
colCount,
SQL_DESC_TYPE_NAME, /* record field */
desc_char, /* The result */
25,
NULL ); /* ignored */
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf(" - IRD record descriptor field SQL_DESC_TYPE_NAME is %s\n",
desc_char);
/* Call SQLGetDescField() to see how the descriptor record */
/* field SQL_DESC_LABEL is set */
rc = SQLGetDescField( hIRDdesc,
colCount,
SQL_DESC_LABEL, /* record field */
desc_char, /* The result */
25,
NULL ); /* ignored */
CHECK_HANDLE( SQL_HANDLE_STMT, hstmt, rc ) ;
printf(" - IRD record descriptor field SQL_DESC_LABEL is %s\n",
desc_char);
} /* End of the for statement */