PART 1. Working with Databases
CHAPTER 9. Using SQL in Applications
Some applications build SQL statements, perhaps in response to the user's actions, which cannot be completely specified in the application. For example, a reporting application may allow a user to select which columns they wish to display.
In such a case, the application needs a method for retrieving information about the nature of the result set itself (the number and type of columns), as well as the contents of the result set. This resultset metadata information is manipulated using descriptors. Obtaining and managing the result set metadata is called describing.
As result sets are generally obtained from cursors, descriptors and cursors are closely linked.
In some interfaces, use of descriptors is hidden from the user.
A sequence for using a descriptor with a cursor-based operation is as follows:
Allocate the descriptor. This may be done implicitly, although explicit allocation is allowed in some interfaces.
Prepare the statement.
Declare and open a cursor for the statement (Embedded SQL) or execute the statement.
Get the descriptor and modify the allocated area if necessary. This is often done implicitly.
Fetch and process the statement results.
Deallocate the descriptor.
Close the cursor.
Drop the statement. This is done automatically by some interfaces.
Typically, statements that need descriptors are either SELECT statements or stored procedures that return result sets.
The data structure that holds the information concerning the expected number and type of columns that are being returned is called a descriptor. In different interfaces, the descriptor may be implemented in different ways.
In Embedded SQL, a SQLDA (SQL Descriptor Area) structure holds the descriptor information.
For more information, see The SQL descriptor area (SQLDA).
In ODBC, a descriptor handle allocated using SQLAllocHandle provides access to the fields of a descriptor. You can manipulate these fields using SQLSetStmtAttr, SQLSetDescField, SQLGetStmtAttr, and SQLGetDescField.
Alternatively, you can use SQLDescribeCol and SQLColAttributes to obtain column information.
In Open Client, you can use ct_dynamic to prepare a statement and ct_describe to describe the result set of the statemend. However, you can also use ct_command to send a SQL statement without preparing it first, and use ct_results to handle the returned rows one by one. This is the more common way of operating in Open Client application development.
In JDBC, the java.sql.ResultSetMetaData class provides information about result sets.