Definitions of Cursor Types

[Previous] [Next]

Many different types of cursors exist, and each has its own unique set of characteristics. Some cursors allow updating, and some allow you to view changes made by other users. Let's take a look at the features of each type of cursor in some depth.

Forward-Only Cursors

The forward-only cursor is the simplest type of cursor. As its name suggests, and as shown in Figure 7-1, you can only move forward through the records in this cursor. After you move beyond a record, it is no longer available in the cursor. Although this level of functionality might seem limiting, it is extremely fast.

Figure 7-1 The forward-only cursor.

Most client/server developers prefer the forward-only cursor because of its speed and the minimal stress it places on the database system. This cursor allows developers to retrieve the data from the cursor (in the case of ADO, the Recordset) and manage the results of the query on their own—in a Variant array or a collection, for example.

The adOpenForwardOnly constant, a CursorTypeEnum type, corresponds to this type of cursor. It is the default value for a Recordset object's CursorType property when you use the default value (adUseServer) for the Recordset object's CursorLocation property. ADO retrieves from the cursor up to the number of records specified by the CacheSize property, and then when you navigate beyond the data in the cache, ADO retrieves the next set of records.

You can use the Recordset's MoveNext method to navigate forward through the Recordset, but using the MovePrevious, MoveFirst, or MoveLast methods will generate an error stating that the cursor does not support fetching backward. You can use the Move method with a negative number to move backward as long as you do not navigate outside the records currently held in the cache.

Some database systems support updatable forward-only cursors, some do not.

Firehose Cursors

There is a special type of cursor often called a firehose cursor. It's a forward-only cursor that is read-only and returns data one record at a time. The data comes out extremely fast—like water coming out of a fire hose—and it's up to you to figure out what to do with it. Some databases and OLE DB providers support only this type of cursor because of its performance and simplicity. Most database systems use this scenario (forward-only, read-only, and one record at a time) as the default way to return the results of a query, and those systems use no cursor-like structure to store these results. Therefore, a firehose cursor is sometimes not considered a cursor.

In order to use a firehose cursor in Remote Data Objects (RDO), you have to use an rdoQuery object and set the RowsetSize property (similar to the CacheSize property on the ADO Recordset object) to 1 or set the CursorDriver property to rdUseNone. Using firehose cursors with ADO is simpler because this is the default type of cursor used by ADO. If you want to use a non-firehose forward-only cursor, you'll need to set the Recordset's CacheSize property to an integer larger than the default of 1.

Microsoft SQL Server is optimized for this type of query. If you decide to use a firehose cursor with SQL Server, you'll see excellent performance, but with one important caveat: SQL Server can support only one active query on a connection. If you open a firehose cursor and do not fetch all of the data, and then close that cursor, you've tied up that connection. Programmers familiar with RDO might remember the error message "Connection is busy with results from another hstmt." You received this error if you tried to use a connection that was busy, such as in the case just described.

For better or for worse, OLE DB—the technology on which ADO is based—simplifies things for the programmer. Rather than generate an error message, the OLE DB provider will simply request another connection. Thus, if you use code like the following, you'll be using the default firehose cursor type and will open up three separate connections to your SQL Server database:

 Set cnNorthwind = New ADODB.Connection cnNorthwind.CursorLocation = adUseServer cnNorthwind.Open strConn Set rsCustomers = cnNorthwind.Execute("SELECT * FROM Customers") Set rsOrders = cnNorthwind.Execute("SELECT * FROM Orders") Set rsProducts = cnNorthwind.Execute("SELECT * FROM Products") 

If you use any other type of cursor, the connection is available as soon as SQL Server returns data from your query. The following code specifies that we want to retrieve the results of our queries into static cursors.

 Set cnNorthwind = New ADODB.Connection cnNorthwind.CursorLocation = adUseServer cnNorthwind.Open strConn Set rsCustomers = New ADODB.Recordset rsCustomers.Open "SELECT * FROM Customers", cnNorthwind, adOpenStatic Set rsOrders = New ADODB.Recordset rsOrders.Open "SELECT * FROM Orders", cnNorthwind, adOpenStatic Set rsProducts = New ADODB.Recordset rsProducts.Open "SELECT * FROM Products", cnNorthwind, adOpenStatic 

