Cursor Location

The CursorLocation property allows you to specify what is in control of the retrieval and update of your data. You have two choices: client (clUseClient) or server (clUseServer). Your choice affects your dataset's functionality, performance, and scalability.

A client cursor is managed by the ADO Cursor Engine. This engine is an excellent example of an OLE DB service provider: It provides a service to other OLE DB providers. The ADO Cursor Engine manages the data from the client side of the application. All data in the result set is retrieved from the server to the client when the dataset is opened. Thereafter, the data is held in memory, and updates and manipulation are managed by the ADO Cursor Engine. This is similar to using the ClientDataSet component in a dbExpress application. One benefit is that manipulation of the data, after the initial retrieval, is considerably faster. Furthermore, because the manipulation is performed in memory, the ADO Cursor Engine is more versatile than most server-side cursors and offers extra facilities. I'll examine these benefits later, as well as other technologies that depend on client-side cursors (such as disconnected and persistent recordsets).

A server-side cursor is managed by the RDBMS. In a client/server architecture based on a database such as SQL Server, Oracle, or InterBase, this means the cursor is managed physically on the server. In a desktop database such as Access or Paradox, the "server" location is a logical location, because the database is running on the desktop. Server-side cursors are often faster to load than client-side cursors because not all the data is transferred to the client when the dataset is opened. This behavior also makes them more suitable for very large result sets where the client has insufficient memory to hold the entire result set in memory. Often you can determine what kinds of features will be available to you with each cursor location by thinking through how the cursor works. Locking is a good example of how features determine the cursor type; I will discuss locking in more detail later. (To place a lock on a record requires a server-side cursor, because there must be a conversation between the application and the RDBMS.)

Another issue that will affect your choice of cursor location is scalability. Server-side cursors are managed by the RDBMS; in a client/server database, this will be located on the server. As more users use your application, the load on the server increases with each server-side cursor. A greater workload on the server means that the RDBMS becomes a bottleneck more quickly, so the application is less scalable. You can achieve better scalability by using client-side cursors. The initial hit on opening the cursor is often heavier, because all the data is transferred to the client, but the maintenance of the open cursor can be lower. As you can see, many conflicting issues are involved in choosing the correct cursor location for your datasets.

Cursor Type

Your choice of cursor location directly affects your choice of cursor type. To all intents and purposes there are four cursor types, but one value is unused: a cursor type that means unspecified. Many values in ADO signify an unspecified value, and I will cover them all here and explain why you won't have much to do with them. They exist in Delphi because they exist in ADO. ADO was primarily designed for Visual Basic and C programmers. In these languages, you use objects directly without the assistance dbGo provides. As such, you can create and open recordsets, as they are called in ADO-speak, without having to specify every value for every property. The properties for which a value has not been specified have an unspecified value. However, in dbGo you use components. These components have constructors, and these constructors initialize the properties of the components. So, from the moment you create a dbGo component, it will usually have a value for every property. As a consequence, you have little need for the unspecified values in many enumerated types.

Cursor types affect how your data is read and updated. As I mentioned, there are four choices: forward-only, static, keyset, and dynamic. Before we get too involved in all the permutations of cursor locations and cursor types, you should be aware that there is only one cursor type available for client-side cursors: the static cursor. All other cursor types are available only to server-side cursors. I'll return to the subject of cursor type availability after we have looked at the various cursor types, in increasing order of expense:

Forward-Only Cursor  The forward-only cursor is the least expensive cursor type, and therefore the type with the best performance. As the name implies, the forward-only cursor lets you navigate forward. The cursor reads the number of records specified by CacheSize (default of 1); each time it runs out of records, it reads another CacheSize set. Any attempt to navigate backward through the result set beyond the number of records in the cache results in an error. This behavior is similar to that of a dbExpress dataset. A forward-only cursor is not suitable for use in the user interface where the user can control the direction through the result set. However, it is eminently suitable for batch operations, reports and stateless Web applications, because these situations start at the top of the result set and work progressively toward the end, and then the result set is closed.

Static Cursor  A static cursor works by reading the complete result set and providing a window of CacheSize records into the result set. Because the complete result set has been retrieved by the server, you can navigate both forward and backward through the result set. However, in exchange for this facility, the data is static—updates, insertions, and deletions made by other users cannot be seen, because the cursor's data has already been read.

