DEFINE TEMP-TABLE statement

Defines a temp-table that is created at compile time. The AVM stores temp-tables in memory (with potential overflow to disk). Among procedures, a temp-table can be either global (lasting for the entire ABL session) or local (lasting only as long as the procedure that creates it), and either shared (visible to other procedures that want to access it) or non-shared (visible just to the procedure that created it). In a class, a temp-table can be defined for use within a single class or class hierarchy.

Syntax

DEFINE {[[ NEW [ GLOBAL ]] SHARED ]| 
            [ PRIVATE | PROTECTED ][ STATIC ]
            [ SERIALIZABLE | NON-SERIALIZABLE ]}
  TEMP-TABLE temp-table-name[ NO-UNDO ] 
  [ NAMESPACE-URI namespace][ NAMESPACE-PREFIX prefix]
  [ XML-NODE-NAME node-name][ SERIALIZE-NAME serialize-name ] 
  [ REFERENCE-ONLY ]
  [ LIKE table-name
      [ VALIDATE ]
      [ USE-INDEX index-name[ AS PRIMARY ]]...]
  [ LIKE-SEQUENTIAL table-name
      [ VALIDATE ]
      [ USE-INDEX index-name[ AS PRIMARY ]]...]
  [ RCODE-INFORMATION ] 
  [ BEFORE-TABLE before-table-name] 
  [ FIELD field-name
      { AS data-type|  LIKE field[ VALIDATE ]}
  [field-options]
  ]...
  [ INDEX index-name
      [[ AS | IS ][ UNIQUE ][ PRIMARY ][ WORD-INDEX ]]
      {index-field[ ASCENDING | DESCENDING ]}...
  ]...
NEW SHARED TEMP-TABLE temp-table-name
Defines and identifies a temp-table object that can be shared by one or more procedures called directly or indirectly by the current procedure. The temp-table remains available to other procedures until the procedure that defined it ends. The called procedures must define the same temp-table name using a DEFINE SHARED TEMP-TABLE statement.
Note: A SHARED temp-table cannot have a BEFORE-TABLE.
NEW GLOBAL SHARED TEMP-TABLE temp-table-name
Defines and identifies a global shared temp-table object. The scope of a global shared temp-table is the ABL session. The first procedure to define a temp-table NEW GLOBAL SHARED establishes it. Subsequent procedures access it using a DEFINE SHARED TEMP-TABLE statement.
Note: ABL does not establish multiple global shared temp-tables with the same name in the same ABL session.
Note: A SHARED temp-table cannot have a BEFORE-TABLE.
SHARED TEMP-TABLE temp-table-name
Defines and identifies a temp-table object that was initially defined by another procedure using a DEFINE NEW SHARED TEMP-TABLE or DEFINE NEW GLOBAL SHARED TEMP-TABLE statement.

The procedure that establishes the temp-table determines the name. The procedures that share the temp-table use that name to identify it.

[ PRIVATE | PROTECTED ][ STATIC ][ SERIALIZABLE | NON-SERIALIZABLE ] TEMP-TABLE temp-table-name
Defines and identifies a temp-table object as a data member of a class, and optionally specifies an access mode (PRIVATE or PROTECTED) and scope (instance or STATIC) for that data member, as well as whether or not the temp-table will participate in serialization (SERIALIZABLE or NON-SERIALIZABLE). You cannot specify any of these options for a temp-table in an interface definition (INTERFACE statement block) or when defining a temp-table as a data element of a procedure.
Note: The specified options are applicable only when defining a data member for a class in a class definition (.cls) file. The PRIVATE/PROTECTED modifier and the STATIC modifier can appear in either order, e.g., DEFINE STATIC PRIVATE TEMP-TABLE myTempTable... is valid syntax. Note also that you cannot shadow (override) the definition of a given temp-table data member in a class hierarchy.

A temp-table defined with the STATIC option is a static data member of the class type for which it is defined, and it is scoped to the ABL session where it is referenced. ABL creates one copy of the specified class static temp-table at the first reference to the class type, and creates only one such copy for any number of instances of the class that you create. You cannot specify STATIC if you specify the REFERENCE-ONLY option. You can directly reference an accessible static temp-table data member from any other static or instance class member defined in the same class or class hierarchy.

