Navigational Actions with ADS and ADO

ADO supports a number of navigational operations on populated Recordsets, and is second only to Delphi’s capabilities in this area. In fact, the only navigational operation supported by Delphi but not by ADO is setting a range (though you can explicitly set a range by calling into the ACE API using Visual Basic, but this is not an ADO issue).

This section describes the navigational options made available through server-side cursors. These operations can be performed on any SQL result set that returns a live cursor or any table opened directly.

Unlike most remote relational database servers, ADS also supports a non-SQL technique for working with a server-side cursor. It involves opening a table directly. When you open a table directly, the Advantage OLE DB Provider obtains a table handle, which permits ADS to use its high-performance indexes, Advantage Optimized Filters, and read-ahead record caching to supply data to your client application. Fortunately, with live cursors, ADS also uses these high-performance features.

You open a table directly by setting the CommandText property of a Command object or the Source parameter of a Recordset’s Open method to the table name. You then set the CommandType property of the Command, or the Options parameter of the Recordset’s Open method, to adCmdTableDirect. This is shown in the following code segment, which is taken from the ShowInvoiceBtn click event:

If AdsRecordset.State = adStateOpen Then   AdsRecordset.Close End If AdsRecordset.Open "INVOICE", AdsConnection, _   adOpenDynamic, adLockPessimistic, adCmdTableDirect Set DataGrid1.DataSource = AdsRecordset 

It’s worth noting that there are some similarities, but also some significant differences between using a CommandType of adCmdTable and adCmdTableDirect. Just as you do when you set CommandType (or Options) to adCmdTableDirect, when you use adCmdTable, you assign the name of the table to the CommandText property of a Command object, or pass the table name in the Source parameter of a Recordset’s Open method. In response, the Command or Recordset object generates a SELECT * FROM query. Queries like these return a live, server-side cursor (so long as you did not specifically request a client-side cursor), which enables the use of the table’s indexes for searching, filtering, and the like.

By comparison, when you set CommandType (or Options) to adCmdTableDirect, the Advantage SQL engine is bypassed altogether, instead opening the table using an OLE DB Rowset object. Opening a table this way enables additional capabilities, including being able to obtain an exclusive lock on the table, which cannot be done through a SQL SELECT statement.

Performing navigational actions on server-side cursors is described in the following sections.

Note

The Advantage OLE DB Provider also supports high-performance bookmarks on server-side cursors. For information on using bookmarks, see the ADS help.

Setting an Index

From within an ADO application, you select an available index for one of two reasons. Either you want to sort the records in your Recordset based on the index expression, or you want to use the index to enable high-speed searches using a Recordset.

Fortunately, selecting an index that you have defined in a table’s index file is straightforward. You assign the name of an index order to the Index property of a Recordset that returns either a dynamic (live) cursor or a table that is opened directly using the adCmdTableDirect command type. If you are connected to a data dictionary, the index order name can be in any of your table’s auto-open indexes.

You return to the natural index order by setting the Index property of the Recordset to an empty string. Setting an index is demonstrated in the following code segment, which is associated with the Select Invoice No Index button (shown in Figure 14-1):

If AdsRecordset.State <> adStateOpen Then   MsgBox "Please open Invoice table before setting index"   Exit Sub   End If AdsRecordset.Index = "Invoice No"

Note that you cannot set an index if the Recordset is already actively employing a filter. However, you can apply a filter on a Recordset that is using an index.

Finding a Record Based on Data

ADO supports two methods for searching for data in a Recordset. One of these, Find, performs a record-by-record search for data. When used with server-side cursors, these sequential reads are performed by ADS, but nonetheless the sequential nature of this operation means that it is often relatively slow—especially when the result set is large.

The second method, Seek, is only supported in server-side cursors by OLE DB providers that also support indexes, and fortunately, the Advantage OLE DB Provider is one of them. Unlike Find, Seek uses ADS indexes on server-side cursors to quickly locate records. Compared to Find, Seek is typically much faster at finding records with server-side cursors.

Before you can call Seek on a Recordset, you must set an index that you will use to find the record you are looking for. Once the index is set, you call Seek, passing either a single value or an array of values. If you pass a single value, ADS will search the current index for that value in the first field of the index.

You pass an array of values to Seek when you want to search on more than one expression of a multisegment index order (a multisegment index order is based on two or more fields or expressions). In this case, Seek searches for the first array element in the first field or expression of the current index, then searches the second array element, if present, in the second field or expression of the current index, and so on.

