Advantage Database Server and Advantage Local Server support the creation and utilization of temporary tables that are local to each user connection. Temporary tables are otherwise identical to regular tables except for two unique properties that make them ideal for storing temporary or intermediate data for complex transactions. First, temporary tables are visible only on the connection where the temporary tables are created. This eliminates the possibility of name conflict when creating a temporary table. There could be multiple temporary tables named "Temp1" residing on different connections. Secondly, temporary tables are automatically deleted when the connection that creates them is closed. In other words, the scope of the temporary table is the current connection. This removes responsibility of maintaining the temporary table from the application. The Advantage Database Server or Advantage Local Server is responsible for creating the temporary table and removing them when they are out of scope. The application may still explicitly remove the used temporary tables to reduce the resource usage on the server.
Temporary tables can be used on both the free connection and the database connection. Advantage creates physical tables with a unique name to represent each temporary table. Where the physical files are located is dependant on the server type and the connection type. With Advantage Local Server, the temporary files are always created on the local computer and located in the Window’s temporary directory for the current user. With Advantage Database Server, the location of the file depends on the whether the connection is a free connection or a database connection. With a free connection, the temporary files are located in the same directory as the connection path. With a database connection, the files are located in the temporary directory specified in the data dictionary.
Temporary tables can be created and used in SQL statements or they can be created using the AdsCreateTable71 API and opened using the AdsOpenTable API. See the Advantage Client Engine documentation for more information (ACE.hlp or ace.htm). (Note that each of the Advantage products and their corresponding Help files are installed separately.) Temporary tables created using SQL can be used using the APIs and vice versa.
Using Temporary Tables in SQL Statements