Visual Basic Concepts

Running RDO Asynchronously

RDO 2.0 supports far more asynchronous operations than supported by RDO 1.0. For example, all of the following operations now permit use of the rdAsyncEnable option:

• Establishing a connection. When using the OpenConnection or EstablishConnection methods, this option prevents blocking while waiting for connections that can take an indeterminate time to establish.
  
  
• Creating result sets. When using the OpenResultset method, this option prevents blocking while queries are being executed.
  
  
• Executing action queries. When using the Execute method, this option prevents blocking while queries are being executed.
  
  
• Populating result sets. When using the MoveLast method, this option prevents blocking while queries are being completed and result sets are fully populated.
  
  
• Refreshing result sets. If you used the rdAsyncEnable option when the result set was created, it is also used when the result set is refreshed using the Requery method.

To better support asynchronous operations, RDO now supports two properties that can be polled to test for asynchronous operation completion:

• The StillExecuting property to test for completion of queries using the OpenResultset, Execute, or Requery methods.
  
  
• The StillConnecting property to test for completion of a connection attempt using the OpenConnection or EstablishConnection methods.

In addition, RDO also exposes a number of events on the rdoConnection object that are fired as the operations are completed:

• The Connect event fires when the connection attempt is complete. The event fires regardless of the success or failure of the operation.
  
  
• The QueryComplete event fires when the query or reposition (MoveLast) operation is complete.

Handling Asynchronous Queries

When your application submits a query to a remote server, you often have no way of determining how much time will pass before the first row is returned. Because of the way SQL Server and some other servers process result sets, your application might not be passed the results until:

• All rows have been processed — as in the case of a sorted result set.
  
  
• The server-side row buffer is filled (approximately 4K).
  
  
• The query completes and the server-side row buffer is flushed.

A typical query can take from milliseconds to hours or even days to complete — or it might never complete if the server or your workstation runs out of resources before the server completes the job. In some cases, the remote server begins to pass rows back fairly quickly, at which time RDO begins to populate your rdoResultset based on the type of cursor you choose. However, the remote server often stops working until your application begins to access those rows by moving forward in the result set. In many cases, rows or entire pages of data are locked on the server until the query is complete, which can only occur when you populate the last row of the rdoResultset object by using one of the Move methods, like MoveLast, or simply referencing the RowCount property.

Preventing Your Application from Blocking

Because of this uncertainty, it makes sense to prevent your application from blocking while the query is taking place and to ensure your application populates the result sets as quickly as possible. Unless you enable asynchronous operation by setting the rdAsyncEnable option with the OpenResultset method, your application will block until the first row of the result set is returned by the ODBC driver.

In addition to enabling the rdAsyncEnable option, you can also code an event handler to trap the QueryComplete event that fires when the query completes.

When you use the OpenResultset method with the rdAsyncEnable option, RDO returns control to your application immediately. At this point, the rdoResultset object is instantiated, but its rowset might not be ready to use — and won't be until the remote server begins to pass rows back. As an indication of this state, the StillExecuting property will return True until the first row is available. Once StillExecuting returns False, the rdoResultset data is available and the QueryComplete event is fired.

What to Do While Waiting for an Asynchronous Operation

When using the asynchronous operation option, your application regains control while the remote server is busy working on your query or simply populating your result set. Since this operation might take some time to complete, you need to decide what your applications should do in the meantime. The following suggestions can help you choose a viable strategy:

• Establish an event handler to trap the Connect event. This event procedure can be used to finalize the connection operation or simply test for a valid connection. For example, you might wish to submit a separate set of logon or initialization procedures unique to your application in this handler.
  
  
• Establish an event handler to trap the QueryComplete event. This event is fired when RDO detects that the first result set is ready for processing, or when the query has failed. This technique is preferable over polling the StillExecuting property.
  
  
• If you cannot create an event handler to notify your application that the asynchronous operation has completed, you can periodically poll the StillExecuting or StillConnecting properties to test for completion of the operation. This loop should include the DoEvents statement to permit other portions of your application to gain access to the processor (unless this is not possible because of other design considerations).
  
  
• Consider leveraging the workstation's processor to run an overlapping procedure that assumes completion of the previous operation. For example, you might do this in a situation where your application processes rows from an external file and passes these rows to a remote server as INSERT statements. During the time that a batch of INSERT statements is being processed by the remote server, your application could prepare the next batch to be sent. Once the first batch completed, the subsequent batch would be ready to process and the cycle could be repeated.

This drawing illustrates how to setup an asynchronous file import routine.

Figure 11.2 Running overlapping procedures while waiting for an asynchronous operation

This technique leverages both the thread used to execute your query on the remote server and the thread available on the workstation.

What You Can't Do While Waiting for an Asynchronous RDO Operation

As long as the asynchronous operation is still executing, you cannot reference the rdoResultset, rdoQuery, or rdoConnection object being processed — except for the properties and methods listed in the following table.

Valid Properties and Methods During Asynchronous Operations

If you decide that you have waited long enough for an asynchronous operation to complete, you can choose to tell the remote processor to abandon the operation by using either the Cancel or Close methods.

• The Cancel method abandons the query operation.
  
  
• The Close method abandons the operation and discards the RDO object it is applied to.

Using one of these methods is not always prudent in cases where you have submitted complex multistatement or broad-scope update operations because database integrity could be compromised.