Execute CQL
The ExecuteCQL endpoint is used to execute a Cinchy Query Language function directly, without creating a Saved Query, via a POST request.
POST: https://<Cinchy Web URL>/API/ExecuteCQL
Query parameters
| Name | Data Type | Description |
|---|---|---|
| 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 | The format in which to return the results of your query. - XML - JSON - CSV - TSV - PSV - PROTOBUF |
| 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 (in seconds) | Use this parameter to override the default timeout (30s) for long running queries. |
| UserId | string | ID of a user with authorization to run the saved query, if using a Bearer Token for authorization. |
Header parameters
| Name | Data Type | Description |
|---|---|---|
| Authorization | string | Bearer <access_token>. The access token can be either a Bearer token or Personal Access token. See Authentication for details. |
Responses
200 OK: The query was executed successfully.
Parameter Usage
To pass in parameters in your executeCQL, use this format. For one parameter, include 3 query parameters starting with Parameters[0]. For a second parameter, add 3 more query parameters starting with Parameters[1]..
| Query String Parameter Name | Content |
|---|---|
Parameters[n].ParameterName | Name of the parameter, including '@'. Example: @name |
Parameters[n].XmlSerializedValue | XML serialized value. Example: "test" |
Parameters[n].ValueType | Data type of the value. Example: System.String |
info
To use StartRow and RowCount with ExecuteCQL, set ResultFormat to DELIMITED_FILE and include an additional Delimiter parameter, such as , or |.
Example
In the below example, we are executing the following query, which updates the "Colours" column in the specified table from 'Green' to 'Pink':
UPDATE [Product].[Documentation Colours]
SET [Colours] = 'Pink'
WHERE [Colours] = 'Green'
The following parameters are used in the API call:
Query Params
| Parameter | Value |
|---|---|
| ResultsFormat | JSON |
| Type | NONQUERY |
| Query | UPDATE [Product].[Documentation Colours] SET [Colours] = 'Pink' WHERE [Colours] = 'Green' |
Header Params
| Parameter | Value |
|---|---|
| Authorization | Bearer B5CF3E6EE83450THCILOPD209A371F-1 |
