This page outlines how to use saved queries with APIs.
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 of the REST API can be found on the Execute Query screen (Image 1).
GET https://<Cinchy Web URL>/API/MyDomain/MyQuery
{% swagger-response status="400" description="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 below).
EXAMPLE:" %}
The following example shows how to use API Saved Queries (Image 2.)
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.
https://<Cinchy Web URL>/BasicAuthAPI/MyDomain/MyQuery
The following instructions detail how to allow anonymous access to your saved query API endpoint.
Navigate to the table your query will be referencing. In this example, it's the Accessibility Assessments table (Image 1).
Navigate to Data Controls > Entitlements.
On a new row, add in the Anonymous user and ensure that either "View All Columns" (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.
Design your query (Image 4). For more information on creating new saved queries, click here.
Once you have written your query, navigate to Design Query > Info, on the left navigation bar.
Change your API Result Format to JSON (Image 5).
Navigate to Design Controls from the left navigation bar.
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?" (Image 6).
Navigate to "Execute Query" from the left navigation bar.
Copy your REST API endpoint URL (Image 7).
To confirm that anonymous access has been successfully set up, paste the URL into an incognito/private browser (Image 8).
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.
This page gives an overview of APIs.
Cinchy has various available APIs, each performing important functions for your use cases.
For example, using the MyQuery API will allow you to turn any Saved Query on your platform into a REST API.
Many of the APIs listed on this page can use Bearer Tokens or Personal Access Tokens to authenticate. Please review the API Authentication page for further information.
The following is a list of common API endpoints. These follow the format of <baseurl>/endpoint
200 (OK)
To pass in parameters in your executeCQL, you will need to pass in sets of parameters in the following format. If you have one parameter then you would pass in 3 query parameters beginning with Parameters[0]. , and if you have a second parameter you would include an additional 3 query parameters beginning with Parameters[1]. .
200:** The request has successfully returned the record set.**
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)
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.**
200:** The request is successful**
400:** For invalid parameters, a 400 error will be returned with the following JSON response with a description of the error.**
The API will return an object in the below format:
Note that the parameter names "files" and "options" are both case sensitive and must use lowercase in your JSON. The contents of the parameters themselves aren't case sensitive.
Responses
200:** The request was successful.**
400:** The request couldn't be completed.**
This page details how to execute CQL directly without creating a Saved Query.
You can execute CQL directly without creating a Saved Query using the below endpoint:
| Name | Data Type | Description |
|---|---|---|
StartRow and RowCount work with API Saved Queries. When using these parameters with ExecuteCQL, the ResultFormat needs to be set to DELIMITED_FILE and an additional Delimiter parameter needs to be added. The delimiter could be a character of your choosing like "," or "|"
| Name | Data Type | Description |
|---|---|---|
200 (OK)
To pass in parameters in your executeCQL, you will need to pass in sets of parameters in the following format. If you have one parameter then you would pass in 3 query parameters beginning with Parameters[0]. , and if you have a second parameter you would include an additional 3 query parameters beginning with Parameters[1]. .
This page describes the Cinchy Webhook ingestion, including a video walkthrough and step-by-step guide
Compatibility: Webhook ingestion was introduced in Cinchy platform v4.21. Note that previous Cinchy versions won't include a Webhooks system table, and won't support this feature.
Context: A webhook uses a trigger event to initiate a data transfer that can then be ingested by another application. Many applications support webhooks to provide data updates in real time with minimal configuration required. Cinchy users can subscribe to and ingest webhooks via configuring a unique API endpoint. When the external application addresses this endpoint, a pre-identified saved query can be run, under an authorized user account, to ingest the data and insert or update it into Cinchy. The following video walks you through a specific configuration, with a general step-by-step guide below:
Identify the table in Cinchy that you want the information to be pushed to. (Image 1)
See the Creating tables page for instructions on creating a table within Cinchy.
Create your query in Cinchy. This query should take data from the webhook event, and push it into the Cinchy table that you identified in Step 1. (Image 2)
Your query will be running under a specific user account that you will assign in the next step. Ensure that whichever user you choose for this purpose has the correct permissions to execute the query, and to insert / update data in the target table.
See the Saved Queries page for instructions on creating a query within Cinchy.
You will need administrator access to your Cinchy platform to perform Step 3.
Navigate to the webhooks table in Cinchy, and populate the following columns (Image 3):
Key: This can be any string (we recommend that you treat this like a password and make it random and unguessable).
Run As: Insert the user who will be running this query here. This should be the same user to whom you gave permissions in Step 2a.
Saved Query: Select the saved query that you created in Step 2.
Forward Payload as Parameter: This column will depend on the manner in which you would like to ingest the webhook payload.
If you are configuring individual parameters in the webhook payload (for example: @name, @url, etc.), you may leave this column blank.
If you aren't configuring individual parameters, as an alternative you can ingest the entire payload under one parameter and specify it in this field. In the below image, we've defined it as JSON. This means that the full payload (which happens to be a JSON file in this case) will be parameterized as @JSON and then inserted into a table column named JSON.
In your source application, navigate to the webhook settings and configure the following:
URL: This will be your Base URL + /API/callback?key= + the key value that you assigned in _Step 3a._
Example: {baseURL}/API/callback?key=rGh5tfgHK8989J5
| Description |
|---|
| Description |
|---|
| Description |
|---|
| Description |
|---|
| Description |
|---|
| Description |
|---|
| Name | Data Type | Description |
|---|---|---|
| Name | Data Type | Description |
|---|---|---|
| Query String Parameter Name | Content |
|---|---|
| Description |
|---|
| Name | Data Type | Description |
|---|---|---|
| Name | Data Type | Description |
|---|---|---|
| Definition |
|---|
| Name | Data Type | Description |
|---|---|---|
| Name | Data Type | Description |
|---|---|---|
| Description |
|---|
| Description |
|---|
| Name | Data Type | Description |
|---|---|---|
| Parameter | Description |
|---|---|
| Description |
|---|
| Name | Data Type | Description |
|---|---|---|
| Name | Data Type | Description |
|---|---|---|
| Query String Parameter Name | Content |
|---|---|
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.
ResultFormat
string
XML JSONCSVTSVPSVPROTOBUF
Type
string
QUERY- Query (Approved Data Only)DRAFT_QUERY- Query (Include Draft Changes)SCALAR- ScalarNONQUERY- Non Query, such as an insert or deleteVERSION_HISTORY_QUERY- Query (Include Version History)
ConnectionId
string
TransactionId
string
When one or more requests share the same TransactionId, they're considered to be within the scope of a single transaction.
Query
string
The CQL query statement to execute
Parameters
Boolean
See below on format for the parameters.
SchemaOnly
integer
Defaults to false.
StartRow
integer
When implementing pagination, specify a starting offset. Combine with RowCount to set the size of the data window.
RowCount
integer
When implementing pagination, specify the number of rows to retrieve for the current page. Combine with StartRow to set the paging position.
CommandTimeout
string
Use this parameter to override the default timeout (30s) for long running queries. In seconds.
UserId
string
The ID of a user with authorization to run the saved query.
Authorization
string
Bearer <access_token>
See Authentication for details.
Parameters[n].ParameterName
Name of the parameter that's in your query, including the '@'.
Ex. @name
Parameters[n].XmlSerializedValue
XML Serialized version of the value of that parameter.
Ex. "test"
Parameters[n].ValueType
Datatype of the value.
Ex. System.String
This endpoint will bring you to a page with your logs and health check.
This endpoint will open the model loader.
This endpoint will bring you to a page with your platform health check.
This endpoint will bring you to a page with your Meta-Forms health check.
This endpoint will delete your platform cache data.
You can execute CQL directly without creating a Saved Query using the below endpoint:
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.
ResultFormat
string
XMLJSONCSVTSVPSVPROTOBUF
Type
string
QUERY - Query (Approved Data Only) DRAFT_QUERY - Query (Include Draft Changes) SCALAR - Scalar NONQUERY - Non Query, such as an insert or delete VERSION_HISTORY_QUERY - Query (Include Version History)
ConnectionId
string
​
TransactionId
string
When one or more requests share the same TransactionId, they're considered to be within the scope of a single transaction.
Query
string
The CQL query statement to execute
Parameters
Boolean
See below on format for the parameters.
SchemaOnly
integer
Defaults to false.
StartRow
integer
When implementing pagination, specify a starting offset. Combine with RowCount to set the size of the data window.
RowCount
integer
When implementing pagination, specify the number of rows to retrieve for the current page. Combine with StartRow to set the paging position.
CommandTimeout
string
Use this parameter to override the default timeout (30s) for long running queries. In seconds.
UserId
string
ID of a user with authorization to run the saved query.
Authorization
string
Bearer <access_token> The access token can be either a Bearer token or a Personal Access token. See Authentication for details.
Parameters[n].ParameterName
Name of the parameter that's in your query, including the '@'.
Ex. @name
Parameters[n].XmlSerializedValue
XML Serialized version of the value of that parameter.
Ex. "test"
Parameters[n].ValueType
Datatype of the value.
Ex. System.String
To access any Cinchy Saved Query API, pass the access token, received from either the Bearer Token Request or created as a PAT, in the Authorization header, prefixed by Bearer.
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.
Authorization
string
Bearer <access_token> The access token can be either a Bearer token or a Personal Access token. See Authentication for details.
The Post Request will return an access token which can be used to access Cinchy APIs.
Content-type
string
application/x-www-form-urlencoded
Token
string
You can pass in your base64 encoded SAML token instead of your Cinchy username and password
Client_id
string
Client Id value from Integrated Clients table
Client_secret
string
GUID value from Integrated Clients table
Username
string
Username of Cinchy user
Password
string
Password for Cinchy user in plain text
Grant_Type
string
Set as "password" for username/password authentication. Set as "saml2" for SAML token authentication.
Scope
string
Set as js_api
This will return your IDP URL endpoint.
This API will return a secret from the Cinchy Secrets Manager table.
Authorization
string
Optional. Bearer <access_token> The access token can be either a Bearer token or a Personal Access token. See Authentication for details.
<base-url>
Your Cinchy base URL.
<secret-name>
The name of the secret you want to call, as it appears in the Cinchy Secrets table.
<domain-name>
The domain where your secret resides.
This endpoint can be used to trigger a batch data sync job that has been configured in Cinchy.
Authorization
string
Bearer <access_token> Note that this endpoint doesn't support Personal Access Tokens. See Authentication for details.
Content-Type
String
multipart/form-data
files
binary
The URL to any file(s) needed to be uploaded to be used in place of a parameter in the data sync
options
string
A JSON string of the data sync options. See here for the structure of this JSON string.
This page outlines API Authentication methods and details.
The APIs in Cinchy use bearer token based authentication. This token is issued by the Cinchy SSO using the OAuth 2.0 Resource Owner Password Flow and can be retrieved for any Cinchy User Account or SSO Account. API calls made using a bearer token will run under the privileges of the authenticated user, and are driven by the configured data level access controls. You must include the token in each request in the Authorization header.
APIs that are dynamically generated through a Saved Query in Cinchy also allow for basic authentication. In this case, the URL to the saved query is different, it will be:
https://<Cinchy Web URL>/BasicAuthAPI/MyDomain/MyQuery
The Resource Owner Password Flow uses a combination of a client id, client secret, username, and password to authenticate both the calling application as well as the user. To get started with, you must register a client in Cinchy. You should use a different client id for each calling application to distinguish activity from each source.
If you are on Cinchy v5.5+, you can also use Personal Access Token based authentication. These can be used in APIs the same way bearer tokens can be. Review the documentation here for information on generating PATs.
Clients are managed in the Integrated Clients table within the Cinchy domain. To register a client, create a new record in this table. In a fresh install, only members of the Cinchy Administrators group will have access to perform this function.
Below is a description of the value that should be used for each column in the Integrated Clients table.
| Column | Description |
|---|---|
https://<Cinchy SSO URL>/identity/connect/token
The Post Request will return an access token which can be used to access Cinchy APIs.
200: The request is successful
The expiration time is denoted in seconds.
400: For invalid parameters, a 400 error will be returned with the following JSON response with a description of the error.
Example:
To get a bearer token from Cinchy, you can provide either:
Username and password (username, password), or
SAML token (token)
Failure to provide a valid set of one of the above won't return a token.
| Name | Data Type | Description |
|---|---|---|
| Name | Data Type | Description |
|---|---|---|
Client Id
A unique identifier for each client. The client will use this identifier when retrieving a bearer token.
Client Name
A friendly name for the client to help users maintaining this record.
Grant Type
The OAuth 2.0 flow that will be used during authentication. "Resource Owner Password" should be selected for API calls.
Permitted Login Redirect URLs
N/A for the Resource Owner Password flow - leave this blank
Permitted Logout Redirect URLs
N/A for the Resource Owner Password flow - leave this blank
Permitted Scopes
The list of permitted OAuth scopes, please check all available options.
Access Token Lifetime (seconds)
The time after with the token expires. If left blank, the default is 3600 seconds.
Show Cinchy Login Screen
N/A for the Resource Owner Password flow
Enabled
This is used to enable or disable a client
GUID
This is a calculated field that will auto generate the client secret
Content-Type
string
application/x-www-form-urlencoded
token
string
You can pass in your base64 encoded SAML token instead of your Cinchy username and password
client_id
string
Client Id value from Integrated Clients table
client_secret
string
GUID value from Integrated Clients table
username
string
Username of Cinchy user
password
string
Password for Cinchy user in plain text
grant_type
string
Set as "password" for username/password authentication. Set as "saml2" for SAML token authentication.
scope
string
Set as js_api
