Filtering in a Data Access class on a table that is not part of the (default) data-source
Use-case: Optionally filter an OrderBusinessEntity for the eOrder temp-table on an Item number referenced in an Orderline of the orders.
The client will use the normal filter options (e.g. Business Entity Adapter Query String or ListQueryExpressions in the CollectFilterValues event handler) to filter on the non existing field “eOrder.ItemNum”.
METHOD PROTECTED VOID CollectFilterValuesHandler (sender AS Progress.Lang.Object, e AS CollectFilterValuesEventArgs): e:QueryExpressions:GetItem("eOrder"):Add ("eOrder.ItemNum", OperatorEnum:EQ, 30) . END METHOD.
By the default, the query manipulation process in the Consultingwerk.OERA.DataAccess inherited by the OrderDataAccess class will ignore unknown fields in the query string received from the client (consumer). This allows to use the Query String to pass query selection criteria on fields that otherwise would cause errors in the data source query string.
In order to filter on the ItemNum of an OrderLine we need to dynamically put a different DATA-SOURCE and QUERY object handle in place. The one we will use, joins Order with OrderLine or OrderLine with Order – depending on which will be most optimal way of retrieving the records.
The following describes the additional code required in the OrderDataAccess class to support the change in the DATA-SOURCE and QUERY used.
An override to the FetchData method can be used to decide if the default or an alternative DATA-SOURCE and QUERY object handle will be used for the data retrieval. This can be achieved by utilising the QueryParser class which returns the query as a list of QueryExpression objects.
METHOD OVERRIDE PUBLIC VOID FetchData (poFetchDataRequest AS Consultingwerk.OERA.IFetchDataRequest): DEFINE VARIABLE oQueryParser AS QueryParser NO-UNDO . DEFINE VARIABLE oQueryExpression AS IQueryExpression NO-UNDO . DEFINE VARIABLE cQueryString AS CHARACTER NO-UNDO . /* Only try to filter by OrderLine.Item, when eOrder is the first requested table */ IF ENTRY (1, THIS-OBJECT:ExpandTables (poFetchDataRequest:Tables)) = "eOrder":U THEN DO: cQueryString = ENTRY (1, poFetchDataRequest:Queries, CHR(1)) . /* Parse QueryString of the request into a list of query predicates */ oQueryParser = NEW QueryParser () . oQueryExpression = oQueryParser:ParseQueryString (cQueryString) . /* Filter by eOrder.ItemNum? */ IF VALID-OBJECT (QueryExpression:FindQueryExpressionForBufferField (BufferHelper:ParseFieldName ("eOrder.ItemNum":U), oQueryExpression)) THEN ASSIGN cFilterCase = "OrderLine":U . END. SUPER:FetchData(INPUT poFetchDataRequest). FINALLY: ASSIGN cFilterCase = "":U . END FINALLY. END METHOD.
The variable cFilterCase will be used during the data retrieval helper methods in the data access class to decide that the custom query shall be used.
The variable cFilterCase defined among other supporting variables in the header of the class file:
/* Alternative data-source by Order and OrderLine */ DEFINE VARIABLE cFilterCase AS CHARACTER NO-UNDO INIT "":U. DEFINE VARIABLE hDynDataSourceQuery AS HANDLE NO-UNDO . DEFINE VARIABLE hDynDataSource AS HANDLE NO-UNDO . DEFINE VARIABLE hDynDataSourceBuffer AS HANDLE NO-UNDO EXTENT .
The method AttachDataSources is the central point to create the alternative DATA-SOURCE and QUERY object handles. A here challenge is that the default buffers for the source database tables can (by an ABL limitation) only be used in a single DATA-SOURCE at a time. This is why we create dynamic buffers for the new DATA-SOURCE. The cFilterCase condition is wrapped around the default AttachDataSource code which is now contained in the OTHERWISE path.
METHOD OVERRIDE PROTECTED VOID AttachDataSources (): Consultingwerk.Util.DatasetHelper:SetTrackingChanges (DATASET dsOrder:HANDLE, FALSE) . CASE cFilterCase: WHEN "OrderLine" THEN DO: EXTENT (hDynDataSourceBuffer) = ? . EXTENT (hDynDataSourceBuffer) = 2 . CREATE BUFFER hDynDataSourceBuffer[1] FOR TABLE "Order" . CREATE BUFFER hDynDataSourceBuffer[2] FOR TABLE "OrderLine" . CREATE QUERY hDynDataSourceQuery . hDynDataSourceQuery:ADD-BUFFER (hDynDataSourceBuffer[1]) . hDynDataSourceQuery:ADD-BUFFER (hDynDataSourceBuffer[2]) . CREATE DATA-SOURCE hDynDataSource . hDynDataSource:QUERY = hDynDataSourceQuery . THIS-OBJECT:AttachDataSource (BUFFER eOrder:HANDLE, hDynDataSource, "Ordernum,Order.Ordernum,CustNum,Order.CustNum,OrderDate,Order.OrderDate,ShipDate,Order.ShipDate,PromiseDate,Order.PromiseDate, Carrier,Order.Carrier,Instructions,Order.Instructions,PO,Order.PO,Terms,Order.Terms,SalesRep,Order.SalesRep,BillToID,Order.BillToID,ShipToID,Order.ShipToID, OrderStatus,Order.OrderStatus,WarehouseNum,Order.WarehouseNum,Creditcard,Order.Creditcard":U) . END. OTHERWISE DO: @AttachDataSourcesStart. THIS-OBJECT:AttachDataSource (BUFFER eOrder:HANDLE, DATA-SOURCE src_Order:HANDLE, "Ordernum,Order.Ordernum,CustNum,Order.CustNum,OrderDate,Order.OrderDate,ShipDate,Order.ShipDate,PromiseDate,Order.PromiseDate, Carrier,Order.Carrier,Instructions,Order.Instructions,PO,Order.PO,Terms,Order.Terms,SalesRep,Order.SalesRep,BillToID,Order.BillToID,ShipToID,Order.ShipToID, OrderStatus,Order.OrderStatus,WarehouseNum,Order.WarehouseNum,Creditcard,Order.Creditcard":U) . @AttachDataSourcesEnd. END. END CASE . END METHOD.
The method DetachDataSources is used to delete the dynamically created DATA-SOURCE object after the ProDataset has been filled:
METHOD OVERRIDE PROTECTED VOID DetachDataSources (): DEFINE VARIABLE i AS INTEGER NO-UNDO. Consultingwerk.Util.DatasetHelper:SetTrackingChanges (DATASET dsOrder:HANDLE, FALSE) . @DetachDataSourcesStart. BUFFER eOrder:DETACH-DATA-SOURCE () . @DetachDataSourcesEnd. FINALLY: /* Delete dynamic DATA-SOURCE and supporting object handles */ IF cFilterCase > "":U THEN DO: GarbageCollectorHelper:DeleteObject(hDynDataSource) . GarbageCollectorHelper:DeleteObject(hDynDataSourceQuery) . DO i = 1 TO EXTENT (hDynDataSourceBuffer): GarbageCollectorHelper:DeleteObject(hDynDataSourceBuffer[i]) . END. EXTENT (hDynDataSourceBuffer) = ? . END. END FINALLY. END METHOD.
The SourceColumn method of the OrderDataAccess class is extended so that it returns the database source column OrderLine.ItemNum when the client side QueryString did filter for the pseudo eOrder.ItemNum field:
METHOD OVERRIDE PUBLIC CHARACTER SourceColumn (pcTable AS CHARACTER, pcColumn AS CHARACTER): DEFINE VARIABLE cMap AS CHARACTER NO-UNDO. DEFINE VARIABLE iPos AS INTEGER NO-UNDO . IF pcTable = "eOrder" AND pcColumn = "eOrder.ItemNum" THEN RETURN "OrderLine.ItemNum" . @SourceColumnCaseBlock. CASE pcTable: WHEN "eOrder":U THEN cMap = BUFFER eOrder:DATA-SOURCE-COMPLETE-MAP . END. ASSIGN iPos = LOOKUP(pcColumn, cMap) . IF iPos MODULO 2 = 1 THEN RETURN ENTRY(LOOKUP(pcColumn,cMap) + 1,cMap). ELSE RETURN "":U . END METHOD.
And the method SourceDefaultQuery is enhanced, so that it returns an appropriate data-source query string in the case that a query on Order joined with OrderLine is used:
METHOD OVERRIDE PUBLIC CHARACTER SourceDefaultQuery (pcTable AS CHARACTER): IF cFilterCase = "OrderLine" THEN RETURN "FOR EACH Order, FIRST OrderLine WHERE OrderLine.OrderNum = Order.OrderNum use-index OrderLine INDEXED-REPOSITION":U . @SourceDefaultQueryCaseBlock. CASE pcTable: WHEN "eOrder":U THEN RETURN "FOR EACH Order INDEXED-REPOSITION":U. END. END METHOD.
The complete OrderDataAccess.cls source code is attached.
See also Switching the Buffer sequence on a DATA-SOURCE in a DataAccess class and Filtering on calculated fields.