Contents Index Cursor sensitivity and isolation levels Controlling transactions in applications

ASA Programming Guide
  Using SQL in Applications

Describing result sets


Some applications build SQL statements which cannot be completely specified in the application. In some cases, for example, statements depend on a response from the user before the application knows exactly what information to retrieve, such as when a reporting application allows a user to select which columns to display.

In such a case, the application needs a method for retrieving information about both the nature of the result set and the contents of the result set. The information about the nature of the result set, called a descriptor, identifies the data structure, including the number and type of columns expected to be returned. Once the application has determined the nature of the result set, retrieving the contents is straightforward.

This result set metadata (information about the nature and content of the data) is manipulated using descriptors. Obtaining and managing the result set metadata is called describing.

Since cursors generally produce result sets, descriptors and cursors are closely linked, although some interfaces hide the use of descriptors from the user. Typically, statements needing descriptors are either SELECT statements or stored procedures that return result sets.

A sequence for using a descriptor with a cursor-based operation is as follows:

  1. Allocate the descriptor. This may be done implicitly, although some interfaces allow explicit allocation as well.

  2. Prepare the statement.

  3. Describe the statement. If the statement is a stored procedure call or batch, and the result set is not defined by a result clause in the procedure definition, then the describe should occur after opening the cursor.

  4. Declare and open a cursor for the statement (embedded SQL) or execute the statement.

  5. Get the descriptor and modify the allocated area if necessary. This is often done implicitly.

  6. Fetch and process the statement results.

  7. Deallocate the descriptor.

  8. Close the cursor.

  9. Drop the statement. Some interfaces do this automatically.

Implementation notes 

Contents Index Cursor sensitivity and isolation levels Controlling transactions in applications