Coping with mandatory filtering on Exact Online

Wiki (en)
,
1

The following statement is an incremental update, switching all registered accounts to receive both a PDF as well as XML invoice:

update /*+ http_memory_cache(false) http_disk_cache(false) */
       ExactOnlineREST..Accounts@eol
set    InvoiceAttachmentType = 3
where  InvoiceAttachmentType != 3
limit 1000

The typical runtime is less than a second, since due to the incremental behaviour only few records will be selected. The typical API consumption is one API-call to retrieve the records and one API-call for each record to be updated.

When mandatory filtering on Exact Online is activated, an error will occur such as:

itgeneor330:
A required filter is missing.
The remote server returned an error: (400) Bad Request.

or on older releases:

itgenoda061
A GET request for this endpoint must have a $filter parameter with one of the following fields in the url:Accountant,AccountManager,AddressLine1,AddressLine2,AddressLine3,Blocked,BRIN,BSN,ChamberOfCommerce,City,Classification1,Classification2,Classification3,Classification4,Classification5,Classification6,Classification7,Classification8,Classification,Code,CodeAtSupplier,Country,Created,Email,EndDate,Fax,ID,InvoiceAccount,IsAccountant,IsReseller,IsSales,IsSupplier,MainContact,Name,Parent,Phone,Postcode,PriceList,Reseller,RSIN,SearchCode,StartDate,Status,Type,VATNumber.

The implementation of mandatory filtering leaves room for improvements. In this case, the InvoiceAttachmentType was not included as a sufficient filter. Even worse, without mandatory filtering it could be used as a filter and could be very selective since being contained in the base table:

Exact Online table Accounts
Exact Online table Accounts882×791 141 KB

In a future release of Invantive SQL a workaround will be implemented. This might take the form of disabling server-side filtering altogether for columns that could previously be used for optimal performance.

For now, you can use one of the following two alternatives to cope with mandatory filtering on Exact Online.

Combine Use of Exact Online sync API and traditional API

First alternative is to offload processing to the client software using the sync APIs and combining the selection with individual update statements as previously. Using individual update statements does not introduce extra processing time since Exact Online does not support the bulk update specification of OData. The processing requirements locally will grow somewhat because the sync APIs require an update, while in the original query the SQL Server of Exact Online filtered the data.

The resulting code becomes either in PSQL:

begin
  for r in
  ( select id
    from   AccountsIncremental@eol
    where  invoiceattachmenttype != 3
    limit  1000
  )
  loop
    update ExactOnlineREST..Accounts@eol
    set    invoiceattachmenttype = 3
    where  id = r.id
    ;
  end loop;
end;

Or in SQL:

update /*+ http_memory_cache(false) http_disk_cache(false) */
       ExactOnlineREST..Accounts@eol
set    InvoiceAttachmentType = 3
where  ID 
       in 
       ( select /*+ low_cost */ ID 
         from   AccountsIncremental@eol 
         where  InvoiceAttachmentType != 3 
         limit  1000 
       )

Note the use of the low_cost SQL-hint. Otherwise still an itgeneor330 will occur, since the AccountsIncremental is not yet considered a low-cost table which enables early evaluation in query execution.

Fool mandatory filtering

Mandatory filtering is poorly implemented to achieve it’s goal of reaching increased use of the sync APIs. Mandatory filtering can easily be tricked into accepting a filter even when it is no real filter. Mandatory filtering ignores the selectiveness of a filter value or filter operator, so filters can easily be constructed which will return close to 100% of the data can easily be derived.

Using this fact, an optimistic approach would be to use:

update /*+ http_memory_cache(false) http_disk_cache(false) */
       exactonlinerest..accounts@eol
set    invoiceattachmenttype = 3
where  invoiceattachmenttype != 3
and    Created > to_date('19700101', 'YYYYMMDD')

Since Created is a mandatory column with a lower-bound well above 1970, this filter should work. The resulting API call is:

…/Accounts?$select=*&$filter=Created gt datetime’1970-01-01T00:00:00’ and InvoiceAttachmentType ne 3

However, mandatory filtering no longer accepts this filter due to a bug as described above. By rewriting the query to not exclude the filter on InvoiceAttachmentType mandatory filtering from the server-side filter, the Exact Online API can be tricked into accepting the statement:

update /*+ http_memory_cache(false) http_disk_cache(false) */
       exactonlinerest..accounts@eol
set    invoiceattachmenttype = 3
where  invoiceattachmenttype + 1 != 3 + 1
and    Created > to_date('19700101', 'YYYYMMDD')

The last statement has a significant downside: it generates hundreds of API calls like:

…/Accounts?$filter=Created gt datetime’2020-08-02T00:00:00’&$select=*&$skiptoken=guid’…’

and performs the few necessary updates at the end. This alternative implementation can easily take more than hundred times longer to run and consumes hundreds of API calls compared to the single API call of the original statement without mandatory filtering and the variant using a for loop.

The recommended solution when confronted with mandatory filtering is to switch to the for loop implementation.

Learn more on Exact Online mandatory filtering

More relevant topics on mandatory filtering on Exact Online are: