Advantage AdsQuery and RecNo

At the time of this writing, Marco Cantù and I have completed the US part of our 2011 Delphi Developer Days tour, and are beginning the European part at the end of next week (yes, there are a few seats available in each city. If you are interested in attending, you must act soon. Otherwise, you’ll have to wait until next year). One of the benefits of Delphi Developer Days is that we limit registration to just over 30 people in each city, which ensures that we have a sufficient number of attendees to stimulate discussion and networking, but few enough that every attendee has the opportunity to talk with Marco and me on a one-on-one basis.

At our Baltimore stop in April, one of the attendees approached me with a problem that he encountered during his attempt to convert his commercial application from using the Borland Database Engine (BDE) to the Advantage Database Server (ADS). (This is the most logical and easiest migration for BDE developers, in my opinion).

In his tests, the AdsQuery (the ADS equivalent to the BDE Query) did not treat the RecNo property the same as other DataSets. In all other DataSets, he noted, RecNo reports the position of the current record within the current DataSet with respect to the order of the records in the DataSet. For example, in a ClientDataSet, a Query, or a SQLDataSet, RecNo will be equal to 1 when you are on the first record of the DataSet, which is the default when you first open the DataSet, regardless of which index you’ve select (ClientDataSet) or SQL ORDER BY clause (Query or SQLDataSet) you are using.

“There must be a way around this,” he conceded, “since the Advantage Data Architect can report the correct record position for records in queries that include in an ORDER BY clause.”

I put together a simple test between sessions, and confirmed his observation that RecNo, at least using the default settings of the AdsQuery, did in fact return the position of that record within the underlying table, and not the position of the record in the current record order. As a result, when an AdsQuery that employs an ORDER BY clause is first opened, RecNo for the current record (the first record in the view) might 55, or 1,002,837, or whatever position that first record occupies in the underlying table, regardless of the order of that record’s position in the result set.

RecNo has an additional use, as well. RecNo can be used to navigate a DataSet (at least in DataSets that support navigation). Specifically, if you set RecNo to 1, the DataSet navigates to record 1 in the current sort order. However, with the AdsQuery in my test, setting RecNo to 1 navigated to the first record in the underlying table, which is not necessary the first record in the indexed or sorted order of the result set.

I had not seen this problem before, and I didn’t solve the problem on the spot. However, I did say that I would try to understand the issue better, and report my results to the Advantage team if I could not find an acceptable solution.

I’ve been very busy, but I finally found time to put together a demonstration of the issue. It confirmed that in the default configuration, the AdsQuery.RecNo property is associated with a record’s true position in the underlying table. However, after some poking around, I found two properties that change this behavior. Specifically, when you set an AdsQuery’s Sequenced property to True, and its SequenceLevel property to slExact, the AdsQuery treats RecNo the same as all other DataSets I tested.

I imagine that there is a performance explanation for the default values of the AdsQuery properties. Specifically, so long as you don’t need to use the RecNo property to determine the view-specific position of a record in a result set, and do not need to use it to perform view-specific navigation, the default property values result in faster query performance. When the standard DataSet.RecNo behavior is required, however, the extra overhead is worth the benefit. So, if you are using Advantage, and need the AdsQuery.RecNo property to behave the DataSet way, all you need to do it tweak its properties a bit.

I am sharing this story for two reasons. First, I hope that this post helps the next Advantage user who runs into this problem. Second, it demonstrates added value that Marco and I bring to Delphi Developer Days. I can’t promise that we can solve every problem for you, but we are there to listen, and we do our best to offer suggestions for solutions.