Query Operation

The query operation allows you to execute a SQL query against one or more data sources configured in your Connect AI account. SELECT, INSERT, UPDATE, DELETE, and EXEC statements are all supported (though the /exec operation should be preferred for the latter).

Request Format

POST /query

Request Body

All requests must include a JSON body like the following:

{
  "query": "<string>",
  "defaultSchema": "<string>",
  "schemaOnly": <bool>,
  "parameters": {
    "@p1": { "dataType": <int>, "value": <any> },
    "@p2": { "dataType": <int>, "value": <any> },
    ...
  }
}

Fields

query	Required	The SQL statement(s) to execute. Separate multiple statements with semi-colons `;`.
defaultSchema	Optional	If any tables mentioned in query are not prefixed with a schema name, this property must be used to specify a default schema.
schemaOnly	Optional	If `true`, the result only includes column metadata. Default is `false`.
parameters	Optional	A JSON object containing a list of query parameters. All parameter names must begin with `@`.
dataType	Required	The parameter’s data type.
value	Required	The parameter’s value.

Response Format

The query’s result set(s) are returned in our standard JSON result object.

Example

POST https://cloud.cdata.com/api/query

Request Body

{
  "query": "SELECT Id, AccountName, AccountNumber FROM Salesforce1.Salesforce.Account LIMIT 5",
  "timeout": 60,
  "parameters": {
  }
}

Response

{
  "results": [
  {
       "schema": [
  ​    {
  ​     "TABLE_CATALOG": "CData",
  ​     "TABLE_SCHEMA": "Salesforce1",
  ​     "TABLE_NAME": "Account",
  ​     "COLUMN_NAME": "Id",
  ​     "DATA_TYPE": 5,
  ​     "TYPE_NAME": "varchar",
  ​     "COLUMN_SIZE": 18,
  ​     "PRECISION": 18,
  ​     "SCALE": 0,
  ​     "IS_KEY": false,
  ​     "IS_NULLABLE": false,
  ​     "REMARKS": null,
  ​     "ORDINAL_POSITION": 0,
  ​     "IS_AUTOINCREMENT": false,
  ​     "IS_GENERATEDCOLUMN": false
  ​    },
  ​    {
  ​     "TABLE_CATALOG": "CData",
  ​     "TABLE_SCHEMA": "Salesforce1",
  ​     "TABLE_NAME": "Account",
  ​     "COLUMN_NAME": "Name",
  ​     "DATA_TYPE": 5,
  ​     "TYPE_NAME": "varchar",
  ​     "COLUMN_SIZE": 255,
  ​     "PRECISION": 255,
  ​     "SCALE": 0,
  ​     "IS_KEY": false,
  ​     "IS_NULLABLE": false,
  ​     "REMARKS": null,
  ​     "ORDINAL_POSITION": 1,
  ​     "IS_AUTOINCREMENT": false,
  ​     "IS_GENERATEDCOLUMN": false
  ​    },
  ​    {
  ​     "TABLE_CATALOG": "CData",
  ​     "TABLE_SCHEMA": "Salesforce1",
  ​     "TABLE_NAME": "Account",
  ​     "COLUMN_NAME": "AccountNumber",
  ​     "DATA_TYPE": 5,
  ​     "TYPE_NAME": "varchar",
  ​     "COLUMN_SIZE": 40,
  ​     "PRECISION": 40,
  ​     "SCALE": 0,
  ​     "IS_KEY": false,
  ​     "IS_NULLABLE": true,
  ​     "REMARKS": null,
  ​     "ORDINAL_POSITION": 2,
  ​     "IS_AUTOINCREMENT": false,
  ​     "IS_GENERATEDCOLUMN": false
  ​    }
     ],
     "rows": [
  ​    [
  ​     "0011W00002SoIgXQAV",
  ​     "Acme",
  ​     "000001"
  ​    ],
  ​    [
  ​     "0011W00002ShNlMQAV",
  ​     "Great Cakes",
  ​     "000002"
  ​    ],
  ​    [
  ​     "0011W00002TEDa8QAH",
  ​     "Billboard Tech",
  ​     "000003"
  ​    ],
  ​    [
  ​     "0011W00002SiUBQQA3",
  ​     "Firefly",
  ​     "000004"
  ​    ],
  ​    [
  ​     "0011W00002SZpGEQA1",
  ​     "Auto One",
  ​     "000005"
  ​    ]
   ]
  }
 ]
}