A common problem when configuring Invantive Data Replicator in an enterprise environment is that REST APIs have no contents. This note helps you analyze this problem to find the cause.
When running a query for replication, such as
select * from exactonlinerest..accountclassifications
the results might be an empty result set.
However, there can be multiple causes:
- wrong company selected;
- results from a previous replication are used when the table was still empty;
- user is not authorized for data.
Wrong Company Selected
Invantive SQL can run queries on one or thousands of Exact Online companies. By default, the last used company in Exact Online of the user is selected. The outcome of a query can change when people share accounts (not recommended) and independently change the current Exact Online company.
You should forcefully select the company to rule out the cause of selection of the wrong Exact Online company:
use <DIVISION NUMBER>
After that, run your query again.
Results from a Previous Replication
When available, Invantive Data Replicator will use sufficiently fresh replicated data. However, changes after establishing the replica are not included. Especially during setup of an Exact Online environment, tables can be empty at start.
To load fresh results from Exact Online, you can specify a number of hints:
select /*+ ods(false) use_http_disk_cache(false) use_http_memory_cache(false) */ *from exactonlinerest..accountclassifications
Run the query again. The current results are displayed.
You can also check the outcome of the following query to check whether an actual I/O has been executed:
select *from sessionios@datadictionary
User is not Authorized for Data
The XML APIs of Exact Online report an error when you try to access data for which your user is not authorized or licensed. However, the REST API behave differently: no data is returned when your user has no access.
All APIs of Exact Online should follow authorization as seen in the web user interface. Sometimes you can rule out this cause by running the XML API equivalent: the authorization is incorrect when the query on XML fails. For example, you could compare the number of rows returned from:
select * from exactonlinexml..projects
with the rows returned by:
select * from exactonlinerest..projects
You can also query the Exact Online REST API whether a user is authorized on a API. This is done through the UserHasRights table function, but it is quite buggy since it returns HTTP 503 errors on invalid parameters.
A proven solution is to use the following query:
select sio.partition , sio.path , sio.action , uhr.UserHasRights from ( select distinct regexp_replace(regexp_replace(sio.url, '.*/v1/([0-9]+/|)', ''), '\?.*', '') path , case when sio.action = 'READ' then 'GET' else 'POST' end action , sio.partition from SessionIOS@DataDictionary sio where sio.url is not null ) sio join UserHasRights(sio.partition, sio.path, sio.action) uhr
This query checks all your recent API activity, assumes it all concerns the REST API of Exact Online and then presents a list of the authorizations: