Processing Multiple RDO Result Sets

Any SQL statement can include multiple SELECT statements or stored procedures that invoke one or more SELECT statements. Each SELECT statement generates a result set that must be processed by your code or discarded before the RDO resources are released and the next result set is made available.

Action queries also generate row-less result sets that must also be processed — this is another type of multiple result set query. In many cases, when you execute a stored procedure, it might return more than one result set. It is often difficult to determine if a stored procedure will return more than one result set because a stored procedure might call another procedure.

For example, if you submit a query that includes four SELECT queries to populate four local ListBox controls and a stored procedure that updates a table, your code must deal with at least five result sets. Because you might not know how many result sets can be generated by a stored procedure, your code must be prepared to process n sets of results.

There are two approaches to executing queries with multiple result sets:

Execute the OpenResultset method directly against a connection.
Use the OpenResultset method against an rdoQuery.

Both are processed in similar ways, but if you use the rdoQuery, you can examine the RowsAffected property to determine the number of rows affected by action queries. While it is possible to execute a multiple result set query using the Execute method, it is not possible to retrieve the rows affected from individual statements, and a trappable error results if any of the queries returns rows.

Using Server-Side Cursor Libraries with Multiple Result Sets

Not all cursor drivers support the ability to process queries that contain more than one set of results — the SQL Server server-side cursor driver is an example. However, if you request a cursorless result set by using the rdOpenForwardOnly, rdConcurReadOnly options and by setting the RowsetSize property to 1, you can execute queries with multiple result sets using server-side cursors. You can also set these options for all result sets by setting the CursorDriver property to rdUseNone.

Multiple Result Sets: An Example

This section takes you through a step-by-step procedure that demonstrates how to execute a query with multiple result sets by using the rdoQuery object.

Tip Whenever you use the concatenation operator "&" to build SQL queries, be sure to include white space (such as spaces or tabs) between the operators on separate lines.

Create your SQL statement and place it in a string variable — for instance, MySQL. For SQL Server, multiple statements must be separated by semi-colons.

Dim MySQL As String
MySQL = "Select Name from Authors Where ID = 5; " _
& " Select City from Publishers; " _
& " Update MyTable " _
& " Set Age = 18 Where Name = 'Fred'"

Next, create a new rdoQuery and set a variable declared as rdoQuery and multiple result sets to this object — in this case, MyQy. The example assumes an rdoConnection object (Cn) already exists. There are a number of other ways to instantiate and initialize rdoQuery objects; this example illustrates only one of these ways.
```
Dim MyQy As rdoQuery
Set MyQy = Cn.CreateQuery("MyQy1", "")
MyQy.SQL = MySQL
```
Execute the query by using the OpenResultset method against the rdoQuery object. If you do not need the extra properties and the ability to pass parameters to the query, you can use the OpenResultset method directly against the rdoConnection object. The arguments you use here affect all result sets fetched from this query. For example, if you need to use a cursor on the second result set, you must specify a cursor type when the first result set is opened.
```
Dim MyRs As rdoResultset
Set MyRs = MyQy.OpenResultset(rdOpenForwardOnly, _ 
rdConcurReadOnly)
```
You are now ready to process the first result set. Note that the rdAsyncEnable options argument was not set. Because of this, control is not returned to the application until the first row of the first result set is ready for processing. If the current rdoResultset contains rows, the RowCount property is set to a value > 0, and the EOF and BOF properties are both False. If no rows were returned, the RowCount property returns either –1 to indicate that the number of rows is not available, or 0, depending on the driver and data source.
The following example fills a ListBox control called NameList1 with the results of the query.
```
While Not MyRs.EOF         ' Loop through all rows.
' Use the first column.
NameList1.AddItem = MyRs(0)    
MyRs.MoveNext            ' Position to the next row 
' in the result set.
Wend
```
The first result set is now at end-of-file (MyRs.EOF = True). Use the MoreResults method to activate the next result set. Once you execute MoreResults, the first set of rows is no longer available — even if you used one of the cursor options to create it.
```
' Activate the next set of results.
If (MyRs.MoreResults) Then ...
```

You are now ready to process the second result set. This example uses only the first few names and discards the remaining rows.

' Loop through some rows.
Do While Not MyRs.EOF and MyRs(0) < "B"
' Use the first column.
NameList1.AddItem = MyRs(0)
MyRs.MoveNext
Loop
' Activate the next set of results 
' and discard remaining rows.
If (MyRs.MoreResults) Then ...

You are now ready to process the last set of results. Because this is an UPDATE statement, there are no rows to be returned, but you can determine the number of rows affected by using the RowsAffected property. The MoreResults method is used for the last time to release all resources connected with this query.
```
If MyQy.RowsAffected = 0 Then
MsgBox "No rows were updated"
End If
' Activate the next set of results.
If (MyRs.MoreResults) Then ...
```

When you use the MoreResults method against the last result set, it should return False and other resources required to process the query are released. At this point the rdoQuery object can be reused. If you use the Close method against an rdoQuery object, it is removed from the rdoQueries collection.