Using the Business Entity method FetchDataByKeyTable

Introduction

FetchDataByKeyTable is an internal method within the Smart Component Library Business Entity, that provides the user with a mechanism to retrieve multiple specific Database records based on a temp table with the values of the fields composing a (unique) key.

One purpose of the FetchDataByKeyTable method is to remove the unnecessary building of a large Query String composed by OR'ing a large list of key values that potentially could exceed the 32K size limit.

 

FetchDataByKeyTable

The method FetchDataByKeyTable is implemented as an Invokable Method and requires the two usual parameters Dataset Handle and a  parameter object that implements the IFetchDataByKeyTableParameter interface.

The IFetchDataByKeyTableParameter Object

 The IFetchDataByKeyTableParameter parameter object contains two properties allowing PUBLIC GET access.

Property Name
Description
TablesThis property is used to return a comma delimited list of Tables
KeyValueTableHandleThis property is used to return the handle of the temp-table containing the key values


The FetchDataByKeyTable Method expects an object that exposes a temp-table containing a single record per key-value combination. Internally, the Default Query for your Business Entity subsequently performs a Join using the ‘Object’ to return only the records matching those specified within the temp-table.

The Dataset Handle

The Dataset Handle should be a reference to the returned Dataset containing the results.


Example Usage

Outlined below is an example on how to implement the FetchDataByKeyTable Method. The example shall be based on a OrderLine Business Entity.

First, we shall need to create an appropriate Parameter Object Class relevant to the Business Entity in hand.

 

Sample FetchOrderLineParameter Implementation
CLASS /classpath/.FetchOrderLineParameter
      IMPLEMENTS IFetchDataByKeyTableParameter:

      DEFINE TEMP-TABLE ttOrderLineFilter NO-UNDO
          FIELD OrderNum AS INTEGER
          FIELD LineNum AS INTEGER .
      /*------------------------------------------------------------------------------
           Purpose:
           Notes:
      ------------------------------------------------------------------------------*/
      DEFINE PUBLIC PROPERTY KeyValueTableHandle AS HANDLE NO-UNDO
      GET:
            RETURN TEMP-TABLE ttOrderLineFilter:HANDLE .
      END GET.
      /*------------------------------------------------------------------------------
           Purpose:
           Notes:
      ------------------------------------------------------------------------------*/
      DEFINE PUBLIC PROPERTY Tables AS CHARACTER NO-UNDO
      GET.
      SET.
     /*------------------------------------------------------------------------------
          Purpose: Clears the filter values
          Notes:
     ------------------------------------------------------------------------------*/
    METHOD PUBLIC VOID ClearEntries ():
          EMPTY TEMP-TABLE ttOrderLineFilter .
    END METHOD.
    /*------------------------------------------------------------------------------
          Purpose: Creates a record in the filter values temp-table
          Notes:
     ------------------------------------------------------------------------------*/
    METHOD PUBLIC VOID CreateOrderLineEntry (piOrderNum AS INTEGER,
                                             piLineNum AS INTEGER):
         CREATE ttOrderLineFilter .
         ASSIGN ttOrderLineFilter.OrderNum = piOrderNum
                       ttOrderLineFilter.LineNum = piLineNum .
    END METHOD.
END CLASS.

 

With the Parameter Object defined, we can now populate the Parameter Object with the desired Key values and subsequently Invoke the FetchDataByKeyTable Method passing in the appropriate parameters.


Sample Invoke of the method FetchDataByTableKey
DEFINE VARIABLE oParameter AS FetchOrderLineParameter NO-UNDO .

oParameter = NEW FetchOrderLineParameter () .
oParameter:Tables = "eOrderLine,eItem" .
oParameter:CreateOrderLineEntry (1, 1) .
oParameter:CreateOrderLineEntry (1, 2) .
oParameter:CreateOrderLineEntry (1, 3) .
oParameter:CreateOrderLineEntry (2, 1) .
oParameter:CreateOrderLineEntry (2, 2) .
ServiceInterface:InvokeMethod (GET-CLASS (OrderLineBusinessEntity):TypeName,
 							   "FetchDataByKeyTable",
							   INPUT-OUTPUT DATASET dsOrderLine,
							   oParameter) .

 

As the Interface IFetchDataByKeyTableRequest extends the IFetchDataByKeyTableParameter interface, objects implementing this interface can be passed whenever an IFetchDataByKeyTableParameter object is supported as well, e.g.: 

Sample of FetchDataByKeyTableRequest in combination with a structured query for extra filtering
DEFINE VARIABLE oRequest  AS FetchDataByKeyTableRequest NO-UNDO .
DEFINE VARIABLE oQuery    AS CustomerQuery              NO-UNDO .
DEFINE VARIABLE oCustomer AS CustomerDatasetModel       NO-UNDO .

oQuery = NEW CustomerQuery () .
oQuery:SalesRep:Eq("HXM":U) .

oRequest = NEW FetchDataByKeyTableRequest (oQuery) .

FOR EACH Customer WHERE CustNum >= 20 AND CustNum <= 100:
    oRequest:AddCustomer(Customer.CustNum) .
END.

oCustomer = NEW CustomerDatasetModel () .
oCustomer:Customer:Fill (oRequest) .