Analyzing missing rows on Exact Online REST APIs

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.

Instructions

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: