Even with use of the Exact Online sync APIs, the Exact Online daily limit of 5.000 API calls of any kind makes it hard to retrieve initial data sets of more than 5 million rows. Similarly, time-outs of Power BI - see Itgeneor229 Exact API limiet na Itgenboe161 (Dutch) - in version before 22.0.92 could easily exhaust the daily API rate limit of an Exact Online company since the retrieval of the initial data set starts again from scratch over and over again.
Based upon an idea, an improvement has been included in version 22.0.92 to improve restart capabilities across hours or days when initially retrieving the data set using the Exact Online sync API. Between restarts, the data may have been changed by a user so the software can not just start off where it left but has remove older duplicate entries.
This topic describes a way to simulate API rate limit on Invantive Query Tool to test the restart capabilities ultimately leading to a new version.
Simulate Cloud Errors
The Invantive SQL drivers for cloud connections all include a large list of connection attributes for simulating connectivity and data problems. For more information refer to Simulate Cloud API Connection Problems.
Setup HTTP 429
After logging in to Exact Online, first the occurrence of HTTP 429 errors is simulated by applying three settings:
set simulate-http-429-errors true
set simulate-http-429-errors-percentage 20
set download-error-429-too-many-requests-max-tries 1
The last setting must not be forgotten for a speedy test. By default, a special handler tries to execute the HTTP request in a number of ways with increasing intervals. Reducing the maximum number of tries to 1 completely disable the repeating tries of the HTTP 429 error.
Run load
First make sure there are no local disk cache files present for the table that you wish to test with. The location of the folder with cached data for the incremental tables based upon the Exact Online sync APIs can be found using the following query:
select value
from SYSTEMPROPERTIES@DataDictionary
where name = 'environment-incremental-data-folder'
with typical result %USERPROFILE%\invantive\cache\incdata.
Similarly, the HTTP disk cache can be found and cleaned using:
select value
from SYSTEMPROPERTIES@DataDictionary
where name = 'environment-http-cache-folder'
Once checked and cleared, execute the following code:
declare
l_completed boolean;
l_try_cnt pls_integer;
begin
--
-- Select solely one company.
--
use 10523@eol;
--
-- Keep trying till succeeds.
--
l_completed := false;
l_try_cnt := 0;
while l_completed = false
loop
begin
l_try_cnt := l_try_cnt + 1;
dbms_output.put_line('Start try #' || to_char(l_try_cnt) || '.');
create or replace table x@inmemorystorage
as
select *
from ExactOnlineREST.Incremental.ContactsIncremental@eol
;
l_completed := true;
exception
when others
then
dbms_output.put_line('Error: ' || sqlerrm);
end;
end loop;
end;
The exception handler takes care that instead of displaying the following error another effort is made:
During execution, the progress will be printed to the DBMS Output tab in the Query Tool:
With 80.000 contacts, there will be ideally only 80 API calls needed. However, with one out of five calls failing, a number of retries of the retrieval statement is necessary.
After completion, the following query on SessionIOs@DataDictionary returns the actual API calls
select *
from sessionios@datadictionary
order
by id desc
with result:
