Some applications build SQL statements that cannot be completely specified in the application. Sometimes statements are dependent on a user response 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:
Allocate the descriptor. This may be done implicitly, although some interfaces allow explicit allocation as well.
Prepare the statement.
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.
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. Some interfaces do this automatically.
Discuss this page in DocCommentXchange.
|Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1|