Advantage Developer Zone


Closing Tables used in an SQL Statement

Saturday, March 29, 2003

Question: How do I force Advantage to close tables that have been used in an SQL statement?

Answer A: If using Advantage version 7.0 or greater and the Advantage TDataSet Descendant components, call TAdsConnection.CloseCachedTables. If using Advantage version 7.0 or greater and any other Advantage client, call the Advantage Client Engine (ACE) API AdsCloseCachedTables.

Answer B: If using Advantage version 6.2 or older, table caching needs to be turned off. Turning off the table caching will decrease performance but will force tables used in SQL statements to be closed.

If using the Advantage TDataSet Descendant, place an AdsSettings component on your form and set NumCachedCursors property to zero. Call AdsCloseSqlStatement() after the query is closed. Equivalent functionality exists in other Advantage clients to close the underlying tables used by SQL statements.

Question: Why would I want Advantage to close its table cache?

Answer: By default with the Advantage SQL engine, when an SQL statement is closed, the tables used by the query are only cache closed and are held open on the Advantage server. This is done to increase performance of subsequent queries by reducing disk I/O operations. Problems may arise when future operations require exclusive access to one or more tables that are currently in the cache closed state. Since the Advantage server still has these tables open, they are unable to be opened in an exclusive mode by a future database operation. TAdsTable.AdsPackTable, TAdsTable.AdsZapTable, AdsRestructureTable, and TAdsTable.AdsReindex are all examples of functions which require exclusive access to their target table.