Without the STATIC option, ABL creates an instance temp-table data member that is scoped to a single instance of the class where it is defined. ABL creates one copy of the specified instance temp-table for each such class instance that you create. You cannot directly reference an instance temp-table data member from a STATIC class member definition defined within the same class or class hierarchy.

For more information on accessing temp-tables of different access modes and scopes, see the reference entry for Class-based data member access.

Class-based objects that are defined as serializable (using the SERIALIZABLE option in the CLASS statement) can be passed as parameters in remotes call between the AppServer and ABL clients and can be serialized to binary or JSON format. By default, both passing a class as a parameter and serializing an object to binary via the Progress.IO.BinarySerializer class include all data members regardless of access mode. However, for JSON serialization via Progress.IO.JsonSerializer, only public data members are serialized. To include a protected or private property during JSON serialization, SERIALIZABLE must be added to the definition. See Serialize( ) method (JsonSerializer) for more information.

Use the NON-SERIALIZABLE option to exclude a given temp-table from parameter passing between an AppServer and ABL client and from the serialization process via the Progress.IO.BinarySerializer class. (Temp-tables are NON-SERIALIZABLE by default via the Progress.IO.JsonSerializer class because they cannot be defined as public.) Fields of a temp-table marked as NON-SERIALIZABLE revert to their initial values when the class is deserialized.

Note: Members of a class are grouped into six namespaces, including buffers/temp-tables, methods, variables/properties/events, ProDataSets, queries, and data-sources. Buffers and temp-tables defined as members of a class share the same namespace. There can be only one class member in this namespace with a given name.

For more information on where and how to define data members in a class, see the CLASS statement reference entry.

TEMP-TABLE temp-table-name
Defines and identifies a temp-table object in an interface, or for access only within the current external procedure or as a data member of the current class.
NO-UNDO
Specifies that when a transaction is undone, changes to the temp-table records need not be undone. If you do not specify this option, all records in the temp-table are restored to their prior condition when a transaction is undone. The NO-UNDO option can significantly increase the performance for temp-table updates; use it whenever possible.
NAMESPACE-URI namespace
An optional CHARACTER constant that specifies the URI for the namespace of the temp-table.
NAMESPACE-PREFIX prefix
An optional CHARACTER constant that specifies the namespace prefix associated with the NAMESPACE-URI.
XML-NODE-NAME node-name
An optional CHARACTER constant that specifies the name of the XML element representing the temp-table in an XML Document. The default is temp-table-name. This option is useful when:
  • reading a temp-table from XML with a name that contains invalid characters for an ABL name or is an ABL keyword, or
  • writing a temp-table to XML with a name that is an invalid XML tag name.
Note: If you set SERIALIZE-NAME but do not set XML-NODE-NAME, the AVM sets XML-NODE-NAME equal to SERIALIZE-NAME.
SERIALIZE-NAME serialize-name
An optional CHARACTER constant that specifies the name of the element representing the temp-table in an XML Document or JSON string. The default is dataset-name. This option is useful when:
  • reading a temp-table from XML or JSON with a name that contains invalid characters for an ABL name or is an ABL keyword, or
  • writing a temp-table to XML or JSON with a name that is an invalid XML tag name or an invalid JSON property name.
Note: If you set both XML-NODE-NAME and SERIALIZE-NAME, the READ-XML() and WRITE-XML() methods will use XML-NODE-NAME.
REFERENCE-ONLY
Specifies that the procedure defining this temp-table object is using the object definition only as a reference to a temp-table object that is defined and instantiated in another procedure or class, and specified as a parameter in the invocation of a RUN statement, a method in a class, or a user-defined function, using either the BY-REFERENCE or BIND option. The AVM does not instantiate the reference-only object. You cannot specify REFERENCE-ONLY if you specify the STATIC option.

Passing a reference-only temp-table object parameter to a local routine using either the BY-REFERENCE or BIND option allows the calling routine and the called routine to access the same object instance (instead of deep-copying the parameter).

Note: If you pass the parameter to a remote procedure, the AVM deep-copies the parameter on OUTPUT and the reference-only parameter is bound to that copy.

