Consultor Eletrônico

Status: Unverified

GOAL:

How to create and handle dynamic temp-tables?

FACT(s) (Environment):

Progress 9.1X
OpenEdge 10

FIX:

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.

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..