Create a Data Service Query: Example

The following steps walk you through the process of creating a data service query.

Step 1: Determine which table you need to receive data from.

To see the supported tables, send a request to the tables endpoint.

https://<url>/sync/rest-service/dataservice/metadata/tables?configCode=<configuration name>

Step 2: Determine which columns you need from that table.

To see the available columns, send a request to the columns endpoint.

https://<url>/sync/rest-service/dataservice/metadata/columns/<table name>?configCode=<configuration name>

Step 3: Create your query.

Now that you have the table name and column names, you can create your query request. As well as returning the list of records matching your query and the specified sinceDate with the appropriate pagination, the query response contains the following information:

• The SQL query used to generated the response.
• The names of the tables queried.
• The total record count, i.e. the number of records in the given tables which match the conditions of the query.
• The record count, i.e. the number of records in the query response. This number might be different from the total record count if, for example, the total record count is larger than the page size specified in the request.

See the following examples.

Example 1

For this request, we are assigning it a name of Project Data and a page size of 3. We are requesting the columns PROJ_SHORT_NAME, PLAN_START_DATE, and PLAN_END_DATE from the PROJECT table.

Request

{
   "name":"Project Data",
   "pageSize":"3",
   "tables":[ 
      { 
         "tableName":"PROJECT",
         "columns":[ 
            "PROJ_SHORT_NAME",
            "PLAN_START_DATE",
            "PLAN_END_DATE"
         ]
      }
   ]
}

Response

In the response to the request, 3 objects are received, respecting the defined pageSize, from the PROJECT table each containing their respective values for the requested columns. In this example, the pagination data indicates that there is no more data in the response that has not been returned.

{
    "data": {
        "PROJECT": [
            {
                "PROJ_SHORT_NAME": "ProdProg1",
                "PLAN_START_DATE": "2009-12-30 00:00:00 +0530",
                "PLAN_END_DATE": null
            },
            {
                "PROJ_SHORT_NAME": "ProdProg2",
                "PLAN_START_DATE": "2009-12-30 00:00:00 +0530",
                "PLAN_END_DATE": null
            },
            {
                "PROJ_SHORT_NAME": "Templates",
                "PLAN_START_DATE": "2010-08-10 00:00:00 +0530",
                "PLAN_END_DATE": null
            }
        ],
        "pagination": [
            {
                "nextTableName": "-1",
                "nextKey": "0"
            }
        ],
        "safetyDate": [
            {
                "queryName": "Project Data",
                "sinceDate": "2019-02-20 17:02:31 +0530"
            }
        ],"sqlQueriesAndTotalRecordCount": [
      {
        "sqlQueryText": "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT P.PROJ_SHORT_NAME,P.PLAN_END_DATE,P.PLAN_START_DATE FROM PROJECT P ORDER BY P.ROWID) A) WHERE RN < 4 AND RN    >= 1",
        "tableName": "PROJECT",
        "totalRecordCount": "111",
        "recordCount": "3"
      }
    ]
    }
}

Example 2

Request

For this request, we are assigning it a name of Contacts Data and a page size of 5. We are requesting the columns TASK_NAME, PROJ_ID, STATUS_CODE, and TASK_TYPE from the TASK table.

{
 "name": "Test",
 "pageSize": "5",
 "tables": [
   {
     "tableName": "TASK",
     "columns": [
       "TASK_NAME",
       "PROJ_ID",
       "STATUS_CODE",
       "TASK_TYPE"
     ]
   }
 ]
}
 

Response

In the response to the request, 5 objects are received, respecting the defined pageSize, from the TASK table each containing their respective values for the requested columns. In this example, the pagination data indicates that there is more data in the response that has not been returned. To receive the next chunk of data, the pagination data can be included in the next request.

{
    "data": {
        "TASK": [
            {
                "TASK_NAME": "1A Battery Charger: Annual PM",
                "PROJ_ID": "4516",
                "STATUS_CODE": "TK_Complete",
                "TASK_TYPE": "TT_Task"
            },
            {
                "TASK_NAME": "1A Battery Charger: Height Clearance",
                "PROJ_ID": "4516",
                "STATUS_CODE": "TK_Complete",
                "TASK_TYPE": "TT_Task"
            },
            {
                "TASK_NAME": "1A Battery Charger: Release Clearance",
                "PROJ_ID": "4516",
                "STATUS_CODE": "TK_Complete",
                "TASK_TYPE": "TT_Task"
            },
            {
                "TASK_NAME": "1A Battery Charger: Replace Obsolete Components",
                "PROJ_ID": "4516",
                "STATUS_CODE": "TK_Complete",
                "TASK_TYPE": "TT_Task"
            },
            {
                "TASK_NAME": "1A Charging Pump: Height Clearance",
                "PROJ_ID": "4516",
                "STATUS_CODE": "TK_NotStart",
                "TASK_TYPE": "TT_Task"
            }
        ],
        "pagination": [
            {
                "nextTableName": "TASK",
                "nextKey": "6"
            }
        ],
        "safetyDate": [
            {
                "queryName": "Test",
                "sinceDate": null
            }
        ],
       "sqlQueriesAndTotalRecordCount": [
      {
        "sqlQueryText": "SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT P.PROJ_ID,P.TASK_NAME,P.TASK_TYPE,P.STATUS_CODE FROM TASK P ORDER BY P.ROWID) A) WHERE RN < 6 AND RN >= 1",
        "tableName": "TASK",
        "totalRecordCount": "7019",
        "recordCount": "5"
      }
    ]
    }
}