Dealing with Multiple Connections

The behavior in which an OLE DB provider establishes added connections when needed is part of the OLE DB specification. The development team felt that this behavior would simplify programming by avoiding error messages such as "Connection is busy with results from another hstmt." This change is wonderful for programmers developing applications that will be accessed by a small number of simultaneous users.

For applications that need to handle large numbers of concurrent users, however, this change in behavior has met with a bit of frustration. Some developers notice the automatic addition of connections only when they deploy their applications and see a much higher than expected number of connections to their database.

What can you do to avoid a similar fate? My advice is to use a utility that displays the connections to your database. SQL Server 7 users can use SQL Server Profiler or, as it was called in previous versions, SQL Trace. Run your application and see if your code generates added connections. If it does, determine why your code might require the added connections.

The most likely causes for added connections are

• Using a connection that still has pending results from a firehose cursor
• Using a connection that has an asynchronous operation still running

Some OLE DB providers can help you to prevent multiple connections from being established. The OLE DB Provider For SQL Server exposes a dynamic property through the Connection object that you can set to make the provider generate an error rather than create an additional connection. The following code will generate an error when you attempt to retrieve the results of the second query:

 Set cnNorthwind = New ADODB.Connection cnNorthwind.CursorLocation = adUseServer cnNorthwind.Open strConn cnNorthwind.Properties("Multiple Connections") = False Set rsCustomers = cnNorthwind.Execute("SELECT * FROM Customers") Set rsOrders = cnNorthwind.Execute("SELECT * FROM Orders") 

Unfortunately, the error message you'll receive—"Object was open"—doesn't do a great job of explaining the reason the error occurred, but at least you'll keep the OLE DB provider from generating unexpected connections to your database. If you change the code to retrieve the results of the initial query before submitting the second query, as shown below, the code will run without generating an error.

 Set rsCustomers = cnNorthwind.Execute("SELECT * FROM Customers") Do While Not rsCustomers.EOF rsCustomers.MoveNext Loop Set rsOrders = cnNorthwind.Execute("SELECT * FROM Orders") 

Hopefully other OLE DB providers will implement a similar feature. Perhaps it will even become a part of the next set of OLE DB specifications.

Static Cursors

A static cursor—shown in Figure 7-2—is similar to a forward-only cursor except that the static cursor supports scrolling forward and backward. The query processor builds the results of the query and populates the entire cursor. You can navigate back and forth through the cursor as long as you'd like, and the data won't change. As the cursor name implies, the data is static.

Figure 7-2 The static cursor.

Changes made by other users to the data in the database that corresponds to the results of the query will not be visible. You also won't see new records added by other users that satisfy your query criteria, nor will you see that another user has deleted a record that exists in your cursor. If your query retrieved customer account information into a static cursor, you won't see changes that another user has made to the customer's balance due. Again, the data in your static cursor is static.

Static cursors are traditionally defined as read-only. All client-side Recordsets are marked as "static," but we'll talk about this scenario a little later in this chapter in the section "Client-Side Cursors."

If you use an ADO Recordset that communicates using a static cursor, you can use all of the different Move methods to navigate through the Recordset. ADO will fetch the number of records specified by the CacheSize property. As you navigate through the Recordset, ADO will fetch more data from the cursor any time you step outside the data currently cached.

Keyset Cursors

While a static cursor is similar to a forward-only cursor in nature, a keyset cursor is a much more complicated construct. Not only does a keyset cursor allow you to update data, but it also lets you see changes made by other users.

As mentioned in the previous section, the query processor will completely populate a static cursor when you submit your query. For a keyset cursor, the query processor initially retrieves only the data required to locate the records in your tables that will satisfy the results of your query, as shown in Figure 7-3. Generally this data corresponds to the primary key in your tables and is often referred to as the keyset.

Figure 7-3 The keyset cursor.

The data in the keyset serves two purposes. First, the query processor uses this data to locate the records that satisfy your query. Second, if you update data in any of the records in your keyset cursor, the keyset data enables the query processor and/or the database system to locate that record in the table and update it.

