API Saved Queries

This page outlines how to use saved queries with APIs.

Table of Contents

Table of Contents
#1.-overview
#2.-example
#3.-anonymous-access

1. Overview

Cinchy queries are automatically available as REST APIs to allow for external application integration.

See Saved Queries on how to create a saved query. The URL for the REST API can be found on the Execute Query screen (Image 1).

Image 1: Getting the REST API for a Saved Query

How to Use the Access Token to Call Cinchy APIs

GET https://<Cinchy Web URL>/API/MyDomain/MyQuery

To access any Cinchy Saved Query API, pass the access token received from the Bearer Token Request in the Authorization header, prefixed by "Bearer".

Query Parameters

Name	Type	Description
WrapSingleRecordInArray	boolean	Default is true. Add this parameter and set to false if you want single record results returned as an object instead of within an array.
@param	string	If you have parameters in your query, you pass them indirectly as query parameters.
CompressJSON	boolean	Default is true. Add this parameter and set to false if you want the JSON that is returned to be expanded rather than having the schema being returned separately.

Headers

Name	Type	Description
Authorization	string	Bearer <token goes here>

200 The request has successfully returned the record set.

< HTTP/2 200 
< cache-control: private, s-maxage=0
< content-type: application/json; charset=utf-8
< server: Microsoft-IIS/10.0
< x-aspnetmvc-version: 5.2
< access-control-allow-origin: *
< x-aspnet-version: 4.0.30319
< x-powered-by: ASP.NET
< date: Wed, 1 Aug 2020 17:40:13 GMT
< content-length: 2985

400 The request could not be understood, the client is sending a request with incomplete data, poorly constructed data or invalid data. Optional Validation Logic: To validate query business / control conditional logic failure can be added at the beginning of the API which can intentionally generate a 400 error code (using RAISERROR) and stopping (using RETURN) the API. If there is no RETURN the errors will accumulate and will be provided at the end of running the API. An attribute (X-Cinchy Error) will be returned in the HTTP header with the custom RAISERROR message indicated (see example below). EXAMPLE:

--given the following CQL used within a saved query:

IF (ISNULL(@Int,0) = 0)
BEGIN
  RAISERROR('Invalid parameter: @Int cannot be NULL or zero',18,0)
  RETURN
END
IF (ISNULL(@String,'') = '' OR ISNULL(@String,'') = 'X')
BEGIN
  RAISERROR('Invalid parameter: @String cannot be NULL or empty or X',18,0)
  RETURN
END

--note "X-Cinchy-Error" in the sample response:

< HTTP/1.1 400 Bad Request
< Cache-Control: private
< Content-Type: text/html; charset=utf-8
< Server: Microsoft-IIS/10.0
< X-AspNetMvc-Version: 5.2
< Access-Control-Allow-Origin: *
< X-Cinchy-Error: Invalid parameter: @Int cannot be NULL or zero
< X-AspNet-Version: 4.0.30319
< X-Powered-By: ASP.NET
< Date: Wed, 1 Aug 2020 17:43:04 GMT
< Content-Length: 4517

401 The saved query API endpoint will return a 401 error code when you provide invalid credentials, this includes not providing credentials, expired credentials and incorrect credentials.

< HTTP/1.1 401 Unauthorized
< Cache-Control: private
< Content-Type: text/html; charset=utf-8
< Server: Microsoft-IIS/10.0
< X-AspNetMvc-Version: 5.2
< X-AspNet-Version: 4.0.30319

2. Example

The following example shows how to use API Saved Queries (Image 2.)

Image 2: Using API Saved Queries

In the above image, "%40" is the URL encoded version of "@". If you are passing them in as parameters you will need to include the %40 in front of your parameter name.

Use Basic Authentication to Call Cinchy APIs

https://<Cinchy Web URL>/BasicAuthAPI/MyDomain/MyQuery

3. Anonymous Access

The following instructions detail how to allow anonymous access to your saved query API endpoint.

1. Navigate to the table your query will be referencing. In this example, it is the Accessibility Assessments table (Image 1).

2. Navigate to Data Controls > Entitlements.

3. On a new row, add in the Anonymous user and ensure that either "View All Comuns" (to expose all the data) or "View Selected Columns" (to select individual columns) is checked off (Image 3).

Clicking on inline images in Gitbook will open a larger version.

Image 3: The Entitlements Table

4. Design your query (Image 4). For more information on creating new saved queries, click here.

Image 4: Design your Query

5. Once you have written your query, navigate to Design Query > Info, on the left navigation bar.

6. Change your API Result Format to JSON (Image 5).

Image 5: Change your API Result Format to JSON

7. Navigate to Design Controls from the left navigation bar.

8. To ensure that anonymous users have the correct permission needed to execute the query that generates the API response, add the "Anonymous" user to the users permission group uiunder "Who can execute this query?" (Image 6).

Image 6: Ensuring the anonymous user has access

9. Navigate to "Execute Query" from the left navigation bar.

10. Copy your REST API endpoint URL (Image 7).

Image 7: Copy your REST API endpoint URL

11. To confirm that anonymous access has been successfully set up, paste the URL into an incognito/private browser (Image 8).

Image 8: Testing your API

3.1 Troubleshooting

• 401 errors likely mean you have not added the Anonymous user to the list of users (not "Groups") that can execute the query.

• 400 errors likely mean that you have not added the Anonymous user to the list of users that (not "Groups") that can view column data from the tables that your query uses.