Switching the Buffer sequence on a DATA-SOURCE in a DataAccess class

It’s not an uncommon requirement to add additional buffers to the DATA-SOURCE query or switch the buffer sequence of buffers in the DATA-SOURCE QUERY. This might be required when foreign key description have been implemented using calculated fields (e.g. SalesRep.RepName in a Customer Business Entity) and users have the ability to optionally filter on the SalesRep.RepName field.

When this is only required for lookups on the Customer table, the use of lookup business entities is a simple solution: Business Entities that disallow any updates to the data (the SaveChanges method throws a not supported exception and the temp-table is not defined with a BEFORE-TABLE). The DataAccess object of such a business entity might be defined using the two buffers: Customer and SalesRep. While this will provide filtering capabilities on the SalesRep.RepName and Customer fields this QUERY cannot be well performing in any case as the QUERY is not capable of switching the buffer sequence when filtering on some fields only.

For maintenance screens the story is a bit more complicated as the same Business Entity is used for read and update operations.

The article Filtering on calculated fields describes the usage of the QueryParser to determine what fields the user is filtering (or sorting) on.

Developers must realize how the ProDataset or better the DATA-SOURCE widgets work: They always work on a QUERY and this is the QUERY they can deal with selection criteria for. The QUERY cannot do anything with selection criteria from other BUFFERs. This is standard pre-ProDataset ABL behavior.

You can define a DATA-SOURCE with a single buffer – or for a QUERY. When you define it for a buffer the DATA-SOURCE builds a QUERY for that single BUFFER. So the DATA-SOURCE always works on a QUERY.

When a second (or third, or …) DB table is dragged on an existing temp-table in the Business Entity Designer, the tool prompts you if you want to add a second source-table to the temp-table.

When replied with yes two things will happen:

• The DATA-SOURCE used for this table will be created with a QUERY, not for a single buffer
• The fields of the second DB table will be added to the temp-table as they are now accessible (mappable). Extra fields not needed in the temp-table can (must) be removed to reduce the size of the temp-table in memory, the DBI file and on the network

When adding a second (or third, …) database table as a source table to the ProDataset in the Business Entity Design a few things should be considered:

• The query with multiple buffers would always be the default DATA-SOURCE QUERY for a temp-table. The Buffer-Sequence in this QUERY cannot be changed which might also lead to terribly ineffective queries
• During add and delete and update operations by default our DataAccess:SaveRowChanges method will add, create or update the first DATA-SOURCE Buffer of the temp-table. When filtering on for instance a description value (SalesRep.RepName) in a DATA-SOURCE for a eCustomer temp-table on SalesRep, Customer is used the selection performance would be high, but when the UI would create or delete an eCustomer record, by default a SalesRep record would be created or deleted. This would be counterproductive. So always choosing the table to be updated as the first DATA-SOURCE table may simplify the code, but typically causes poor QUERY performance.

This leads to the conclusion that in many cases a single DataAccess class should be using multiple different queries for accessing data – especially for read.

Unfortunately Progress has made this a bit difficult by the fact that in the ABL a single BUFFER can only be member of a single DATA-SOURCE or DATA-SOURCE QUERY at a time. So this is one of the few situations where dynamic DATA-SOURCE widgets with dynamic queries and buffers have advantages. Especially as it’s preferred to be able to use the default buffer Customer in the business entity attached to the standard static DATA-SOURCE for use in the DataAccess validation hook methods. Which is why typically we recommend to keep the simple static DATA-SOURCE (often defined against a buffer) to be used for update operations.

In the DataAccess:FetchData override the QueryParser can be used as demonstrated in the article Filtering on calculated fields to find out which temp-table fields the UI is filtering on. When some of those fields are calculated fields used for description values of foreign fields then define a dynamic QUERY and create the necessary dynamic buffers and add them to it (e.g. SalesRep and Customer). Then create a dynamic DATA-SOURCE and assign the QUERY to it. The handle of the DATA-SOURCE should be a class wide variable (an exception as we generally do not recommend the use of class with variables) for later use. This method allows you to use either the standard QUERY only on Customer, one of SalesRep, Customer, one on SalesRep, Customer, County or one on Country, Customer etc…. The SUPER:FetchData call will process further and call back into the AttachDataSources, DetachDataSources and SourceDefaultQuery methods.

At the end of the FetchData method it’s advised to use a FINALLY block to delete all dynamically created handle based objects. The class wide handle variable to the dynamic DATA-SOURCE should further be assigned to ?.

In the DataAccess:AttachDataSources method you can test if THIS-OBJECT:RequestType is FetchData and if the DATA-SOURCE handle is valid (previous paragraph). In that case, you attach the dynamically created DATA-SOURCE to the temp-table, not the static one.

In the DataAccess:DetachDataSources you’ll have to detach the DATA-SOURCE from the temp-table.

In the DataAccess:SourceColumn use the appropriate DATA-SOURCE (that static one or the dynamic one). As the fields from the additional DATA-SOURCE buffers are typically not mapped to temp-table fields, cusotm code is required to return the appropriate database table field name for a temp-table field (e.g. a calculated field that contains a description text) that may occur in the query string or query sort. This article provides a simple example of this: Filtering in a Data Access class on a table that is not part of the (default) data-source.

In the DataAccess:SourceDefaultQuery you’ll have to return the default query that fits for the current use case. Similar to what you need to do in AttachDataSources you’ll have to consider the current case. In many cases you can return the standard query string generated by the Business Entity Designer. But when the dynamic data-source handle is valid and it’s a read operation you may return “FOR EACH SalesRep, EACH Customer OF SalesRep”.

The SourceDefaultQuery does not need to incorporate the filter values that the consumer has provided. As those values are added to the query string by the DataSource’s query processing engine.

The article Filtering in a Data Access class on a table that is not part of the (default) data-source provides a code sample.