Progress
Programming
Handbook


Using Database, Temporary, and Work Tables

Database tables provide data storage in a permanent database and can be accessed by single or multiple users. However, if you want to process data for the duration of a procedure in complete privacy, use temporary tables or work tables. Temporary tables have several advantages over work tables:

There are some situations where you might want to use work tables:

Table 15–1 compares database tables, temporary tables, and work tables.

Table 15–1: Database, Temporary, and Work Tables
Progress
Feature
Database
Table
Temporary
Table
Work
Table
Database manager
Progress uses the database manager in a single- or multi-user mode when working with database tables.
Progress uses the database manager in single-user mode to access the temporary database that contains the temporary tables.
Progress does not use the database manager when working with work tables. Work tables are stored in memory.
Indexes
You can define indexes for database tables.
You can define indexes for temporary tables.
You cannot define indexes for work tables.
Record deletion
To remove database records from the database, you must explicitly delete them with the DELETE statement.
If you do not explicitly delete records in the temporary table, Progress discards those records and the temporary table itself at the end of the procedure or session that initially defined the temporary table.
If you do not explicitly delete records in a work table, Progress discards those records and the work table itself at the end of the procedure that initially defined the work table.
Record movement
A database table can move data between a record buffer and a database record.
A temporary table cannot move data between a record buffer and a database record.
Work tables cannot move data between a record buffer and a database record.
Multi-user record access
Multiple users can access the same database table at the same time.
Users do not have access to each other’s temporary tables.
Users do not have access to each other’s work tables.
Transactions
Progress automatically starts transactions for certain blocks and statements.
Database tables use the before-image file to roll back changes.
You must start transactions explicitly.
Temporary tables use the local before-image file to roll back changes.
You must start transactions explicitly.
Work tables use the local before-image file to roll back transactions.
Buffer size
Use the –B parameter to specify the database table buffer size.
Use the –Bt parameter to specify the temporary table buffer size.
Available memory determines the work table buffer size.
Location
Database tables are stored directly in the database.
Temporary tables are stored in the directory where Progress stores temporary files.
Work tables are stored in memory.
Special data type support
You can store RAW and RECID fields, but not ROWID fields, in a database table.
You can store RAW, ROWID, and RECID fields in a temporary table.
You can store RAW, ROWID, and RECID fields in a work table.
Triggers and Events
Database tables support triggers and events.
Temporary tables do not support triggers and events.
Work tables do not support triggers and events.

The following sections describe in greater detail the similarities and differences between temporary tables and work tables, as well as how to define indexes for temporary tables.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095