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 |