Skip to main content

Documentation Portal

Important Notes

Wichtig

The access token will expire after 12 hours. Best practice is to request an access token before every API call.

Only SQL Server 2008 and later are supported. Earlier versions of MS SQL may give the following error when executing the SQL query: “Cannot find data type datetimeoffset.”

If a query is used by an active sync job, the query can not be updated.

Examples

In the examples bellow, we will use the following tm_contextitems table as context items data source:

Context_SQL_Sync_2021i.png

  1. Setup historical sync with positional parameters:

    Query definition - here a positional parameter criteria is applied on a datetime field:

    {
"name": "histSyncQuery1",
"supportsParameters": true,
"supportsNamedParameters": false,
"componentLookupStrategy": "TAGS",
"timeZone": "UTC",
"fieldNames": " V_DLM_AVG, V_OPA_AVG",
"sqlString": "SELECT id, componentId, product_code as type, 'Context item description' as description, 'START' as start_event, startDateTime, 'END' as stop_event, endDatetime, V_DLM_AVG, V_OPA_AVG
FROM   tm_contextitems where ? > last_modified_date and last_modified_date <= ?;",
"typeMapping": [
{
"B100610": "PRODUCTION"
}
] 
}

    The historical sync job can be defined as follow:

    {
    "datasourceId": "1",
    "queryId": "1",
    "externalIdPrefix": "sync1",
    "chunkSize": 10,
    "startDate": "2020-06-24 00:00:00",
    "endDate": "2020-06-25 00:00:00"               
}

  2. Setup historical sync with named parameters:

    Query definition - here a named parameter criteria is applied on a datetime field:

    {
"name": "histSyncQuery2",
"supportsParameters": false,
"supportsNamedParameters": true,
"componentLookupStrategy": "TAGS",
"timeZone": "UTC",
"fieldNames": " V_DLM_AVG, V_OPA_AVG",
"sqlString": "SELECT id, componentId, product_code as type, 'Context item description' as description, 'START' as start_event, startDateTime, 'END' as stop_event, endDatetime, V_DLM_AVG, V_OPA_AVG
FROM   tm_contextitems where :startDate > last_modified_date and last_modified_date <= :endDate;",
"typeMapping": [
{
"B100610": "PRODUCTION"
}
]
}

    The historical sync job can be defined as follow:

      {
    "datasourceId": "1",
    "queryId": "1",
    "externalIdPrefix": "sync2",
    "chunkSize": 10,
    "startDate": "2020-06-24 00:00:00",
    "endDate": "2020-06-25 00:00:00"    
  }

  3. Setup historical sync without parameters:

    Query definition - in this case no parameters are used as query criteria:

    {
"name": "histSyncQuery3",
"componentLookupStrategy": "TAGS",
"timeZone": "UTC",
"fieldNames": " V_DLM_AVG, V_OPA_AVG",
"sqlString": "SELECT id, componentId, product_code as type, 'Context item description' as description, 'START' as start_event, startDateTime, 'END' as stop_event, endDatetime, V_DLM_AVG, V_OPA_AVG
FROM   tm_contextitems where  last_modified_date > '2020-06-24 00:00:00'",
"typeMapping": [
{
"B100610": "PRODUCTION"
}
]
}

    In such case no parameters are needed for historical sync job definition:

      {
    "datasourceId": "1",
    "queryId": "1",
    "externalIdPrefix": "sync3",
    "chunkSize": 10
  }

  4. Setup sync job

    Query definition - sync job always provides time interval, so in the query either positional or named parameters can be provided.

    {
"name": "syncJobQuery1",
"supportsParameters": false,
"supportsNamedParameters": true,
"componentLookupStrategy": "TAGS",
"timeZone": "UTC",
"fieldNames": " V_DLM_AVG, V_OPA_AVG",
"sqlString": "SELECT id, componentId, product_code as type, 'Context item desrtiption' as description, 'START' as start_event, startDateTime, 'END' as stop_event, endDatetime, V_DLM_AVG, V_OPA_AVG
FROM   tm_contextitems where :startDate > last_modified_date and last_modified_date <= :endDate;",
"typeMapping": [
{
"B100610": "PRODUCTION"
}
]
}

    But in some cases, the query used by the sync job may not require parameters, even though an interval is provided by sync job. In such case query can be defined without parameters and different criteria can be used in the sql:

    {
"name": "syncJobQuery2",
"componentLookupStrategy": "TAGS",
"timeZone": "UTC",
"fieldNames": " V_DLM_AVG, V_OPA_AVG",
"sqlString": "SELECT id, componentId, product_code as type, 'Context item description' as description, 'START' as start_event, startDateTime, 'END' as stop_event, endDatetime, V_DLM_AVG, V_OPA_AVG
FROM   tm_contextitems where  last_modified_date < GETDATE();",
"typeMapping": [
{
"B100610": "PRODUCTION"
}
]
}

    In both cases, sync job definition is similar:

    {
  "datasourceId": "1",
  "queryId": "1",
  "externalIdPrefix": "prefix",
  "syncJobInterval": "10",
  "chunkSize": 10
}
In diesem Abschnitt: