Query Operation


Query Operation


The query operation allows you to execute a SQL query against one or more data sources configured in your CData Connect Cloud 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"
      ]
   ]
  }
 ]
}