API saved queries

Overview

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

To use your query programatically, you must call the /MyQuery API endpoint using the specific REST API URL associated with your query, along with some form of authentication (Basic auth, a bearer token or a personal access token).

Find the REST API URL

The REST API URL can be found in two places:

1. In the REST API URL column of the [Cinchy].[Saved Queries] table (Cinchy v5.10+ only).
2. From the REST API button on the Execute Query screen.

You can also manually construct the URL. Note that the URL differs slightly based on your platform version and auth type.

| | Basic auth | Bearer token | Personal access token | | ----------- | -------------------------------------------------------- | -------------------------------------------------------------- | -------------------------------------------------------------- | ----------------------------------------------- | | v4.0 - v5.9 | Https://[Cinchy Web URL]/BasicAuthAPI/MyDomain/MyQuery | Https://[Cinchy Web URL]/API/MyDomain/MyQuery | Https://[Cinchy Web URL]/API/MyDomain/MyQuery | Https://[Cinchy Web URL]/API/MyDomain/MyQuery | | v5.10+ | Https://[Cinchy Web URL]/BasicAuthAPI/MyDomain/MyQuery | Https://[Cinchy Web URL]/API/v[API Version]/MyDomain/MyQuery | Https://[Cinchy Web URL]/API/v[API Version]/MyDomain/MyQuery |

URL parameters

Column	Description	Example
Cinchy Web URL	The URL of your Cinchy instance.	Cinchy.net
MyDomain	The name of the domain where the query resides.	Product
MyQuery	The name of the query.	Tasks
API Version	Only when using a bearer or personal access token in Cinchy v5.10+. This value can be found in the [Cinchy].[Saved Queries] table.	v1.0

Example: Https://cinchy.net/BasicAuthAPI/Product/Tasks

API parameters

Use the following parameters to configure your request.

Query parameters

Name	Data 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's returned to be expanded rather than having the schema being returned separately.

Header parameters

Name	Data Type	Description
Authorization	string	Bearer <access_token>, if using a bearer token or personal access token. Basic <username:password>, if using basic auth. Note that this must be a base64 encoded value. See Authentication for details.

Responses

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 couldn't 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)

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 for invalid credentials. This includes missing, expired, or 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

Example

In this example, we want to use an API to get the results from the below simple query:

SELECT [Title]
FROM [Product].[Product Content Backlog]
WHERE [Deleted] IS NULL

Passing in the REST API URL and a personal access token returns a successful 200 response.

Anonymous access

You can also allow anonymous access to your saved query.

note

Anonymous access supports GET actions only.

1. Navigate to the table your query will be referencing, for example, [Product].[Accessibility Assessments].
2. Navigate to 'Data Controls' > 'Entitlements'.
3. In a new row, add in the Anonymous user.
   1. Ensure that either 'View All Columns' (to expose all the data) or 'View Selected Columns' (to select individual columns) is checked.

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

5. From the Query Creator, navigate to 'Design Query' > 'Info'.

6. Change your 'API Result Format' to JSON.

7. Navigate to 'Design Controls'.

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 UI under 'Who can execute this query?'.

9. Navigate to 'Execute Query'.

10. 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.

Troubleshooting

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