Processing of Multiple Result Sets

Article
10/23/2006

Your application retrieves multiple result sets when you use the SQLEXEC( ) function to issue more than one SQL SELECT statement, or to execute a stored procedure that issues multiple SELECT statements. The results of each SQL SELECT statement are returned in a separate Visual FoxPro cursor.

The default name SQLRESULT is used for the first cursor; subsequent cursors are assigned unique names by indexing the default name. For example, the default names for the cursors returned by a SQLEXEC( ) statement requesting three result sets are Sqlresult, Sqlresult1, and Sqlresult2.

In batch mode, if a function returns multiple result sets, the respective cursor names in Visual FoxPro have unique suffixes and can have up to 255 characters. For example, the following example sets the BatchMode property to batch mode, and then issues a SQLEXEC( ) statement containing four SQL SELECT statements that build four result sets:

? SQLSETPROP(nConnectionHandle,'BatchMode', .T.) 
? SQLEXEC(nConnectionHandle,'select * from authors ; 
                     select * from titles ; 
                     select * from roysched ; 
                     select * from titleauthor','ITEM')

When the function above has completed processing, Visual FoxPro returns the four result sets as the Visual FoxPro cursors Item, Item1, Item2, and Item3.

You can change the default name by using the cCursorname parameter with the SQLEXEC( ) or SQLMORERESULTS( ) functions. If the name you specify for a result set has already been used, the new result set overwrites the existing cursor.

When your application retrieves multiple result sets, you can choose between asynchronous or synchronous processing and batch or non-batch modes.

Using Batch Mode Processing

The BatchMode property, set with SQLSETPROP( ), controls how SQLEXEC( ) returns multiple result sets. The default value is .T., for batch mode. Batch mode processing means that Visual FoxPro doesn't return any results from a still-executing SQLEXEC( ) call until all of the individual result sets have been retrieved.

Using Non-Batch Mode Processing

If you use SQLSETPROP( ) to set the BatchMode property to .F., for non-batch mode, each result set is returned individually. The first result set is returned by the SQLEXEC( ) function call. Your application must then call SQLMORERESULTS( ) repeatedly until a value of 2 is returned, indicating that no more results are available.

In non-batch mode, the cursor name can be changed in each subsequent SQLMORERESULTS( ) call. In the previous example, if the first cursor name in a SQLEXEC( ) sequence is Item, and the second SQLMORERESULTS( ) call changes the cCursorName parameter to Otheritem, the resulting cursors will be named Item, Item1, Otheritem, and Otheritem1.

The next section describes batch mode and non-batch mode processing with synchronous and asynchronous detail added. The following diagram provides a representation of the four possible processing combinations. The numbers 0, 1, and 2 represent the values returned when you call each function.

Visual FoxPro synchronous and asynchronous processing modes

The behavior of each type of processing is explained below: the labels A, B, C, and D reference the preceding diagram. Each explanation assumes the execution of a statement that will return three result sets, represented in the diagram by three horizontal bands.

Using Synchronous Processing

In synchronous mode, control doesn't return to your application until the execution of a function is complete.

A: Synchronous Batch Mode

When you execute a SQL pass-through statement synchronously in batch mode, control isn't returned until all result sets have been retrieved. You specify the name of the first cursor by using the cCursorname parameter in the original function. If the cursor you specify already exists, the result set overwrites the existing cursor. When you request multiple result sets in synchronous batch mode, Visual FoxPro creates the names of additional cursors by uniquely indexing the name of the first cursor.

B: Synchronous Non-Batch Mode

When you execute a SQL pass-through statement synchronously in non-batch mode, the first statement retrieves the first result set and returns a 1. You must then call the SQLMORERESULTS( ) function repeatedly, and optionally specify a new name for the cursor. If you don't specify a new name for the cursor, multiple names for multiple result sets are created by uniquely indexing the base name. When SQLMORERESULTS( ) returns a value of 2, there are no more results available.

Using Asynchronous Processing

In asynchronous mode, your application must continue calling the same SQL pass-through function until it returns a value other than 0 (still executing). The default result set name, Sqlresult, can be explicitly changed with the cCursorname parameter the first time you call the function. If the name you specify for a result set has already been used, the new result set overwrites the information in the existing cursor.

C: Asynchronous Batch Mode

When you execute asynchronously in batch mode, each repeat call of the original function returns a 0 (still executing) until all of the multiple result sets have been returned to the specified cursors. When all results have been retrieved, the return value is either the number of cursors, or a negative number indicating an error.

D: Asynchronous Non-Batch Mode

When processing asynchronously in non-batch mode, SQLEXEC( ) returns a value of 1 when it completes the retrieval of each result set. Your application must then call SQLMORERESULTS( ) repeatedly until a value of 2 is returned, indicating that no more results are available.

Tip Remote result sets are retrieved in two stages: first, the result set is prepared on the server; then the result set is fetched into a local Visual FoxPro cursor. In asynchronous mode, you can call the USED( ) function to see whether Visual FoxPro has started fetching the cursor you requested.

Share via