Skip to content

Getting started with the SQL Passthrough API

This page assumes you have already read the Halo Cloud overview and SQL Passthrough API overview.

Prerequisites

To get started with the SQL Passthrough API, you will need:

  • Your Halo Connect subscription key, as supplied by Halo Connect
  • A test environment on which you have installed:
    • A PMS database, as supplied by the PMS
    • Halo Link, as supplied by Halo Connect and installed according to the Halo Link installation instructions for the correct environment for your use case

Using the SQL Passthrough

Staging vs Production

  • Staging is recommended for development and testing, both before and after your integration via Halo Connect goes live on real practices. PMS databases connected to the staging environment should not be in use by real practices.
  • Production should only be used for connecting with real practices using well-tested software.

Halo Link installs in Production by default. See the Halo Link installation documentation for how to install Halo Link in staging.

Base API URL

This is the base API for connecting with the SQL Passthrough API. All endpoints start with this URL.

For example: The full URL for the Get Site Status endpoint is https://api.haloconnect.io/integrator/sites/{siteID}.

https://api.stage.haloconnect.io/integrator

https://api.haloconnect.io/integrator

API reference

https://api.stage.haloconnect.io/integrator-docs/openapi

https://api.haloconnect.io/integrator-docs/openapi

The Halo Connect API has been documented using the OpenAPI Specification.

Query examples

Sending an immediate query

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": "@siteId",
                "direction": "output",
                "type": "BigInt",
                "value": "66",
                "size": 43
            }
        ]
    }
}

Sending an async query

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": "@siteId",
        "direction": "output",
        "type": "BigInt",
        "value": "66",
        "size": 43
        }
    ]
}

Prev: SQL Passthrough API overview

Version: 24.326.2298