Query examples

Sending an immediate query with custom SQL

POST sites/{HaloGUID}/queries/immediate

Request example

{
    "command": {
        "text": "SELECT 1",
        "executionMode": "reader",
        "type": "text",
        "parameters": []
    }
}

Response example

{
    "id": "{queryID}",
    "integratorId": "{integratorID}",
    "siteId": "{haloGUID}",
    "mode": "immediate",
    "status": "successful",
    "maxTimeInQueue": 15000,
    "sqlQuerySize": 46,
    "commandSize": 98,
    "createdAt": "2022-08-24T14:15:22Z",
    "updatedAt": "2022-08-24T14:15:22Z",
    "statusTiming": {
        "initialising": {
        "startTime": "2022-08-24T14:15:22Z",
        "endTime": "2022-08-24T14:15:22Z"
        },
        "executing": {
        "startTime": "2022-08-24T14:15:22Z",
        "endTime": "2022-08-24T14:15:22Z"
        }
    },
    "metrics": {
        "sqlExecutionDuration": 1000
    },
    "result": {
        "rows": 30,
        "size": 25,
        "data": "{resultData}",
        "parameters": [
            {
                "name": "@haloGUID",
                "direction": "output",
                "type": "BigInt",
                "value": "66",
                "size": 43
            }
        ]
    }
}

Sending an immediate query with a stored procedure

POST sites/{HaloGUID}/queries/immediate

Request example

{
    "command": {
    "text": "SomeStoredProcedure",
    "executionMode": "nonQuery",
    "type": "storedProcedure",
    "parameters": [
        {
            "name": "@haloGuid",
            "value": "",
            "type": "Int",
            "direction": "output",
            "size": 1
            }
        ]
    }
}

Response example

{
    "id": "{queryID}",
    "integratorId": "{integratorID}",
    "siteId": "{haloGUID}",
    "mode": "immediate",
    "status": "successful",
    "maxTimeInQueue": 15000,
    "sqlQuerySize": 46,
    "commandSize": 98,
    "createdAt": "2022-08-24T14:15:22Z",
    "updatedAt": "2022-08-24T14:15:22Z",
    "statusTiming": {
        "initialising": {
        "startTime": "2022-08-24T14:15:22Z",
        "endTime": "2022-08-24T14:15:22Z"
        },
        "executing": {
        "startTime": "2022-08-24T14:15:22Z",
        "endTime": "2022-08-24T14:15:22Z"
        }
    },
    "metrics": {
        "sqlExecutionDuration": 1000
    },
    "result": {
        "rows": 30,
        "size": 25,
        "data": "{resultData}",
        "parameters": [
            {
                "name": "@haloGUID",
                "direction": "output",
                "type": "Int",
                "value": "0",
                "size": 1
            }
        ]
    }
}

Sending an async query with custom SQL

POST sites/{HaloGUID}/queries/async

Request example

{
    "command": {
        "text": "SELECT 1",
        "executionMode": "reader",
        "type": "text",
        "parameters": []
    }
}

Response example

{queryID} is used with the other async query endpoints to check the status of the async query and to get its results once it is complete.

{
    "id": "{queryID}",
    "integratorId": "{integratorID}",
    "siteId": "{haloGUID}",
    "mode": "async",
    "status": "initialising",
    "maxTimeInQueue": 900000,
    "sqlQuerySize": 46,
    "commandSize": 98,
    "createdAt": "2019-08-24T14:15:22Z",
    "updatedAt": "2019-08-24T14:15:22Z",
    "statusTiming": {
        "initialising": {
            "startTime": "2019-08-24T14:15:22Z"
        }
    }
}

Checking the status and result pagination of an async query

GET sites/{HaloGUID}/queries/{queryID}

Response example

... indicates nested data that has been left out for clarity. See the API reference for more information.
The results object currently only appears when status is successful, and contains pagination information for use with the Get Query Results endpoint (used in the next example).

{
    "id": "{queryID}",
    "integratorId": "{integratorID}",
    "siteId": "{haloGUID}",
    "mode": "async",
    "status": "successful",
    "maxTimeInQueue": 900000,
    "sqlQuerySize": 46,
    "commandSize": 98,
    "createdAt": "2019-08-24T14:15:22Z",
    "updatedAt": "2019-08-24T14:15:22Z",
    "statusTiming": { ... },
    "metrics": { ... }
    "result": {
        "rows": 30,
        "size": 25,
        "pages": [ ... ]
    }
}

Retrieving async query result pages

GET sites/{HaloGUID}/queries/{queryID}/results/{pageNumber}

Response example

{
    "data": "{resultData}",
    "parameters": [
        {
        "name": "@haloGUID",
        "direction": "output",
        "type": "BigInt",
        "value": "66",
        "size": 43
        }
    ]
}

Prev: SQL Passthrough overview