You can open a keyset cursor as read-only or updatable. In addition, changes made by other users will be visible in your keyset cursor. As it does with the static cursor, ADO stores the number of records in its local cache specified by the CacheSize property. Each time ADO requests data from the keyset cursor to fill the cache, the query processor locates the appropriate entries in the database based on the keyset data.

For example, if you open a keyset cursor on the contents of the Customers table, the query processor will store the CustomerID field in the keyset. Then every time ADO refills the cache, the query processor will use the CustomerID to return the latest information from the database to the keyset cursor. If another user has changed a particular customer's balance due, you'll see that change the next time you fetch that customer's record.

Although you can see changes made by another user after you've opened your keyset cursor, you can't see new records added by other users that satisfy the criteria of your query. The data in the keyset (remember that this is the set of key values, not the cursor itself) is static.

Records deleted by other users will be removed from your keyset cursor the next time you refresh the cache. Be sure to do some testing with your particular OLE DB provider or ODBC driver. Depending on the behavior of that provider or driver, you might receive a trappable run-time error if you attempt to navigate to a deleted record. For example, if you move to a deleted record in a keyset cursor by setting the Bookmark property on your Recordset, you'll receive a run-time error if you're using the SQL Server ODBC driver but not if you're using the OLE DB provider. I'd love to tell you that the behavior you'll see will be consistent, but that's not the case.

Dynamic Cursors

The dynamic cursor behaves a lot like the keyset cursor. Initially, the query processor retrieves the key field information for the records that satisfy the search criteria, just like a keyset cursor. Also, the query processor returns the initial set of records to fill the client's cache. But this is where any similarity ends. If you are using a dynamic cursor, the next time the client requests another set of records the query processor will reexamine the contents of the database and rebuild the keyset before returning more records to the client application, as shown in Figure 7-4.

Figure 7-4 The dynamic cursor.

This functionality means that a dynamic cursor can contain records that other users have added since you initially submitted the query. Say you move to the beginning of your Recordset object and count the number of records in the Recordset by counting the number of times you need to call MoveNext until EOF is True. There's no guarantee that you'll come up with the same number of records in the Recordset if you call MoveFirst and loop through its contents again.

Mixed Cursors

The mixed cursor, a hybrid of the keyset and dynamic cursors, is seldom used and is not supported in ADO.

Client-Side Cursors

If you request a client-side recordset and then check the CursorType property, ADO will report that you're using a static cursor. This is not the "classic" use of a static cursor as described earlier in the "Static Cursors" section, and it's worth discussing how this cursor behaves.

When you request a client-side recordset, ADO passes your query to the OLE DB provider and retrieves the results through a firehose cursor. ADO stores the results of the query in its own Cursor Engine, as shown in Figure 7-5. At this point, you can scroll back and forth through your Recordset object, and ADO will retrieve the requested data from the Cursor Engine.

Figure 7-5 Client-side cursor.

So, as you navigate through your recordset you will not see changes made to the database by other users. Because the cursor supports scrolling, and because changes made by other users are not visible, the behavior of a client-side recordset most closely resembles that of a static cursor.

However, a client-side recordset can be updatable, a fact that seems to cause more confusion for experienced database developers than for novices. While discussing customers' applications with them, I found that quite a few experienced developers were basing their Recordset objects on server-side keyset cursors. They were doing this because more often than not they required an updatable Recordset and they understood that the ADO Cursor Engine only supports static cursors, which are read-only.

How are these static cursors updatable? We'll cover this topic in more depth in the next chapter, but here's a quick overview. ADO lets you modify the contents of the cursor. However, because this static cursor is maintained by the ADO Cursor Engine rather than by the database system, changing the contents of the cursor does not automatically update the corresponding records in your database. When you modify your client-side recordset and ask ADO to update the database, by means of the Update or UpdateBatch method, ADO interprets the changes you've made to the Recordset object and attempts to update the database by using action queries that reflect these changes.

Client-side cursors scale well because the ADO Cursor Engine stores the results of your query and lets your database system do what it was designed to do—maintain your data and process your queries.