Consultor Eletrônico

Status: Verified

GOAL:

ADM2. How to create and handle dynamic temp-tables.

FACT(s) (Environment):

Progress 9.1X

CAUSE:

Beginning with Version 9.1A, the Progress 4GL includes Dynamic temp-tables as a new feature. Using dynamic temp-tables to pass table records between the Client and Server side via the AppServer, you can reduce the .r file size on the Server machine.

FIX:

There are several methods and properties to create and handle dynamic temp-tables.

There are also different ways to create the dynamic temp-table definitions.

What follows is an explanation of the most used method and the different ways to create the dynamic temp-table definitions.

-- Steps to Create a Dynamic Temp-Table:

1) Define a variable as handle.

2) Create the dynamic temp-table using the handle variable.

3) Create the dynamic temp-table definitions.

4) Prepare the temp-table to be used.

5) Get the buffer handle of the dynamic temp-table.

6) Delete dynamic temp-table object.

Steps 1 and 2 are the standards steps to create any dynamic objects using the CREATE statement, as shown here:

DEFINE VARIABLE hTempTable AS HANDLE NO-UNDO.
CREATE TEMP-TABLE hTempTable.

There are several methods you can use to create the temp-table definition (Step 3 above).

a) Using CREATE-LIKE method -- this method creates the dynamic temp-table definitions from a database table.

CREATE-LIKE method receives two parameters, the first parameter is the table name reference and the second parameter is a list of indexes to be added in the dynamic temp-table definition.

CREATE-LIKE method using a table *name* as parameter.

hTempTable:CREATE-LIKE("customer")

or

DEFINE VARIABLE cTable AS CHARACTER NO-UNDO.
ASSIGN cTable = "customer".
htempTable:CREATE-LIKE(cTable).


CREATE-LIKE method using a table *handle* as parameter.

/*Just the 'name' index will be added to the dynamic temp-table definition */
hTempTable:CREATE-LIKE(BUFFER customer:HANDLE, "name").


b) Using ADD-FIELDS-FROM method.

ADD-FIELDS-FROM method copies the field definitions to the dynamic temp-table from the name or handle of the table specified as first parameter.

The first parameter is used as in the CREATE-LIKE method; it could be a table name or table handle.

The second parameter is used to exclude fields from the source table.

Is very important to use the ADD-FIELDS-FROM if some of the table
fields are needed in order to improve performance.

c) Using ADD-NEW-FIELD and ADD-NEW-INDEX methods.

ADD-NEW-FIELD and ADD-NEW-INDEX methods are used to create custom fields and indexes to the dynamic temp-table definition.

Here is an example of how to define a dynamic temp-table using customs fields and index.

DEFINE VARIABLE hTempTable AS HANDLE NO-UNDO.

CREATE TEMP-TABLE hTempTable.

hTempTable:ADD-NEW-FIELD(
'name' /*field name*/,
'CHAR' /*field type*/,
?, /*extents*/,
'x(10)' /*field format*/
? /*Initial value (optional)*/,
'Name' /*Label (optional)*/,
'Name' /*Column label (optional)*/).

hTempTable:ADD-NEW-INDEX(
'idxName' /*index name*/,).
False /*Unique (optional)*/,
False /*Primary (optional)*/,
True /*Word-Index (optional)*/).

hTempTable:Add-INDEX-FIELD(
'idxName' /*index name*/,
'name' /*field name*/,
'desc' /*Mode (optional)*/).

After creating the dynamic temp-table definitions, it should be prepared using the TEMP-TABLE-PREPARE method (step 4 above). This method receives one parameter, which is the name of the temp-table.

hTempTable:TEMP-TABLE-PREPARE('myDynTempTable').

In order to create, delete or update records in the dynamic temp-table, the dynamic temp-table buffer handle must be captured (step 5 above). The DEFAULT-BUFFER-HANDLE property retrieves the temp-table buffer.

For an example of how to use dynamic temp-tables, see Progress Solution 21396, "ADM2: How To Pass Dynamic Temp-tables Between Processes".