What API calls did Invantive UniversalSQL actually perform?

Go to Dutch version

Invantive’s UniversalSQL translates SQL select-statements to data requests to the underlying platform. Insert, update and delete statements are also translated into data requests.

The data requests take place in a “native” format that depends on the capabilities of the underlying platform. A large number of optimizations - partly depending on the capabilities of the platform - are applied before the execution of the statement(s) begins.

In addition to the options described below, it is also possible on many environments to enable native call logging; see Collect Native Platform Call Data.

PostgreSQL native SQL with where clause

A query with a where clause with a field name and a constant for PostgreSQL is converted into a query according to PostgreSQL standards. The where clause is incorporated into this whenever possible so that the query can be processed as quickly as possible and transports as little data as possible.

Visma net API calls

A query across multiple administrations on Visma net is translated into multiple API calls to Visma net (one per administration). These are often executed in parallel.

Exact Online XML and REST

A query with a join between an Exact Online XML API and an Exact Online REST API is done for the XML API based on XMLDownload.aspx and for the Exact Online REST API based on REST with, for example, a $filter= in the URL.

Analysis using sessionios@datadictionary

You can of course use Invantive Trace to track execution, but that is very detailed. Through the data dictionary view sessionios you are usually already well served if you are only interested in the actual data requests. I will demonstrate this using an example.

You can define the API calls of Invantive UniversalSQL as follows:

  • Start an Invantive UniversalSQL product such as Invantive Query Tool.
  • Choose the connector for Simplicate (as an example) and provide the login credentials:
  • Execute a query on timesheets in Simplicate:
select *
from   hours /* Simplicate hours API */
  • Execute another query on timesheets in Simplicate, but now specify a filter:
select *
from   hours /* Simplicate hours API met filter */
where  start_date >= '2020-10'
  • As an aside, note that specifically the Simplicate API treats the date as a text when comparing hours. Should you use start_date in an equation with a date such as trunc(sysdate) then you probably fetch all rows first after which Invantive UniversalSQL filters the data locally. This is many times slower.
  • Finally, execute a query to find out which API calls Invantive UniversalSQL actually performed:
select *
from   sessionios@datadictionary
by     id
  • The end result shows that the first query did not pass a filter to the Simplicate API server, but the second did:
  • If you want to export these results to a file, use the Export button.
  • You can also export from a script using:
local export results as "c:\temp\ios.xlsx" format xlsx include technical headers
  • The easiest way to put together such an export statement is to do a right-click in the query editor and then via Scripting, Data transfer, Export results to have a template for the export statement added to the query editor.

An extended version of exporting the list of API calls is to use with a variable filename containing, for example, the time. This can be done using:

local define OUT_PATH "${system:userdocumentsdirectory}\${database:connection}"

select *
from   SESSIONIOS@DataDictionary

local create directory "${OUT_PATH}"

local export results as "${OUT_PATH}\sessionios.xlsx" format xlsx include technical headers

If you want the filename of the API call dump to be completely flexible, use a piece of SQL with Invantive Script:

select to_char(sysdateutc, 'YYYYMM') folder
,      to_char(sysdateutc, 'YYYYMMDDHH24MISS') fileprefix

local define OUT_PATH "${system:userdocumentsdirectory}\${database:connection}\${outcome:0,0}"

local define FILE_PREFIX "${outcome:0,1}"

select *
from   SESSIONIOS@DataDictionary

local create directory "${OUT_PATH}"

local export results as "${OUT_PATH}\${FILE_PREFIX}sessionios.xlsx" format xlsx include technical headers