What the Heck is a Cursor, Anyway?
No, this is not the little flashing mark that shows you where you are on the screen. You can think of a cursor – in ADO terms - as another way of referring to a recordset. All of the cursor information - what the underlying OLE DB/ODBC code retrieves from the data source - is contained in the Recordset. The Recordset object is referred to as your 'cursor of data'.
When we have been programming databases so far, we have tended to think of processing our data in terms of a logical sequence of records. For example, we have written an application that read through the records in the publishers table and displayed the name in a grid. The application read through all of the records in a recordset and displayed the name field from each record until it reached the end of the file (EOF).
When your database applications use queries to do data access, the "data" that is returned is a query result set based on the SQL query statements. When we consider the query result set, or recordset, we can't think of it in terms of a "next row" concept, as we can think about the rows in a spreadsheet. Nor is there any way to operate on the individual recordset rows.
This scenario tends to be a bit awkward because most developers understand sequential record-based retrieval – however they many times have no corresponding experience with query result sets, our recordsets. While your query-based database application knows typically what to expect in the recordset, it may need to do more processing. For example, it may need to evaluate certain columns in selected rows to reach some sort of conclusion.
What is meant by this? Well, consider our application that retrieved all of the records from the Publishers table but only displayed – say - 10 records at a time in the grid. Such applications need some sort of mechanism to map one row (or a small block of 10 rows simultaneously displayed in our grid ) from the recordset set into the bound grid control. How can the grid know which records to display out of the over 700 when only 10 are shown at a single time? The user can scroll forwards, backwards, or jump to the end of the recordset using the scroll bar. How can the program know which records are to be displayed when it working on a recordset that has no concept of 'next row'?
Enter the cursor. Cursors are animals that expose the entire recordset so that your application can use rows in much the same way we would use records in a sequential file. The following shows how a cursor 'really' makes rows available to your application. Let's say that our application issued the following SQL Query:
SELECT * FROM Publishers WHERE City = "New York"
Conceptually, our recordset looks like this:
.gif)
We can see our records as if they were indeed sequential. This permits us to iterate through the recordset and display the publisher's name in a grid. We have been blissfully unaware of the magic of cursors that makes this happen. Let's take a look at what is really going on beneath the smooth surface that is presented to us:
.gif)
Notice that when our application issues the SQL query, the results are really returned in no specific order. The records returned reflect the arbitrary order in which they exist in the database – usually the order that they were entered in. However, they are presented to us in a nice, sequential order. This is what a cursor does for us – it manages the recordset. With our cursor, we can:
Specify positioning at a specific row in the recordset
Retrieve one row, or a block of rows to display in our grid, based on the current result set position
Modify data in the row at the current position in the recordset
Notice that, as far as the user is concerned, the publisher records are appearing one at a time. However, behind the scenes the application is using a scrollable cursor to browse up and down through the recordset.
By using a read-only cursor, the user can browse through the recordset but not make updates. A read/write cursor can implement changes to individual rows. More complex cursors can be defined with keysets. These point back to base rows directly in a database table.
Some cursors are read-only in a forward direction, which makes them very fast. They don't have to bother with handling the mechanics of moving backwards as well. These are great for updating a read-only grid: we just loop through the recordset and display the data. Other cursors can move back and forth in the recordset and provide a dynamic refresh of the recordset based on changes other users happen to make to the database. Although each cursor "library" uses a slightly different syntax and usually has somewhat different approach to implement these things, they are all similar in most respects
But don't worry. Cursors aren't really as enigmatic as they might appear. Of course, we have actually been using them all along - albeit unwittingly - every time we have written a DAO or ADO program. These interfaces all use cursors in some form. Whenever our VB6.0 database application requires data access, we request a recordset to be opened. We really received a type of cursor from the interface. These DAO or ADO interfaces can have their own cursor library. Or they might use the cursors provided by the data source we are accessing.
Luckily, as a user of a cursor, we don't have to create the cursor directly. We are really requesting it from some service provider, such as a relational database or a cursor library.
So think of a cursor as being the manipulator of a set of data. This data is prepared by a service such as Jet, and it uses the resources of the owner of the cursor. So a cursor manages our data: it has the ability to retrieve a portion of that data in the recordset. When the user us using our grid control to move from record to record, a request is made by the user of the cursor to retrieve a piece of data. This, in cursor lingo, is called scrolling*.*
While cursors were always with us when we were using DAO, we didn't need to concern ourselves with them. In fact, if you take a look at the intrinsic data control, a choice of cursors is there as plain as day:
.gif)