Keyset Cursor  A keyset cursor is best understood by breaking keyset into the two words key and set. Key, in this context, refers to an identifier for each row. Often this will be a primary key. A keyset cursor, therefore, is a set of keys. When the result set is opened, the complete list of keys for the result set is read. If, for example, the dataset was a query like SELECT * FROM CUSTOMER, then the list of keys would be built from SELECT CUSTID FROM CUSTOMER. This set of keys is held until the cursor is closed. When the application requests data, the OLE DB provider reads the rows using the keys in the set of keys. Consequently, the data is always up to date. If another user changes a row in the result set, then the changes will be seen when the data is reread. However, the set of keys is static; it is read only when the result set is first opened. If another user adds new records, these additions will not be seen. Deleted records become inaccessible, and changes to primary keys (you don't let your users change primary keys, do you?) are also inaccessible.

Dynamic Cursor  The most expensive cursor type, a dynamic cursor is almost identical to a keyset cursor. The sole difference is that the set of keys is reread when the application requests data that is not in the cache. Because the default for ADODataSet.CacheSize is 1, such requests occur frequently. You can imagine the additional load this behavior places on the DBMS and the network, and why this is the most expensive cursor. However, the result set can see and respond to additions and deletions made by other users.

Ask and Ye Shall Not Receive

Now that you know about cursor locations and cursor types, a word of warning: Not all combinations of cursor location and cursor type are possible. Usually, this limitation is imposed by the RDBMS and/or the OLE DB provider as a result of the functionality and architecture of the database. For example, client cursors always force the cursor type to static. You can see behavior this for yourself. Add an ADODataSet component to a form, set its ConnectionString to any database, and set the ClientLocation property to clUseCursor and the CursorType property to ctDynamic. Now set Active to True and keep your eye on the CursorType; it changes to ctStatic. You learn an important lesson from this example: What you ask for is not necessarily what you get. Always check your properties after opening a dataset to see the actual effect of your requests.

Each OLE DB provider will make different changes according to different requests and circumstances, but here are a few examples to give you an idea of what to expect:

• The Jet 4.0 OLE DB provider changes most cursor types to keyset.

• The SQL Server OLE DB provider often changes keyset and static to dynamic.

• The Oracle OLE DB provider changes all cursor types to forward-only.

• The ODBC OLE DB provider makes various changes according to the ODBC driver in use.

No Record Count

ADO datasets sometimes return –1 for their RecordCount. A forward-only cursor cannot know how many records are in the result set until it reaches the end, so it returns –1 for the RecordCount. A static cursor always knows how many records are in the result set, because it reads the entire set when it is opened, so it returns the number of records in its result set. A keyset cursor also knows how many records are in the result set, because it has to retrieve a fixed set of keys when the result set is opened, so it also returns a useful value for RecordCount. A dynamic cursor does not reliably know how many records are in the result set, because it is regularly rereading the set of keys, so it returns –1. You can avoid using RecordCount altogether and execute SELECT COUNT(*) FROM tablename, but the result will be an accurate reflection of the number of records in the database table—which is not necessarily the same as the number of records in the dataset.

Client Indexes

One of the many benefits of client-side cursors is the ability to create local, or client, indexes. Assuming you have an ADO client-side dataset for the DBDemos Customer table, which has a grid attached to it, set the dataset's IndexFieldNames property to CompanyName. The grid will immediately show that the data is in CompanyName order. There is an important point here: In order to index the data, ADO did not have to reread the data from its source. The index was created from the data in memory. So, not only is the index created as quickly as possible, but the network and the DBMS are not overloaded by transferring the same data over and over in different orders.

The IndexFieldNames property has more potential. Set it to Country;CompanyName and you will see the data ordered first by country and then, within country, by company name. Now set IndexFieldNames to CompanyName DESC. Be sure to write DESC in capitals (and not desc or Desc). The data is now sorted in descending order.

This simple but powerful feature allows you to solve one of the great bugbears of database developers. Users ask the inevitable, and quite reasonable, question, "Can I click the columns of the grid to sort my data?" Answers like replacing grids with non–data-aware controls such as ListView that have the sorting built into the control or like trapping the DBGrid's OnTitleClick event and reissuing the SQL SELECT statement after including an appropriate ORDER BY clause are far from satisfactory.

If you have the data cached on the client side (as you've also seen in the use of the ClientDataSet component), you can use a client index computed in memory. Add the following OnTitleClick event to the grid (the code is available in the ClientIndexes example):

procedure TForm1.DBGrid1TitleClick(Column: TColumn);
begin
  if ADODataSet1.IndexFieldNames = Column.Field.FieldName then
    ADODataSet1.IndexFieldNames := Column.Field.FieldName + ' DESC'
  else
    ADODataSet1.IndexFieldNames := Column.Field.FieldName
end;

This simple event checks to see whether the current index is built on the same field as the column. If it is, then a new index is built on the column, but in descending order. If not, then a new index is built on the column. When the user clicks the column for the first time, it is sorted in ascending order; when it is clicked for the second time, it is sorted in descending order. You could extend this functionality to allow the user to Ctrl+click several column titles to build up more complicated indexes.

Cloning

ADO is crammed with features. You can argue that feature-rich can translate into footprint-rich, but it also translates into more powerful and reliable applications. One such powerful feature is cloning. A cloned recordset is a new recordset that has all the same properties as the original from which it is cloned. First I'll explain how you can create and use a clone, and then I'll explain why clones are so useful.

You can clone a recordset (or, in dbGo-speak, a dataset) using the Clone method. You can clone any ADO dataset, but you will use ADOTable in this example. The DataClone example (see Figure 15.6) uses two ADOTable components, one hooked to database data and the other empty. Both datasets are hooked to a DataSource and grid. A single line of code, executed when the user clicks a button, clones the dataset:

ADOTable2.Clone(ADOTable1);

Figure 15.6: The form of the DataClone example, with two copies of a dataset (the original and a clone)

This line clones ADOTable1 and assigns the clone to ADOTable2. In the program, you'll see a second view of the data. The two datasets have their own record pointers and other status information, so the clone does not interfere with its original copy. This behavior makes clones ideal for operating on a dataset without affecting the original data. Another interesting feature is that you can have multiple different active records, one for each of the clones— functionality you cannot achieve in Delphi with a single dataset.