When you pass a temp-table parameter to a local routine using the BY-REFERENCE option, both the calling and called routines access the calling routine's object instance (and ignore the called routine's object instance). Since the called routine's object instance is ignored, you should define the object as a reference-only object. When you define a reference-only temp-table object in the called routine and receive it from the calling routine using the BY-REFERENCE option, the AVM binds the definition of the object in the called routine to the object instance in the calling routine for the duration of the called routine. You cannot define a reference-only temp-table object in the calling routine and pass it to the called routine using the BY-REFERENCE option.

When you pass a temp-table parameter to a local routine using the BIND option, you can define a reference-only temp-table object in either the calling routine or the called routine as follows:

  • When you define a reference-only temp-table object in the calling routine and pass it to the called routine using the BIND option, the AVM binds the calling routine to the object instance in the called routine. The reference-only object definition remains bound to the object instance until the routine containing the reference-only object definition is deleted or terminates. The parameter must be an OUTPUT parameter.
    Note: If you also define the temp-table object instance in the called routine as a reference-only object, you must bind the object in the called routine before returning to the calling routine.
  • When you define a reference-only temp-table object in the called routine and receive it from the calling routine using the BIND option, the AVM binds the called routine to the object instance in the calling routine. The reference-only object definition remains bound to the object instance until the routine containing the reference-only object definition is deleted or terminates. The parameter must be an INPUT or INPUT-OUTPUT parameter.

In either case, you must specify the BIND option for the parameter in both the invocation of a RUN statement, a method in a class, or a user-defined function, and in the DEFINE PARAMETER statement.

Caution:
Do not delete the object or routine to which a reference-only temp-table object is bound, or you might be left with references to an object that no longer exists.

A reference-only temp-table object can be a member of a reference-only ProDataSet object or a standard ProDataSet object. However, if you define a reference-only temp-table in a standard ProDataSet object, you cannot use the ProDataSet object until you bind the reference-only temp-table.

LIKE table-name[ USE-INDEX index-name  [ AS PRIMARY ]]...
Specifies the name of a table whose characteristics the temp-table inherits. All field definitions of table-name are added to the temp-table. table-name can represent a database table or another temp-table.
Note: The source (temp-table or database table) for table-name can have any access mode or scope as long as its definition is accessible to the current temp-table definition.

If you reference a database field, the database containing that field must be connected at compile time. If the database field has a validation expression defined in the dictionary that contains a database reference, and the VALIDATE option is specified, the database must also be connected at run-time.

HELP options are inherited from the table-name. Validate options are inherited only if the VALIDATE keyword is used.

Some index definitions from the specified table might also be added to the temp-table:

  • If you use the USE-INDEX option, only the definitions of indexes you specify with that option are copied to the temp-table. If one of these indexes is the primary index of the LIKE table, it becomes the default primary index of the temp-table. You can, however, use the AS PRIMARY option to override this default primary index.

    For example, to make the index country-post the primary index (thereby, overriding the default primary index CustNum in the table Customer), you specify it as follows:

    DEFINE TEMP-TABLE mycust LIKE Customer
      USE-INDEX CustNum USE-INDEX country-post AS PRIMARY.
  • If you do not specify the USE-INDEX option and do not use the INDEX option of the DEFINE TEMP-TABLE statement, then all index definitions are copied from the specified table to the temp-table. In this case, the primary index of the specified table becomes the primary index of the temp-table.
  • If you do not specify the USE-INDEX option but do use the INDEX option of the DEFINE TEMP-TABLE statement, then no indexes are copied from the specified table unless the specified table is another temp-table.
  • The AVM does not copy inactive indexes to the temp-table.
  • If the source database table contains inactive indexes, then you must specify one or both of the USE-INDEX and INDEX options. If you do not, a compile time error is generated.
LIKE-SEQUENTIAL table-name
[ USE-INDEX index-name [ AS PRIMARY ]]...
Specifies the name of a table whose characteristics the temp-table inherits. All field definitions of table-name are added to the temp-table. table-name can represent a database table or another temp-table.

LIKE-SEQUENTIAL is similar to LIKE in all ways except one. Unlike LIKE, which creates temp-table fields in _field._field-rpos order (POSITION order in the .df schema definition file) of the source table's fields, LIKE-SEQUENTIAL creates fields in _field._order sequence.

