Visual Basic Concepts
Working with RDO Result Sets
Once the rdoResultset is created, you can begin to access the data values it contains. When working with any result set there are a few points to keep in mind:
- If the result set is created as a cursor, you should fully populate it as soon as possible because the rows and pages fetched for your cursor might be locked by the remote database engine. A result set is not fully populated until you have positioned to its last row.
- In many cases a cursorless result set can be more efficient. Do not create rdoResultset objects that enable more features than absolutely necessary. For example, do not request an updatable result set if you do not intend to change data using the cursor. And do not request a fully scrollable cursor if you simply need to populate a list box.
- Consider using cursorless result sets and action queries to perform updates. In many cases these techniques can improve performance. Although somewhat more complex to implement, they can be used when direct access to underlying data tables is not possible.
- Use asynchronous operations and event procedures wherever possible to prevent application lockups, or at least mitigate their impact. It is also possible to enable asynchronous processing of Move methods like MoveLast to prevent lockups during result set population.
- Creating several smaller result sets for complex result set processing is often faster and manages system resources better.
- Generate your result sets from stored procedures whenever possible, because it increases efficiency on the server, on the network, and on the workstation and makes application development simpler.
- Dissociate an existing rdoResultset object created as a static client batch cursor from its connection. You can continue to make changes to the data using the Edit or AddNew methods. When you are ready to post the changes back to the database, you can associate the rdoResultset with an open connection by setting the ActiveConnection object to an open rdoConnection object.
Setting RDO Cursor Limits
You can limit the number of rows returned by a query by creating an rdoQuery and setting the MaxRows property. Once the query processor returns MaxRows rows, it stops working on the query. This is an especially easy way to govern the number of rows returned from ad hoc queries.
Note The MaxRows property also affects data modification queries. For example, if MaxRows is set to 100, and you execute an action query such as an UPDATE statement, only the first 100 qualifying rows are updated.
Determining How Many Rows Are Returned
Generally, you can use the RowCount property to determine the number of rows that qualified to be members of your result set. When you access this property, RDO basically executes a MoveLast method to fully populate the result set before returning to your application. This operation is not performed asynchronously, so your application might block while the query is completed. In some cases, there is no RowCount support from ODBC, so the property returns –1.
Handling RDO QueryTimeout Events
To limit the amount of time the query processor works on a query, set the QueryTimeout property on the rdoConnection or rdoQuery. Once the QueryTimeout period expires, the QueryTimeout event fires. This event is fired each time the QueryTimeout time is reached — whether or not you are using the rdAsyncEnable option.
Your event handler has the choice of continuing or canceling the asynchronous query operation. If you or your user decide to continue working and wait for another QueryTimeout period, pass False to the Cancel parameter. Changes to the QueryTimeout property do not take effect until you start another query.
If you do not code a QueryTimeout event procedure, the query is canceled, the StillExecuting property is set to False, and control returns to the application.