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 |
---|---|
Tables | This property is used to return a comma delimited list of Tables |
KeyValueTableHandle | This 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.
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.
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.:
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) .