You can guarantee agreement of temp-table field order between any client and any AppServer using LIKE-SEQUENTIAL, as long as the _field._order values are the same. LIKE-SEQUENTIAL uses the field order as defined in the Data Dictionary when the source is a database table.

Note: The original behavior of LIKE was used to support RAW-TRANSFER with temp-tables. If you are using RAW-TRANSFER between a database table and a temp-table defined LIKE the database table, then you should not use LIKE-SEQUENTIAL.
VALIDATE
The temp-table fields inherit, from the dictionary, validation expressions and validation messages from the database table, table-name.
RCODE-INFORMATION
This option is supported only for backward compatibility.
BEFORE-TABLE before-table-name
Specifies the name of the before-image table associated with a compile-time defined temp-table in a ProDataSet object. You must specify a before-image table name for any compile-time defined ProDataSet temp-table for which you want to track changes. If you try to modify the records in this before-image table, the AVM generates a run-time error. You cannot use this option on a SHARED temp-table.
FIELD field-name
Defines a field in the temp-table. You can use FIELD clauses with the LIKE option to define additional fields for the temp-table, or you can define all your fields with FIELD clauses.
AS data-type
Specifies the data type of the field. The valid data types are BLOB, CHARACTER, CLASS, CLOB, COM-HANDLE, DATE, DATETIME, DATETIME-TZ, DECIMAL, HANDLE, INT64, INTEGER, LOGICAL, RAW, RECID, and ROWID.

For more information on these data types, see the Data types reference entry.

For the CLASS data type, you define a field in a temp-table as a class by specifying the built-in Progress.Lang.Object class name. For example:

DEFINE TEMP-TABLE ttObjHolder FIELD MyObj AS CLASS Progress.Lang.Object.

When you assign a class instance to a field, ABL implicitly casts the instance to its root super class, which is the Progress.Lang.Object class. After the assignment, the field contains an object reference to the class instance, not the object itself.

You cannot define a field in a database table as a class.

LIKE field
Specifies a database field or a variable whose characteristics the temp-table field inherits. If you name a variable with this option, that variable must have been defined earlier in the procedure. The temp-table field inherits the data type, extents, format, initial value, label, and column label.

If the database field is a COLUMN-CODEPAGE CLOB, the temp-table field is in the database field's code page. If the database field is a DBCODEPAGE CLOB, the temp-table field's code page is -cpinternal.

You can override selected characteristics of the field or variable with the field-options parameter.

If you reference a database field in the LIKE option, the database containing that field must be connected at both compile time and run time. Therefore, use the LIKE option with caution.

field-options
Specifies options for the temp-table field. Any options you specify override any options inherited through the LIKE option. This is the syntax for field-options:
{
   [ BGCOLOR expression]
   [ COLUMN-LABEL label]
   [ DCOLOR expression]
   [ DECIMALS n]
   [ EXTENT n]
   [ FONT expression]
   [ FGCOLOR expression]
   [ FORMAT string] 
   [ HELP help-text]
   [ INITIAL
       {constant|{ [ constant[ , constant]... ] }}
   ]
   [ LABEL label[ , label]...]
   [ MOUSE-POINTER expression]
   [[ NOT ] CASE-SENSITIVE ]
   [ PFCOLOR expression]
   [ SERIALIZE-HIDDEN ]
   [ SERIALIZE-NAME serialize-name ]
   [ TTCODEPAGE | COLUMN-CODEPAGE codepage]
   [ XML-DATA-TYPE string]
   [ XML-NODE-TYPE string]
   [ XML-NODE-NAME node-name]
   {[view-as-phrase]}
}
HELP help-text
A quoted CHARACTER string that represents the help text.
SERIALIZE-HIDDEN
Indicates that this field is not written when the temp-table is serialized, for example into JSON or XML. This option also applies for serialization via the Progress.IO.JsonSerializer class.
Note: If you also specify XML-NODE-TYPE, the WRITE-XML( ) method uses the value of XML-NODE-TYPE and ignores this option.
SERIALIZE-NAME serialize-name
An optional CHARACTER constant that specifies the name of the temp-table field as it should appear when serialized, for example into JSON or XML. The default is temp-table field-name. Use this option when the serialized name either contains invalid characters for an ABL name or the serialized name is an ABL keyword.
Note: If you also specify XML-NODE-NAME, the READ-XML( ) and WRITE-XML( ) methods use the value of XML-NODE-NAME and ignore this option.
TTCODEPAGE | COLUMN-CODEPAGE codepage
Specifies the code page for a CLOB field in the temp-table. If you specify TTCODEPAGE, the code page is -cpinternal. If you specify COLUMN-CODEPAGE, codepage must be a valid code page name available in the DLC/convmap.cp file. You cannot specify the "undefined" code page for a CLOB. The code page you specify overrides any code page inherited through the LIKE option.