The second, optional parameter, SeekOption, defines how the Seek is performed. Valid values for this second parameter include adSeekAfter, adSeekAfterEQ, adSeekBefore, adSeekBeforeEQ, adSeekFirstEQ, and adSeekLastEQ. The default value is adSeekFirstEQ.

If a matching record is found, based on the SeekOption, the record associated with the value or values is made the current record. If the value or values are not found, the Recordset will point to the end-of-file marker.

The use of Seek is demonstrated in the following code segment. This code is associated with the change event of the TextBox named SearchText. After clicking the Show Invoice Table and Set Invoice No Index buttons, this code permits an incremental search through the INVOICE table:

If AdsRecordset.State <> adStateOpen Then   MsgBox "Please open Invoice table before setting index"   Exit Sub End If If AdsRecordset.Index <> "Invoice No" Then   MsgBox "You must set the Invoice No index before searching"   Exit Sub End If AdsRecordset.Seek SearchText.Text, adSeekAfterEQ If AdsRecordset.EOF Then   MsgBox "End of file" End If 

Setting a Filter

You use a filter to limit a Recordset to a subset of records. When executed on a server-side Recordset, ADS produces an AOF (Advantage Optimized Filter), after which it repopulates the Recordset based on the filtered view.

You set a filter by assigning a filter expression to a Recordset’s Filter property. You drop a filter by setting the Filter property to an empty string.

Although the filter expressions that you can assign to the Filter property of a Recordset are similar to those that you can set to ADS using other mechanisms (such as using the Advantage Data Architect or the Advantage TDataSet Descendant), there is one important difference. If you include a field name that contains embedded spaces, you must enclose the field name in square brace delimiters.

Setting and dropping a filter is demonstrated in the following code, which is located in the click subprocedure for the Set Filter button (shown in Figure 14-1):

If AdsRecordset.State <> adStateOpen Then   MsgBox "Please open Invoice table before setting index"   Exit Sub End If If FilterBtn.Caption = "Drop Filter" Then   AdsRecordset.Filter = ""   FilterBtn.Caption = "Set Filter" Else   AdsRecordset.Filter = FilterText.Text   FilterBtn.Caption = "Drop Filter" End If Set DataGrid1.DataSource = AdsRecordset

If you run this project, click the Show Invoice Table button and then enter the following filter expression:

[Customer ID] = 12037 and [Employee ID] = 89

Once you click the Set Filter button, the DataGrid at the bottom of the form will display only two records, as shown in Figure 14-4.

Figure 14-4: A filter has been applied to a Recordset.

Note

Although a filter does not rely on the current index, the speed with which a filter can be applied is directly related to the available indexes on the table. Specifically, filters can be applied quickly when the expressions in the filter expression map to available indexes on the underlying table.

Scanning a Result Set

Scanning is the process of sequentially reading every record in a result set, or every record in the filtered view of the result set if a filter is active. In most cases, scanning involves an initial navigation to the first record of the result set, followed by repeated calls to advance one record until all of the records have been visited.

Although scanning is a common task, it is important to note that it necessarily requires the client application to retrieve all of the records in the result set.

When using a client-side cursor, all records must be retrieved to the client before any action can be taken. However, once retrieved, the scanning process itself is very fast. By comparison, when using a server-side cursor, the records are read to the client during the scanning process. Consequently, scanning can initiate faster but may take longer when using a server-side cursor.

Tip

If you are using ADS, and you must scan a large number of records, implement the operation using an AEP as described in Chapter 7. Scanning from an AEP installed on ADS requires no network resources.

The following code demonstrates scanning records in a Recordset. This code, associated with the click subprocedure of the List Products button (shown in Figure 14-1), navigates the entire PRODUCTS table, assigning data from each record to the ProductList list box:

If AdsRecordset.State = adStateOpen Then   AdsRecordset.Close End If AdsRecordset.Open "SELECT * FROM PRODUCTS", AdsConnection, _   adOpenDynamic, adLockPessimistic, adCmdText ProductList.Clear AdsRecordset.MoveFirst While Not AdsRecordset.EOF   ProductList.AddItem (AdsRecordset.Fields(0).Value & _   vbTab & AdsRecordset.Fields(1).Value)   AdsRecordset.MoveNext Wend AdsRecordset.Close