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_datein an equation with a date such astrunc(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
order
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