If you do not specify a code page for a CLOB field in the temp-table, the default code page is -cpinternal.

XML-DATA-TYPE string
An optional CHARACTER constant that specifies the XML Schema data type for the field in the temp-table. The XML Schema data type must be compatible with the ABL data type for the field.

For more information about the ABL XML data type mapping rules, see OpenEdge Development: Working with XML.

XML-NODE-TYPE string
An optional CHARACTER constant that specifies the XML node type of the temp-table field, which lets you specify how the field is represented in XML. Valid option values are: "ATTRIBUTE", "ELEMENT", "HIDDEN", and "TEXT". The default value is "ELEMENT".

The following table lists the valid XML node types.

XML node types
When the XML node type is . . . The buffer field is . . .
ATTRIBUTE Represented as an attribute of the temp-table element in both the XML Schema and data.
ELEMENT Represented as a child element of the temp-table element in both the XML schema and data.
HIDDEN Omitted from both the XML Schema and data.
TEXT Represented as a text element in both the XML Schema and data.
Note: Each table can contain only one TEXT field. When a table contains a TEXT field, it cannot contain ELEMENT fields; it can contain only ATTRIBUTE fields. A table that contains a TEXT field cannot be part of a nested data-relation.

The XML node type of a temp-table field that represents an array must be either "ELEMENT" or "HIDDEN".

Note: If you specify SERIALIZE-HIDDEN but do not set XML-NODE-TYPE, the AVM sets XML-NODE-TYPE to "HIDDEN".
XML-NODE-NAME node-name
An optional CHARACTER constant that specifies the name of the XML element or XML attribute representing the temp-table field in an XML Document. The default is the temp-table field-name.
Note: If you set SERIALIZE-NAME but do not set XML-NODE-NAME, the AVM sets XML-NODE-NAME equal to SERIALIZE-NAME.
Note: You cannot specify an indeterminate array field in a temp-table using the EXTENT field option.

For more information and a description of all other field options, see the DEFINE VARIABLE statement.

INDEX index-name
[[ AS | IS ][ UNIQUE ][ PRIMARY ][ WORD-INDEX ]]
Defines an index on the temp-table. To define a unique index, specify the UNIQUE option. To define the primary index, specify the PRIMARY option. To define a word-index, specify the WORD-INDEX option.

If you define more than one index on the temp-table, you can specify PRIMARY for none or one of the indexes. If you specify PRIMARY for none of the indexes, the AVM makes the first index you specify the primary index.

If you define no indexes on the temp-table, and the temp-table does not inherit the indexes of another table through the LIKE option of the DEFINE TEMP-TABLE statement, the AVM creates a default index, makes it the primary index, and sorts the records in entry order. If you do not specify the USE-INDEX option under LIKE but do use the INDEX option, then no indexes are copied from the specified table unless the specified table is another temp-table.

index-field [ ASCENDING | DESCENDING ]
Specifies a temp-table field to use as a component of the index. You can use the ASCENDING or DESCENDING option to specify that the component has ascending or descending order.

If you do not specify a sort orientation (ASCENDING or DESCENDING), the index component gets the sort orientation of the previous index component, or, if there is no previous index component, ASCENDING. This rule applies only to index components of temp-tables.

Note: You cannot use a BLOB or CLOB field as a component of an index.

For example, the following two temp-table definitions are equivalent:

DEFINE TEMP-TABLE foo NO-UNDO
  FIELD a AS CHARACTER
  FIELD b AS CHARACTER
  FIELD c AS CHARACTER
  INDEX x a DESC b DESC c DESC.
DEFINE TEMP-TABLE foo NO-UNDO
  FIELD a AS CHARACTER
  FIELD b AS CHARACTER
  FIELD c AS CHARACTER
  INDEX x a DESC b c.

