13.2.3- What is the Cursor Type?

What is the Cursor Type?

As we've said, we can think of the recordset's cursor as a type of pointer. Every recordset has exactly one cursor, which (at any given time) points to exactly one of the records in the recordset.

We've also very gently hinted that there are different types of cursors. When we open the recordset, we can specify the cursor type – and this will affect the available functionality of the recordset that is returned to us. For example, if we select a forward-only cursor (as we've done in the examples so far), we'll get a recordset whose cursor is only able to move forwards through the records.

In addition to the cursor type, there  are other factors that also affect the available functionality of the recordset – including the lock type and the cursor location. We'll be looking at what these things are, and what they mean, shortly.

Recordset Characteristics

So before we consider the available values for the cursor type, let's consider some of the characteristics that we may want our recordset to display. This will help us decide what cursor type and lock type we'll need when we use the Open method in future.

There are four major characteristics that we are interested in:

Updateable vs Non-Updateable

Are you planning to open a recordset in order to change the data within it? If not, then it makes sense to use a non-updateable (or read-only) recordset to read the data. Using a non-updateable recordset means that the data provider can simply send the data to you and forget about it. You're telling it not to expect any changes – this means that it doesn't need to keep track of what you are doing. This can give performance benefits.

By contrast, if you're planning to change the data in your recordset, then you'll need your recordset to be updateable.

Scrollable vs Non-Scrollable

When you're moving the cursor through the records in your recordset, will you need to move backwards as well as forwards? If so, you'll need a scrollable recordset. The cursor of a scrollable recordset allows both backward and forward movement of the cursor.

If you're only going to need forward movement of the cursor – as in the Recordset.asp example earlier in this chapter – then a non-scrollable recordset will suffice. A non-scrollable recordset can also give performance benefits over a scrollable one, because the recordset doesn't have to keep track of the data once the cursor has moved passed it.

Keyset vs Non-Keyset

Most tables in a database have some kind of unique key – and this is the heart of a keyset recordset. Even those that don't have a visible (defined by us) unique key will have a unique key that the database maintains. When you request a non-keyset recordset you get all of the data back; while a keyset recordset just returns the unique keys, and only fetches the data itself at the time you request that record.

In fact, this is a generalization: in reality you'll probably find a set of records returned as well. The idea is that if all of the keys, plus data contained in the first few records, are returned then there is very little delay in getting the first set of data. If you move to a key that is not within the current block, then another block of data is fetched. The advantage of a keyset recordset is that the recordset only has to keep track of the keys, which are generally small, rather than a large amount of data.

Dynamic vs Static

This characteristic determines the availability of particular records in the recordset, at a specific time. A static recordset contains only those records that were available when the recordset was created. The records are cached in local memory and the recordset will be unaware of any changes to data in the data store, made by other users. For example, another user could subsequently delete a record included in your static recordset when it was created, but this will not be reflected in your recordset.

In contrast, a dynamic recordset will accurately represent any changes made to the data, either by you or by another database user. You can think of a RECORDSET as a "window" onto the data in the database. The rows are loaded into the recordset "as requested" (and not cached locally, as for a static cursor). This means that if new records are added, deleted, or changed by other users while you are accessing the database they will become visible when these records scroll into the part of the recordset you are viewing. The recordset changes dynamically, in demand to the records you are actually managing at the time.

So it all boils down to how you see the records and how you navigate through the records. Let's look at the ADO cursor types, and how they relate to the characteristics we've outlined here.

ADO Cursor Types

The updateability of your recordset isn't directly related to the cursor type – that's more the territory of the lock type (which we'll come to shortly). However, the other three characteristics are closely related to the list of possible cursor types we have to choose from. There are four in total, as follows:

• Forward-only (adOpenForwardOnly): this is the default type and gives you a non-scrollable recordset. This is often perfect in ASP code, because we can often find ourselves just stepping through a list of records in order to display each of them on the browser. In that case, we just need to start at the first record and move forwards through the recordset until we get to the end – we don't need to move backwards. It's also static, so changes to the underlying data are not represented.
• Static (adOpenStatic): this is similar to a forward-only recordset, except that it is scrollable, so you can move back to previous records as well as moving forwards.
• Dynamic (adOpenDynamic): the recordset is fully dynamic, and lets you see additions, amendments and deletions that are made by other users. It's fully scrollable so you can move around the recordset any way you like.
• Keyset (adOpenKeyset): this is similar to the dynamic recordset, but you can't see records that other users add, although you can see changes to existing records. Any records that other users delete become inaccessible.

All you have to bear in mind is what you actually want to do with your records. If you just want to step through them one at a time, then a forward-only cursor is the one you need. If you want to scroll backwards too, but still don't want to make any changes, then you need a static cursor. If you need to be able to see any changes you make to your recordset, then a dynamic or keyset cursor is required (otherwise you may need to Close and re-Open it).

The CursorType Property

If you flick back through the pages of this chapter to the Recordset.asp example, you'll see that we specified the cursor type in the third parameter of the Open method (here we created a recordset with a forward-only cursor):

objRS.Open "Movies", strConnect, adOpenForwardOnly, adLockReadOnly, adCmdTable

Alternatively, you can also the set the cursor type directly, using the CursorType property like this:

objRS.CursorType = adOpenForwardOnly

You can read the CursorType property at any time, but you can only set it before the recordset is assigned a live connection.

Cursor Location

The location of your cursor can also have an effect on how your recordset operates. By cursor location we don't mean the record that the cursor is currently pointing to – rather, we mean the body that's responsible for creating the cursor – the client or the server.

Particular functionality will be available depending on which you choose. If you want a dynamic cursor, you must choose server-side. If you choose a client-side cursor then it will be a static cursor.

Certain methods won't work on server-side cursors and you can't do things such as creating local indexes, which can only be done on the client. However, at least 90% of methods will function the same on both the client and server, but it is something to look out for.