ADS is a high-performance server; it uses a number of advanced techniques for optimizing the operations that it performs. One of these optimizations is to keep the cursor of a SELECT query in cache following the execution of the query. This technique improves performance since there is a high likelihood that a just executed query will be executed again very soon.
While this optimization increases performance, it has one major drawback. If you are working in an environment that permits you to place an exclusive lock on a table, that lock will fail if the table you are locking has an open cursor in the cursor cache. In other words, an exclusive lock may fail even though the table being locked is not being used by another user.
For those development environments that permit exclusive table locks, such as Delphi, or any development language that can make direct calls to the ACE (Advantage Client Engine) API (such as Delphi, Visual Basic, Visual C++, and C++Builder), there is a way to disable cursor caching. In short, you either call the AdsCloseCachedTables function to close all cursors in the cache or set the size of the cursor cache to 0.
Even if you are not interested in the following technique, we recommend that you check out the Technical Tip of the Month Web page. It contains many good articles that can help you use ADS better.
A more detailed discussion of disabling cursor caching can be found online at the Advantage Database Server site, in one of the Advantage Monthly Tech Tips. To locate this tech tip, go to http://www.AdvantageDatabase.com. Look for Monthly Tech Tips in the Support section. This link takes you to the Technical Tip of the Month page. (Alternatively, use this Web site’s search function to locate the Monthly Tech Tips page.) Scroll to the end of this page to find past technical tips, including the one titled “Closing Tables used in an SQL Statement.”