The following two temp-table definitions are also equivalent:

DEFINE TEMP-TABLE foo NO-UNDO
  FIELD a AS CHARACTER
  FIELD b AS CHARACTER
  FIELD c AS CHARACTER
  INDEX x a ASC b DESC c DESC.
DEFINE TEMP-TABLE foo NO-UNDO
  FIELD a AS CHARACTER
  FIELD b AS CHARACTER
  FIELD c AS CHARACTER
  INDEX x a ASC b DESC c.

Example

The following procedure creates a temp-table (temp-item) that stores the total inventory value (Item.Price * Item.OnHand) for each catalog page (Item.CatPage) in the sports2000 database. It builds temp-item with two indexes-one that sorts the table in ascending order by catalog page and a second that sorts the table in descending order by inventory value.

After building temp-item, the procedure displays a dialog box that prompts for report parameters. These parameters include the cutoff value of catalog page inventory to report, and whether to display the report by catalog page (ascending) or inventory value (descending). After displaying the report, the procedure displays another dialog box to repeat the process. The process is repeated until you press the CANCEL button. This procedure shows how you can use a temp-table to store a calculated result from the database, and efficiently report the same result according to different sorting and selection criteria:

r-tmptb1.p

DEFINE TEMP-TABLE temp-item
  FIELD cat-page  LIKE Item.CatPage
  FIELD inventory LIKE Item.Price LABEL "Inventory Value"
  INDEX cat-page  IS PRIMARY cat-page ASCENDING
  INDEX inventory-value inventory DESCENDING.
DEFINE VARIABLE cutoff      NO-UNDO LIKE item.price.
DEFINE VARIABLE inv-value   NO-UNDO LIKE item.price.
DEFINE VARIABLE report-type AS INTEGER NO-UNDO INITIAL 1.

DEFINE BUTTON ok-butt     LABEL "OK" AUTO-GO.
DEFINE BUTTON cancel-butt LABEL "CANCEL" AUTO-ENDKEY.
FORM
  cutoff LABEL "Inventory Lower Cutoff for each Catalog Page"
    AT ROW 1.25 COLUMN 2
  report-type LABEL "Report Sorted ..." AT ROW 2.25 COLUMN 2
    VIEW-AS RADIO-SET RADIO-BUTTONS
      "By Catalog Page",   1,
      "By Inventory Value", 2 SKIP
  ok-butt cancel-butt
  WITH FRAME select-frame SIDE-LABELS WIDTH 70
  TITLE "Specify Report ..." VIEW-AS DIALOG-BOX.

FOR EACH Item BREAK BY Item.CatPage:
  ACCUMULATE Item.Price * Item.OnHand (SUB-TOTAL BY Item.CatPage).
  IF LAST-OF(Item.CatPage) THEN DO:
    inv-value = ACCUM SUB-TOTAL BY Item.CatPage (Item.Price * Item.OnHand).
    CREATE temp-item.
    temp-item.cat-page = Item.CatPage.
    inventory = inv-value.
  END.
END. /* FOR EACH item */
ON CHOOSE OF ok-butt DO:
  HIDE FRAME select-frame.
  IF report-type = 1 THEN
    FOR EACH temp-item USE-INDEX cat-page WITH FRAME rpt1-frame:
      IF inventory >= cutoff THEN
        DISPLAY temp-item.cat-page inventory.
    END.
  ELSE
    FOR EACH temp-item USE-INDEX inventory-value WITH FRAME rpt2-frame:
      IF inventory >= cutoff THEN
        DISPLAY temp-item.cat-page inventory.
    END.
    VIEW FRAME select-frame.
END.

ENABLE ALL WITH FRAME select-frame.
WAIT-FOR CHOOSE OF cancel-butt OR WINDOW-CLOSE OF CURRENT-WINDOW.

For examples of instance and static temp-table data member definitions, see the descriptions of r-CustObj.cls, r-CustObjStatic.cls, and r-CustObjAbstract.cls in the CLASS statement reference entry.

Notes

See also

Class-based data member access, CREATE-LIKE( ) method, CREATE-LIKE-SEQUENTIAL( ) method, CREATE TEMP-TABLE statement, DEFINE DATASET statement, DEFINE WORK-TABLE statement, NUM-REFERENCES attribute, RUN statement